前言
看完本文,刪庫跑路!? 再也不可能發生了!一定要養成風險操作備份數據的習慣,避免恢復數據浪費時間。
本文內容:
- binlog 的作用
- mysqldump 和 mysqlbinlog 做數據備份和數據恢復。
- XtraBackup 全量備份和增量備份
binlog
binlog 作用及配置
Mysql 的 binlog
(二進制日誌) 是 Server
層的,不管你的存儲引擎是什麼都可以使用 binlog
。
binlog
記錄的是數據庫 DML
和 DDL
修改的數據內容,也可以用於數據的備份與恢復。一般我們會用
binlog
也用於主從複製,從庫請求主庫的 binlog
寫入到自己的中繼日誌,然後將中繼日誌轉換為 sql
,然後將 sql 執行在從庫執行。
-- 查看是否開啟 binlog
SHOW VARIABLES LIKE '%log_bin%'
mysql> SHOW VARIABLES LIKE '%log_bin%';
+---------------------------------+-----------------------------+
| Variable_name | Value |
+---------------------------------+-----------------------------+
| log_bin | ON |
| log_bin_basename | /var/lib/mysql/binlog |
| log_bin_index | /var/lib/mysql/binlog.index |
| log_bin_trust_function_creators | OFF |
| log_bin_use_v1_row_events | OFF |
| sql_log_bin | ON |
+---------------------------------+-----------------------------+
開啟二進制日誌配置
log_bin
配置是否啟用 binlog
。Mysql 8.0
默認開啟 binlog
。
log_bin_index
配置的是 binlog
日誌文件的索引信息。這個配置最好配置了之後不要修改。
log_bin_basename
配置的是 binlog
日誌的基礎路徑名稱。
server_id
這個也需要配置,在一個集群中這個數字不能重複。
sql_log_bin
配置當前會話 DML 和 DDL 語句是否記錄。
[root@centos-7 mysql]# pwd
/var/lib/mysql
[root@centos-7 mysql]# ll | grep binlog
-rw-r-----. 1 mysql mysql 16162 11月 21 15:58 binlog.000013
-rw-r-----. 1 mysql mysql 179 11月 21 15:58 binlog.000014
-rw-r-----. 1 mysql mysql 3765 11月 22 14:42 binlog.000015
-rw-r-----. 1 mysql mysql 1700 11月 23 23:40 binlog.000016
-rw-r-----. 1 mysql mysql 64 11月 22 14:42 binlog.index
[root@centos-7 mysql]#
binlog 日誌格式
-- 查看當前 binlog 文件存儲什麼數據
SHOW VARIABLES LIKE '%binlog_format%';
binlog
日誌格式有以下三種
STATEMENT
記錄的是 sql 語句。
ROW
Mysql 8.0
默認採用這個格式。記錄每行的修改。相較於 STATEMENT
它可能記錄的內容會更多,但是主從複製時更安全。
比如全表更新 update test set a=1;
STATEMENT
只會記錄這個 sql
,而 ROW
會記錄所有數據的修改。
MIXED
當需要時,Mysql 將日誌格式從 STATEMENT
切換為 ROW
。
比如說更新語句可能記錄為邏輯 sql (STATEMENT
),而插入語句記錄為(ROW
) 格式。
binlog 日誌格式驗證
創建一張表,插入 10 w 數據
DROP TABLE IF EXISTS `account`;
CREATE TABLE `account` (
`id` int NOT NULL AUTO_INCREMENT,
`username` varchar(255) DEFAULT NULL,
`age` int DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=100000 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
DROP PROCEDURE if EXISTS insertData;
CREATE PROCEDURE insertData ( )
BEGIN
DECLARE i INT DEFAULT 1;
WHILE i < 10000 DO
SET i = i + 1;
INSERT INTO account ( username, age )VALUES( '測試', 12 );
END WHILE;
END;
CALL insertData ( );
-
binlog_format
在ROW
模式下記錄的是每行數據的修改
mysql> SHOW BINLOG EVENTS IN 'binlog.000018' limit 10;
+---------------+-------+----------------+-----------+-------------+--------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+---------------+-------+----------------+-----------+-------------+--------------------------------------+
| binlog.000018 | 4 | Format_desc | 1 | 125 | Server ver: 8.0.21, Binlog ver: 4 |
| binlog.000018 | 125 | Previous_gtids | 1 | 156 | |
| binlog.000018 | 156 | Anonymous_Gtid | 1 | 236 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| binlog.000018 | 236 | Query | 1 | 322 | BEGIN |
| binlog.000018 | 322 | Table_map | 1 | 386 | table_id: 99 (ceshi2.account) |
| binlog.000018 | 386 | Update_rows | 1 | 8600 | table_id: 99 |
| binlog.000018 | 8600 | Update_rows | 1 | 16814 | table_id: 99 |
| binlog.000018 | 16814 | Update_rows | 1 | 25028 | table_id: 99 |
| binlog.000018 | 25028 | Update_rows | 1 | 33242 | table_id: 99 |
| binlog.000018 | 33242 | Update_rows | 1 | 41456 | table_id: 99 |
+---------------+-------+----------------+-----------+-------------+--------------------------------------+
-
binlog_format
在STATEMENT
模式下記錄的是 sql
flush logs;
update ceshi2.account set username='2';
mysql> SHOW BINLOG EVENTS IN 'binlog.000019' limit 10;
+---------------+-----+----------------+-----------+-------------+----------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+---------------+-----+----------------+-----------+-------------+----------------------------------------+
| binlog.000019 | 4 | Format_desc | 1 | 125 | Server ver: 8.0.21, Binlog ver: 4 |
| binlog.000019 | 125 | Previous_gtids | 1 | 156 | |
| binlog.000019 | 156 | Anonymous_Gtid | 1 | 235 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| binlog.000019 | 235 | Query | 1 | 324 | BEGIN |
| binlog.000019 | 324 | Query | 1 | 446 | update ceshi2.account set username='2' |
| binlog.000019 | 446 | Xid | 1 | 477 | COMMIT /* xid=300671 */ |
+---------------+-----+----------------+-----------+-------------+----------------------------------------+
binlog 操作
查看所有的 binlog
-- 查看鏈接的數據庫 binlog 文件信息
SHOW BINARY LOGS;
SHOW MASTER LOGS;
mysql> SHOW BINARY LOGS;
+---------------+-----------+-----------+
| Log_name | File_size | Encrypted |
+---------------+-----------+-----------+
| binlog.000013 | 16162 | No |
| binlog.000014 | 179 | No |
| binlog.000015 | 3765 | No |
| binlog.000016 | 1700 | No |
+---------------+-----------+-----------+
查看當前正在寫入的 binlog
-- 查看當前正在寫入的 binlog 文件信息
SHOW MASTER STATUS;
mysql> SHOW MASTER STATUS;
+---------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| binlog.000016 | 1700 | | | |
+---------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
生成新的 binlog
-- 刷新產生新的日誌文件
FLUSH LOGS;
-- 原來的日誌文件是 binlog.000016
mysql> SHOW MASTER STATUS;
+---------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| binlog.000017 | 156 | | | |
+---------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
查看 binlog 中的操作
SHOW BINLOG EVENTS
[IN 'log_name']
[FROM pos]
[LIMIT [offset,] row_count]
mysql> show binlog events limit 100,3;
+---------------+------+----------------+-----------+-------------+-------------------------------------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+---------------+------+----------------+-----------+-------------+-------------------------------------------------------------------------------------+
| binlog.000013 | 9382 | Query | 1 | 9539 | use `ceshi`; GRANT SELECT ON `ceshi`.`test2` TO 'db_dev'@'localhost' /* xid=1023 */ |
| binlog.000013 | 9539 | Anonymous_Gtid | 1 | 9616 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| binlog.000013 | 9616 | Query | 1 | 9711 | use `ceshi`; FLUSH PRIVILEGES |
+---------------+------+----------------+-----------+-------------+-------------------------------------------------------------------------------------+
binlog 落盤時機
Mysql 中有很多 Buffer Pool
(可以簡單理解為內存),為了提高數據庫性能,一般提交事務之後,二進制日誌先寫入 Buffer Poll
,在寫入到二進制文件中。
如果二進制日誌沒有落盤,那麼二進制日誌有可能丟失,從庫進行復制時會丟失數據。
參數 sync_binlog
配置寫入 Buffer Poll
多少次的時候調用系統調用 fsync
將內存中的二進制日誌數據落盤。
-
sync_binlog=1
表示提交事務的時候同步將二進制日誌數據落盤。配合innodb_flush_log_at_trx_commit=1
(控制 redo log 落盤) 數據安全。 -
sync_binlog=N
表示提交了 N 個二進制數據時才將日誌數據落盤。也有人將其設置為 2,提高併發性,系統崩潰時可能丟失二進制日誌數據。 -
sync_binlog=0
表示由操作系統 IO 調度來決定日誌什麼時候落盤。一般沒人採用這個。
Mysql 備份和恢復
為了避免意外情況發生導致數據丟失,數據庫需要定時全備和增量備份。以便於可以將數據庫恢復到任意時間點的數據。
根據備份方法的不同可以劃分為:
- 熱備(Hot Backup)
- 冷備 (Clod Backup)
熱備是在數據庫正在運行時直接備份,對業務的影響不大。
冷備需要停止 Mysql 進行備份,速度較快。可以在從庫進行冷備。
根據備份後的文件內容可以劃分為:
- 邏輯備份,數據庫執行的 sql 內容
- 文件備份,備份數據庫的物理文件
一般我們會定時對數據執行備份腳本,然後將備份的內容壓縮發送到存儲文件的服務器,比如 OSS
。
備份與恢復使用到程序
- mysqldump,對數據庫進行不停機執行邏輯備份及恢復
- mysqlbinlog,操作 binlog 日誌,使數據恢復到某個時間點的數據
- xtrabackup,percona 開源工具,對數據庫不停機進行文件備份
mysqldump 使用
備份某些數據庫
mysqldump --master-data --single-transaction --databases ceshi2 ceshi -h10.211.55.8 -uroot -pMysql@12345678 > backup.sql
備份所有數據庫
mysqldump --master-data --single-transaction --all-databases -h10.211.55.8 -uroot -pMysql@12345678 > backup.sql
參數說明
-
--single-transaction
用於全是InnoDB
表的備份。備份開始執行前START TRANSACTION
會開啟事務,由於MVCC
的特性這種備份不會影響數據庫讀寫,而且還保證了備份期間數據的一致性 -
--master-data
為 1 時記錄CHANGE MASTER
語句,可以在從庫中使用備份的文件,比如新增加一個從庫,就可以在從庫上執行這個備份的數據。為 2 時 會註釋CHANGE MASTER
。 -
--lock-tables
鎖住單個數據庫中所有表,只允許讀取數據。為了保證備份時數據的一致性。因為只能鎖住單個數據庫,如果有多個數據庫就不能保證數據的一致性了。當數據庫採用的存儲引擎既有InnoDB
和MyISAM
時需要使用這個屬性 -
--lock-all-tables
鎖住備份所有數據庫的表,能保證多個數據庫數據的一致性。 -
--databases
可以指定備份哪些數據庫實例 -
--all-databases
備份連接中所有的數據庫實例。 -
--evnets
備份事件調度器 -
--routines
備份存儲過程和存儲函數 -
--triggers
備份觸發器 -
--flush-logs
導出之前刷新日誌,因為有的數據在內存中,可能還沒有寫入到二進制日誌中
mysqlbinlog 使用
mysqlbinlog 可以解析 binlog
生成 sql語句。
# 在本地生成 sql
mysqlbinlog --disable-log-bin /Users/zhangpanqin/Desktop/binlog.000019 > test.sql
mysqlbinlog --disable-log-bin /Users/zhangpanqin/Desktop/binlog.000019 > test.sql
# 根據日誌的位置
mysqlbinlog binlog.000019 --disable-log-bin --start-position 775 > 775.sql
mysqlbinlog binlog.000019 --disable-log-bin --start-position 477 --stop-position 556 > 477-556.sql
# 根據時間
mysqlbinlog binlog.000019 --start-date='2017-12-19 10:10:00' --stop-date='2017-12-19 18:52:00' > aa.sql
# 鏈接遠程使用
mysqlbinlog --disable-log-bin --read-from-remote-server --host=10.211.55.8 --user=root --password=Mysql@12345678 binlog.000019 binlog.000020> remote_test.sql
-
--start-position
指定從哪個位置開始 -
--stop-position
指定從哪個位置開始 -
--start-datetime
指定開始時間 -
--stop-datetime
指定結束時間 -
--disable-log-bin
生成的 sql 語句中,添加SET SQL_LOG_BIN=0
,執行轉換的 sql 時,不會生成二進制日誌 -
--read-from-remote-server
從遠程服務器讀取
數據恢復
一般我們會使用 mysqldump
進行一個全量備份,在這個全量備份的基礎上,從 binlog
提取後續 sql 進行數據恢復。
模擬一個場景
1、比如我們在某個 2020-11-28 16:30:00
進行了全量備份。
2、2020-11-28 16:35:00
刪除了 account
表中全部數據
3、刪除之後不知道,又插入了兩條數據
INSERT INTO `ceshi2`.`account`(`id`, `username`, `age`) VALUES (11111111, '刪除全庫之後插入', 11);
INSERT INTO `ceshi2`.`account`(`id`, `username`, `age`) VALUES (11111112, 'asdfasd', 12);
恢復數據的時候,為避免恢復操作寫入到二進制日誌中去,需要暫時關閉二進制日誌,恢復會話期間不寫入二進制日誌
SET SQL_LOG_BIN=0;
SHOW VARIABLES LIKE '%sql_log_bin%';
現在開始對數據庫進行數據恢復
- 開始恢復之前先
flush logs
刷新新的二進制日誌
mysql> show master status;
+---------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| binlog.000020 | 156 | | | |
+---------------+----------+--------------+------------------+-------------------+
- 設置當前會話不記錄二進制日誌,並恢復全備數據
(echo "SET SQL_LOG_BIN=0;";cat /Users/zhangpanqin/Desktop/backup.sql) | mysql -u root -h 10.211.55.8 -pMysql@12345678 -f
- 查看 backup.sql 記錄的是什麼時候備份的數據
/*CHANGE MASTER TO MASTER_LOG_FILE='binlog.000019', MASTER_LOG_POS=477;*/
- 使用 mysqlbinlog 導出
binlog
從位置477
開始的 sql
-- 笨的方法就是,查看刪除的 sql 語句
SHOW BINLOG EVENTS IN 'binlog.000019' FROM 477 LIMIT 0,10;
mysql> SHOW BINLOG EVENTS IN 'binlog.000019' FROM 477 LIMIT 0,10;
+---------------+------+----------------+-----------+-------------+-------------------------------------------------------------------------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+---------------+------+----------------+-----------+-------------+-------------------------------------------------------------------------------------------------------------------------+
| binlog.000019 | 477 | Anonymous_Gtid | 1 | 556 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| binlog.000019 | 556 | Query | 1 | 642 | BEGIN |
| binlog.000019 | 642 | Query | 1 | 744 | use `ceshi2`; DELETE FROM `account` |
| binlog.000019 | 744 | Xid | 1 | 775 | COMMIT /* xid=300922 */ |
| binlog.000019 | 775 | Anonymous_Gtid | 1 | 854 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| binlog.000019 | 854 | Query | 1 | 940 | BEGIN |
| binlog.000019 | 940 | Query | 1 | 1126 | use `ceshi2`; INSERT INTO `ceshi2`.`account`(`id`, `username`, `age`) VALUES (11111111, '刪除全庫之後插入', 11) |
| binlog.000019 | 1126 | Xid | 1 | 1157 | COMMIT /* xid=301033 */ |
| binlog.000019 | 1157 | Anonymous_Gtid | 1 | 1236 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| binlog.000019 | 1236 | Query | 1 | 1322 | BEGIN |
+---------------+------+----------------+-----------+-------------+-------------------------------------------------------------------------------------------------------------------------+
-- 導出 477-556 之間的 sql
mysqlbinlog binlog.000019 --start-position 477 --stop-position 556 > 477-556.sql
-- 導出 從 775 開始的 sql
mysqlbinlog binlog.000019 --start-position 775> 775.sql
這裡比較好的做法就是直接使用工具直接解析 sql
https://github.com/danfengcao/binlog2sql
- 執行剩下的 sql
(echo "SET SQL_LOG_BIN=0;";cat /Users/zhangpanqin/Desktop/477-556.sql) | mysql -u root -h 10.211.55.8 -pMysql@12345678 -f
(echo "SET SQL_LOG_BIN=0;";cat /Users/zhangpanqin/Desktop/775.sql) | mysql -u root -h 10.211.55.8 -pMysql@12345678 -f
- 查看 binlog 日誌,沒有添加二進制日誌到數據庫中,不影響從庫
mysql> SHOW MASTER STATUS;
+---------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| binlog.000020 | 156 | | | |
+---------------+----------+--------------+------------------+-------------------+
XtraBackup 使用
XtraBackup 只能備份InnoDB和XtraDB 兩種數據表。
安裝
系統環境:Centos 7 x86_64
數據庫:Mysql 8.0.21
由以上環境決定了 xtrabackup
需要安裝 8.0.14 版本。
https://www.percona.com/doc/percona-xtrabackup/8.0/installation/yum_repo.html
wget https://downloads.percona.com/downloads/Percona-XtraBackup-LATEST/Percona-XtraBackup-8.0.14/binary/redhat/7/x86_64/percona-xtrabackup-80-8.0.14-1.el7.x86_64.rpm
yum localinstall percona-xtrabackup-80-8.0.14-1.el7.x86_64.rpm
# 驗證版本
xtrabackup --version
命令講解
-
--backup
備份操作,備份數據到--target-dir
指定的目錄。 -
--prepare
恢復數據執行的階段。 -
--use-memory
指定備份時佔用的內存,--use-memory=4G。 -
--copy-back
將準備好的數據文件複製到 mysql datadir 目錄。 -
`--apply-log-only
阻止回滾未完成的事務
全量備份
創建備份使用的用戶
CREATE USER 'xtrabackup'@'localhost' IDENTIFIED BY 'Mysql@12345678';
GRANT BACKUP_ADMIN, PROCESS, RELOAD, LOCK TABLES, REPLICATION CLIENT ON *.* TO 'xtrabackup'@'localhost';
GRANT SELECT ON performance_schema.log_status TO 'xtrabackup'@'localhost';
FLUSH PRIVILEGES;
xtrabackup 全量備份
xtrabackup --host=localhost --user=xtrabackup --password=Mysql@12345678 --backup --target-dir=/opt/test22/backup
全量數據恢復
# 先停止數據庫
systemctl stop mysqld
# 恢復數據執行的準備
xtrabackup --host=localhost --user=xtrabackup --password=Mysql@12345678 --prepare --target-dir=/opt/test22/backup
# 備份數據庫文件,並刪除數據庫數據目錄下的文件
cp -r /var/lib/mysql{,"$(date '+%Y-%m-%d %H:%M:%S')"_bak} && rm -fr /var/lib/mysql/*
# 恢復數據
xtrabackup --host=localhost --user=xtrabackup --password=Mysql@12345678 --copy-back --target-dir=/opt/test22/backup
# 查看 /var/lib/mysql 目錄下所有文件的所屬人,需要改成 mysqld 運行的用戶
chown -R mysql:mysql /var/lib/mysql
# 啟動 mysql 數據庫
systemctl start mysqld
增量備份
在全量備份的基礎上,增量備份。
增量備份
# 創建全量備份在那個目錄下
mkdir -p /opt/xtrabackup_mysql/full_data_dir
# 全量基礎之後的增量數據一次
mkdir -p /opt/xtrabackup_mysql/increment_data_dir
# 在上一次增量備份的基礎上在增量備份一次
mkdir -p /opt/xtrabackup_mysql/increment_data_dir_2
# 全量備份
xtrabackup --defaults-file=/etc/my.cnf --host=localhost --user=xtrabackup --password=Mysql@12345678 --backup --parallel=3 --target-dir=/opt/xtrabackup_mysql/full_data_dir
# 全量備份之後,操作數據。
# 做增量備份
xtrabackup --defaults-file=/etc/my.cnf --host=localhost --user=xtrabackup --password=Mysql@12345678 --backup --parallel=3 --target-dir=/opt/xtrabackup_mysql/increment_data_dir --incremental-basedir=/opt/xtrabackup_mysql/full_data_dir
# 操作了數據之後,在上一次增量備份基礎上做第二次增量備份
xtrabackup --defaults-file=/etc/my.cnf --host=localhost --user=xtrabackup --password=Mysql@12345678 --backup --parallel=3 --target-dir=/opt/xtrabackup_mysql/increment_data_dir_2 --incremental-basedir=/opt/xtrabackup_mysql/increment_data_dir
增量備份數據恢復
# 停止數據庫
systemctl stop mysqld
# 準備全備份日誌數據
xtrabackup --defaults-file=/etc/my.cnf --prepare --apply-log-only --target-dir=/opt/xtrabackup_mysql/full_data_dir
# 合併第一次增量備份數據到全量中,注意路徑別寫錯了
xtrabackup --defaults-file=/etc/my.cnf --prepare --apply-log-only --target-dir=/opt/xtrabackup_mysql/full_data_dir --incremental-dir=/opt/xtrabackup_mysql/increment_data_dir
# 合併第二次增量備份數據到全量中,注意路徑。最後一次不需要添加 --apply-log-only
xtrabackup --defaults-file=/etc/my.cnf --prepare --target-dir=/opt/xtrabackup_mysql/full_data_dir --incremental-dir=/opt/xtrabackup_mysql/increment_data_dir_2
# 將原來數據庫備份
cp -r /var/lib/mysql{,"$(date '+%Y-%m-%d %H:%M:%S')"_bak} && rm -fr /var/lib/mysql/*
-- 拷回數據
xtrabackup --defaults-file=/etc/my.cnf --copy-back --target-dir=/opt/xtrabackup_mysql/full_data_dir
# 修改mysql 數據文件的權限為 mysql
chown -R mysql:mysql /var/lib/mysql
# 啟動數據庫
systemctl start mysqld