(mysql.info.gz) Backing up

(mysql.info.gz) Adding and removing
 
 15.9 Backing Up and Recovering an `InnoDB' Database
 ===================================================
 
 The key to safe database management is taking regular backups.
 
 `InnoDB Hot Backup' is an online backup tool you can use to backup your
 `InnoDB' database while it is running. `InnoDB Hot Backup' does not
 require you to shut down your database and it does not set any locks or
 disturb your normal database processing. `InnoDB Hot Backup' is a
 non-free (commercial) additional tool whose annual license fee is 390
 euros per computer where the MySQL server is run.  See the `InnoDB Hot
 Backup' home page (http://www.innodb.com/order.html) for detailed
 information and screenshots.
 
 If you are able to shut down your MySQL server, you can make a "binary"
 backup that consists of all files used by `InnoDB' to manage its
 tables. Use the following procedure:
 
   1. Shut down your MySQL server and make sure that it shuts down
      without errors.
 
   2. Copy all your data files (`ibdata' files, `.ibd' files) into a
      safe place.
 
   3. Copy all your `ib_logfile's to a safe place.
 
   4. Copy your `my.cnf' configuration file or files to a safe place.
 
   5. Copy all the `.frm' files for your `InnoDB' tables to a safe place.
 
 Replication works with `InnoDB' type tables, so you can use MySQL
 replication capabilities to keep a copy of your database at database
 sites requiring high availability.
 
 In addition to taking binary backups as just described, you should also
 regularly take dumps of your tables with `mysqldump'. The reason for
 this is that a binary file might be corrupted without you noticing it.
 Dumped tables are stored into text files that are human-readable, so
 spotting table corruption becomes easier. Also, since the format is
 simpler, the chance for serious data corruption is smaller.
 `mysqldump' also has a `--single-transaction' option that you can use
 to take a consistent snapshot without locking out other clients.
 
 To be able to recover your `InnoDB' database to the present from the
 binary backup described above, you have to run your MySQL server with
 binary logging turned on. Then you can apply the binary log to the
 backup database to achieve point-in-time recovery:
 
      mysqlbinlog YOURHOSTNAME-bin.123 | mysql
 
 To recover from a crash of your MySQL server process, the only thing
 you have to do is to restart it. `InnoDB' will automatically check the
 logs and perform a roll-forward of the database to the present.
 `InnoDB' will automatically roll back uncommitted transactions that were
 present at the time of the crash. During recovery, `mysqld' will
 display output something like this:
 
      InnoDB: Database was not shut down normally.
      InnoDB: Starting recovery from log files...
      InnoDB: Starting log scan based on checkpoint at
      InnoDB: log sequence number 0 13674004
      InnoDB: Doing recovery: scanned up to log sequence number 0 13739520
      InnoDB: Doing recovery: scanned up to log sequence number 0 13805056
      InnoDB: Doing recovery: scanned up to log sequence number 0 13870592
      InnoDB: Doing recovery: scanned up to log sequence number 0 13936128
      ...
      InnoDB: Doing recovery: scanned up to log sequence number 0 20555264
      InnoDB: Doing recovery: scanned up to log sequence number 0 20620800
      InnoDB: Doing recovery: scanned up to log sequence number 0 20664692
      InnoDB: 1 uncommitted transaction(s) which must be rolled back
      InnoDB: Starting rollback of uncommitted transactions
      InnoDB: Rolling back trx no 16745
      InnoDB: Rolling back of trx no 16745 completed
      InnoDB: Rollback of uncommitted transactions completed
      InnoDB: Starting an apply batch of log records to the database...
      InnoDB: Apply batch completed
      InnoDB: Started
      mysqld: ready for connections
 
 If your database gets corrupted or your disk fails, you have to do the
 recovery from a backup. In the case of corruption, you should first
 find a backup that is not corrupted. After restoring the base backup,
 do the recovery from the binary log files.
 
 In some cases of database corruption it is enough just to dump, drop,
 and re-create one or a few corrupt tables. You can use the `CHECK
 TABLE' SQL statement to check whether a table is corrupt, although
 `CHECK TABLE' naturally cannot detect every possible kind of corruption.
 You can use `innodb_tablespace_monitor' to check the integrity of the
 file space management inside the tablespace files.
 
 In some cases, apparent database page corruption is actually due to the
 operating system corrupting its own file cache, and the data on disk
 may be okay. It is best first to try restarting your computer.  It may
 eliminate errors that appeared to be database page corruption.
(mysql.info.gz) Backing up

Menu
