MySQL Phrasebook

It is vital to back up your data regularly. The actual backup strategy you use depends on many factors, including how often your database is updated and the severity of the consequences of your database being offline.

You should schedule your backups so that, should the worst happen, recovery onto a new database server can be done quickly and with no mission-critical data lost.

Taking a Full Backup

The mysqldump command outputs the contents of a database as a series of SQL files. Each table is converted into a CREATE TABLE statement, and the data rows are converted into an INSERT statement. The SQL commands are output to screen by default, so you should redirect the output of this command to a filename to create a backup file.

The name of the database to back up comes after the connection options. You can then optionally specify the names of tables to extractif you do not supply any table names, the entire database is dumped. The following command would dump only the book and person tables from the sample database:

mysqldump --user=zak --password=phrasebook \ sampdb book person > sampdb.sql

The host and port options were omitted in the previous example, so this command would try to connect to a MySQL server running on the default port on the local machine.

If you want to specify multiple databases for mysqldump, use the --databases switch. All subsequent words are treated as database names. The following command dumps the contents of two databases named db1 and db2:

mysqldump --user=zak --password=phrasebook \ --databases db1 db2 > dump.sql

To dump all the databases on your MySQL server in one operation, use the --all-databases switch.

Taking an Incremental Backup

A full database backup can be quite an intensive process. Every row from every table has to be returned to the mysqldump program, and then each line has to be written to a file. Running a full database backup on a busy server can affect performance for users.

The alternative is to use incremental backups that MySQL implements via its binary logging feature. To activate binary logging, add the log-bin option to my.cnf and restart the MySQL server. Alternatively, start mysqld with the --log-bin switch.

Binary logs are written to the home directory for the mysql user, usually /var/lib/mysql. The default filename is hostname-bin.XXX, where hostname is the server's hostname and XXX is a sequence number. Each time the MySQL server is restarted or you issue the FLUSH LOGS command, a new binary log is started.

The binary log contains any SQL statement issued that might have updated data. Any UPDATE, INSERT, or DELETE is recorded, even if no changes were made as a result of that commandfor instance, an UPDATE statement where the WHERE clause matched no rows. This level of logging allows you to roll the database forward from a full backup right up to the very latest database change that was made.

In order to restore successfully, your binary logs must begin with transactions that took place immediately after a full backup was taken. To synchronize the binary log with a backup file, use the --flush-logs option to mysqldump, which causes a FLUSH LOGS command to be issued as soon as the dump begins. Any further database activity is then written to the next binary log in sequence.

As you might expect, writing the binary log does have an effect on your server's performance, but the benefits gained usually outweigh the performance lost. According to the MySQL documentation, MySQL runs approximately 1% slower with binary logging enableda very minor decrease.