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)

至此備份恢復實驗完成；