Home arrow MySQL arrow Disaster Prevention and Recovery with the MySQL Database

Disaster Prevention and Recovery with the MySQL Database

If you need to administer MySQL, this article gets you off to a good start. In this section, we discuss the issues of disaster prevention and recovery. The fifth of a multi-part series, it is excerpted from chapter four of the book MySQL Administrator's Guide, written by Paul Dubois (Sams; ISBN: 0672326345).

  1. Disaster Prevention and Recovery with the MySQL Database
  2. General Options for myisamchk
  3. Repair Options for myisamchk
  4. Using myisamchk for Crash Recovery
  5. How to Repair Tables
  6. 4.6.3 Setting Up a Table Maintenance Schedule
  7. 4.6.4 Getting Information About a Table
By: Sams Publishing
Rating: starstarstarstarstar / 7
June 22, 2006

print this article



4.6 Disaster Prevention and Recovery

This section discusses how to make database backups and how to perform table maintenance. The syntax of the SQL statements described here is given in the MySQL Language Reference. Much of the information here pertains primarily to MyISAM tables. InnoDB backup procedures are given in Section 9.9, "Backing Up and Recovering an InnoDB Database."

4.6.1 Database Backups

Because MySQL tables are stored as files, it is easy to do a backup. To get a consistent backup, do a LOCK TABLES on the relevant tables, followed by FLUSH TABLES for the tables. You need only a read lock; this allows other clients to continue to query the tables while you are making a copy of the files in the database directory. The FLUSH TABLES statement is needed to ensure that the all active index pages are written to disk before you start the backup.

If you want to make an SQL-level backup of a table, you can use SELECT INTO ... OUTFILE or BACKUP TABLE. For SELECT INTO ... OUTFILE, the output file cannot already exist. For BACKUP TABLE, the same is true as of MySQL 3.23.56 and 4.0.12, because this would be a security risk.

Another way to back up a database is to use the mysqldump program or the mysqlhotcopy script. See Section 7.8, "The mysqldump Database Backup Program," and Section 7.9, "The mysqlhotcopy Database Backup Program."

Do a full backup of your database:

shell> mysqldump --tab=/path/to/some/dir

--opt db_name

  1. Or:

shell> mysqlhotcopy db_name /path/to/some/dir
  1. You can also simply copy all table files (*.frm, *.MYD, and *.MYI files) as long as the server isn't updating anything. The mysqlhotcopy script uses this method. (But note that these methods will not work if your database contains InnoDB tables. InnoDB does not store table contents in database directories, and mysqlhotcopy works only for MyISAM and ISAM tables.)

  2. Stop mysqld if it's running, then start it with the --log-bin[=file_name] option. See Section 4.8.4, "The Binary Log." The binary log files provide you with the information you need to replicate changes to the database that are made subsequent to the point at which you executed mysqldump.

If your MySQL server is a slave replication server, then regardless of the backup method you choose, you should also back up the master.info and relay-log.info files when you back up your slave's data. These files are always needed to resume replication after you restore the slave's data. If your slave is subject to replicating LOAD DATA INFILE commands, you should also back up any SQL_LOAD-* files that may exist in the directory specified by the --slave-load-tmpdir option. (This location defaults to the value of the tmpdir variable if not specified.) The slave needs these files to resume replication of any interrupted LOAD DATA INFILE operations.

If you have to restore MyISAM tables, try to recover them using REPAIR TABLE or myisamchk -r first. That should work in 99.9% of all cases. If myisamchk fails, try the following procedure. Note that it will work only if you have enabled binary logging by starting MySQL with the --log-bin option; see Section 4.8.4, "The Binary Log."

  1. Restore the original mysqldump backup, or binary backup.

  2. Execute the following command to re-run the updates in the binary logs:

shell> mysqlbinlog hostname-bin.[0-9]* |
  1. In your case, you may want to re-run only certain binary logs, from certain positions (usually you want to re-run all binary logs from the date of the restored backup, excepting possibly some incorrect queries). See Section 7.5, "The mysqlbinlog Binary Log Utility," for more information on the mysqlbinlog utility and how to use it.

    If you are using the update logs instead, you can process their contents like this:

shell> ls -1 -t -r hostname.[0-9]* | xargs
cat | mysql
  1. ls is used to sort the update log filenames into the right order.

You can also do selective backups of individual files:

  • To dump the table, use SELECT * INTO OUTFILE 'file_name' FROM 'tbl_name.

  • To reload the table, use and restore with LOAD DATA INFILE 'file_name' REPLACE ... To avoid duplicate records, the table must have a PRIMARY KEY or a UNIQUE index. The REPLACE keyword causes old records to be replaced with new ones when a new record duplicates an old record on a unique key value.

If you have performance problems with your server while making backups, one strategy that can help is to set up replication and perform backups on the slave rather than on the master. See Section 5.1, "Introduction to Replication."

If you are using a Veritas filesystem, you can make a backup like this:

  1. From a client program, execute FLUSH TABLES WITH READ LOCK.

  2. From another shell, execute mount vxfs snapshot.

  3. From the first client, execute UNLOCK TABLES.

  4. Copy files from the snapshot.

  5. Unmount the snapshot.

4.6.2 Table Maintenance and Crash Recovery

The following text discusses how to use myisamchk to check or repair MyISAM tables (tables with .MYI and .MYD files). The same concepts apply to using isamchk to check or repair ISAM tables (tables with .ISM and .ISD files). See Chapter 8, "MySQL Storage Engines and Table Types."

You can use the myisamchk utility to get information about your database tables or to check, repair, or optimize them. The following sections describe how to invoke myisamchk (including a description of its options), how to set up a table maintenance schedule, and how to use myisamchk to perform its various functions.

Even though table repair with myisamchk is quite secure, it's always a good idea to make a backup before doing a repair (or any maintenance operation that could make a lot of changes to a table).

myisamchk operations that affect indexes can cause FULLTEXT indexes to be rebuilt with full-text parameters that are incompatible with the values used by the MySQL server. To avoid this, read the instructions in Section, "General Options for myisamchk."

In many cases, you may find it simpler to do MyISAM table maintenance using the SQL statements that perform operations that myisamchk can do:

  • To check or repair MyISAM tables, use CHECK TABLE or REPAIR TABLE.

  • To optimize MyISAM tables, use OPTIMIZE TABLE.

  • To analyze MyISAM tables, use ANALYZE TABLE.

These statements were introduced in different versions, but all are available from MySQL 3.23.14 on. The statements can be used directly, or by means of the mysqlcheck client program, which provides a command-line interface to them.

One advantage of these statements over myisamchk is that the server does all the work. With myisamchk, you must make sure that the server does not use the tables at the same time. Otherwise, there can be unwanted interaction betweeen myisamchk and the server. myisamchk Invocation Syntax

Invoke myisamchk like this:

shell> myisamchk [options] tbl_name

The options specify what you want myisamchk to do. They are described in the following sections. You can also get a list of options by invoking myisamchk --help.

With no options, myisamchk simply checks your table as the default operation. To get more information or to tell myisamchk to take corrective action, specify options as described in the following discussion.

tbl_name is the database table you want to check or repair. If you run myisamchk somewhere other than in the database directory, you must specify the path to the database directory, because myisamchk has no idea where the database is located. In fact, myisamchk doesn't actually care whether the files you are working on are located in a database directory. You can copy the files that correspond to a database table into some other location and perform recovery operations on them there.

You can name several tables on the myisamchk command line if you wish. You can also specify a table by naming its index file (the file with the .MYI suffix). This allows you to specify all tables in a directory by using the pattern *.MYI. For example, if you are in a database directory, you can check all the MyISAM tables in that directory like this:

shell> myisamchk *.MYI

If you are not in the database directory, you can check all the tables there by specifying the path to the directory:

shell> myisamchk /path/to/database_dir/*.MYI

You can even check all tables in all databases by specifying a wildcard with the path to the MySQL data directory:

shell> myisamchk /path/to/datadir/*/*.MYI

The recommended way to quickly check all MyISAM and ISAM tables is:

shell> myisamchk --silent --fast
/path/to/datadir/*/*.MYI shell> isamchk --silent

If you want to check all MyISAM and ISAM tables and repair any that are corrupted, you can use the following commands:

shell> myisamchk --silent --force --fast
--update-state \
-O key_buffer=64M -O sort_buffer=64M \
-O read_buffer=1M -O write_buffer=1M \
/path/to/datadir/*/*.MYI shell> isamchk --silent --force -O
key_buffer=64M \
-O sort_buffer=64M -O read_buffer=1M
-O write_buffer=1M \

These commands assume that you have more than 64MB free. For more information about memory allocation with myisamchk, see Section, "myisamchk Memory Usage."

You must ensure that no other program is using the tables while you are running myisamchk. Otherwise, when you run myisamchk, it may display the following error message:

warning: clients are using or haven't closed
the table properly

This means that you are trying to check a table that has been updated by another program (such as the mysqld server) that hasn't yet closed the file or that has died without closing the file properly.

If mysqld is running, you must force it to flush any table modifications that are still buffered in memory by using FLUSH TABLES. You should then ensure that no one is using the tables while you are running myisamchk. The easiest way to avoid this problem is to use CHECK TABLE instead of myisamchk to check tables.

>>> More MySQL Articles          >>> More By Sams Publishing

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort


- Oracle Unveils MySQL 5.6
- MySQL Vulnerabilities Threaten Databases
- MySQL Cloud Options Expand with Google Cloud...
- MySQL 5.6 Prepped to Handle Demanding Web Use
- ScaleBase Service Virtualizes MySQL Databases
- Oracle Unveils MySQL Conversion Tools
- Akiban Opens Database Software for MySQL Use...
- Oracle Fixes MySQL Bug
- MySQL Databases Vulnerable to Password Hack
- MySQL: Overview of the ALTER TABLE Statement
- MySQL: How to Use the GRANT Statement
- MySQL: Creating, Listing, and Removing Datab...
- MySQL: Create, Show, and Describe Database T...
- MySQL Data and Table Types
- McAfee Releases Audit Plugin for MySQL Users

Developer Shed Affiliates


Dev Shed Tutorial Topics: