Making Backups Using a File System Snapshot Making Backups Using a File System Snapshot 7.3 Example Backup and Recovery Strategy 1617 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 For cases of operating system crashes or power failures, we can assume that MySQL’s disk data is available after a restart. The  InnoDB  data files might not contain consistent data due to the crash, but InnoDB  reads its logs and finds in them the list of pending committed and noncommitted transactions that have not been flushed to the data files.  InnoDB  automatically rolls back those transactions that were not committed, and flushes to its data files those that were committed. Information about this recovery process is conveyed to the user through the MySQL error log. The following is an example log excerpt: Assume that data is stored in the  InnoDB  storage engine, which has support for transactions and automatic crash recovery. Assume also that the MySQL server is under load at the time of the crash. If it were not, no recovery would ever be needed. The example commands do not include options such as  --user  and  --password  for the  mysqldump and  mysql  client programs. You should include such options as necessary to enable client programs to connect to the MySQL server. This section discusses a procedure for performing backups that enables you to recover data after several types of crashes: Similar snapshot capabilities may be available in other file systems, such as LVM or ZFS. If you are using a Veritas file system, 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. •Operating system crash •Power failure •File system crash •Hardware problem (hard drive, motherboard, and so forth)