DOC HOME SITE MAP MAN PAGES GNU INFO SEARCH
 

(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