7.3 备份与恢复机制实例¶

以下几种类型崩溃后需要进行备份恢复:
- 操作系统崩溃
- 电源断开
- 文件系统崩溃
- 硬件问题(硬件驱动、主板等)

以InnoDB来说，出现如下崩溃，MySQL会自动恢复:

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

建立一个备份机制¶

备份操作:
- 命令:
  shell> mysqldump --single-transaction --all-databases > backup_date_1_pm.sql
- 选项 --single-transaction: 对InnoDB来说，一致性读，确保由 mysqldump 看到的数据不会改变(其他客户端引起的改变不会被 mysqldump 看到)。对非事务性表来说(如MyISAM)，一致性需要在备份的过程中不能修改。对MyISAM类型表来说，对MySQL帐号一定不能有管理性改变。
全局备份是必要的，但有时不方便。它会生成很大的备份文件而且执行时间很长。最好的方法是做一个初使化的全备份、然后其余的做增量备份。
每次重启服务器时都会生成一个新的binary log file。

如下命令可以让服务器在不中断的情况下，生成新的binary log file:

mysql> flush logs;
or
shell> mysqladmin flush-logs
or
shell> mysqldump --flush-logs... ...

二进制日志文件列表:

-rw-rw---- 1 guilhem  guilhem   1277324 Nov 10 23:59 gbichot2-bin.000001
-rw-rw---- 1 guilhem  guilhem         4 Nov 10 23:59 gbichot2-bin.000002
-rw-rw---- 1 guilhem  guilhem        79 Nov 11 11:06 gbichot2-bin.000003
-rw-rw---- 1 guilhem  guilhem       508 Nov 11 11:08 gbichot2-bin.000004
-rw-rw---- 1 guilhem  guilhem 220047446 Nov 12 16:47 gbichot2-bin.000005
-rw-rw---- 1 guilhem  guilhem    998412 Nov 14 10:08 gbichot2-bin.000006
-rw-rw---- 1 guilhem  guilhem       361 Nov 14 10:07 gbichot2-bin.index

说明:

每次gbichot2-bin.xxxxxxxx文件都会增加一个

二进制日志文件占用空间很大，有时需要删除日志文件:

shell> mysqldump --single-transaction --flush-logs --master-data=2 \
     --all-databases --delete-master-logs > backup_sunday_1_PM

注意:
使用命令 mysqldump --delete-master-logs 删除是非常危险的事！一定要小心！

使用备份文件恢复¶

把最后一次全备份恢复:
```
shell> mysql < backup_sunday_1_PM.sql
```

进行多次增量备份恢复:

shell> mysqlbinlog gbichot2-bin.000007 gbichot2-bin.000008 | mysql

备份机制总结¶

一定在运行mysql服务时加上 --log-bin 选项、甚至加上 --log-bin=log_name 选项(如果你有可靠的介质)！
使用命令 mysqldump 命令进行定期的全文备份
通过刷新日志的方式定期进行增量备份(使用命令 FLUSH LOGS or mysqladmin flush-logs)