(mysql.info) innodb-backup

 
 14.2.8 Backing Up and Recovering an `InnoDB' Database
 -----------------------------------------------------

 
* forcing-recovery             Forcing `InnoDB' Recovery
* innodb-checkpoints           Checkpoints
 
 The key to safe database management is making 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) add-on tool with an annual license fee of €390
 per computer on which 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 and `.ibd' files) into a
      safe place.
 
   3. Copy all your `ib_logfile' files 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' tables, so you can use MySQL
 replication capabilities to keep a copy of your database at database
 sites requiring high availability.
 
 In addition to making binary backups as just described, you should also
 regularly make 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, because the format is
 simpler, the chance for serious data corruption is smaller.
 `mysqldump' also has a -single-transaction option that you can use to
 make a consistent snapshot without locking out other clients.
 
 To be able to recover your `InnoDB' database to the present from the
 binary backup just described, 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, the only requirement is
 to restart it. `InnoDB' automatically checks the logs and performs a
 roll-forward of the database to the present. `InnoDB' automatically
 rolls back uncommitted transactions that were present at the time of the
 crash. During recovery, `mysqld' displays 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 using `mysqlbinlog' and
 `mysql' to restore the changes performed after the backup was made.
 
 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. Doing so
 may eliminate errors that appeared to be database page corruption.

Info Catalog

(mysql.info) adding-and-removing

(mysql.info) innodb

(mysql.info) moving

automatically generated byinfo2html

(mysql.info) innodb-backup

Menu