免費AI編程助手
MySQL備份與還原
防偽碼：我依舊相信努力的意義，奮斗的價值，因為那是本質問題。
一、mysqldump 備份結合 binlog 日志恢復
MySQL 備份一般采取全庫備份加日志備份的方式，例如每天執(zhí)行一次全備份，每小時執(zhí)行一
次二進制日志備份。這樣在 MySQL 故障后可以使用全備份和日志備份將數(shù)據(jù)恢復到最后一個
二進制日志備份前的任意位置或時間。
1、binlog介紹
mysql 的二進制日志記錄著該數(shù)據(jù)庫的所有增刪改的操作日志(前提是要在自己的服務器上
開啟 binlog)，還包括了這些操作的執(zhí)行時間。為了顯示這些二進制內容，我們可以使用
mysqlbinlog 命令來查看。
Binlog 的用途
1：主從同步
2：恢復數(shù)據(jù)庫
開啟 binary log 功能
通過編輯 my.cnf 中的 log-bin 選項可以開啟二進制日志；形式如下：
log-bin [=DIR/[filename]]
其中，DIR 參數(shù)指定二進制文件的存儲路徑；filename 參數(shù)指定二級制文件的文件名，其形
式為 filename.number，number 的形式為 000001、000002 等。每次重啟 mysql 服務或運行
mysql> flush logs;都會生成一個新的二進制日志文件，這些日志文件的 number 會不斷地遞增。
除了生成上述的文件外還會生成一個名為 filename.index 的文件。這個文件中存儲所有二進
制日志文件的清單又稱為二進制文件的索引
配置保存以后重啟 mysql 的服務器，用 mysql> show variables like 'log_bin';查看 bin-log 是否
開啟，如圖：
查看產(chǎn)生的 binary log 注：查看 binlog 內容是為了恢復數(shù)據(jù)
bin-log 因為是二進制文件，不能通過文件內容查看命令直接打開查看，mysql 提供兩種方式
查看方式，在介紹之前，我們先對數(shù)據(jù)庫進行一下增刪改的操作，否則 log 里邊數(shù)據(jù)有點空。
#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')"
重新開始一個新的日志文件
#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 上的二進制日志
mysql> show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 1087 |
| mysql-bin.000002 | 673 |
+------------------+-----------+
查看二進制日志信息的命令：
語法格式：SHOW BINLOG EVENTS [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count]
查看二進制日志中的事件
mysql> show binlog events;
默認顯示可找到的第一個二進制日志文件中的事件，包含了日志文件名、事件的開始位置、
事件類型、結束位置、信息等內容
+------------------+------+----------------+-----------+-------------+----------------------------------------------------------------------------
------+
| 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
| // 為查詢事件
| 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
| // 為查詢事件，事務開始
| 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 時間是自動提交事務的動作
| 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 開啟新日志文件引起的。
查看指定的二進制日志中的事件
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 */ |
該命令還包含其他選項以便靈活查看
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 等價于 SHOW MASTER LOGS
PURGE BINARY LOGS 用于刪除二進制日志，如：
PURGE BINARY LOGS TO 'mysql-bin.00010'; //把這個文件之前的其他文件都刪除掉
PURGE BINARY LOGS BEFORE '2016-08-28 22:46:26';//把指定時間之前的二進制文件
刪除了
RESET MASTER 與 RESET SLAVE
前者清空 index 文件中列出的所有二進制日志，重置 index 文件為空，并創(chuàng)建一個新的二進
制日志文件，一般用于 MASTER 首次啟動時。后者使 SLAVE 忘記其在 MASTER 二進制日
志文件中的復制位置，它會刪除 master.info、relay-log.info 和所有中繼日志文件并開始一
個新的中繼日志文件，以便于開始一個干凈的復制。在使用 RESET SLAVE 前需先關閉
SLAVE 復制線程。
上述方式可以查看到服務器上存在的二進制日志文件及文件中的事件，但是想查看到文件中
具體的內容并應于恢復場景還得借助 mysqlbinlog 這個工具。
語法格式： mysqlbinlog [options] log_file ...
輸出內容會因日志文件的格式以及 mysqlbinlog 工具使用的選項不同而略不同。
mysqlbinlog 的可用選項可參考 man 手冊。
二進制日志文件的格式包含行模式、語句模式和混合模式（也即有服務器決定在什么情況下
記錄什么類型的日志），基于語句的日志中事件信息包含執(zhí)行的語句等，基于行的日志中事
件信息包含的是行的變化信息等。混合模式的日志中兩種類型的事件信息都會記錄。
為了便于查看記錄了行變化信息的事件在當時具體執(zhí)行了什么樣的 SQL 語句可以使用
mysqlbinlog 工具的-v（--verbose）選項，該選項會將行事件重構成被注釋掉的偽 SQL 語句，
如果想看到更詳細的信息可以將該選項給兩次如-vv，這樣可以包含一些數(shù)據(jù)類型和元信息
的注釋內容，如
先切換到 binlog 所在的目錄下
#mysqlbinlog mysql-bin.000001
#mysqlbinlog -v mysql-bin.000001
#mysqlbinlog -vv mysql-bin.000001
另外 mysqlbinlog 和可以通過--read-from-remote-server 選項從遠程服務器讀取二進制日志文
件，這時需要一些而外的連接參數(shù)，如-h，-P，-p，-u 等，這些參數(shù)僅在指定了
--read-from-remote-server 后有效。
無論是本地二進制日志文件還是遠程服務器上的二進制日志文件，無論是行模式、語句模式
還是混合模式的二進制日志文件，被 mysqlbinlog 工具解析后都可直接應用與 MySQL Server
進行基于時間點、位置或數(shù)據(jù)庫的恢復。
未經(jīng)允許不得轉載傳播--陳英宏
博客地址：hongge.blog.51cto.com
下面我們就來演示如何使用 binlog 恢復之前刪除數(shù)據(jù)（id=2 那條記錄）
注意：在實際生產(chǎn)環(huán)境中，如果遇到需要恢復數(shù)據(jù)庫的情況，不要讓用戶能訪問到數(shù)據(jù)庫，
以避免新的數(shù)據(jù)插入進來，以及在主從的環(huán)境下，關閉主從。
查看 binlog 文件，從中找出 delete from test.tb1 where id=2
# cd /usr/local/mysql/data/
# mysqlbinlog -v mysql-bin.000002
顯示結果
# 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，事件結束 position 是 416
恢復流程：直接用 bin-log 日志將數(shù)據(jù)庫恢復到刪除位置 287 前,然后跳過故障點,再進行恢復
下面所有的操作,命令如下
由于之前沒有做過全庫備份，所以要使用所有 binlog 日志恢復，所以生產(chǎn)環(huá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ù)庫
mysql>drop database test;
利用 binlog 恢復數(shù)據(jù)
#mysql -uroot -p123456< /opt/mysql-bin.000001.sql
#mysql -uroot -p123456< /opt/287.sql
#mysql -uroot -p123456< /opt/416.sql
恢復完成后，我們檢查下表的數(shù)據(jù)是否完整
mysql> select * from test.tb1;
+----+----------+
| id | name |
+----+----------+
| 1 | lisi |
| 2 | zhangsan |
| 3 | tom |
+----+----------+
Ok 完整的都恢復過來了
mysqlbinlog 選項示例
常見的選項有以下幾個：
--start-datetime
從二進制日志中讀取指定時間戳或者本地計算機時間之后的日志事件。
--stop-datetime
從二進制日志中讀取指定時間戳或者本地計算機時間之前的日志事件。
--start-position
從二進制日志中讀取指定 position 事件位置作為開始。
--stop-position
從二進制日志中讀取指定 position 事件位置作為事件截至。
2、mysqldump 介紹
mysqldump 是 mysql 用于備份和數(shù)據(jù)轉移的一個工具。它主要產(chǎn)生一系列的 SQL 語句，可以
封裝到文件，該文件包含有所有重建你的數(shù)據(jù)庫所需要的 SQL 命令如 CREATE DATABASE，
CREATE TABLE，INSERT 等等?？梢杂脕韺崿F(xiàn)輕量級的快速遷移或恢復數(shù)據(jù)庫。
mysqldump 是將數(shù)據(jù)表導成 SQL 腳本文件，在不同的 MySQL 版本之間升級時相對比較合適，
這也是最常用的備份方法。
mysqldump 一般在數(shù)據(jù)量很小的時候（幾個 G）可以用于備份。當數(shù)據(jù)量比較大的情況下，
就不建議用 mysqldump 工具進行備份了。
數(shù)據(jù)庫的導出
導出對象說明：
mysqldump 可以針對單個表、多個表、單個數(shù)據(jù)庫、多個數(shù)據(jù)庫、所有數(shù)據(jù)庫進行導出的操
作
# mysqldump [options] db_name [tbl_name ...] //導出指定數(shù)據(jù)庫或單個表
# mysqldump [options] --databases db_name ... //導出多個數(shù)據(jù)庫
#mysqldump [options] --all-databases //導出所有
導出數(shù)據(jù)庫 test
# mysqldump -uroot -p --flush-logs test > /opt/test.sql //--flush-logs 這個選項就會完整備份
的時候重新開啟一個新 binlog
數(shù)據(jù)庫的導入
# mysql -uroot -p test < /opt/test.sql
在前面我們介紹了 mysql 的 binlog 和 mysqldump 工具，下面我們來學習如何實現(xiàn) mysqldump
全庫備份+binlog 的數(shù)據(jù)恢復
環(huán)境準備與備份還原：
檢查開啟 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 全庫備份+binlog 還原
1、mysqldump 備份方案：
每周一凌晨 1 點全庫備份
2、備份步驟
（1） 創(chuàng)建備份目錄
# mkdir /opt/mysqlbackup
# mkdir /opt/mysqlbackup/daily
（2）全庫備份
這里我們模擬周一的完整備份數(shù)據(jù)庫任務
#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 全庫備份之前的 binlog 日志文（注：生產(chǎn)環(huán)境中可能不只一個 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ù)修改錯誤了。
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 的全庫備份和 binlog 來恢復數(shù)據(jù)。
使用 mysqldump 的備份進行全庫恢復
# mysql -uroot -p test_db < /opt/mysqlbackup/test_db_2016_09_12.sql
查詢一下數(shù)據(jù)
[root@localhost ~]# mysql -uroot -p -e "select * from test_db.tb1"
Enter password:
+----+------+
| id | name |
+----+------+
| 1 | tom1 |
| 2 | tom2 |
+----+------+
從顯示結果可以看到使用 mysqldump 備份將數(shù)據(jù)還原到了備份時的狀態(tài)，剛才刪除的數(shù)據(jù)
（id=2）恢復回來了，但備份后產(chǎn)生的數(shù)據(jù)卻丟失了所以還得利用 binlog 進一步不原
因為刪除是在全庫備份后發(fā)生的，而 mysqldump 全庫備份時使用--flush-logs 選項，所以只需
要分析全庫備份后的 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 文件的詳細事件。
恢復流程：我們直接用 bin-log 日志將數(shù)據(jù)庫恢復到刪除位置前,然后跳過故障點,再進行恢復
刪除后的所有操作。
# mysqlbinlog -v /opt/mysqlbackup/daily/mysql-bin.000002
我們先用 mysqlbinlog 命令找到 delete 那條語句的位置
# 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/*!*/;
通過 mysqlbinlog 命令所顯示的結果可以看到誤操作 delete 的開始 postion 為 219，結束
position 是 422。
從二進制日志中讀取指定 position=219 事件位置作為截至，即把數(shù)據(jù)恢復到 delete 刪除前
# mysqlbinlog --stop-position=219 /opt/mysqlbackup/daily/mysql-bin.000002 | mysql -u root -p
從二進制日志中讀取指定 position=422 事件位置作為開始，即跳過刪除事件，恢復刪除事件
之后對數(shù)據(jù)的正常操作
#mysqlbinlog --start-position=422 /opt/mysqlbackup/daily/mysql-bin.000002 | mysql -u root -p
查看恢復結果：
# mysql -uroot -p -e "select * from test_db.tb1"
Enter password:
+----+------+
| id | name |
+----+------+
| 1 | tom1 |
| 2 | tom2 |
| 3 | tom3 |
+----+------+
從上面顯示可以看出數(shù)據(jù)恢復到正常狀態(tài)
生產(chǎn)環(huán)境中 Mysql 數(shù)據(jù)庫的備份是周期性重復的操作，所以通常是要編寫腳本實現(xiàn)，通過
crond 計劃任務周期性執(zhí)行備份腳本
mysqldump 備份方案：
周日凌晨 1 點全庫備份
周一到周六凌晨每隔 4 個小時增量備份一次
設置 crontab 任務，每天執(zhí)行備份腳本
# crontab –e
#每個星期日凌晨 1:00 執(zhí)行完全備份腳本
0 1 * * 0 /root/mysqlfullbackup.sh >/dev/null 2>&1
#周一到周六每隔 4 個小時增量備份一次
0 */4 * * 1-6 /root/mysqldailybackup.sh >/dev/null 2>&1
mysqlfullbackup.sh 腳本內容：
[root@localhost ~]# cat mysqlfullbackup.sh
#!/bin/sh
# Name:mysqlFullBackup.sh
# 定義數(shù)據(jù)庫目錄
mysqlDir=/usr/local/mysql
# 定義用于備份數(shù)據(jù)庫的用戶名和密碼
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ù)庫，請根據(jù)具體情況設置參數(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
# 寫日志文件
echo "--------------------------------------------------------" >> $logfile
cat $emailfile >> $logfile
# 發(fā)送郵件通知
cat $emailfile | mail -s "MySQL Backup" $email
mysqldailybackup.sh 腳本內容：
[root@localhost ~]# cat mysqldailybackup.sh
#!/bin/sh
# Name:mysqlDailyBackup.sh
# 定義數(shù)據(jù)庫目錄和數(shù)據(jù)目錄
mysqldir=/usr/local/mysql
datadir=$mysqldir/data
# 定義用于備份數(shù)據(jù)庫的用戶名和密碼
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ù)庫使用新的二進制日志文件
$mysqldir/bin/mysqladmin -u$user -p$userpwd flush-logs
cd $datadir
# 得到二進制日志列表
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`
# 跳過最后一個二進制日志（數(shù)據(jù)庫當前使用的二進制日志文件）
if [ $nextnum -eq $icounter ]; then
echo "Skip lastest!" > /dev/null
else
dest=$dailybackupdir/$binlogname
# 跳過已經(jīng)備份的二進制日志文件
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
# 寫日志文件
echo "--------------------------------------------------------" >> $logfile
cat $emailfile >> $logfile
二、使用 xtrabackup 進行 MySQL 數(shù)據(jù)庫備份
前面介紹 mysqldump 備份方式是采用邏輯備份，其最大的缺陷就是備份和恢復速度都慢，
對于一個小于 50G 的數(shù)據(jù)庫而言，這個速度還是能接受的，但如果數(shù)據(jù)庫非常大，那再使
用 mysqldump 備份就不太適合了。
這時就需要一種好用又高效的工具，xtrabackup 就是其中一款，號稱免費版的 InnoDB
HotBackup。
Xtrabackup 實現(xiàn)是物理備份，而且是物理熱備
目前主流的有兩個工具可以實現(xiàn)物理熱備：ibbackup 和 xtrabackup；ibbackup 是商業(yè)軟件，
需要授權，非常昂貴。而 xtrabackup 功能比 ibbackup 還要強大，但卻是開源的。因此我們
這里就來介紹 xtrabackup 的使用。
Xtrabackup 提供了兩種命令行工具：
xtrabackup：專用于備份 InnoDB 和 XtraDB 引擎的數(shù)據(jù)；
innobackupex：這是一個 perl 腳本，在執(zhí)行過程中會調用 xtrabackup 命令，這樣用該命令即
可以實現(xiàn)備份 InnoDB，也可以備份 MyISAM 引擎的對象。
Xtrabackup 是由 percona 提供的 mysql 數(shù)據(jù)庫備份工具，特點：
(1)備份過程快速、可靠；
(2)備份過程不會打斷正在執(zhí)行的事務；
(3)能夠基于壓縮等功能節(jié)約磁盤空間和流量；
(4)自動實現(xiàn)備份檢驗；
(5)還原速度快。
官方鏈接地址：http://www.percona.com/software/percona-xtrabackup；可以下載源碼編譯安
裝，也可以下載適合的 RPM 包或使用 yum 進行安裝或者下載二進制源碼包。
安裝 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)進入解壓目錄
# cd percona-xtrabackup-2.4.4-Linux-x86_64/
4)復制 bin 下的所有程序到/usr/bin
[root@localhost percona-xtrabackup-2.4.4-Linux-x86_64]# cp bin/* /usr/bin/
Xtrabackup 中主要包含兩個工具：
xtrabackup：是用于熱備份 innodb, xtradb 表中數(shù)據(jù)的工具，支持在線熱備份，可以在不
加鎖的情況下備份 Innodb 數(shù)據(jù)表，不過此工具不能操作 Myisam 引擎表；
innobackupex：是將 xtrabackup 進行封裝的 perl 腳本，能同時處理 Innodb 和 Myisam，
但在處理 Myisam 時需要加一個讀鎖。
由于操作 Myisam 時需要加讀鎖，這會堵塞線上服務的寫操作，而 Innodb 沒有這樣的
限制，所以數(shù)據(jù)庫中 Innodb 表類型所占的比例越大，則越有利。
4)安裝相關插件
#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
下面就可以啟動備份了
方案：xtrabackup 完全備份+binlog 增量備份
1、備份
創(chuàng)建備份目錄
# mkdir -p /opt/mysqlbackup/{full,inc}
full：全備存放的目錄；inc：增量備份存放的目錄
1)完全備份
基本語法：# innobackupex --user=DBUSER --password=DBUSERPASS /path/to/BACKUP-DIR/
執(zhí)行下面的命令進行完全備份：
# innobackupex --user=root --password=123456 /opt/mysqlbackup/full
注： --defaults-file=/etc/my.cnf 指定 mysql 的配置文件 my.cfg，如果指定則必須是第一個
參數(shù)。
/path/to/BACKUP-DIR/指定備份所存放的目標目錄，備份過程會創(chuàng)建一個以當時備份時間
命名的目錄存放備份文件。
出現(xiàn)如下提示。表示成功
備份后的文件：
在備份的同時，備份數(shù)據(jù)會在備份目錄下創(chuàng)建一個以當前日期時間為名字的目錄存放備
份文件：
各文件說明：
(1)xtrabackup_checkpoints —— 備份類型（如完全或增量）、備份狀態(tài)（如是否已經(jīng)為
prepared 狀態(tài)）和 LSN(日志序列號)范圍信息；
每個 InnoDB 頁(通常為 16k 大小)都會包含一個日志序列號，即 LSN。LSN 是整個數(shù)據(jù)庫
系統(tǒng)的系統(tǒng)版本號，每個頁面相關的 LSN 能夠表明此頁面最近是如何發(fā)生改變的。
(2)xtrabackup_binlog_info —— mysql 服務器當前正在使用的二進制日志文件及至備份
這一刻為止二進制日志事件的位置。
(3)xtrabackup_binlog_pos_innodb —— 二進制日志文件及用于 InnoDB 或 XtraDB 表的二
進制日志文件的當前 position。
(4)xtrabackup_binary —— 備份中用到的 xtrabackup 的可執(zhí)行文件；
(5)backup-my.cnf —— 備份命令用到的配置選項信息；
在使用 innobackupex 進行備份時，還可以使用--no-timestamp 選項來阻止命令自動創(chuàng)建
一個以時間命名的目錄；如此一來，innobackupex 命令將會創(chuàng)建一個 BACKUP-DIR 目錄
來存儲備份數(shù)據(jù)
注意：相關選項說明：
其中，--user 指定連接數(shù)據(jù)庫的用戶名，--password 指定連接數(shù)據(jù)庫的密碼，--defaults-file
指定數(shù)據(jù)庫的配置文件，innobackupex 要從其中獲取 datadir 等信息；--database 指定要
備份的數(shù)據(jù)庫，這里指定的數(shù)據(jù)庫只對 MyISAM 表有效，對于 InnoDB 數(shù)據(jù)來說都是全
備（所有數(shù)據(jù)庫中的 InnoDB 數(shù)據(jù)都進行了備份，不是只備份指定的數(shù)據(jù)庫，恢復時也
一樣）；/opt/mysqlbackup/full 是備份文件的存放位置。
注意：備份數(shù)據(jù)庫的用戶需要具有相應權限，如果要使用一個最小權限的用戶進行備份，
則可基于如下命令創(chuàng)建此類用戶：
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 某個庫插入幾條數(shù)據(jù)，然后進行增量備份
對完全備份的后數(shù)據(jù)庫更改進行二進制日志增量備份：
查看完全備份時 binlog 日志位置(position)：
模擬數(shù)據(jù)庫修改：
2)增量備份二進制文件：
#mysqlbinlog --start-position=2378 /usr/local/mysql/data/mysql-bin.000023 >
/opt/mysqlbackup/inc/`date +%F`.sql
2、還原數(shù)據(jù)庫：
模擬數(shù)據(jù)庫損壞：
我這里直接使用刪除數(shù)據(jù)目錄文件來模擬損壞。
# rm -fr /usr/local/mysql/data/*
還原完全備份：
（1）準備(prepare)一個完全備份
一般情況下，在備份完成后，數(shù)據(jù)尚且不能用于恢復操作，因為備份的數(shù)據(jù)中可能會包
含尚未提交的事務或已經(jīng)提交但尚未同步至數(shù)據(jù)文件中的事務。因此，此時數(shù)據(jù)文件仍
處理不一致狀態(tài)。“準備”的主要作用正是通過回滾未提交的事務及同步已經(jīng)提交的事
務至數(shù)據(jù)文件也使得數(shù)據(jù)文件處于一致性狀態(tài)。
在準備（prepare）過程結束后，InnoDB 表數(shù)據(jù)已經(jīng)前滾到整個備份結束的點，而不是
回滾到 xtrabackup 剛開始時的點。
innobakupex 命令的--apply-log 選項可用于實現(xiàn)上述功能。如下面的命令：
--apply-log 指明是將日志應用到數(shù)據(jù)文件上，完成之后將備份文件中的數(shù)據(jù)恢復到數(shù)據(jù)
庫中：
# innobackupex --apply-log /opt/mysqlbackup/full/2016-09-12_11-29-55/
注：/opt/mysqlbackup/full/2016-09-12_11-29-55/備份文件所在目錄名稱
如果執(zhí)行正確，其最后輸出的幾行信息通常如下：
在實現(xiàn)“準備”的過程中，innobackupex 通常還可以使用--use-memory 選項來指定其可
以使用的內存的大小，默認通常為 100M。如果有足夠的內存可用，可以多劃分一些內
存給 prepare 的過程，以提高其完成速度。
innobackupex 命令的--copy-back 選項用于執(zhí)行恢復操作，其通過復制所有數(shù)據(jù)相關的文
件至 mysql 服務器 DATADIR 目錄中來執(zhí)行恢復過程。innobackupex 通過 backup-my.cnf
來獲取 DATADIR 目錄的相關信息。
（2）還原數(shù)據(jù)庫語法：
# innobackupex --copy-back /opt/mysqlbackup/full/2016-09-12_11-29-55/
這里的--copy-back 指明是進行數(shù)據(jù)恢復。數(shù)據(jù)恢復完成之后，需要修改相關文件的權
限 mysql 數(shù)據(jù)庫才能正常啟動。
如果執(zhí)行正確，其輸出信息的最后幾行通常如下：
請確保如上信息的最行一行出現(xiàn)“completed OK!”。
修改還原后的數(shù)據(jù)目錄權限:
當數(shù)據(jù)恢復至 DATADIR 目錄以后，還需要確保所有數(shù)據(jù)文件的屬主和屬組均為正確的用
戶，如 mysql，否則，在啟動 mysqld 之前還需要事先修改數(shù)據(jù)文件的屬主和屬組。如：
# chown -R mysql:mysql /usr/local/mysql/data/
重啟動 MySQL：
# systemctl restart mysqld
驗證還原后的數(shù)據(jù)：
mysql> select * from tb1;
+----+------+
| id | name |
+----+------+
| 1 | tom1 |
| 2 | tom2 |
+----+------+
（3）還原增量備份：
為了防止還原時產(chǎn)生大量的二進制日志，在還原時可臨時關閉二進制日志后再還原：
mysql> set sql_log_bin=0;
mysql> source /opt/mysqlbackup/inc/2016-09-12.sql
重新啟動二進制日志并驗證還原數(shù)據(jù)：
mysql> set sql_log_bin=1;
驗證數(shù)據(jù)是否恢復回來
附：Xtrabackup 的“流”及“備份壓縮”功能
Xtrabackup 對備份的數(shù)據(jù)文件支持“流”功能，即可以將備份的數(shù)據(jù)通過 STDOUT 傳
輸給 tar 程序進行歸檔，而不是默認的直接保存至某備份目錄中。要使用此功能，僅需
要使用--stream 選項即可。如：
# innobackupex --user=root --password="123456" --stream=tar /opt/mysqlbackup/full/
| gzip >/opt/mysqlbackup/full/full_`date +%F_%H%M%S`.tar.gz
本文出自 “一盞燭光” 博客，謝絕轉載！
更多建議：