GNU Info

(mysql.info)Backup

---

Next: BACKUP TABLE Prev: Disaster Prevention Up: Disaster Prevention

---

Enter node , (file) or (file)node

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.  Note: `LOCK TABLES'.
  Note: `FLUSH'.  You only need a read lock; this allows
other threads to continue to query the tables while you are making a
copy of the files in the database directory.  The `FLUSH TABLE' is
needed to ensure that the all active index pages is written to disk
before you start the backup.

If you want to make a SQL level backup of a table, you can use `SELECT
INTO OUTFILE' or `BACKUP TABLE'. Note: SELECT.  Note: BACKUP TABLE.

Another way to back up a database is to use the `mysqldump' program or
the `mysqlhotcopy script'. Note: `mysqldump'.  Note:
`mysqlhotcopy'.

  1. Do a full backup of your databases:

          shell> mysqldump --tab=/path/to/some/dir --opt --full
          
          or
          
          shell> mysqlhotcopy database /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
     script `mysqlhotcopy' does use this method.

  2. Stop `mysqld' if it's running, then start it with the
     `--log-update[=file_name]' option.  Note: Update log. The update
     log file(s) 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 you have to restore something, try to recover your tables using
`REPAIR TABLE' or `myisamchk -r' first.  That should work in 99.9% of
all cases.  If `myisamchk' fails, try the following procedure: (This
will only work if you have started MySQL with `--log-update'. Note:
Update log.):

  1. Restore the original `mysqldump' backup.

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

          shell> mysqlbinlog hostname-bin.[0-9]* | mysql

     If you are using the update log you can use:

          shell> ls -1 -t -r hostname.[0-9]* | xargs cat | mysql

`ls' is used to get all the update log files in the right order.

You can also do selective backups with `SELECT * INTO OUTFILE
'file_name' FROM tbl_name' and restore with `LOAD DATA INFILE
'file_name' REPLACE ...' To avoid duplicate records, you need a
`PRIMARY KEY' or a `UNIQUE' key in the table. 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 get performance problems in making backups on your system, you
can solve this by setting up replication and do the backups on the slave
instead of on the master. Note: Replication Intro.

If you are using a Veritas file system, you can do:

  1. Execute in a client (perl ?) `FLUSH TABLES WITH READ LOCK'

  2. Fork a shell or execute in another client `mount vxfs snapshot'.

  3. Execute in the first client `UNLOCK TABLES'

  4. Copy files from snapshot

  5. Unmount snapshot