Performing regular backups is an essential part of even the smallest database project. Fortunately MySQL makes this very easy by offering several backup solutions.
If your tables use the MyISAM storage engine, you can backup the database simply by copying the files used to store the tables and data. To do so, you’ll need to first execute the LOCK TABLES command (only a read lock is required), followed by FLUSH TABLES. Once executed, copy the files, and when the copy is complete, execute UNLOCK TABLES.
Creating Delimited Backups
To backup the table data in delimited format, use the SELECT INTO OUTFILE command. For instance to backup the authors table used in previous examples, execute:
mysql>SELECT * INTO OUTFILE 'authors090308.sql' FROM authors;
The mysqldump client is convenient because it supports creating backups of all databases using any MySQL storage engine, not to mention that it automatically takes care of important details such as locking the tables during the backup.
The mysqldump client supports an enormous number of options, and it’s recommended you take some time to review them in the MySQL manual, however this section will give you enough to at least remind you of what’s required to perform a variety of different backups.
Backing Up a Specific Database
To backup a single database, just pass the database name to the mysqldump client, piping the output to a text file:
%>mysqldump [options] database_name > backup0903.sql
Of course, you’ll require proper permissions to execute mysqldump in conjunction with a specific database (namely the SELECT and LOCK privileges), therefore you’ll typically also need to pass along your username and password. In this case, this command typically looks similar to:
%>mysqldump -u root -p database_name > backup0903.sql
Backing Up Specific Tables
To backup specific tables, you’ll need to identify the database, followed by each specific table name you’d like to backup:
%>mysqldump [options] database_name table_name [table_
name2...] > backupfile.sql
Backing Up All Databases
To backup all databases, pass the --all-databases option:
%>mysqldump [options] --all-databases > backupfile.sql
If all of your backup tables use the MyISAM storage engine, and you’re able to log into the server where the tables are stored, the mysqlhotcopy might be the ideal solution due to its speed advantages.
To backup the dzone database to a directory located at /home/jason/backups using mysqlhotcopy, execute:
%>mysqlhotcopy -u root -p dzone /home/jason/backups
To copy multiple databases, just string each database name together:
%>mysqlhotcopy -u root -p dzone wjgilmore /home/jason/backups
Like mysqldump, mysqlhotcopy offers an enormous number of options, so be sure to review the MySQL manual (http://dev. mysql.com/doc/) to learn all that’s available.
MySQL’s replication features make it possible to maintain a consistently synchronized version of the live database. Replication is out of the scope of this reference card, but be sure to visit the MySQL documentation (http://dev.mysql.com/doc/) if replication is more suitable to your needs.
Performing Backups, Continued
If you’re looking for a more robust backup solution, check out mylvmbackup (http://lenz.homelinux.org/mylvmbackup/), which when configured properly can typically perform a safe backup while your application is still running. Another great solution is Zmanda (http://www.zmanda.com/), which is available in both community and enterprise versions.