Like Web servers, most MySQL servers are expected to remain online 24 hours a day, 7 days a week. This makes backups of MySQL database files problematic. Because the MySQL server uses memory caches and buffers to improve the efficiency of updates to the database files stored on disk, these files may be in an inconsistent state at any given time. Since standard backup procedures involve merely copying system and data files, backups of MySQL data files cannot be relied upon, as they can't guarantee that the files that are copied are in a fit state to be used as replacements in the event of a crash.
Furthermore, as many databases receive new information at all hours of the day, standard backups can provide only “snapshots” of database data. Any information stored in the database that's changed after the last backup will be lost in the event that the MySQL data files are destroyed or become unusable. In many situations, such as when a MySQL server is used to track customer orders on an ecommerce site, this is an unacceptable loss.
Facilities exist in MySQL to keep up-to-date backups that are not adversely affected by server activity at the time at which the backups are generated. Unfortunately, they require you to set up a backup scheme specifically for your MySQL data, completely apart from whatever backup measures you have established for the rest of your data. As with any good backup system, however, you'll appreciate it when the time comes to use it.
In this chapter, the instructions I'll provide will be designed for use on a computer running Linux, or some other UNIX-based operating system. If you're running your MySQL server under Windows, the methods and advice provided here will all apply equally well, but you'll have to come up with some of the specific commands yourself. If you have any trouble, don't hesitate to post your questions in the SitePoint Forums.
In addition to mysqld, the MySQL server, and mysql, the MySQL client, a MySQL installation comes with many useful utility programs. We have seen mysqladmin, which is responsible for the control and retrieval of information about an operational MySQL server, for example.
mysqldump is another such program. When run, it connects to a MySQL server (in much the same way as the mysql program or the PHP language does) and downloads the complete contents of the database you specify. It then outputs these as a series of SQL CREATE TABLE and INSERT commands that, if run in an empty MySQL database, would create a MySQL database with exactly the same contents as the original.
If you redirect the output of mysqldump to a file, you can store a “snapshot” of the database as a backup. The following command (typed all on one line) connects to the MySQL server running on myhost as user root with password mypass, and saves a backup of the database called dbname into the file dbname_backup.sql:
shell%mysqldump -h myhost -u root -pmypass dbname > dbname_backup.sql
To restore this database after a server crash, you would use these commands:
shell%mysqladmin -h myhost -u root -pmypass create dbname shell%mysql -h myhost -u root -pmypass dbname < dbname_backup.sql
The first command uses the mysqladmin program to create the database; alternatively, you can do this at the MySQL command line. The second connects to the MySQL server using the usual mysql program, and feeds in our backup file as the commands to be executed.
In this way, we can use mysqldump to create backups of our databases. mysqldump connects through the MySQL server to perform backups, rather than by directly accessing the database files in the MySQL data directory. So the backup it produces is guaranteed to be a valid copy of the database, and not a snapshot of the database files, which may be in a state of flux as long as the MySQL server is online.
But how do we bridge the gap between these snapshots to maintain a backup of a database that is always up to date? The solution is simple: instruct the server to keep an update log.
As I mentioned above, many situations in which MySQL databases are used would make the loss of data—any data—unacceptable. In cases like these, we need some way to bridge the gap between the backups we made using mysqldump as described above. The solution is to instruct the MySQL server to keep an update log. An update log is a record of all SQL queries that were received by the database, and which modified the contents of the database in some way. This includes INSERT, UPDATE, and CREATE TABLE statements (among others), but doesn't include SELECT statements.
The basic idea is that you can restore the contents of the database at the very moment at which a disaster occurred, with the application of a backup (made using mysqldump), followed by the application of the contents of the update logs that were generated after that backup was made.
You can also edit update logs to undo mistakes that may have been made. For example, if a co-worker comes to you after having accidentally issued a DROP TABLE command, you can edit your update log to remove that command before you restore your database using your last backup and the log application. In this way, you can even keep changes to other tables that were made after the accident. And, as a precaution, you should probably also revoke your co-worker's DROP privileges (see the next section to find out how).
To tell the MySQL server to keep update logs, simply add an option to the server command line:
shell%safe_mysqld --log-update=update
The above command starts the MySQL server and tells it to create files named update.001, update.002, and so on, in the server's data directory (/usr/local/mysql/var if you set up the server according to the instructions in "Installation"). A new file will then be created each time the server flushes its log files; in practice, this occurs whenever the server is restarted. If you want to store your update logs somewhere else (usually a good idea—if the disk that contains your data directory dies, you don't want it to take your backups with it!), you can specify the full path to the update files.
However, if you run your MySQL server full time, you probably have your system set up to launch the MySQL server at start-up. The addition of command-line options to the server can be difficult in this case. A simpler way to have update logs created is to add the option to the MySQL configuration file, my.cnf, which you should have created in your system's /etc directory as part of the procedure to auto-start the MySQL server (see "Installation"). To set MySQL to create update logs by default, simply add a log-update line below [mysqld] in your my.cnf file. For Windows users, the my.cnf file should be located in the root of your C: drive; alternatively, it may be named my.ini and placed in your Windows directory.
[mysqld] log-update=/usr/backups/mysql/update
Feel free to specify whatever location to which you'd like the server to write the update logs. Save the file and restart your MySQL server. From now on, the server will behave by default as if you'd specified the --log-update option on the command line.
Obviously, update logs can take up a lot of space on an active server. For this reason, and because MySQL will not automatically delete old log files as it creates new ones, it's up to you to manage your update log files. The following UNIX shell script, for example, tells MySQL to flush its log files, and then deletes all update files that were last modified more than a week ago.
#!/bin/sh /usr/local/mysql/bin/mysqladmin -u root –pmypasswd flush-logs find /usr/backups/mysql/ -name "update.[0-9]*" -type f -mtime +6 | xargs rm -f
This first step (flushing the log files) creates a new update log in case the current one is about to be deleted. This deletion will occur if the server has been online, and has not received any queries that changed database contents, for over a week. If you're an experienced user, setting up a script that uses cron[1] or Windows' Task Scheduler to periodically (say, once a week) perform a database backup and delete old update logs should be fairly easy. If you need a little help with this, speak to your Web host, system administrator, or local guru, or post a message to the SitePoint Forums (we'll be glad to help!).
If you have a backup and a copy of the update logs since the backup was made, then the restoration of your database should be fairly simple. After you create the empty database and apply the backup as described in the previous section, apply the update logs, using the --one-database command-line option for mysql. This command instructs the server to run only those queries in the update log that pertain to the database you want to restore (db in this example):
shell%mysql -u root -pmypasswd --one-database db < update.100 shell%mysql -u root -pmypasswd --one-database db < update.102 ...
[1]cron is a well-known task scheduling utility available on most Linux and UNIX-based systems. To learn how to set up cron tasks, begin by typing man crontab at your server's command prompt.