Learning MySQL

Dumping a Database as SQL Statements

You can make a database backup by generating a file of all the SQL commands necessary to re-create the existing database structure from scratch, and (if you want) the SQL commands to insert all the data. Note that this is different from exporting table contents using the SELECT INTO OUTFILE syntax that we saw in Writing Data into Comma-Delimited Files” in Chapter 8 since we get the actual SQL INSERT statements, rather than just the raw values.

SQL statements are an excellent form of backup. One of the easiest ways to safely back up your data is to export it from MySQL, write it to stable media (such as a high-quality recordable CD or DVD), and store it in a safe location. Since the file of SQL statements contains just text, it can be compressed to a fraction of its original size using a compression program. Suitable compression programs on Linux or Mac OS X are gzip, bzip2, or zip; you can also use the StuffIt program under Mac OS X. Under Windows, you can compress a file by right-clicking on the file icon and selecting “Send To” and then “Compressed (zipped) Folder.” You can also use third-party tools such as WinZip and PKZIP.

Let’s try a simple example to back up the music database. To do this, we’ll run the mysqldump utility and save the output to the file music.sql:

$ mysqldump --user=root --password=the_mysql_root_password \
--result-file=music.sql music

This tries to create the file music.sql in the current directory. If you don’t have permission ...