mysqldump & binlog做完全备份

注：mysqldump备份数据库实验（本文有两种方法实现备份，本实验使用的法1）

步骤：

1.用mysqldump对数据库完全备份

法1：

# mysqldump --all-databases --lock-all-tables --master-data=2 --flush-logs > /backup/all-`date +%F`.sql

锁定表后滚动binlog数据文件，然后再备份数据库，下次增量备份时只需要从新创建的binlog文件开始即可

或者：

法2：

# mysqldump --all-databases --lock-all-tables --master-data=2 > /backup/all-`date +%F`.sql

并打开备份的文件，查看这一行：

-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000010', MASTER_LOG_POS=8230;

找到MASTER_LOG_POS=8230；代表着备份的结束事务点为8230，则增量备份需要从8230开始记录备份；

2.尝试着修改数据库中的数据，新建text2表，并插入一行数据（模拟线上运行新增数据）

MariaDB [hellodb]> create table test2(id int);

Query OK, 0 rows affected (0.09 sec)

MariaDB [hellodb]> insert into test2 values (1);

Query OK, 1 row affected (0.04 sec)

3.使用mysqlbinlog命令做增量备份

法1：

# mysqlbinlog mysql-bin.000011 > /backup/zlbf-`date +%F`.sql

或者：

法2：

# mysqlbinlog --start-position=8230 mysql-bin.000010 > /backup/zlbf-`date +%F`.sql

4.再次尝试着修改数据库中的数据：删除text1表（作为未来得及备份的数据）

MariaDB [hellodb]> show tables;

+-------------------+

| Tables_in_hellodb |

+-------------------+

| classes           |

| coc               |

| courses           |

| scores            |

| students          |

| teachers          |

| test2             |

| text1             |

| toc               |

+-------------------+

9 rows in set (0.00 sec)

MariaDB [hellodb]> drop table text1;

Query OK, 0 rows affected (0.17 sec)

5.删除hellodb数据库，测试数据恢复

MariaDB [hellodb]> drop database hellodb;

Query OK, 8 rows affected (0.07 sec)

6.导出，未来得及备份的binlog文件

# mysqlbinlog mysql-bin.000011 > /backup/binlog.`date +%F`.sql

并修改导出的binlog文件，删除掉末尾导致数据库删除的sql语句

#vim binlog.2015-04-09.sql

# at 355

#150409  0:15:01 server id 1  end_log_pos 442   Query   thread_id=6104  exec_time=0     error_code=0

SET TIMESTAMP=1428509701/*!*/;

drop database hellodb          （删除此行的误操作sql语句）

/*!*/;

DELIMITER ;

# End of log file

7.开始还原

首先还原完全备份：

# mysql < all-2015-04-08.sql

查看还原的数据：（可以看见完全备份的数据已然恢复回来）

MariaDB [(none)]> use hellodb;

Database changed

MariaDB [hellodb]> show tables;

+-------------------+

| Tables_in_hellodb |

+-------------------+

| classes           |

| coc               |

| courses           |

| scores            |

| students          |

| teachers          |

| text1             |

| toc               |

+-------------------+

8 rows in set (0.02 sec)

接着还原增量备份：

# mysql < zlbf-2015-04-09.sql

查看还原的数据：（可以看到增量备份以前的数据也已经恢复）

MariaDB [(none)]> use hellodb;

Database changed

MariaDB [hellodb]> show tables;

+-------------------+

| Tables_in_hellodb |

+-------------------+

| classes           |

| coc               |

| courses           |

| scores            |

| students          |

| teachers          |

| test2             |

| text1             |

| toc               |

+-------------------+

9 rows in set (0.03 sec)

接着通过binlog文件恢复未备份的数据：

# mysql < binlog.2015-04-09.sql

查看还原的数据：（未备份时候删除的text1表也已经成功删除，可以看到未备份的数据也成功恢复）

MariaDB [(none)]> use hellodb;

Database changed

MariaDB [hellodb]> show tables;

+-------------------+

| Tables_in_hellodb |

+-------------------+

| classes           |

| coc               |

| courses           |

| scores            |

| students          |

| teachers          |

| test2             |

| toc               |

+-------------------+

8 rows in set (0.00 sec)

至此备份恢复实验完成；