F7 MySQL Server

F7 MySQL Server
Until recently, I have never found the need to use to use the MySQL database server — until I wanted to install MediaWiki for one of my classes and the phpBB Forum discussion board to help answer general class, research, and Linux questions. Because of this, this section of the Installation Notes is going to be drawn directly from Stanton Finley's pages, simply because I am still becoming familiar with MySQL myself. I have written him about the issue, and he noted that his documentation falls under the GNU Free Documentation License (as does mine), which means that I can provide a direct adaptation of what he has written there. Since he is no longer maintaining his Fedora notes, I am sure that these notes will morph and change over the next couple of years.

If you have not already done so, check out my Services page and make sure that you have enabled mysqld for your server. If you are not sure if it is currently running, restart the daemon:

~> sudo service mysqld restart

You should see MySQL successfully restart:

Stopping MySQL: [ OK ]
Starting MySQL: [ OK ]

Next edit the /etc/my.cnf file using sudo and scroll down past the old_passwords=1 line and add the following:

skip-innodb

Your /etc/my.cnf file should now look like:

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1
skip-innodb

[mysql.server]
user=mysql
basedir=/var/lib

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

Save and exit. Next, login to MySQL as root:

~> mysql -u root

Hit Enter, and you will see the mysql> query prompt. Next, type:

mysql> select user, host, password, select_priv, update_priv, delete_priv, insert_priv from mysql.user;

After pressing Enter, you will see a table with the following information:

+------+------------------------+----------+-------------+-------------+-------------+-------------+

| user | host                   | password | select_priv | update_priv | delete_priv | insert_priv |

+------+------------------------+----------+-------------+-------------+-------------+-------------+

| root | localhost              |          | Y           | Y           | Y           | Y           |

| root | [host].[domain].[name] |          | Y           | Y           | Y           | Y           |

|      | [host].[domain].[name] |          | N           | N           | N           | N           |
|      | localhost              |          | N           | N           | N           | N           |

+------+------------------------+----------+-------------+-------------+-------------+-------------+

4 rows in set (0.00 sec)

mysql>

The first order of business is to set a root password. Type:

mysql> set password for 'root'@'localhost' = password ('[newpassword]');

where [newpassword] is the password that you want to use for the root MySQL super user. Hit Enter. Likewise, type:

mysql> set password for 'root'@'[host.domain.name]' = password ('[newpassword]');

where [host.domain.name] is the name of your host as reported in the "host" column in the table above and [newpassword] is the password that you want to use for the root MySQL super user. The password that you use should be the same one as before. Again, Hit Enter.

Let's take a look at the table again:

mysql> select user, host, password, select_priv, update_priv, delete_priv, insert_priv from mysql.user;

After pressing Enter, you will now see your password hash-encrypted in the "password" column. Notice that the hash-encryption is the same because we used the same password for localhost as well as [host.domain.name]:

+------+------------------------+------------------+-------------+-------------+-------------+-------------+

| user | host                   | password         | select_priv | update_priv | delete_priv | insert_priv |

+------+------------------------+------------------+-------------+-------------+-------------+-------------+

| root | localhost              | 28b2dd9b1e88beba | Y           | Y           | Y           | Y           |

| root | [host].[domain].[name] | 28b2dd9b1e88beba | Y           | Y           | Y           | Y           |

|      | [host].[domain].[name] |                  | N           | N           | N           | N           |
|      | localhost              |                  | N           | N           | N           | N           |

+------+------------------------+------------------+-------------+-------------+-------------+-------------+

4 rows in set (0.00 sec)


mysql>

Next, exit:

mysql> exit

and restart the MySQL daemon so that the password changes come into effect:

~> sudo service mysqld restart

You should see MySQL successfully restart:

Stopping MySQL: [ OK ]
Starting MySQL: [ OK ]

From now on, if you want to login to MySQL from the command prompt, we have to use the -p (password) option:

~> mysql -u root -p

When asked for the MySQL super user password, type it in and hit Enter. You should successfully log back into MySQL. Finally, you can exit once again:

mysql> exit

MySQL has successfully been setup.

Backup your MySQL Databases
I find it wise to backup the MySQL database on a daily basis. To do this, we can use a cron job and the mysqldump command. First, create a ~/linux/backups/mysql directory:

~> mkdir -p ~/linux/backups/mysql

Since we have already setup daily backups on the Backups & rsync page, we can just edit the /etc/cron.daily/backup-daily script. Open the file and add the following lines above the rsync line that backups up the /home/[user] directory:

# Backup the MySQL database:
mysqldump --user=root --password=[password] --opt --all-databases | gzip -c > /home/[user]/linux/backups/mysql/db_backup.sql.gz
chown [user].[user] /home/[user]/linux/backups/mysql/db_backup.sql.gz

Make sure that "/home/[user]/linux/backups/mysql/db_backup.sql.gz/" is on the same line as the mysqldump command. Type in your password for [password] and your username for [user]. The "--opt" option is the same as specifying "--add-drop-table --add-locks --create-options --disable-keys --extended-insert --lock-tables --quick --set-charset" (which allow for safe backups), and the "--all-databases" backs up all of the databases at once. The output is redirected to gzip, which saves the file as "db_backup.sql.gz" in /home/[user]/linux/backups/mysql/. Save and exit.

At this point, it is wise to change the permissions of /etc/cron.daily/backup-daily so that it is no longer world-readable, since the MySQL super user password is now located in the file. To do this, use chmod:

~> sudo chmod o-r /etc/cron.daily/backup-daily

Restore your MySQL Databases
If your databases are compromised, corrupted, or if there is a system crash, you can restore the contents of your database very simply. First, gunzip the backed up database file:

~> gunzip ~/linux/backups/mysql/db_backup.sql.gz

Then use mysql to restore the database:

~> mysql --user=root --password=[password] < db_backup.sql

Next, restart the daemon:

~> sudo service mysqld restart

You should see MySQL successfully restart:

Stopping MySQL: [ OK ]
Starting MySQL: [ OK ]

All of your databases should now be restored. I find this to be especially useful when installing Fedora onto a new system.

F7 Apache/MySQL/PHP Services & Applications — MySQL Server