CSUF LogoCSUF Site Navigation
optics.csufresno.edu

F14 Apache/MySQL/PHP Services & Applications MySQL Server

Department of Electrical and Computer Engineering
Associate Professor Gregory R. Kriehn
Forums
Wiki
F14 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 (now defunct) Fedora pages, since 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, run the mysql_secure_installation script:
~> sudo mysql_secure_installation
When prompted for the root password, hit Enter. Then select 'Y' to change the root password and enter a new root password. Next hit 'Y' to remove anonymous users, 'Y' to disallow remote root login, 'Y' to remove the test database, and 'Y' to reload the privilege tables. This will provide some initial security to the database. Next login:
~> mysql -u root -p
When prompted for the root password, type the root password you entered for the MySQL database. 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 your MySQL root password hash-encrypted in the "password" column:
+------+------------------------+------------------+-------------+-------------+-------------+-------------+
| user | host                   | password         | select_priv | update_priv | delete_priv | insert_priv |
+------+------------------------+------------------+-------------+-------------+-------------+-------------+
| root | localhost              | 28b2dd9b1e88beba | Y           | Y           | Y           | Y           |
+------+------------------------+------------------+-------------+-------------+-------------+-------------+
1 rows in set (0.00 sec)

mysql>
Next, exit:
mysql> exit
and restart the MySQL daemon so that all of the 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.