Previous Section  < Day Day Up >  Next Section

14.4 MyISAM Backup and Recovery

The following list indicates available methods for backing up MyISAM tables:

  • Use the mysqldump program, which runs on any platform supported by MySQL. It can tell the server to send backups to the client in the form of SQL statements or to write data directly to files on the server host.

  • Use the mysqlhotcopy script. This is a Perl script; it requires the DBI module and is available on Unix and NetWare. mysqlhotcopy is very fast, but can be used with the local server only.

  • Use the BACKUP TABLE and RESTORE TABLE statements. BACKUP TABLE makes copies of the MySQL format file and datafile on the server host. RESTORE TABLE restores them and rebuilds the index file.

  • Use the SELECT … INTO OUTFILE statement to save the contents of a table into a file on the server host. More information about this statement is available in the "Core Study Guide."

Except for mysqlhotcopy, any of the methods just listed can be used with either local or remote servers. mysqlhotcopy can be used only with a local server.

Some of the preceding backup methods also can be used for InnoDB tables, as discussed in section 15.5, "InnoDB Backup and Recovery."

14.4.1 Using mysqldump

The mysqldump client program dumps table contents to files. It is useful for making database backups or for transferring database contents to another server. mysqldump can produce SQL-format dump files that contain CREATE TABLE and INSERT statements for re-creating the dumped files, or it can produce tab-delimited datafiles. The "Core Study Guide" discusses general mysqldump operation for producing SQL-format dump files, and it is assumed here that you're familiar with that material. This section discusses how to produce tab-delimited datafiles.

Normally, mysqldump generates output as a set of SQL statements. These take the form of CREATE TABLE statements that create the dumped tables, and INSERT statements that load them. However, when you use the --tab=dir_name (or -T dir_name) option, mysqldump writes each dumped table as a tab-delimited text file in the dir_name directory. It also writes a file containing a CREATE TABLE statement that you can use to re-create the table.

Using --tab to produce tab-delimited dump files is much faster than creating SQL-format files, but you should keep in mind the following points:

  • The CREATE TABLE statement for each table table_name is sent by the server to mysqldump, which writes it to a file named table_name.sql in the dump directory on the client host. The .sql files are owned by you.

  • The table contents are written directly by the server into a file named table_name.txt in the dump directory on the server host. The .txt files are owned by the server.

  • Using --tab can be confusing because some files are created by the client and some by the server, and because the .sql files have different ownerships than the .txt files. To minimize confusion, run mysqldump on the server host, specify the dump directory using a full pathname so that mysqldump and the server both interpret it as the same location, and specify a dump directory that is writable both to you and to the server.

  • You must have the FILE privilege because the dump operation causes the server to write datafiles on the server host.

  • To write only the data files and not the .sql files, use the --no-create-info option.

The default datafile format produced by the --tab option consists of tab-delimited lines with newline terminators. To control the format of the datafiles that mysqldump generates, use the following options:

  • --lines-terminated-by=string

    string specifies the character sequence that each input line should end with. The default is \n (linefeed, also known as newline). Other common line terminators are \r (carriage return) and \r\n (carriage return/linefeed pairs).

  • --fields-terminated-by=string

    string specifies the delimiter to write between data values within input lines. The default delimiter is \t (tab).

  • --fields-enclosed-by=char or --fields-optionally-enclosed-by=char

    char indicates a quoting character that should be written surrounding data values. By default, values are not quoted. A common value for char is double quote. With --fields-enclosed-by, all values are quoted. With --fields-optionally-enclosed-by, only values from CHAR and VARCHAR columns are quoted.

  • --fields-escaped-by=char

    By default, special characters in data values are written preceded by \as an escape character, and NULL values are written as \N. Use this option to specify a different escape character. To turn escaping off (no escape character), specify an empty value for char.

You cannot use --tab with the --all-databases or --databases option. With --tab, mysqldump writes the files for all dumped tables to a single directory. You would have no way to tell which files correspond to tables in each database.

14.4.1.1 Reloading mysqldump Output

To reload a SQL-format dump file produced by mysqldump, read it with mysql. For example:







shell> mysqldump --opt world Country > dump.sql

shell> mysql world < dump.sql

mysql can read from a pipe, so you can combine the use of mysqldump and mysql into a single command. One use for this is for copying a database over the network to another server. If you have a MySQL account for the server on another host, you can pipe the output to that server like this:







shell> mysqldump world Country | mysql -h other.host.com mysql world

If a dump file contains very long INSERT statements, they might exceed the default size of the communications buffer (1MB). You can increase the buffer size for both mysqldump and mysql with the --max-allowed-packet option. The option value may be given in bytes or followed by K, M, or G to indicate a size in kilobytes, megabytes, or gigabytes. For example, --max-allowed-packet=32M specifies a size of 32MB. The server must also be run with a --max-allowed-packet value that increases its own communications buffer to be large enough.

If you use the --tab option to produce tab-delimited datafiles, reloading the files requires a different approach. Suppose that you dump the table City from the world database using the /tmp directory as the output directory:







shell> mysqldump --opt --tab=/tmp world City

The output will consist of a City.sql file containing the CREATE TABLE statement for the table, and a City.txt file containing the table data. To reload the table, change location into the dump directory, process the .sql file using mysql, and load the .txt file using mysqlimport:







shell> cd /tmp

shell> mysql world < City.sql

shell> mysqlimport world City.txt

If you combine the --tab option with format-control options such as --fields-terminated-by and --fields-enclosed-by, you should specify the same format-control options with mysqlimport so that it knows how to interpret the datafiles.

14.4.2 Using mysqlhotcopy

The mysqlhotcopy script copies MyISAM tables to a backup directory. It is a Perl script and requires the DBI module to be installed. It runs on Unix and NetWare.

mysqlhotcopy is fast because it copies table files directly rather than backing them up over the network. It's also more convenient than issuing statements to the server to lock the tables and flush any pending changes to disk, because it handles those operations for you.

mysqlhotcopy must be run on the server host so that it can copy table files while the table locks are in place. It must be run while the server is running because it must connect to the server to lock and flush the tables.

mysqlhotcopy has many options, which you can see by invoking the script with the --help option. The following examples present some simple ways to use the script:

  • Back up the world database to a directory named world_copy in the server's data directory:

    




    
shell> mysqlhotcopy world

  • Back up the world database to a directory named world in the /var/archive directory:

    




    
shell> mysqlhotcopy world /var/archive

14.4.3 Using BACKUP TABLE and RESTORE TABLE

BACKUP TABLE and RESTORE TABLE save and reload a copy of one or more MyISAM tables. (In both statements, TABLE or TABLES can be used.) These statements require that you have the FILE privilege because they create and read files on the server host.

To save a copy of a table with BACKUP TABLE, name the table and the full pathname of the directory on the server host where the copy should be written. The following statement saves the City table into the /var/archive directory:







BACKUP TABLE City TO '/var/archive';

To back up several tables, name them as a comma-separated list:







BACKUP TABLES City, Country, CountryLanguage TO '/var/archive';

For each file to be saved, BACKUP TABLE locks it to prevent changes and copies its .frm and .MYD files to the backup directory. The .MYI file is not copied because it can be rebuilt from the other two files when the table is restored.

BACKUP TABLE locks each table individually while copying it. To lock the tables as a group, lock them explicitly:







LOCK TABLES City READ, Country READ, CountryLanguage READ;

BACKUP TABLES City, Country, CountryLanguage TO '/var/archive';

UNLOCK TABLES;

To restore a saved copy of a table, use RESTORE TABLE:







RESTORE TABLE City FROM '/var/archive';

RESTORE TABLE copies the .frm and .MYD files from the named directory into the database directory, and then rebuilds the .MYI file.

BACKUP TABLE fails if a table already has been saved in the named directory. RESTORE TABLE fails if the table to be restored already exists.

    Previous Section  < Day Day Up >  Next Section