(mysql.info.gz) Backup
Info Catalog
(mysql.info.gz) Disaster Prevention
(mysql.info.gz) Disaster Prevention
(mysql.info.gz) Backup strategy example
5.7.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'
LOCK TABLES. and `FLUSH' 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.
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 previously 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. See `SELECT' SELECT. and `BACKUP TABLE'
BACKUP TABLE.
Another way to back up a database is to use the `mysqldump' program or
the `mysqlhotcopy script'. See `mysqldump' mysqldump. and
`mysqlhotcopy' mysqlhotcopy.
1. Do 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 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. 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's possible to perform an online backup that
takes no locks on tables; see `mysqldump' 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 --flushlogs'. See
`mysqldump' mysqldump. and `mysqlhotcopy' 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 will work only if you have enabled binary logging by starting
MySQL with the `--log-bin' option; see 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]* | mysql
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 `mysqlbinlog' mysqlbinlog. for more
information on the `mysqlbinlog' utility and how to use it.
If you are using the update logs instead (which is a deprecated
feature removed in MySQL 5.0), you can process their contents like
this:
shell> ls -1 -t -r hostname.[0-9]* | xargs cat | mysql
`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. 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.gz) Disaster Prevention
(mysql.info.gz) Disaster Prevention
(mysql.info.gz) Backup strategy example
automatically generated byinfo2html