目錄

• binlog日志恢復(fù)
• binlog介紹
• Binlog的用途
• 開啟binary log功能
• 配置binlog
• mysqldump
• 數(shù)據(jù)庫(kù)的導(dǎo)出
• 數(shù)據(jù)庫(kù)的導(dǎo)入
• mysqldump+binlog
• 總結(jié)

binlog日志恢復(fù)

MySQL備份一般采取全庫(kù)備份加日志備份的方式，例如每天執(zhí)行一次全備份，每小時(shí)執(zhí)行一次二進(jìn)制日志備份。這樣在MySQL故障后可以使用全備份和日志備份將數(shù)據(jù)恢復(fù)到最后一個(gè)二進(jìn)制日志備份前的任意位置或時(shí)間。

binlog介紹

mysql的二進(jìn)制日志記錄著該數(shù)據(jù)庫(kù)的所有增刪改的操作日志(前提是要在自己的服務(wù)器上開啟binlog)，還包括了這些操作的執(zhí)行時(shí)間。為了顯示這些二進(jìn)制內(nèi)容，我們可以使用mysqlbinlog命令來(lái)查看。

Binlog的用途

主從同步

恢復(fù)數(shù)據(jù)庫(kù)

開啟binary log功能

通過(guò)編輯my.cnf中的log-bin選項(xiàng)可以開啟二進(jìn)制日志；形式如下： log-bin[=DIR/[filename]]（配置文件中只寫log_bin不寫后面的文件名和路徑時(shí)，默認(rèn)存放在/usr/local/mysql/data目錄下，文件名為主機(jī)名-bin.000001…命名） 其中，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)制日志文件的清單又稱為二進(jìn)制文件的索引

配置保存以后重啟mysql的服務(wù)器，用mysql> show variables like 'log_bin';查看bin-log是否開啟如下所示。

[root@mysql ~]# vim /etc/my.cnf 
log_bin=mysql-bin
server_id=1
[root@mysql ~]# systemctl restart mysqld
[root@mysql ~]# mysql -uroot -p123 -e "show variables like "log_bin""
mysql: [Warning] Using a password on the command line interface can be insecure.
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | ON    |
+---------------+-------+

配置binlog

1. 查看產(chǎn)生的binary log

注：查看binlog內(nèi)容是為了恢復(fù)數(shù)據(jù) bin-log因?yàn)槭嵌M(jìn)制文件，不能通過(guò)文件內(nèi)容查看命令直接打開查看，mysql提供兩種方式查看方式，在介紹之前，我們先對(duì)數(shù)據(jù)庫(kù)進(jìn)行一下增刪改的操作，否則log里邊數(shù)據(jù)有點(diǎn)空。

[root@mysql ~]# mysql -uroot -p123
#省略部分內(nèi)容
mysql> reset master;    #清空所有二進(jìn)制文件，從000001開始
Query OK, 0 rows affected (0.00 sec)
?
mysql> create database bbs character set utf8 collate utf8_bin;
Query OK, 1 row affected (0.01 sec)
?
mysql> use bbs;
Database changed
mysql> create table tb1(
    -> id int primary key auto_increment,
    -> name varchar(20));
Query OK, 0 rows affected (0.02 sec)
?
mysql> insert into tb1(name) values("z3"),("l4");
Query OK, 2 rows affected (0.02 sec)
Records: 2  Duplicates: 0  Warnings: 0
?
mysql> flush logs;  #刷新日志，下面操作將在000002
Query OK, 0 rows affected (0.01 sec)
?
mysql> delete from tb1 where id=2;
Query OK, 1 row affected (0.00 sec)
?
mysql> insert into tb1(name) values("w5");
Query OK, 1 row affected (0.00 sec)
?
mysql> select * from tb1;
+----+------+
| id | name |
+----+------+
|  1 | z3   |
|  3 | w5   |
+----+------+
2 rows in set (0.00 sec)

2. 查看MySQL Server上的二進(jìn)制日志

mysql> show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 |       866 |
| mysql-bin.000002 |       670 |
+------------------+-----------+
2 rows in set (0.00 sec)

3. 查看二進(jìn)制日志信息的命令：

語(yǔ)法格式：SHOW BINLOG EVENTS[IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count]

查看二進(jìn)制日志中的事件，默認(rèn)顯示可找到的第一個(gè)二進(jìn)制日志文件中的事件，包含了日志文件名、事件的開始位置、事件類型、結(jié)束位置、信息等內(nèi)容

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.40-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 | 346 | create database bbs character set utf8 collate utf8_bin   //為查詢事件 |
| mysql-bin.000001 | 346 | Anonymous_Gtid | 1 | 411 | SET @@SESSION.GTID_NEXT= "ANONYMOUS"      |
| mysql-bin.000001 | 411 | Query  | 1 | 553 | use `bbs`; create table tb1(
id int primary key auto_increment,
name varchar(20)) |
| mysql-bin.000001 | 553 | Anonymous_Gtid | 1 | 618 | SET @@SESSION.GTID_NEXT= "ANONYMOUS"      |
| mysql-bin.000001 | 618 | Query  | 1 | 689 | BEGIN     #為查詢事件，事務(wù)開始 |
| mysql-bin.000001 | 689 | Table_map      | 1 | 737 | table_id: 109 (bbs.tb1)   #為表映射事件   |
| mysql-bin.000001 | 737 | Write_rows     | 1 | 788 | table_id: 109 flags: STMT_END_F   #為我們執(zhí)行的insert事件|
| mysql-bin.000001 | 788 | Xid    | 1 | 819 | COMMIT /* xid=13 */       #Xid時(shí)間是自動(dòng)提交事務(wù)的動(dòng)作|
| mysql-bin.000001 | 819 | Rotate | 1 | 866 | mysql-bin.000002;pos=4    #為日志輪換事件，是我們執(zhí)行flush logs開啟新日志文件引起的|
+------------------+-----+----------------+-----------+-------------+-----------------------------------------------------------------------------------+
12 rows in set (0.01 sec)

4. 查看指定的二進(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.40-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 | 290 | BEGIN |
| mysql-bin.000002 | 290 | Table_map      | 1 | 338 | table_id: 109 (bbs.tb1)       |
| mysql-bin.000002 | 338 | Delete_rows    | 1 | 381 | table_id: 109 flags: STMT_END_F       |
| mysql-bin.000002 | 381 | Xid    | 1 | 412 | COMMIT /* xid=15 */   |
| mysql-bin.000002 | 412 | Anonymous_Gtid | 1 | 477 | SET @@SESSION.GTID_NEXT= "ANONYMOUS"  |
| mysql-bin.000002 | 477 | Query  | 1 | 548 | BEGIN |
| mysql-bin.000002 | 548 | Table_map      | 1 | 596 | table_id: 109 (bbs.tb1)       |
| mysql-bin.000002 | 596 | Write_rows     | 1 | 639 | table_id: 109 flags: STMT_END_F       |
| mysql-bin.000002 | 639 | Xid    | 1 | 670 | COMMIT /* xid=16 */   |
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
12 rows in set (0.01 sec)

該命令還包含其他選項(xiàng)以便靈活查看，以pos219下面起始到第三個(gè)結(jié)束。

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 | 290 | Table_map   | 1 | 338 | table_id: 109 (bbs.tb1) |
| mysql-bin.000002 | 338 | Delete_rows | 1 | 381 | table_id: 109 flags: STMT_END_F |
| mysql-bin.000002 | 381 | Xid | 1 | 412 | COMMIT /* xid=15 */     |
+------------------+-----+-------------+-----------+-------------+---------------------------------+
3 rows in set (0.00 sec)

SHOW BINARY LOGS 等價(jià)于 SHOW MASTER LOGS PURGE BINARY LOGS用于刪除二進(jìn)制日志。

如： PURGEBINARY 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 和所有中繼日志文件并開始一個(gè)新的中繼日志文件，以便于開始一個(gè)干凈的復(fù)制。在使用RESET SLAVE前需先關(guān)閉SLAVE復(fù)制線程。 上述方式可以查看到服務(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ù)器決定在什么情況下記錄什么類型的日志），基于語(yǔ)句的日志中事件信息包含執(zhí)行的語(yǔ)句等，基于行的日志中事件信息包含的是行的變化信息等。混合模式的日志中兩種類型的事件信息都會(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ù)類型和元信息的注釋內(nèi)容，如 先切換到binlog所在的目錄下

[root@mysql ~]# cd /usr/local/mysql/data
[root@mysql data]# mysqlbinlog mysql-bin.000001     #查看二進(jìn)制文件
?
[root@mysql data]# mysqlbinlog -v mysql-bin.000001  #查看詳細(xì)內(nèi)容
?
[root@mysql data]# mysqlbinlog -vv mysql-bin.000001 #查看更詳細(xì)內(nèi)容

另外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ù)。

下面我們就來(lái)演示如何使用binlog恢復(fù)之前刪除數(shù)據(jù)（id=2那條記錄） 注意：在實(shí)際生產(chǎn)環(huán)境中，如果遇到需要恢復(fù)數(shù)據(jù)庫(kù)的情況，不要讓用戶能訪問到數(shù)據(jù)庫(kù)，以避免新的數(shù)據(jù)插入進(jìn)來(lái)，以及在主從的環(huán)境下，關(guān)閉主從。 查看binlog文件，從中找出delete from test.tb1 where id=2

[root@mysql ~]# cd /usr/local/mysql/data
[root@mysql data]# mysqlbinlog -v mysql-bin.000002 
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#230324  8:44:33 server id 1  end_log_pos 123 CRC32 0xcbae27e2  Start: binlog v 4, server v 5.7.40-log created 230324  8:44:33
# Warning: this binlog is either in use or was not closed properly.
BINLOG "
cfIcZA8BAAAAdwAAAHsAAAABAAQANS43LjQwLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA
AeInrss=
"/*!*/;
# at 123
#230324  8:44:33 server id 1  end_log_pos 154 CRC32 0xc6b0dd29  Previous-GTIDs
# [empty]
# at 154
#230324  8:45:29 server id 1  end_log_pos 219 CRC32 0x59f973f8  Anonymous_GTID  last_committed=0    sequence_number=1   rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= "ANONYMOUS"/*!*/;
# at 219
#230324  8:45:29 server id 1  end_log_pos 290 CRC32 0xe9a3eaa9  Query   thread_id=3   exec_time=0   error_code=0
SET TIMESTAMP=1679618729/*!*/;
SET @@session.pseudo_thread_id=3/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1436549152/*!*/;
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 290
#230324  8:45:29 server id 1  end_log_pos 338 CRC32 0xe66de950  Table_map: `bbs`.`tb1` mapped to number 109
# at 338
#230324  8:45:29 server id 1  end_log_pos 381 CRC32 0x6c2d4b4b  Delete_rows: table id 109 flags: STMT_END_F
?
BINLOG "
qfIcZBMBAAAAMAAAAFIBAAAAAG0AAAAAAAEAA2JicwADdGIxAAIDDwI8AAJQ6W3m
qfIcZCABAAAAKwAAAH0BAAAAAG0AAAAAAAEAAgAC//wCAAAAAmw0S0stbA==
"/*!*/;
### DELETE FROM `bbs`.`tb1`
### WHERE
###   @1=2
###   @2="l4"
# at 381
#230324  8:45:29 server id 1  end_log_pos 412 CRC32 0x09d061ff  Xid = 15
COMMIT/*!*/;
# at 412
#230324  8:45:49 server id 1  end_log_pos 477 CRC32 0x00977c6e  Anonymous_GTID  last_committed=1    sequence_number=2   rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= "ANONYMOUS"/*!*/;
# at 477
#230324  8:45:49 server id 1  end_log_pos 548 CRC32 0x8ea03cb0  Query   thread_id=3   exec_time=0   error_code=0
SET TIMESTAMP=1679618749/*!*/;
BEGIN
/*!*/;
# at 548
#230324  8:45:49 server id 1  end_log_pos 596 CRC32 0xe32cd3c5  Table_map: `bbs`.`tb1` mapped to number 109
# at 596
#230324  8:45:49 server id 1  end_log_pos 639 CRC32 0x30b3d697  Write_rows: table id 109 flags: STMT_END_F
?
BINLOG "
vfIcZBMBAAAAMAAAAFQCAAAAAG0AAAAAAAEAA2JicwADdGIxAAIDDwI8AALF0yzj
vfIcZB4BAAAAKwAAAH8CAAAAAG0AAAAAAAEAAgAC//wDAAAAAnc1l9azMA==
"/*!*/;
### INSERT INTO `bbs`.`tb1`
### SET
###   @1=3
###   @2="w5"
# at 639
#230324  8:45:49 server id 1  end_log_pos 670 CRC32 0xcfda2a0b  Xid = 16
COMMIT/*!*/;
SET @@SESSION.GTID_NEXT= "AUTOMATIC" /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

從中可以看出delete事件發(fā)生position是290，事件結(jié)束position是412 恢復(fù)流程：直接用bin-log日志將數(shù)據(jù)庫(kù)恢復(fù)到刪除位置290前,然后跳過(guò)故障點(diǎn),再進(jìn)行恢復(fù)下面所有的操作,命令如下 由于之前沒有做過(guò)全庫(kù)備份，所以要使用所有binlog日志恢復(fù)，所以生產(chǎn)環(huán)境中需要很長(zhǎng)時(shí)間恢復(fù)，導(dǎo)出相關(guān)binlog文件。

[root@mysql ~]# mysqlbinlog /usr/local/mysql/data/mysql-bin.000001> /opt/mybin.000001.sql
[root@mysql ~]# mysqlbinlog --stop-position=290 /usr/local/mysql/data/mysql-bin.000002> /opt/mybin.290.sql
[root@mysql ~]# mysqlbinlog --start-position=412 /usr/local/mysql/data/mysql-bin.000002> /opt/mybin.412.sql
?

刪除bbs數(shù)據(jù)庫(kù)

mysql> drop database bbs;
Query OK, 1 row affected (0.09 sec)

利用binlog恢復(fù)數(shù)據(jù)

逐步恢復(fù)，查看是否恢復(fù)全表。

[root@mysql ~]# mysql -uroot -p123 < /opt/mybin.000001.sql 
#驗(yàn)證
mysql> select * from bbs.tb1;
+----+------+
| id | name |
+----+------+
|  1 | z3   |
|  2 | l4   |
+----+------+
2 rows in set (0.00 sec)
[root@mysql ~]# mysql -uroot -p123 < /opt/mybin.290.sql 
#驗(yàn)證
mysql> select * from bbs.tb1;
+----+------+
| id | name |
+----+------+
|  1 | z3   |
|  2 | l4   |
+----+------+
2 rows in set (0.00 sec)
[root@mysql ~]# mysql -uroot -p123 < /opt/mybin.412.sql
#驗(yàn)證
mysql> select * from bbs.tb1;
+----+------+
| id | name |
+----+------+
|  1 | z3   |
|  2 | l4   |
|  3 | w5   |
+----+------+
3 rows in set (0.00 sec)

        可以看到完整的都恢復(fù)過(guò)來(lái)了 mysqlbinlog 可以使用多個(gè)選項(xiàng)，常見的選項(xiàng)有以下幾個(gè)：

--start-datetime 從二進(jìn)制日志中讀取指定時(shí)間戳或者本地計(jì)算機(jī)時(shí)間之后的日志事件。

--stop-datetime 從二進(jìn)制日志中讀取指定時(shí)間戳或者本地計(jì)算機(jī)時(shí)間之前的日志事件。

--start-position從二進(jìn)制日志中讀取指定position 事件位置作為開始。

--stop-position 從二進(jìn)制日志中讀取指定position 事件位置作為事件截至。

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)出的操作

    #導(dǎo)出單表
[root@mysql ~]# mysqldump -uroot -p123 庫(kù)名  表名 > 備份路徑
    #導(dǎo)出多表
[root@mysql ~]# mysqldump -uroot -p123 庫(kù)名 表名1 表名2 ...> 備份路徑
    #導(dǎo)出所有表
[root@mysql ~]# mysqldump -uroot -p123 庫(kù)名 > 備份路徑
    #導(dǎo)出單庫(kù)
[root@mysql ~]# mysqldump -uroot -p123 --databases[-B] 庫(kù)名  > 備份路徑
    #導(dǎo)出多庫(kù)
[root@mysql ~]# mysqldump -uroot -p123 --databases[-B] 庫(kù)名1 庫(kù)名2 ... > 備份路徑
    #導(dǎo)出所有庫(kù)
[root@mysql ~]# mysqldump -uroot -p123 --all-databases[-A] > 備份路徑
    #--flush-logs這個(gè)選項(xiàng)就會(huì)完整備份的時(shí)候重新開啟一個(gè)新binlog
[root@mysql ~]# mysqldump -uroot -p --flush-logs 庫(kù)名 > 備份路徑

數(shù)據(jù)庫(kù)的導(dǎo)入

[root@mysql ~]# mysql -uroot -p123 庫(kù)名 < 備份路徑 

mysql安裝自帶的一些庫(kù)丟失，靠備份導(dǎo)入?yún)s不能實(shí)現(xiàn)恢復(fù)，需要初始化庫(kù)后在導(dǎo)入才能恢復(fù)。那核心庫(kù)丟失如何恢復(fù)？下面跟著步驟備份庫(kù)，刪除庫(kù)，并且恢復(fù)回來(lái)。

mysqldump+binlog

在前面我們介紹了mysql的binlog和mysqldump工具，下面我們來(lái)學(xué)習(xí)如何實(shí)現(xiàn)mysqldump全庫(kù)備份+binlog的數(shù)據(jù)恢復(fù)。

先開啟二進(jìn)制日志

[root@mysql ~]# vim /etc/my.cnf
log_bin=mysql-bin
server_id=1
[root@mysql ~]# systemctl restart mysqld

檢查開啟binlog 先創(chuàng)建一些原始數(shù)據(jù)

mysql> reset master;
Query OK, 0 rows affected (0.00 sec)
?
mysql> create database test_db;
Query OK, 1 row affected (0.00 sec)
?
mysql> use test_db;
Database changed
mysql> create table tb1(id int primary key auto_increment,name varchar(20));
Query OK, 0 rows affected (0.07 sec)
?
mysql> insert into tb1(name) values("tom1"),("tom2");
Query OK, 2 rows affected (0.02 sec)
Records: 2  Duplicates: 0  Warnings: 0
?
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
?
mysql> select * from tb1;
+----+------+
| id | name |
+----+------+
|  1 | tom1 |
|  2 | tom2 |
+----+------+
2 rows in set (0.00 sec)

方案：mysqldump全庫(kù)備份+binlog還原

1、mysqldump備份方案： 每周一凌晨1點(diǎn)全庫(kù)備份

2、備份步驟

（1） 創(chuàng)建備份目錄

[root@mysql ~]# mkdir -p /opt/mysqlbackup/daily

（2）全庫(kù)備份 這里我們模擬周一的完整備份數(shù)據(jù)庫(kù)任務(wù)

[root@mysql ~]# mysqldump -uroot -p123 --flush-logs test_db > /opt/mysqlbackup/test_db_`date +%Y%m%d_%H%M%S`.sql    #備份庫(kù) 時(shí)間戳命名
[root@mysql ~]# ll /opt/mysqlbackup/
總用量 4
drwxr-xr-x. 2 root root    6 3月  29 13:45 daily
-rw-r--r--. 1 root root 1871 3月  29 13:46 test_db_20230329_134659.sql

備份mysqldump全庫(kù)備份之前的binlog日志文（注：生產(chǎn)環(huán)境中可能不只一個(gè)binlog文件）

[root@mysql ~]# cp /usr/local/mysql/data/mysql-bin.000001 /opt/mysqlbackup/daily/
[root@mysql ~]# mysql -uroot -p123 -e "purge binary logs to "mysql-bin.000002""

登錄mysql模擬下操作失誤,將數(shù)據(jù)修改錯(cuò)誤了。

mysql> use test_db;
Database changed
mysql> delete from tb1 where id=1;
Query OK, 1 row affected (0.01 sec)
?
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
?
mysql> insert into tb1(name) values("tom3");
Query OK, 1 row affected (0.00 sec)
?
mysql> commit;
Query OK, 0 rows affected (0.00 sec)

備份自mysqldump之后的binlog日志文件

[root@mysql ~]# 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ù)

[root@mysql ~]# mysql -uroot -p123 test_db < /opt/mysqlbackup/test_db_20230329_135149.sql 

查詢數(shù)據(jù)

[root@mysql ~]# mysql -uroot -p123 -e "select * from test_db.tb1"
mysql: [Warning] Using a password on the command line interface can be insecure.
+----+------+
| 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 |
+------------------+-----------+
1 row in set (0.01 sec)

查看mysql-bin.000002中的事件，可以看到有刪除事件

mysql> show binlog events in "mysql-bin.000002";
#省略部分內(nèi)容
| mysql-bin.000002 |  219 | Query  | 1 | 294 | BEGIN |
| mysql-bin.000002 |  294 | Table_map      | 1 | 346 | table_id: 109 (test_db.tb1)   |
| mysql-bin.000002 |  346 | Delete_rows    | 1 | 391 | table_id: 109 flags: STMT_END_F   |
| mysql-bin.000002 |  391 | Xid    | 1 | 422 | COMMIT /* xid=43 */       

使用mysqlbinlog 命令可以查看備份的binlog文件的詳細(xì)事件。 恢復(fù)流程：我們直接用bin-log日志將數(shù)據(jù)庫(kù)恢復(fù)到刪除位置前,然后跳過(guò)故障點(diǎn),再進(jìn)行恢復(fù)刪除后的所有操作。

[root@mysql ~]# mysqlbinlog -v /opt/mysqlbackup/daily/mysql-bin.000002 
#省略查看內(nèi)容

我們先用mysqlbinlog命令找到delete那條語(yǔ)句的位置

# at 219
#230329 13:53:58 server id 1  end_log_pos 294 CRC32 0x557ff3dc  Query   thread_id=2 exec_time=0 error_code=0
SET TIMESTAMP=1680069238/*!*/;
SET @@session.pseudo_thread_id=2/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1436549152/*!*/;
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
#230329 13:53:58 server id 1  end_log_pos 346 CRC32 0xa80266ea  Table_map: `test_db`.`tb1` mapped to number 109
# at 346
#230329 13:53:58 server id 1  end_log_pos 391 CRC32 0x69164e4d  Delete_rows: table id 109 flags: STMT_END_F
?
BINLOG "
dtIjZBMBAAAANAAAAFoBAAAAAG0AAAAAAAEAB3Rlc3RfZGIAA3RiMQACAw8CPAAC6mYCqA==
dtIjZCABAAAALQAAAIcBAAAAAG0AAAAAAAEAAgAC//wBAAAABHRvbTFNThZp
"/*!*/;
### DELETE FROM `test_db`.`tb1`
### WHERE
###   @1=1
###   @2="tom1"
# at 391
#230329 13:53:58 server id 1  end_log_pos 422 CRC32 0xfa0ce547  Xid = 43
COMMIT/*!*/;

通過(guò)mysqlbinlog命令所顯示的結(jié)果可以看到誤操作delete的開始postion為219，結(jié)束position是422。 從二進(jìn)制日志中讀取指定position=219事件位置作為截至，即把數(shù)據(jù)恢復(fù)到delete刪除前

[root@mysql ~]# mysqlbinlog --stop-position=219 /opt/mysqlbackup/daily/mysql-bin.000002 | mysql -uroot -p123

從二進(jìn)制日志中讀取指定position=422事件位置作為開始，即跳過(guò)刪除事件，恢復(fù)刪除事件之后對(duì)數(shù)據(jù)的正常操作

[root@mysql ~]# mysqlbinlog --start-position=422 /opt/mysqlbackup/daily/mysql-bin.000002 | mysql -uroot -p123

查看恢復(fù)結(jié)果：

[root@mysql ~]# mysql -uroot -p123 -e "select * from test_db.tb1"
mysql: [Warning] Using a password on the command line interface can be insecure.
+----+------+
| id | name |
+----+------+
|  1 | tom1 |
|  2 | tom2 |
|  3 | tom3 |
+----+------+

從上面顯示可以看出數(shù)據(jù)恢復(fù)到正常狀態(tài) 生產(chǎn)環(huán)境中Mysql數(shù)據(jù)庫(kù)的備份是周期性重復(fù)的操作，所以通常是要編寫腳本實(shí)現(xiàn)，通過(guò)crond計(jì)劃任務(wù)周期性執(zhí)行備份腳本 mysqldump

備份方案： 周日凌晨1點(diǎn)全庫(kù)備份 周一到周六凌晨每隔4個(gè)小時(shí)增量備份一次 設(shè)置crontab任務(wù)，每天執(zhí)行備份腳本

[root@mysql ~]# 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@mysql ~]# vim mysqlfullbackup.sh
#!/bin/sh
# Name:mysqlFullBackup.sh
# 定義數(shù)據(jù)庫(kù)目錄
mysqlDir=/usr/local/mysql
# 定義用于備份數(shù)據(jù)庫(kù)的用戶名和密碼
user=root
userpwd=123
dbname=test_db
# 定義備份目錄
databackupdir=/opt/mysqlbackup
[ ! -d $databackupdir ] && mkdir $databackupdir
# 定義郵件正文文件
emailfile=$databackupdir/email.txt
# 定義郵件地址
[email protected]
# 定義備份日志文件
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
# 寫日志文件
echo "--------------------------------------------------------" >> $logfile
cat $emailfile >> $logfile
# 發(fā)送郵件通知
cat $emailfile | mail -s "MySQL Backup" $email

mysqldailybackup.sh腳本內(nèi)容：

[root@mysql ~]# vim mysqldailbackup.sh
#!/bin/sh
# Name:mysqlDailyBackup.sh
# 定義數(shù)據(jù)庫(kù)目錄和數(shù)據(jù)目錄
mysqldir=/usr/local/mysql
datadir=$mysqldir/data
# 定義用于備份數(shù)據(jù)庫(kù)的用戶名和密碼
user=root
userpwd=123456
# 定義備份目錄，每日備份文件備份到$dataBackupDir/daily
databackupdir=/opt/mysqlbackup
dailybackupdir=$databackupdir/daily
[ ! -d $dailybackupdir ] && mkdir -p $databackupdir/daily
# 定義郵件正文文件
emailfile=$databackupdir/email.txt
# 定義郵件地址
[email protected]
# 定義日志文件
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
# 寫日志文件
echo "--------------------------------------------------------" >> $logfile
cat $emailfile >> $logfile

總結(jié)

到此這篇關(guān)于MySQL中binlog+dump備份還原的文章就介紹到這了,更多相關(guān)MySQL binlog+dump備份還原內(nèi)容請(qǐng)搜索以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持！