(mysql.info) backup
Info Catalog
(mysql.info) disaster-prevention
(mysql.info) disaster-prevention
(mysql.info) backup-strategy-example
5.10.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. See lock-tables, and
flush. 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.
To make an SQL-level backup of a table, you can use `SELECT INTO ...
OUTFILE'. For this statement, the output file cannot previously exist
because allowing extant files to be overwritten would constitute a
security risk. See select.
Another technique for backing up a database is to use the `mysqldump'
program or the `mysqlhotcopy script'. See mysqldump, and
mysqlhotcopy.
1. Create a full backup of your database:
shell> mysqldump --tab=/PATH/TO/SOME/DIR --opt DB_NAME
Or:
shell> mysqlhotcopy DB_NAME /PATH/TO/SOME/DIR
You can also create a binary backup simply by copying 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 do not work if your database
contains `InnoDB' tables. `InnoDB' does not store table contents
in database directories, and `mysqlhotcopy' works only for
`MyISAM' tables.)
2. Stop `mysqld' if it is running, then start it with the
-log-bin[=FILE_NAME] option. See 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'.
For `InnoDB' tables, it is possible to perform an online backup that
takes no locks on tables; see mysqldump.
MySQL supports incremental backups: You need to start the server with
the -log-bin option to enable binary logging; see binary-log.
At the moment you want to make an incremental backup (containing all
changes that happened since the last full or incremental backup), you
should rotate the binary log by using `FLUSH LOGS'. This done, you
need to copy to the backup location all binary logs which range from
the one of the moment of the last full or incremental backup to the
last but one. These binary logs are the incremental backup; at restore
time, you apply them as explained further below. The next time you do a
full backup, you should also rotate the binary log using `FLUSH LOGS',
`mysqldump --flush-logs', or `mysqlhotcopy --flushlog'. See
mysqldump, and mysqlhotcopy.
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 works only if you have enabled binary logging by starting MySQL with
the -log-bin option.
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 binlog.[0-9]* | mysql
In some cases, 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 statements). See mysqlbinlog, for more
information on the `mysqlbinlog' utility and how to use it.
You can also make selective backups of individual files:
* To dump the table, use `SELECT * INTO OUTFILE 'FILE_NAME' FROM
TBL_NAME'.
* To reload the table, use `LOAD DATA INFILE 'FILE_NAME' REPLACE
...'. To avoid duplicate rows, the table must have a `PRIMARY KEY'
or a `UNIQUE' index. The `REPLACE' keyword causes old rows to be
replaced with new ones when a new row duplicates an old row 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 replication-intro.
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.
Info Catalog
(mysql.info) disaster-prevention
(mysql.info) disaster-prevention
(mysql.info) backup-strategy-example
automatically generated byinfo2html