|< Day Day Up >|
14.4 MyISAM Backup and Recovery
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 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:
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.
22.214.171.124 Reloading mysqldump Output
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
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.
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.
|< Day Day Up >|