免費(fèi)AI編程助手
MySQL備份與還原
防偽碼：我依舊相信努力的意義，奮斗的價(jià)值，因?yàn)槟鞘潜举|(zhì)問(wèn)題。
一、mysqldump 備份結(jié)合 binlog 日志恢復(fù)
MySQL 備份一般采取全庫(kù)備份加日志備份的方式，例如每天執(zhí)行一次全備份，每小時(shí)執(zhí)行一
次二進(jìn)制日志備份。這樣在 MySQL 故障后可以使用全備份和日志備份將數(shù)據(jù)恢復(fù)到最后一個(gè)
二進(jìn)制日志備份前的任意位置或時(shí)間。
1、binlog介紹
mysql 的二進(jìn)制日志記錄著該數(shù)據(jù)庫(kù)的所有增刪改的操作日志(前提是要在自己的服務(wù)器上
開(kāi)啟 binlog)，還包括了這些操作的執(zhí)行時(shí)間。為了顯示這些二進(jìn)制內(nèi)容，我們可以使用
mysqlbinlog 命令來(lái)查看。
Binlog 的用途
1：主從同步
2：恢復(fù)數(shù)據(jù)庫(kù)
開(kāi)啟 binary log 功能
通過(guò)編輯 my.cnf 中的 log-bin 選項(xiàng)可以開(kāi)啟二進(jìn)制日志；形式如下：
log-bin [=DIR/[filename]]
其中，DIR 參數(shù)指定二進(jìn)制文件的存儲(chǔ)路徑；filename 參數(shù)指定二級(jí)制文件的文件名，其形
式為 filename.number，number 的形式為 000001、000002 等。每次重啟 mysql 服務(wù)或運(yùn)行
mysql> flush logs;都會(huì)生成一個(gè)新的二進(jìn)制日志文件，這些日志文件的 number 會(huì)不斷地遞增。
除了生成上述的文件外還會(huì)生成一個(gè)名為 filename.index 的文件。這個(gè)文件中存儲(chǔ)所有二進(jìn)
制日志文件的清單又稱(chēng)為二進(jìn)制文件的索引
配置保存以后重啟 mysql 的服務(wù)器，用 mysql> show variables like 'log_bin';查看 bin-log 是否
開(kāi)啟，如圖：
查看產(chǎn)生的 binary log 注：查看 binlog 內(nèi)容是為了恢復(fù)數(shù)據(jù)
bin-log 因?yàn)槭嵌M(jìn)制文件，不能通過(guò)文件內(nèi)容查看命令直接打開(kāi)查看，mysql 提供兩種方式
查看方式，在介紹之前，我們先對(duì)數(shù)據(jù)庫(kù)進(jìn)行一下增刪改的操作，否則 log 里邊數(shù)據(jù)有點(diǎn)空。
#mysql -uroot -p -e "reset master"
#mysql -uroot -p -e "create database test"
#mysql -uroot -p -e "use test;create table tb1(id int primary key auto_increment,name
varchar(20))"
#mysql -uroot -p -e "insert into test.tb1(name) values('lisi')"
#mysql -uroot -p -e "insert into test.tb1(name) values('zhangsan')"
重新開(kāi)始一個(gè)新的日志文件
#mysql -uroot -p -e "flush logs"
#mysql -uroot -p -e "delete from test.tb1 where id=2"
#mysql -uroot -p -e "insert into test.tb1(name) values('tom')"
# mysql -uroot -p -e "select * from test.tb1"
Enter password:
+----+------+
| id | name |
+----+------+
| 1 | lisi |
| 3 | tom |
+----+------+
查看 MySQL Server 上的二進(jìn)制日志
mysql> show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 1087 |
| mysql-bin.000002 | 673 |
+------------------+-----------+
查看二進(jìn)制日志信息的命令：
語(yǔ)法格式：SHOW BINLOG EVENTS [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count]
查看二進(jìn)制日志中的事件
mysql> show binlog events;
默認(rèn)顯示可找到的第一個(gè)二進(jìn)制日志文件中的事件，包含了日志文件名、事件的開(kāi)始位置、
事件類(lèi)型、結(jié)束位置、信息等內(nèi)容
+------------------+------+----------------+-----------+-------------+----------------------------------------------------------------------------
------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info
|
+------------------+------+----------------+-----------+-------------+----------------------------------------------------------------------------
------+
| mysql-bin.000001 | 4 |Format_desc | 1 | 123 | Server ver: 5.7.13-log, Binlog ver:
4 | //此 事件為格式描述事件
| mysql-bin.000001 | 123 | Previous_gtids | 1 | 154 |
|
| mysql-bin.000001 | 154 | Anonymous_Gtid | 1 | 219 | SET @@SESSION.GTID_NEXT=
'ANONYMOUS' |
| mysql-bin.000001 | 219 | Query | 1 | 313 | create database test
| // 為查詢(xún)事件
| mysql-bin.000001 | 313 | Anonymous_Gtid | 1 | 378 | SET @@SESSION.GTID_NEXT=
'ANONYMOUS' |
| mysql-bin.000001 | 378 | Query | 1 | 520 | use `test`; create table tb1(id int
primary key auto_increment,name varchar(20)) |
| mysql-bin.000001 | 520 | Anonymous_Gtid | 1 | 585 | SET @@SESSION.GTID_NEXT=
'ANONYMOUS' |
| mysql-bin.000001 | 585 | Query | 1 | 653 | BEGIN
| // 為查詢(xún)事件，事務(wù)開(kāi)始
| mysql-bin.000001 | 653 | Table_map | 1 | 702 | table_id: 110 (test.tb1)
| // 為表映射事件
| mysql-bin.000001 | 702 | Write_rows | 1 | 747 | table_id: 110 flags: STMT_END_F
| //為我們執(zhí)行的 insert 事件
| mysql-bin.000001 | 747 | Xid | 1 | 778 | COMMIT /* xid=2052 */
| // Xid 時(shí)間是自動(dòng)提交事務(wù)的動(dòng)作
| mysql-bin.000001 | 778 | Anonymous_Gtid | 1 | 843 | SET @@SESSION.GTID_NEXT=
'ANONYMOUS' |
| mysql-bin.000001 | 843 | Query | 1 | 911 | BEGIN
|
| mysql-bin.000001 | 911 | Table_map | 1 | 960 | table_id: 110 (test.tb1)
|
| mysql-bin.000001 | 960 | Write_rows | 1 | 1009 | table_id: 110 flags: STMT_END_F
|
| mysql-bin.000001 | 1009 | Xid | 1 | 1040 | COMMIT /* xid=2055 */
|
| mysql-bin.000001 | 1040 | Rotate | 1 | 1087 | mysql-bin.000002;pos=4
| // 為日志輪換事件，是我們執(zhí)行 flush logs 開(kāi)啟新日志文件引起的。
查看指定的二進(jìn)制日志中的事件
mysql> show binlog events in 'mysql-bin.000002';
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info
|
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
| mysql-bin.000002 | 4 | Format_desc | 1 | 123 | Server ver:
5.7.13-log, Binlog ver: 4 |
| mysql-bin.000002 | 123 | Previous_gtids | 1 | 154 |
|
| mysql-bin.000002 | 154 | Anonymous_Gtid | 1 | 219 | SET
@@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000002 | 219 | Query | 1 | 287 | BEGIN
|
| mysql-bin.000002 | 287 | Table_map | 1 | 336 | table_id: 110
(test.tb1) |
| mysql-bin.000002 | 336 | Delete_rows | 1 | 385 | table_id: 110 flags:
STMT_END_F |
| mysql-bin.000002 | 385 | Xid | 1 | 416 | COMMIT /*
xid=2068 */ |
| mysql-bin.000002 | 416 | Anonymous_Gtid | 1 | 481 | SET
@@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000002 | 481 | Query | 1 | 549 | BEGIN
|
| mysql-bin.000002 | 549 | Table_map | 1 | 598 | table_id: 110
(test.tb1) |
| mysql-bin.000002 | 598 | Write_rows | 1 | 642 | table_id: 110 flags:
STMT_END_F |
| mysql-bin.000002 | 642 | Xid | 1 | 673 | COMMIT /*
xid=2071 */ |
該命令還包含其他選項(xiàng)以便靈活查看
mysql> show binlog events in 'mysql-bin.000002' from 219 limit 1,3;
+------------------+-----+-------------+-----------+-------------+---------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info
|
+------------------+-----+-------------+-----------+-------------+---------------------------------+
| mysql-bin.000002 | 287 | Table_map | 1 | 336 | table_id: 110 (test.tb1)
|
| mysql-bin.000002 | 336 | Delete_rows | 1 | 385 | table_id: 110 flags:
STMT_END_F |
| mysql-bin.000002 | 385 | Xid | 1 | 416 | COMMIT /* xid=2068
*/ |
SHOW BINARY LOGS 等價(jià)于 SHOW MASTER LOGS
PURGE BINARY LOGS 用于刪除二進(jìn)制日志，如：
PURGE BINARY LOGS TO 'mysql-bin.00010'; //把這個(gè)文件之前的其他文件都刪除掉
PURGE BINARY LOGS BEFORE '2016-08-28 22:46:26';//把指定時(shí)間之前的二進(jìn)制文件
刪除了
RESET MASTER 與 RESET SLAVE
前者清空 index 文件中列出的所有二進(jìn)制日志，重置 index 文件為空，并創(chuàng)建一個(gè)新的二進(jìn)
制日志文件，一般用于 MASTER 首次啟動(dòng)時(shí)。后者使 SLAVE 忘記其在 MASTER 二進(jìn)制日
志文件中的復(fù)制位置，它會(huì)刪除 master.info、relay-log.info 和所有中繼日志文件并開(kāi)始一
個(gè)新的中繼日志文件，以便于開(kāi)始一個(gè)干凈的復(fù)制。在使用 RESET SLAVE 前需先關(guān)閉
SLAVE 復(fù)制線(xiàn)程。
上述方式可以查看到服務(wù)器上存在的二進(jìn)制日志文件及文件中的事件，但是想查看到文件中
具體的內(nèi)容并應(yīng)于恢復(fù)場(chǎng)景還得借助 mysqlbinlog 這個(gè)工具。
語(yǔ)法格式： mysqlbinlog [options] log_file ...
輸出內(nèi)容會(huì)因日志文件的格式以及 mysqlbinlog 工具使用的選項(xiàng)不同而略不同。
mysqlbinlog 的可用選項(xiàng)可參考 man 手冊(cè)。
二進(jìn)制日志文件的格式包含行模式、語(yǔ)句模式和混合模式（也即有服務(wù)器決定在什么情況下
記錄什么類(lèi)型的日志），基于語(yǔ)句的日志中事件信息包含執(zhí)行的語(yǔ)句等，基于行的日志中事
件信息包含的是行的變化信息等?；旌夏Ｊ降娜罩局袃煞N類(lèi)型的事件信息都會(huì)記錄。
為了便于查看記錄了行變化信息的事件在當(dāng)時(shí)具體執(zhí)行了什么樣的 SQL 語(yǔ)句可以使用
mysqlbinlog 工具的-v（--verbose）選項(xiàng)，該選項(xiàng)會(huì)將行事件重構(gòu)成被注釋掉的偽 SQL 語(yǔ)句，
如果想看到更詳細(xì)的信息可以將該選項(xiàng)給兩次如-vv，這樣可以包含一些數(shù)據(jù)類(lèi)型和元信息
的注釋內(nèi)容，如
先切換到 binlog 所在的目錄下
#mysqlbinlog mysql-bin.000001
#mysqlbinlog -v mysql-bin.000001
#mysqlbinlog -vv mysql-bin.000001
另外 mysqlbinlog 和可以通過(guò)--read-from-remote-server 選項(xiàng)從遠(yuǎn)程服務(wù)器讀取二進(jìn)制日志文
件，這時(shí)需要一些而外的連接參數(shù)，如-h，-P，-p，-u 等，這些參數(shù)僅在指定了
--read-from-remote-server 后有效。
無(wú)論是本地二進(jìn)制日志文件還是遠(yuǎn)程服務(wù)器上的二進(jìn)制日志文件，無(wú)論是行模式、語(yǔ)句模式
還是混合模式的二進(jìn)制日志文件，被 mysqlbinlog 工具解析后都可直接應(yīng)用與 MySQL Server
進(jìn)行基于時(shí)間點(diǎn)、位置或數(shù)據(jù)庫(kù)的恢復(fù)。
未經(jīng)允許不得轉(zhuǎn)載傳播--陳英宏
博客地址：hongge.blog.51cto.com
下面我們就來(lái)演示如何使用 binlog 恢復(fù)之前刪除數(shù)據(jù)（id=2 那條記錄）
注意：在實(shí)際生產(chǎn)環(huán)境中，如果遇到需要恢復(fù)數(shù)據(jù)庫(kù)的情況，不要讓用戶(hù)能訪問(wèn)到數(shù)據(jù)庫(kù)，
以避免新的數(shù)據(jù)插入進(jìn)來(lái)，以及在主從的環(huán)境下，關(guān)閉主從。
查看 binlog 文件，從中找出 delete from test.tb1 where id=2
# cd /usr/local/mysql/data/
# mysqlbinlog -v mysql-bin.000002
顯示結(jié)果
# at 219
#160913 20:59:51 server id 1 end_log_pos 287 CRC32 0x1a97741b Query thread_id=42
exec_time=0 error_code=0
SET TIMESTAMP=1473771591/*!*/;
SET @@session.pseudo_thread_id=42/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0,
@@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1075838976/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8 *//*!*/;
SET
@@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_
server=33/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
BEGIN
/*!*/;
# at 287
#160913 20:59:51 server id 1 end_log_pos 336 CRC32 0x930ab248 Table_map: `test`.`tb1`
mapped to number 125
# at 336
#160910 23:17:43 server id 1 end_log_pos 385 CRC32 0xdede3eb7 Delete_rows: table id 110
flags: STMT_END_F
BINLOG '
FyTUVxMBAAAAMQAAAFABAAAAAG4AAAAAAAEABHRlc3QAA3RiMQACAw8CPAAC2t7UdQ==
FyTUVyABAAAAMQAAAIEBAAAAAG4AAAAAAAEAAgAC//wCAAAACHpoYW5nc2Futz7e3g==
'/*!*/;
### DELETE FROM `test`.`tb1`
### WHERE
### @1=2
### @2='zhangsan'
# at 385
#160910 23:17:43 server id 1 end_log_pos 416 CRC32 0x7881c9da Xid = 2068
COMMIT/*!*/;
從中可以看出 delete 事件發(fā)生 position 是 287，事件結(jié)束 position 是 416
恢復(fù)流程：直接用 bin-log 日志將數(shù)據(jù)庫(kù)恢復(fù)到刪除位置 287 前,然后跳過(guò)故障點(diǎn),再進(jìn)行恢復(fù)
下面所有的操作,命令如下
由于之前沒(méi)有做過(guò)全庫(kù)備份，所以要使用所有 binlog 日志恢復(fù)，所以生產(chǎn)環(huán)境中需要很長(zhǎng)時(shí)
間恢復(fù)，導(dǎo)出相關(guān) binlog 文件
#mysqlbinlog /usr/local/mysql/data/mysql-bin.000001 > /opt/mysql-bin.000001.sql
#mysqlbinlog --stop-position=287 /usr/local/mysql/data/mysql-bin.000002 > /opt/287.sql
#mysqlbinlog --start-position=416 /usr/local/mysql/data/mysql-bin.000002 > /opt/416.sql
刪除 test 數(shù)據(jù)庫(kù)
mysql>drop database test;
利用 binlog 恢復(fù)數(shù)據(jù)
#mysql -uroot -p123456< /opt/mysql-bin.000001.sql
#mysql -uroot -p123456< /opt/287.sql
#mysql -uroot -p123456< /opt/416.sql
恢復(fù)完成后，我們檢查下表的數(shù)據(jù)是否完整
mysql> select * from test.tb1;
+----+----------+
| id | name |
+----+----------+
| 1 | lisi |
| 2 | zhangsan |
| 3 | tom |
+----+----------+
Ok 完整的都恢復(fù)過(guò)來(lái)了
mysqlbinlog 選項(xiàng)示例
常見(jiàn)的選項(xiàng)有以下幾個(gè)：
--start-datetime
從二進(jìn)制日志中讀取指定時(shí)間戳或者本地計(jì)算機(jī)時(shí)間之后的日志事件。
--stop-datetime
從二進(jìn)制日志中讀取指定時(shí)間戳或者本地計(jì)算機(jī)時(shí)間之前的日志事件。
--start-position
從二進(jìn)制日志中讀取指定 position 事件位置作為開(kāi)始。
--stop-position
從二進(jìn)制日志中讀取指定 position 事件位置作為事件截至。
2、mysqldump 介紹
mysqldump 是 mysql 用于備份和數(shù)據(jù)轉(zhuǎn)移的一個(gè)工具。它主要產(chǎn)生一系列的 SQL 語(yǔ)句，可以
封裝到文件，該文件包含有所有重建你的數(shù)據(jù)庫(kù)所需要的 SQL 命令如 CREATE DATABASE，
CREATE TABLE，INSERT 等等。可以用來(lái)實(shí)現(xiàn)輕量級(jí)的快速遷移或恢復(fù)數(shù)據(jù)庫(kù)。
mysqldump 是將數(shù)據(jù)表導(dǎo)成 SQL 腳本文件，在不同的 MySQL 版本之間升級(jí)時(shí)相對(duì)比較合適，
這也是最常用的備份方法。
mysqldump 一般在數(shù)據(jù)量很小的時(shí)候（幾個(gè) G）可以用于備份。當(dāng)數(shù)據(jù)量比較大的情況下，
就不建議用 mysqldump 工具進(jìn)行備份了。
數(shù)據(jù)庫(kù)的導(dǎo)出
導(dǎo)出對(duì)象說(shuō)明：
mysqldump 可以針對(duì)單個(gè)表、多個(gè)表、單個(gè)數(shù)據(jù)庫(kù)、多個(gè)數(shù)據(jù)庫(kù)、所有數(shù)據(jù)庫(kù)進(jìn)行導(dǎo)出的操
作
# mysqldump [options] db_name [tbl_name ...] //導(dǎo)出指定數(shù)據(jù)庫(kù)或單個(gè)表
# mysqldump [options] --databases db_name ... //導(dǎo)出多個(gè)數(shù)據(jù)庫(kù)
#mysqldump [options] --all-databases //導(dǎo)出所有
導(dǎo)出數(shù)據(jù)庫(kù) test
# mysqldump -uroot -p --flush-logs test > /opt/test.sql //--flush-logs 這個(gè)選項(xiàng)就會(huì)完整備份
的時(shí)候重新開(kāi)啟一個(gè)新 binlog
數(shù)據(jù)庫(kù)的導(dǎo)入
# mysql -uroot -p test < /opt/test.sql
在前面我們介紹了 mysql 的 binlog 和 mysqldump 工具，下面我們來(lái)學(xué)習(xí)如何實(shí)現(xiàn) mysqldump
全庫(kù)備份+binlog 的數(shù)據(jù)恢復(fù)
環(huán)境準(zhǔn)備與備份還原：
檢查開(kāi)啟 binlog
先創(chuàng)建一些原始數(shù)據(jù)
mysql> reset master;
mysql> create database test_db;
mysql> use test_db;
mysql> create table tb1(id int primary key auto_increment,name varchar(20));
mysql> insert into tb1(name) values('tom1');
mysql> insert into tb1(name) values('tom2');
mysql> commit;
mysql> select * from tb1;
+----+------+
| id | name |
+----+------+
| 1 | tom1 |
| 2 | tom2 |
+----+------+
方案：mysqldump 全庫(kù)備份+binlog 還原
1、mysqldump 備份方案：
每周一凌晨 1 點(diǎn)全庫(kù)備份
2、備份步驟
（1） 創(chuàng)建備份目錄
# mkdir /opt/mysqlbackup
# mkdir /opt/mysqlbackup/daily
（2）全庫(kù)備份
這里我們模擬周一的完整備份數(shù)據(jù)庫(kù)任務(wù)
#mysqldump -uroot -p --flush-logs test_db > /opt/mysqlbackup/test_db_2016_09_12.sql
[root@localhost data]# ls -l /opt/mysqlbackup/
-rw-r--r--. 1 root root 1871 Sep 13 21:06 test_db_2016_09_12.sql
備份 mysqldump 全庫(kù)備份之前的 binlog 日志文（注：生產(chǎn)環(huán)境中可能不只一個(gè) binlog 文件）
# cp /usr/local/mysql/data/mysql-bin.000001 /opt/mysqlbackup/daily/
# mysql -uroot -p -e "purge binary logs to 'mysql-bin.000002'"
模擬下操作失誤,將數(shù)據(jù)修改錯(cuò)誤了。
mysql> use test_db;
mysql> delete from tb1 where id=1;
mysql> commit;
mysql> insert into tb1(name) values('tom3');
mysql> commit;
備份自 mysqldump 之后的 binlog 日志文件
cp /usr/local/mysql/data/mysql-bin.000002 /opt/mysqlbackup/daily/
上面的模擬的誤操作是刪除了 id=1 的記錄
（3）現(xiàn)在我們使用 mysqldump 的全庫(kù)備份和 binlog 來(lái)恢復(fù)數(shù)據(jù)。
使用 mysqldump 的備份進(jìn)行全庫(kù)恢復(fù)
# mysql -uroot -p test_db < /opt/mysqlbackup/test_db_2016_09_12.sql
查詢(xún)一下數(shù)據(jù)
[root@localhost ~]# mysql -uroot -p -e "select * from test_db.tb1"
Enter password:
+----+------+
| id | name |
+----+------+
| 1 | tom1 |
| 2 | tom2 |
+----+------+
從顯示結(jié)果可以看到使用 mysqldump 備份將數(shù)據(jù)還原到了備份時(shí)的狀態(tài)，剛才刪除的數(shù)據(jù)
（id=2）恢復(fù)回來(lái)了，但備份后產(chǎn)生的數(shù)據(jù)卻丟失了所以還得利用 binlog 進(jìn)一步不原
因?yàn)閯h除是在全庫(kù)備份后發(fā)生的，而 mysqldump 全庫(kù)備份時(shí)使用--flush-logs 選項(xiàng)，所以只需
要分析全庫(kù)備份后的 binlog 即 mysql-bin.000002。
mysql> show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000002 | 1853 |
+------------------+-----------+
查看 mysql-bin.000002 中的事件，可以看到有刪除事件
mysql> show binlog events in 'mysql-bin.000002';
| mysql-bin.000002 | 219 | Query | 1 | 294 | BEGIN
|
| mysql-bin.000002 | 294 | Table_map | 1 | 346 | table_id: 118
(test_db.tb1)
|
| mysql-bin.000002 | 346 | Delete_rows | 1 | 391 | table_id: 118
flags: STMT_END_F
|
| mysql-bin.000002 | 391 | Xid | 1 | 422 | COMMIT /*
xid=2739 */
使用 mysqlbinlog 命令可以查看備份的 binlog 文件的詳細(xì)事件。
恢復(fù)流程：我們直接用 bin-log 日志將數(shù)據(jù)庫(kù)恢復(fù)到刪除位置前,然后跳過(guò)故障點(diǎn),再進(jìn)行恢復(fù)
刪除后的所有操作。
# mysqlbinlog -v /opt/mysqlbackup/daily/mysql-bin.000002
我們先用 mysqlbinlog 命令找到 delete 那條語(yǔ)句的位置
# at 219
#160911 17:19:55 server id 1 end_log_pos 294 CRC32 0x84590493 Query thread_id=66
exec_time=0 error_code=0
SET TIMESTAMP=1473585595/*!*/;
SET @@session.pseudo_thread_id=66/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0,
@@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1075838976/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8 *//*!*/;
SET
@@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_
server=33/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
BEGIN
/*!*/;
# at 294
#160911 17:19:55 server id 1 end_log_pos 346 CRC32 0x5cdccf9e Table_map: `test_db`.`tb1`
mapped to number 118
# at 346
#160911 17:19:55 server id 1 end_log_pos 391 CRC32 0x320c4935 Delete_rows: table id
118 flags: STMT_END_F
BINLOG '
uyHVVxMBAAAANAAAAFoBAAAAAHYAAAAAAAEAB3Rlc3RfZGIAA3RiMQACAw8CPAACns/cXA==
uyHVVyABAAAALQAAAIcBAAAAAHYAAAAAAAEAAgAC//wBAAAABHRvbTE1SQwy
'/*!*/;
### DELETE FROM `test_db`.`tb1`
### WHERE
### @1=1
### @2='tom1'
# at 391
#160911 17:19:55 server id 1 end_log_pos 422 CRC32 0x5e4a6699 Xid = 2739
COMMIT/*!*/;
通過(guò) mysqlbinlog 命令所顯示的結(jié)果可以看到誤操作 delete 的開(kāi)始 postion 為 219，結(jié)束
position 是 422。
從二進(jìn)制日志中讀取指定 position=219 事件位置作為截至，即把數(shù)據(jù)恢復(fù)到 delete 刪除前
# mysqlbinlog --stop-position=219 /opt/mysqlbackup/daily/mysql-bin.000002 | mysql -u root -p
從二進(jìn)制日志中讀取指定 position=422 事件位置作為開(kāi)始，即跳過(guò)刪除事件，恢復(fù)刪除事件
之后對(duì)數(shù)據(jù)的正常操作
#mysqlbinlog --start-position=422 /opt/mysqlbackup/daily/mysql-bin.000002 | mysql -u root -p
查看恢復(fù)結(jié)果：
# mysql -uroot -p -e "select * from test_db.tb1"
Enter password:
+----+------+
| id | name |
+----+------+
| 1 | tom1 |
| 2 | tom2 |
| 3 | tom3 |
+----+------+
從上面顯示可以看出數(shù)據(jù)恢復(fù)到正常狀態(tài)
生產(chǎn)環(huán)境中 Mysql 數(shù)據(jù)庫(kù)的備份是周期性重復(fù)的操作，所以通常是要編寫(xiě)腳本實(shí)現(xiàn)，通過(guò)
crond 計(jì)劃任務(wù)周期性執(zhí)行備份腳本
mysqldump 備份方案：
周日凌晨 1 點(diǎn)全庫(kù)備份
周一到周六凌晨每隔 4 個(gè)小時(shí)增量備份一次
設(shè)置 crontab 任務(wù)，每天執(zhí)行備份腳本
# crontab –e
#每個(gè)星期日凌晨 1:00 執(zhí)行完全備份腳本
0 1 * * 0 /root/mysqlfullbackup.sh >/dev/null 2>&1
#周一到周六每隔 4 個(gè)小時(shí)增量備份一次
0 */4 * * 1-6 /root/mysqldailybackup.sh >/dev/null 2>&1
mysqlfullbackup.sh 腳本內(nèi)容：
[root@localhost ~]# cat mysqlfullbackup.sh
#!/bin/sh
# Name:mysqlFullBackup.sh
# 定義數(shù)據(jù)庫(kù)目錄
mysqlDir=/usr/local/mysql
# 定義用于備份數(shù)據(jù)庫(kù)的用戶(hù)名和密碼
user=root
userpwd=123456
dbname=test_db
# 定義備份目錄
databackupdir=/opt/mysqlbackup
[ ! -d $databackupdir ] && mkdir $databackupdir
# 定義郵件正文文件
emailfile=$databackupdir/email.txt
# 定義郵件地址
email=root@localhost.localdomain
# 定義備份日志文件
logfile=$databackupdir/mysqlbackup.log
DATE=`date -I`
echo "" > $emailfile
echo $(date +"%y-%m-%d %H:%M:%S") >> $emailfile
cd $databackupdir
# 定義備份文件名
dumpfile=mysql_$DATE.sql
gzdumpfile=mysql_$DATE.sql.tar.gz
# 使用 mysqldump 備份數(shù)據(jù)庫(kù)，請(qǐng)根據(jù)具體情況設(shè)置參數(shù)
$mysqlDir/bin/mysqldump -u$user -p$userpwd --flush-logs -x $dbname > $dumpfile
# 壓縮備份文件
if [ $? -eq 0 ]; then
tar czf $gzdumpfile $dumpfile >> $emailfile 2>&1
echo "BackupFileName:$gzdumpfile" >> $emailfile
echo "DataBase Backup Success!" >> $emailfile
rm -f $dumpfile
else
echo "DataBase Backup Fail!" >> $emailfile
fi
# 寫(xiě)日志文件
echo "--------------------------------------------------------" >> $logfile
cat $emailfile >> $logfile
# 發(fā)送郵件通知
cat $emailfile | mail -s "MySQL Backup" $email
mysqldailybackup.sh 腳本內(nèi)容：
[root@localhost ~]# cat mysqldailybackup.sh
#!/bin/sh
# Name:mysqlDailyBackup.sh
# 定義數(shù)據(jù)庫(kù)目錄和數(shù)據(jù)目錄
mysqldir=/usr/local/mysql
datadir=$mysqldir/data
# 定義用于備份數(shù)據(jù)庫(kù)的用戶(hù)名和密碼
user=root
userpwd=123456
# 定義備份目錄，每日備份文件備份到$dataBackupDir/daily
databackupdir=/opt/mysqlbackup
dailybackupdir=$databackupdir/daily
[ ! -d $dailybackupdir ] && mkdir -p $databackupdir/daily
# 定義郵件正文文件
emailfile=$databackupdir/email.txt
# 定義郵件地址
email=root@localhost.localdomain
# 定義日志文件
logfile=$databackupdir/mysqlbackup.log
echo "" > $emailfile
echo $(date +"%y-%m-%d %H:%M:%S") >> $emailfile
#
# 刷新日志，使數(shù)據(jù)庫(kù)使用新的二進(jìn)制日志文件
$mysqldir/bin/mysqladmin -u$user -p$userpwd flush-logs
cd $datadir
# 得到二進(jìn)制日志列表
filelist=`cat mysql-bin.index`
icounter=0
for file in $filelist
do
icounter=`expr $icounter + 1`
done
nextnum=0
ifile=0
for file in $filelist
do
binlogname=`basename $file`
nextnum=`expr $nextnum + 1`
# 跳過(guò)最后一個(gè)二進(jìn)制日志（數(shù)據(jù)庫(kù)當(dāng)前使用的二進(jìn)制日志文件）
if [ $nextnum -eq $icounter ]; then
echo "Skip lastest!" > /dev/null
else
dest=$dailybackupdir/$binlogname
# 跳過(guò)已經(jīng)備份的二進(jìn)制日志文件
if [ -e $dest ]; then
echo "Skip exist $binlogname!" > /dev/null
else
# 備份日志文件到備份目錄
cp $binlogname $dailybackupdir
if [ $? -eq 0 ]; then
ifile=`expr $ifile + 1`
echo "$binlogname backup success!" >> $emailfile
fi
fi
fi
done
if [ $ifile -eq 0 ];then
echo "No Binlog Backup!" >> $emailfile
else
echo "Backup $ifile File(s)." >> $emailfile
echo "Backup MySQL Binlog OK!" >> $emailfile
fi
# 發(fā)送郵件通知
cat $emailfile | mail -s "MySQL Backup" $email
# 寫(xiě)日志文件
echo "--------------------------------------------------------" >> $logfile
cat $emailfile >> $logfile
二、使用 xtrabackup 進(jìn)行 MySQL 數(shù)據(jù)庫(kù)備份
前面介紹 mysqldump 備份方式是采用邏輯備份，其最大的缺陷就是備份和恢復(fù)速度都慢，
對(duì)于一個(gè)小于 50G 的數(shù)據(jù)庫(kù)而言，這個(gè)速度還是能接受的，但如果數(shù)據(jù)庫(kù)非常大，那再使
用 mysqldump 備份就不太適合了。
這時(shí)就需要一種好用又高效的工具，xtrabackup 就是其中一款，號(hào)稱(chēng)免費(fèi)版的 InnoDB
HotBackup。
Xtrabackup 實(shí)現(xiàn)是物理備份，而且是物理熱備
目前主流的有兩個(gè)工具可以實(shí)現(xiàn)物理熱備：ibbackup 和 xtrabackup；ibbackup 是商業(yè)軟件，
需要授權(quán)，非常昂貴。而 xtrabackup 功能比 ibbackup 還要強(qiáng)大，但卻是開(kāi)源的。因此我們
這里就來(lái)介紹 xtrabackup 的使用。
Xtrabackup 提供了兩種命令行工具：
xtrabackup：專(zhuān)用于備份 InnoDB 和 XtraDB 引擎的數(shù)據(jù)；
innobackupex：這是一個(gè) perl 腳本，在執(zhí)行過(guò)程中會(huì)調(diào)用 xtrabackup 命令，這樣用該命令即
可以實(shí)現(xiàn)備份 InnoDB，也可以備份 MyISAM 引擎的對(duì)象。
Xtrabackup 是由 percona 提供的 mysql 數(shù)據(jù)庫(kù)備份工具，特點(diǎn)：
(1)備份過(guò)程快速、可靠；
(2)備份過(guò)程不會(huì)打斷正在執(zhí)行的事務(wù)；
(3)能夠基于壓縮等功能節(jié)約磁盤(pán)空間和流量；
(4)自動(dòng)實(shí)現(xiàn)備份檢驗(yàn)；
(5)還原速度快。
官方鏈接地址：http://www.percona.com/software/percona-xtrabackup；可以下載源碼編譯安
裝，也可以下載適合的 RPM 包或使用 yum 進(jìn)行安裝或者下載二進(jìn)制源碼包。
安裝 xtrabackup
1)下載 xtrabackup
wget https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.4.4/bin
ary/tarball/percona-xtrabackup-2.4.4-Linux-x86_64.tar.gz
2)解壓
# tar zxf percona-xtrabackup-2.4.4-Linux-x86_64.tar.gz
3)進(jìn)入解壓目錄
# cd percona-xtrabackup-2.4.4-Linux-x86_64/
4)復(fù)制 bin 下的所有程序到/usr/bin
[root@localhost percona-xtrabackup-2.4.4-Linux-x86_64]# cp bin/* /usr/bin/
Xtrabackup 中主要包含兩個(gè)工具：
xtrabackup：是用于熱備份 innodb, xtradb 表中數(shù)據(jù)的工具，支持在線(xiàn)熱備份，可以在不
加鎖的情況下備份 Innodb 數(shù)據(jù)表，不過(guò)此工具不能操作 Myisam 引擎表；
innobackupex：是將 xtrabackup 進(jìn)行封裝的 perl 腳本，能同時(shí)處理 Innodb 和 Myisam，
但在處理 Myisam 時(shí)需要加一個(gè)讀鎖。
由于操作 Myisam 時(shí)需要加讀鎖，這會(huì)堵塞線(xiàn)上服務(wù)的寫(xiě)操作，而 Innodb 沒(méi)有這樣的
限制，所以數(shù)據(jù)庫(kù)中 Innodb 表類(lèi)型所占的比例越大，則越有利。
4)安裝相關(guān)插件
#yum install perl-DBI perl-DBD-MySQL perl-Time-HiRes perl-IO-Socket-SSL
perl-TermReadKey.x86_64 perl-Digest-MD5 –y
5)下載 percona-toolkit 并安裝
#wget https://www.percona.com/downloads/percona-toolkit/2.2.19/RPM/percona-toolkit-2
.2.19-1.noarch.rpm
# rpm -vih percona-toolkit-2.2.19-1.noarch.rpm
下面就可以啟動(dòng)備份了
方案：xtrabackup 完全備份+binlog 增量備份
1、備份
創(chuàng)建備份目錄
# mkdir -p /opt/mysqlbackup/{full,inc}
full：全備存放的目錄；inc：增量備份存放的目錄
1)完全備份
基本語(yǔ)法：# innobackupex --user=DBUSER --password=DBUSERPASS /path/to/BACKUP-DIR/
執(zhí)行下面的命令進(jìn)行完全備份：
# innobackupex --user=root --password=123456 /opt/mysqlbackup/full
注： --defaults-file=/etc/my.cnf 指定 mysql 的配置文件 my.cfg，如果指定則必須是第一個(gè)
參數(shù)。
/path/to/BACKUP-DIR/指定備份所存放的目標(biāo)目錄，備份過(guò)程會(huì)創(chuàng)建一個(gè)以當(dāng)時(shí)備份時(shí)間
命名的目錄存放備份文件。
出現(xiàn)如下提示。表示成功
備份后的文件：
在備份的同時(shí)，備份數(shù)據(jù)會(huì)在備份目錄下創(chuàng)建一個(gè)以當(dāng)前日期時(shí)間為名字的目錄存放備
份文件：
各文件說(shuō)明：
(1)xtrabackup_checkpoints —— 備份類(lèi)型（如完全或增量）、備份狀態(tài)（如是否已經(jīng)為
prepared 狀態(tài)）和 LSN(日志序列號(hào))范圍信息；
每個(gè) InnoDB 頁(yè)(通常為 16k 大小)都會(huì)包含一個(gè)日志序列號(hào)，即 LSN。LSN 是整個(gè)數(shù)據(jù)庫(kù)
系統(tǒng)的系統(tǒng)版本號(hào)，每個(gè)頁(yè)面相關(guān)的 LSN 能夠表明此頁(yè)面最近是如何發(fā)生改變的。
(2)xtrabackup_binlog_info —— mysql 服務(wù)器當(dāng)前正在使用的二進(jìn)制日志文件及至備份
這一刻為止二進(jìn)制日志事件的位置。
(3)xtrabackup_binlog_pos_innodb —— 二進(jìn)制日志文件及用于 InnoDB 或 XtraDB 表的二
進(jìn)制日志文件的當(dāng)前 position。
(4)xtrabackup_binary —— 備份中用到的 xtrabackup 的可執(zhí)行文件；
(5)backup-my.cnf —— 備份命令用到的配置選項(xiàng)信息；
在使用 innobackupex 進(jìn)行備份時(shí)，還可以使用--no-timestamp 選項(xiàng)來(lái)阻止命令自動(dòng)創(chuàng)建
一個(gè)以時(shí)間命名的目錄；如此一來(lái)，innobackupex 命令將會(huì)創(chuàng)建一個(gè) BACKUP-DIR 目錄
來(lái)存儲(chǔ)備份數(shù)據(jù)
注意：相關(guān)選項(xiàng)說(shuō)明：
其中，--user 指定連接數(shù)據(jù)庫(kù)的用戶(hù)名，--password 指定連接數(shù)據(jù)庫(kù)的密碼，--defaults-file
指定數(shù)據(jù)庫(kù)的配置文件，innobackupex 要從其中獲取 datadir 等信息；--database 指定要
備份的數(shù)據(jù)庫(kù)，這里指定的數(shù)據(jù)庫(kù)只對(duì) MyISAM 表有效，對(duì)于 InnoDB 數(shù)據(jù)來(lái)說(shuō)都是全
備（所有數(shù)據(jù)庫(kù)中的 InnoDB 數(shù)據(jù)都進(jìn)行了備份，不是只備份指定的數(shù)據(jù)庫(kù)，恢復(fù)時(shí)也
一樣）；/opt/mysqlbackup/full 是備份文件的存放位置。
注意：備份數(shù)據(jù)庫(kù)的用戶(hù)需要具有相應(yīng)權(quán)限，如果要使用一個(gè)最小權(quán)限的用戶(hù)進(jìn)行備份，
則可基于如下命令創(chuàng)建此類(lèi)用戶(hù)：
mysql> create user 'bkpuser'@'localhost' identified by '123456';
mysql> revoke all privileges,grant option from 'bkpuser'@'localhost';
mysql> grant reload,lock tables,replication client, process on *.* to 'bkpuser'@'localhost';
mysql> flush privileges;
至此全備完全成功，然后向 mysql 某個(gè)庫(kù)插入幾條數(shù)據(jù)，然后進(jìn)行增量備份
對(duì)完全備份的后數(shù)據(jù)庫(kù)更改進(jìn)行二進(jìn)制日志增量備份：
查看完全備份時(shí) binlog 日志位置(position)：
模擬數(shù)據(jù)庫(kù)修改：
2)增量備份二進(jìn)制文件：
#mysqlbinlog --start-position=2378 /usr/local/mysql/data/mysql-bin.000023 >
/opt/mysqlbackup/inc/`date +%F`.sql
2、還原數(shù)據(jù)庫(kù)：
模擬數(shù)據(jù)庫(kù)損壞：
我這里直接使用刪除數(shù)據(jù)目錄文件來(lái)模擬損壞。
# rm -fr /usr/local/mysql/data/*
還原完全備份：
（1）準(zhǔn)備(prepare)一個(gè)完全備份
一般情況下，在備份完成后，數(shù)據(jù)尚且不能用于恢復(fù)操作，因?yàn)閭浞莸臄?shù)據(jù)中可能會(huì)包
含尚未提交的事務(wù)或已經(jīng)提交但尚未同步至數(shù)據(jù)文件中的事務(wù)。因此，此時(shí)數(shù)據(jù)文件仍
處理不一致?tīng)顟B(tài)。“準(zhǔn)備”的主要作用正是通過(guò)回滾未提交的事務(wù)及同步已經(jīng)提交的事
務(wù)至數(shù)據(jù)文件也使得數(shù)據(jù)文件處于一致性狀態(tài)。
在準(zhǔn)備（prepare）過(guò)程結(jié)束后，InnoDB 表數(shù)據(jù)已經(jīng)前滾到整個(gè)備份結(jié)束的點(diǎn)，而不是
回滾到 xtrabackup 剛開(kāi)始時(shí)的點(diǎn)。
innobakupex 命令的--apply-log 選項(xiàng)可用于實(shí)現(xiàn)上述功能。如下面的命令：
--apply-log 指明是將日志應(yīng)用到數(shù)據(jù)文件上，完成之后將備份文件中的數(shù)據(jù)恢復(fù)到數(shù)據(jù)
庫(kù)中：
# innobackupex --apply-log /opt/mysqlbackup/full/2016-09-12_11-29-55/
注：/opt/mysqlbackup/full/2016-09-12_11-29-55/備份文件所在目錄名稱(chēng)
如果執(zhí)行正確，其最后輸出的幾行信息通常如下：
在實(shí)現(xiàn)“準(zhǔn)備”的過(guò)程中，innobackupex 通常還可以使用--use-memory 選項(xiàng)來(lái)指定其可
以使用的內(nèi)存的大小，默認(rèn)通常為 100M。如果有足夠的內(nèi)存可用，可以多劃分一些內(nèi)
存給 prepare 的過(guò)程，以提高其完成速度。
innobackupex 命令的--copy-back 選項(xiàng)用于執(zhí)行恢復(fù)操作，其通過(guò)復(fù)制所有數(shù)據(jù)相關(guān)的文
件至 mysql 服務(wù)器 DATADIR 目錄中來(lái)執(zhí)行恢復(fù)過(guò)程。innobackupex 通過(guò) backup-my.cnf
來(lái)獲取 DATADIR 目錄的相關(guān)信息。
（2）還原數(shù)據(jù)庫(kù)語(yǔ)法：
# innobackupex --copy-back /opt/mysqlbackup/full/2016-09-12_11-29-55/
這里的--copy-back 指明是進(jìn)行數(shù)據(jù)恢復(fù)。數(shù)據(jù)恢復(fù)完成之后，需要修改相關(guān)文件的權(quán)
限 mysql 數(shù)據(jù)庫(kù)才能正常啟動(dòng)。
如果執(zhí)行正確，其輸出信息的最后幾行通常如下：
請(qǐng)確保如上信息的最行一行出現(xiàn)“completed OK!”。
修改還原后的數(shù)據(jù)目錄權(quán)限:
當(dāng)數(shù)據(jù)恢復(fù)至 DATADIR 目錄以后，還需要確保所有數(shù)據(jù)文件的屬主和屬組均為正確的用
戶(hù)，如 mysql，否則，在啟動(dòng) mysqld 之前還需要事先修改數(shù)據(jù)文件的屬主和屬組。如：
# chown -R mysql:mysql /usr/local/mysql/data/
重啟動(dòng) MySQL：
# systemctl restart mysqld
驗(yàn)證還原后的數(shù)據(jù)：
mysql> select * from tb1;
+----+------+
| id | name |
+----+------+
| 1 | tom1 |
| 2 | tom2 |
+----+------+
（3）還原增量備份：
為了防止還原時(shí)產(chǎn)生大量的二進(jìn)制日志，在還原時(shí)可臨時(shí)關(guān)閉二進(jìn)制日志后再還原：
mysql> set sql_log_bin=0;
mysql> source /opt/mysqlbackup/inc/2016-09-12.sql
重新啟動(dòng)二進(jìn)制日志并驗(yàn)證還原數(shù)據(jù)：
mysql> set sql_log_bin=1;
驗(yàn)證數(shù)據(jù)是否恢復(fù)回來(lái)
附：Xtrabackup 的“流”及“備份壓縮”功能
Xtrabackup 對(duì)備份的數(shù)據(jù)文件支持“流”功能，即可以將備份的數(shù)據(jù)通過(guò) STDOUT 傳
輸給 tar 程序進(jìn)行歸檔，而不是默認(rèn)的直接保存至某備份目錄中。要使用此功能，僅需
要使用--stream 選項(xiàng)即可。如：
# innobackupex --user=root --password="123456" --stream=tar /opt/mysqlbackup/full/
| gzip >/opt/mysqlbackup/full/full_`date +%F_%H%M%S`.tar.gz
本文出自 “一盞燭光” 博客，謝絕轉(zhuǎn)載！
更多建議：