MySQL 备份和恢复

方案：

Full backups are necessary, but it is not always convenient to create them. They produce large backup files and take time to generate. They are not optimal in the sense that each successive full backup includes all data, even that part that has not changed since the previous full backup. It is more efficient to make an initial full backup, and then to make incremental backups. The incremental backups are smaller and take less time to produce. The tradeoff is that, at recovery time, you cannot restore your data just by reloading the full backup. You must also process the incremental backups to recover the incremental changes.

 

 

步骤：

http://dev.mysql.com/doc/refman/5.6/en/backup-policy.html

http://dev.mysql.com/doc/refman/5.6/en/recovery-from-backups.html

1，启用bin-log

关闭数据库，编辑/usr/my.cnf,my.ini，增加配置:

[mysqld]
log-bin=mysql-bin
# server-id=1 # replication时用到

innodb_flush_log_at_trx_commit=1

sync_binlog=1

重启mysql

 

2，给当前数据库做一个full backup

首先查看mysql的data目录，看看有没有以mysql-bin.000* 开头的文件，这些都是bin-log，记下当前最大的number号，如：mysql-bin.000005

 

执行full backup

shell> mysqldump --single-transaction --flush-logs --master-data=2 
         --all-databases > backup_sunday_1_PM.sql

执行完之后，目录下多了一个bin-log文件：mysql-bin.000006，因为--flush-logs会让mysql flush，

--master-data=2会在输出的sql文件中添加下面2行注释，（在replication中，你可以拿上面的.sql去初始化slave数据库，然后告诉slave从master上mysql-bin.000006文件的位置4开始replication；当然，如果你配置好了master，你也可以执行SHOW MASTER STATUS获取这些信息）

-- Position to start replication or point-in-time recovery from
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000006',MASTER_LOG_POS=4; 

• 输出的.sql文件包含mysql-bin.000006之前所有的数据库改变。

• 在这次full backup之后，所有的数据库改变都将会记录到mysql-bin.000006以及后续number的bin-log中。

3，定期增量备份

保存好上面的.sql文件，以后每天使用以下命令做一个增量备份，

shell> mysqladmin flush-logs

就会生成mysql-bin.000007文件，当天所有的log都在mysql-bin.000006中，把mysql-bin.000006文件保存好。

明天再执行上面的命令生成08文件，明天所有的log都07中，把07文件保存好。

 

4，Recovery

当有一天，你的数据库服务器down掉了或者磁盘坏了，你需要restore数据库时，先执行上面的.sql文件，然后从mysql-bin.000006文件开始，依次按照序号restore数据即可。

shell> mysql < backup_sunday_1_PM.sql

shell> mysqlbinlog mysql-bin.000006 mysql-bin.000007 | mysql

 

5，查看某个bin-log文件中的sql语句，sql执行时间，postion等信息

shell> mysqlbinlog mysql-bin.000006 | more

shell> mysqlbinlog mysql-bin.000006 > temp.sql

# at 199

#141213  3:12:15 server id 1
use `test`/*!*/;
SET TIMESTAMP=1418469135/*!*/;
insert into user_list (name, age) values ('haha', 50)

 

6，按时间点恢复

http://dev.mysql.com/doc/refman/5.6/en/point-in-time-recovery-times.html

shell> mysqlbinlog --stop-datetime="2012-12-12 12:12:12" mysql-bin.000006 | mysql

shell> mysqlbinlog --start-datetime="2012-12-12 12:12:12" mysql-bin.000006 | mysql

 

7，按postion恢复

http://dev.mysql.com/doc/refman/5.6/en/point-in-time-recovery-positions.html

shell> mysqlbinlog --stop-position=120 mysql-bin.000006 | mysql

shell> mysqlbinlog --start-position=100 mysql-bin.000006 | mysql

 

8，参考资料：

http://dev.mysql.com/doc/refman/5.6/en/backup-policy.html

http://dev.mysql.com/doc/refman/5.6/en/recovery-from-backups.html

http://dev.mysql.com/doc/refman/5.6/en/point-in-time-recovery-times.html

http://dev.mysql.com/doc/refman/5.6/en/point-in-time-recovery-positions.html

 

9，