MySQL中binlog+dump備份還原詳細(xì)教程
binlog日志恢復(fù)
MySQL備份一般采取全庫備份加日志備份的方式，例如每天執(zhí)行一次全備份，每小時(shí)執(zhí)行一次二進(jìn)制日志備份。這樣在MySQL故障后可以使用全備份和日志備份將數(shù)據(jù)恢復(fù)到最后一個(gè)二進(jìn)制日志備份前的任意位置或時(shí)間。
binlog介紹
mysql的二進(jìn)制日志記錄著該數(shù)據(jù)庫的所有增刪改的操作日志(前提是要在自己的服務(wù)器上開啟binlog)，還包括了這些操作的執(zhí)行時(shí)間。為了顯示這些二進(jìn)制內(nèi)容，我們可以使用mysqlbinlog命令來查看。
Binlog的用途
主從同步
恢復(fù)數(shù)據(jù)庫
開啟binary log功能
通過編輯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)制文件，不能通過文件內(nèi)容查看命令直接打開查看，mysql提供兩種方式查看方式，在介紹之前，我們先對數(shù)據(jù)庫進(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)制日志信息的命令：
語法格式：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ù)場景還得借助mysqlbinlog這個(gè)工具。
語法格式： mysqlbinlog [options] log_file ... 輸出內(nèi)容會(huì)因日志文件的格式以及mysqlbinlog工具使用的選項(xiàng)不同而略不同。 mysqlbinlog的可用選項(xiàng)可參考man手冊。 二進(jìn)制日志文件的格式包含行模式、語句模式和混合模式（也即有服務(wù)器決定在什么情況下記錄什么類型的日志），基于語句的日志中事件信息包含執(zhí)行的語句等，基于行的日志中事件信息包含的是行的變化信息等?；旌夏Ｊ降娜罩局袃煞N類型的事件信息都會(huì)記錄。 為了便于查看記錄了行變化信息的事件在當(dāng)時(shí)具體執(zhí)行了什么樣的SQL語句可以使用mysqlbinlog工具的-v（--verbose）選項(xiàng)，該選項(xiàng)會(huì)將行事件重構(gòu)成被注釋掉的偽SQL語句，如果想看到更詳細(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和可以通過--read-from-remote-server選項(xiàng)從遠(yuǎn)程服務(wù)器讀取二進(jìn)制日志文件，這時(shí)需要一些而外的連接參數(shù)，如-h，-P，-p，-u等，這些參數(shù)僅在指定了--read-from-remote-server后有效。 無論是本地二進(jìn)制日志文件還是遠(yuǎn)程服務(wù)器上的二進(jìn)制日志文件，無論是行模式、語句模式還是混合模式的二進(jìn)制日志文件，被mysqlbinlog工具解析后都可直接應(yīng)用與MySQL Server進(jìn)行基于時(shí)間點(diǎn)、位置或數(shù)據(jù)庫的恢復(fù)。
下面我們就來演示如何使用binlog恢復(fù)之前刪除數(shù)據(jù)（id=2那條記錄） 注意：在實(shí)際生產(chǎn)環(huán)境中，如果遇到需要恢復(fù)數(shù)據(jù)庫的情況，不要讓用戶能訪問到數(shù)據(jù)庫，以避免新的數(shù)據(jù)插入進(jìn)來，以及在主從的環(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ù)庫恢復(fù)到刪除位置290前,然后跳過故障點(diǎn),再進(jìn)行恢復(fù)下面所有的操作,命令如下 由于之前沒有做過全庫備份，所以要使用所有binlog日志恢復(fù)，所以生產(chǎn)環(huán)境中需要很長時(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ù)庫
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ù)過來了 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語句，可以封裝到文件，該文件包含有所有重建你的數(shù)據(jù)庫所需要的 SQL命令如CREATE DATABASE，CREATE TABLE，INSERT等等。可以用來實(shí)現(xiàn)輕量級(jí)的快速遷移或恢復(fù)數(shù)據(jù)庫。 mysqldump 是將數(shù)據(jù)表導(dǎo)成 SQL 腳本文件，在不同的 MySQL 版本之間升級(jí)時(shí)相對比較合適，這也是最常用的備份方法。 mysqldump一般在數(shù)據(jù)量很小的時(shí)候（幾個(gè)G）可以用于備份。當(dāng)數(shù)據(jù)量比較大的情況下，就不建議用mysqldump工具進(jìn)行備份了。
數(shù)據(jù)庫的導(dǎo)出
導(dǎo)出對象說明：mysqldump可以針對單個(gè)表、多個(gè)表、單個(gè)數(shù)據(jù)庫、多個(gè)數(shù)據(jù)庫、所有數(shù)據(jù)庫進(jìn)行導(dǎo)出的操作
#導(dǎo)出單表
[root@mysql ~]# mysqldump -uroot -p123 庫名 表名 > 備份路徑
#導(dǎo)出多表
[root@mysql ~]# mysqldump -uroot -p123 庫名 表名1 表名2 ...> 備份路徑
#導(dǎo)出所有表
[root@mysql ~]# mysqldump -uroot -p123 庫名 > 備份路徑
#導(dǎo)出單庫
[root@mysql ~]# mysqldump -uroot -p123 --databases[-B] 庫名 > 備份路徑
#導(dǎo)出多庫
[root@mysql ~]# mysqldump -uroot -p123 --databases[-B] 庫名1 庫名2 ... > 備份路徑
#導(dǎo)出所有庫
[root@mysql ~]# mysqldump -uroot -p123 --all-databases[-A] > 備份路徑
#--flush-logs這個(gè)選項(xiàng)就會(huì)完整備份的時(shí)候重新開啟一個(gè)新binlog
[root@mysql ~]# mysqldump -uroot -p --flush-logs 庫名 > 備份路徑數(shù)據(jù)庫的導(dǎo)入
[root@mysql ~]# mysql -uroot -p123 庫名 < 備份路徑
mysql安裝自帶的一些庫丟失，靠備份導(dǎo)入?yún)s不能實(shí)現(xiàn)恢復(fù)，需要初始化庫后在導(dǎo)入才能恢復(fù)。那核心庫丟失如何恢復(fù)？下面跟著步驟備份庫，刪除庫，并且恢復(fù)回來。
mysqldump+binlog
在前面我們介紹了mysql的binlog和mysqldump工具，下面我們來學(xué)習(xí)如何實(shí)現(xiàn)mysqldump全庫備份+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全庫備份+binlog還原
1、mysqldump備份方案： 每周一凌晨1點(diǎn)全庫備份
2、備份步驟
（1） 創(chuàng)建備份目錄
[root@mysql ~]# mkdir -p /opt/mysqlbackup/daily
（2）全庫備份 這里我們模擬周一的完整備份數(shù)據(jù)庫任務(wù)
[root@mysql ~]# mysqldump -uroot -p123 --flush-logs test_db > /opt/mysqlbackup/test_db_`date +%Y%m%d_%H%M%S`.sql #備份庫 時(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全庫備份之前的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的全庫備份和binlog來恢復(fù)數(shù)據(jù)。 使用mysqldump的備份進(jìn)行全庫恢復(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ù)回來了，但備份后產(chǎn)生的數(shù)據(jù)卻丟失了所以還得利用binlog進(jìn)一步還原 因?yàn)閯h除是在全庫備份后發(fā)生的，而mysqldump全庫備份時(shí)使用--flush-logs選項(xiàng)，所以只需要分析全庫備份后的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ù)庫恢復(fù)到刪除位置前,然后跳過故障點(diǎn),再進(jìn)行恢復(fù)刪除后的所有操作。
[root@mysql ~]# mysqlbinlog -v /opt/mysqlbackup/daily/mysql-bin.000002 #省略查看內(nèi)容
我們先用mysqlbinlog命令找到delete那條語句的位置
# 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/*!*/;
通過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事件位置作為開始，即跳過刪除事件，恢復(fù)刪除事件之后對數(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ù)庫的備份是周期性重復(fù)的操作，所以通常是要編寫腳本實(shí)現(xiàn)，通過crond計(jì)劃任務(wù)周期性執(zhí)行備份腳本 mysqldump
備份方案： 周日凌晨1點(diǎn)全庫備份 周一到周六凌晨每隔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ù)庫目錄 mysqlDir=/usr/local/mysql # 定義用于備份數(shù)據(jù)庫的用戶名和密碼 user=root userpwd=123 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è)置參數(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ù)庫目錄和數(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ù)庫使用新的二進(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`
# 跳過最后一個(gè)二進(jìn)制日志（數(shù)據(jù)庫當(dāng)前使用的二進(jìn)制日志文件）
if [ $nextnum -eq $icounter ]; then
echo "Skip lastest!" > /dev/null
else
dest=$dailybackupdir/$binlogname
# 跳過已經(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)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！
相關(guān)文章
優(yōu)化 MySQL 3 個(gè)簡單的小調(diào)整
本文給大家?guī)砹藘?yōu)化 MySQL 3 個(gè)簡單的小調(diào)整，需要的朋友參考下2018-02-02
在我做的一個(gè)項(xiàng)目中，最近我對生產(chǎn)服務(wù)器上的一系列系統(tǒng)軟件進(jìn)行了升級(jí)，包括git、nginx、MySQL和PHP。這篇文章講的是升級(jí)MySQL的過程，其他軟件的升級(jí)將在其他文章中介紹。2016-01-01
這篇文章主要給大家介紹了關(guān)于mysql優(yōu)化之like和=性能的相關(guān)資料，文中通過示例代碼介紹的非常詳細(xì)，對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧2020-08-08
MySQL錯(cuò)誤代碼3140:無效的JSON文本編碼問題解決辦法
下面這篇文章主要給大家介紹了關(guān)于MySQL錯(cuò)誤代碼3140:無效的JSON文本編碼問題的解決辦法,文中通過代碼介紹的非常詳細(xì),對大家學(xué)習(xí)或者使用mysql具有一定的參考借鑒價(jià)值,需要的朋友可以參考下2024-03-03
教你自動(dòng)恢復(fù)MySQL數(shù)據(jù)庫的日志文件(binlog)
如果MySQL服務(wù)器啟用了二進(jìn)制日志，你可以使用mysqlbinlog工具來恢復(fù)從指定的時(shí)間點(diǎn)開始2014-05-05
Mysqlslap是從5.1.4版開始的一個(gè)MySQL官方提供的壓力測試工具。通過模擬多個(gè)并發(fā)客戶端訪問MySQL來執(zhí)行壓力測試，同時(shí)詳細(xì)的提供了“高負(fù)荷攻擊MySQL”的數(shù)據(jù)性能報(bào)告。并且能很好的對比多個(gè)存儲(chǔ)引擎在相同環(huán)境下的并發(fā)壓力性能差別2011-10-10
這篇文章主要介紹了Mysql5.7.18的安裝與主從復(fù)制圖文詳解,需要的朋友可以參考下2017-08-08
mysql中drop、truncate與delete的區(qū)別詳析
對于drop、truncate和delete雖然簡單,但是真要使用或者面試時(shí)候問到還是需要有一定的總結(jié),下面這篇文章主要給大家介紹了關(guān)于mysql中drop、truncate與delete區(qū)別的相關(guān)資料,需要的朋友可以參考下2022-03-03
這篇文章主要介紹了簡單了解mysql存儲(chǔ)字段類型查詢效率,文中通過示例代碼介紹的非常詳細(xì)，對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值,需要的朋友可以參考下2020-09-09
這篇文章主要介紹了MySQL中的常用函數(shù)的相關(guān)資料,需要的朋友可以參考下2016-08-08