資安

Mysql數據備份與恢復

2020-11-30

前言

看完本文，刪庫跑路！？ 再也不可能發生了！一定要養成風險操作備份數據的習慣，避免恢復數據浪費時間。

本文內容：

  • binlog 的作用
  • mysqldump 和 mysqlbinlog 做數據備份和數據恢復。
  • XtraBackup 全量備份和增量備份

binlog

mysql 邏輯架構

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

binlog2sql

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

Leave a Reply

Your email address will not be published. Required fields are marked *