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
shell> mysqlhotcopy db_name /path/to/some/dir
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."
shell> mysqlbinlog hostname-bin.[0-9]* |
shell> ls -1 -t -r hostname.[0-9]* | xargs
You can also do selective backups of individual files:
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:
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 184.108.40.206, "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:
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.
220.127.116.11 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
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
These commands assume that you have more than 64MB free. For more information about memory allocation with myisamchk, see Section 18.104.22.168, "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
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.
blog comments powered by Disqus