故障分析 | DDL 導致的 Xtrabackup 備份失敗

作者：趙黎明
愛可生 MySQL DBA 團隊成員，熟悉 Oracle、MySQL 等資料庫，擅長資料庫效能問題診斷、事務與鎖問題的分析等，負責處理客戶 MySQL 及我司自研 DMP 平臺日常運維中的問題，對開源資料庫相關技術非常感興趣。
本文來源：原創投稿
*愛可生開源社群出品，原創內容未經授權不得隨意使用，轉載請聯絡小編並註明來源。

背景

近日，客戶反饋某生產業務系統凌晨的物理備份都失敗了（一主二從的叢集，僅在兩個從庫上做 Xtrabackup 全備，主庫不參與備份），需排查備份失敗的原因。

案例分析

由於客戶使用的是我司愛可生的 DMP 資料庫管理平臺，當備份失敗時，在備份目錄中會寫入一個 FAIL 的標誌檔案，然後回滾掉殘留檔案，此時 Xtrabackup 自身的日誌已無法檢視，不過可以通過 urman-agent 元件（負責備份恢復）日誌來獲取備份失敗的資訊，以下是當時兩個從庫上的報錯資訊

從庫1日誌

從庫2日誌

兩個從庫雖然報錯的時間不同，但報錯的內容一致，都指向了“不記錄 redo 日誌的 DDL 操作”：

[FATAL] InnoDB: An optimized(without redo logging) DDLoperation has been performed. All modified pages may not have been flushed to the disk yet.
PXB will not be able take a consistent backup. Retry the backup operation

經確認，客戶的確是在凌晨執行了 DDL 業務變更，變更的內容為建立一張新表，並給現存的兩張表新增欄位，加欄位的表大約有幾百萬行記錄，這一資訊與日誌給出的內容吻合，看來問題大概率是出在加欄位的 DDL 操作上

那什麼是不記錄 redo 的 DDL 的操作呢？為何會存在不記錄 redo 的 DDL ？

首先，我們知道，在 MySQL 5.7 中給表加欄位屬於 ONLINE DLL ，會重建表，但允許併發 DML（PS：MySQL 8.0 加欄位不需要重建表）

由於 MySQL 採用的是索引組織表（IOT），表上的索引當然也需要重建，由於採用了 ALGORITHM=INPLACE 的方式，允許併發 DML

在 MySQL 5.7 中，對索引有一個優化，即 Sorted Index Builds ，會在建立或重建索引的時候通過 bulk load 、bottom-up 的方式來填充索引記錄

<br/>

採用 Sorted Index Build 方式建立索引時，不會記錄到 redo 日誌中，而這就是之前 Xtrabackup 報錯中所描述的“An optimized(without redo logging) DDL operation”的場景

Percona 稱這是 Xtrabackup 2.4.x 的一個 bug ，主要是為了避免得到一個錯誤的備份集，一旦檢測到有不記錄 redo 的 DDL 操作，就會將備份程式終止，而客戶生產環境中的 PXB 版本正是2.4.5

針對這一問題，Percona在Xtrabackup 2.4.8 及之後的版本中，新增了--lock-ddl，--lock-ddl-timeout，--lock-ddl-per-table這幾個引數，使其可以在備份時加上備份鎖，或給表加上 MDL 鎖來阻塞其他的 DDL 操作，使備份順利完成

原因明確了，就知道如何解決問題了，最後分別在2個從庫上執行手動備份，每個例項（500G左右）大約耗時2小時40分鐘完成備份

Percona 以增加引數的方式提供瞭解決備份失敗的方法，那如果暫時無法升級 PXB 版本，僅在 MySQL 層面，有沒有解決方法呢？其實也是可以的。

對於以上提到的幾種場景，我們都來測試一下吧

場景測試

環境準備

建立測試表

/usr/local/sysbench/share/sysbench# sysbench oltp_insert.lua --db-driver=mysql --threads=256 --time=300 --mysql-host=10.186.60.68 --mysql-port=3332 --mysql-user=zlm --mysql-password=zlm --tables=2 --table-size=2000000 --db-ps-mode=disable --report-interval=10 prepare
sysbench 1.0.17 (using bundled LuaJIT 2.1.0-beta2)

Initializing worker threads...

Creating table 'sbtest2'...
Inserting 2000000 records into 'sbtest2'
Creating table 'sbtest1'...
Inserting 2000000 records into 'sbtest1'
Creating a secondary index on 'sbtest2'...
Creating a secondary index on 'sbtest1'...

準備指令碼（batch_ddl.sh）

-- 該指令碼的作用是對測試表進行持續的DDL操作（增加/刪除欄位，模擬客戶的業務變更）
dmp2 (master) ~/script# echo > batch_ddl.sh
dmp2 (master) ~/script# cat << EOF > batch_ddl.sh
> #!/bin/bash
> echo "alter table sbtest1 add sid varchar(32);"|/data/mysql/base/5.7.36/bin/mysql -h10.186.60.68 -P3332 -uzlm -pzlm sbtest
> sleep 1
> echo "alter table sbtest2 add sid varchar(32);"|/data/mysql/base/5.7.36/bin/mysql -h10.186.60.68 -P3332 -uzlm -pzlm sbtest
> sleep 10
> echo "alter table sbtest1 drop sid;"|/data/mysql/base/5.7.36/bin/mysql -h10.186.60.68 -P3332 -uzlm -pzlm sbtest
> sleep 1
> echo "alter table sbtest2 drop sid;"|/data/mysql/base/5.7.36/bin/mysql -h10.186.60.68 -P3332 -uzlm -pzlm sbtest
> EOF

場景1：備份時併發執行 DDL（無額外引數）

執行指令碼

dmp2 (master) ~/script# while true; do bash batch_ddl.sh; done
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql: [Warning] Using a password on the command line interface can be insecure.
... 略

驗證指令碼是否生效（觀察測試表的 sid 欄位，時而有，時而無）

zlm@10.186.60.68 [sbtest]> show create table sbtest1\G
*************************** 1. row ***************************
       Table: sbtest1
Create Table: CREATE TABLE `sbtest1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `k` int(11) NOT NULL DEFAULT '0',
  `c` char(120) NOT NULL DEFAULT '',
  `pad` char(60) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `k_1` (`k`)
) ENGINE=InnoDB AUTO_INCREMENT=2000001 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

zlm@10.186.60.68 [sbtest]> show create table sbtest1\G
*************************** 1. row ***************************
       Table: sbtest1
Create Table: CREATE TABLE `sbtest1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `k` int(11) NOT NULL DEFAULT '0',
  `c` char(120) NOT NULL DEFAULT '',
  `pad` char(60) NOT NULL DEFAULT '',
  `sid` varchar(32) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `k_1` (`k`)
) ENGINE=InnoDB AUTO_INCREMENT=2000001 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

執行 Xtrabackup 備份

-- 檢查Xtrabackup的版本（客戶生產環境為2.4.5）
dmp2 /data/urman-agent/bin# ./xtrabackup --version
xtrabackup: recognized server arguments: --datadir=/var/lib/mysql
./xtrabackup version 2.4.20 based on MySQL server 5.7.26 Linux (x86_64) (revision id: c8b4056)

-- 執行備份
./xtrabackup --defaults-file=/data/mysql/mysql3332/my.cnf \
--user=zlm --password=zlm --target-dir=/data/backup/`date +%F_%H-%M-%S` \
--backup --host=127.0.0.1 --port=3332 --parallel 4 \
--check-privileges --no-version-check
... 略
220512 15:11:11 [01] Copying ./sbtest/db.opt to /data/backup/2022-05-12_15-10-56/sbtest/db.opt
220512 15:11:11 [01]        ...done
220512 15:11:11 [01] Copying ./sbtest/sbtest2.frm to /data/backup/2022-05-12_15-10-56/sbtest/sbtest2.frm
220512 15:11:11 [01]        ...done
220512 15:11:11 [01] Copying ./sbtest/sbtest1.frm to /data/backup/2022-05-12_15-10-56/sbtest/sbtest1.frm
220512 15:11:11 [01]        ...done
220512 15:11:11 Finished backing up non-InnoDB tables and files
220512 15:11:11 [00] Writing /data/backup/2022-05-12_15-10-56/xtrabackup_binlog_info
220512 15:11:11 [00]        ...done
220512 15:11:11 Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS...
xtrabackup: The latest check point (for incremental): '250912794909'
xtrabackup: Stopping log copying thread.
.InnoDB: Last flushed lsn: 250912650797 load_index lsn 250913206547
InnoDB: An optimized (without redo logging) DDL operation has been performed. All modified pages may not have been flushed to the disk yet.
PXB will not be able to make a consistent backup. Retry the backup operation
dmp2 /data/urman-agent/bin#

## 以上步驟，直接復現了客戶生產環境的故障場景

終止指令碼

mysql: [Warning] Using a password on the command line interface can be insecure.
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql: [Warning] Using a password on the command line interface can be insecure.
^C^C -- query aborted
ERROR 1317 (70100) at line 1: Query execution was interrupted

小結

預設情況下，即使是 Xtrabackup 高版本，如果備份時併發執行 DDL ，並且沒有指定 DDL 鎖引數（--lock-ddl，--lock-ddl-per-table），會導致備份失敗

場景2：備份時併發執行 DDL（加--lock-ddl引數）

執行指令碼

dmp2 (master) ~/script# while true; do bash batch_ddl.sh; done
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql: [Warning] Using a password on the command line interface can be insecure.
... 略

執行 Xtrabackup 備份

dmp2 /data/urman-agent/bin# ./xtrabackup --defaults-file=/data/mysql/mysql3332/my.cnf --user=zlm --password=zlm --target-dir=/data/backup/`date +%F_%H-%M-%S` --backup --host=127.0.0.1 --port=3332 --parallel 4 --check-privileges --no-version-check --lock-ddl
xtrabackup: recognized server arguments: --datadir=/data/mysql/mysql3332/data --tmpdir=/data/mysql/mysql3332/tmp --open_files_limit=65535 --server-id=31782647 --log_bin=/data/mysql/mysql3332/logs/mysql-bin --innodb_buffer_pool_size=100M --innodb_data_file_path=ibdata1:100M:autoextend --innodb_flush_log_at_trx_commit=1 --innodb_log_buffer_size=8M --innodb_log_file_size=100M --innodb_log_files_in_group=3 --innodb_max_dirty_pages_pct=50 --innodb_file_per_table=1 --innodb_io_capacity=2000 --innodb_flush_method=O_DIRECT --innodb_flush_log_at_trx_commit=1 --parallel=4
xtrabackup: recognized client arguments: --port=3332 --socket=/tmp/mysql3332.sock --user=zlm --password=* --target-dir=/data/backup/2022-05-12_16-02-37 --backup=1 --host=127.0.0.1 --port=3332 --check-privileges=1 --no-version-check=1 --lock-ddl=1
220512 16:02:37 Connecting to MySQL server host: 127.0.0.1, user: zlm, password: set, port: 3332, socket: /tmp/mysql3332.sock
Using server version 5.7.36-log
220512 16:02:37 Error: LOCK TABLES FOR BACKUP is not supported.

## MySQL社群版不支援--lock-ddl引數，直接報錯

小結

lock-ddl 其實就是在進行備份的時候，執行備份鎖（LOCK TABLES FOR BACKUP）來阻塞 DDL 語句，由於只有 Percona Server 支援該語法，MySQL 社群版暫時無法模擬，MariaDB 分支也同樣不支援該語法

場景3：備份時併發執行 DDL（加--lock-ddl-per-table引數）

執行指令碼

dmp2 (master) ~/script# while true; do bash batch_ddl.sh; done
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql: [Warning] Using a password on the command line interface can be insecure.
... 略

執行 Xtrabackup 備份

dmp2 /data/urman-agent/bin# ./xtrabackup --defaults-file=/data/mysql/mysql3332/my.cnf --user=zlm --password=zlm --target-dir=/data/backup/`date +%F_%H-%M-%S` --backup --host=127.0.0.1 --port=3332 --parallel 4 --check-privileges --no-version-check --lock-ddl-per-table
xtrabackup: recognized server arguments: --datadir=/data/mysql/mysql3332/data --tmpdir=/data/mysql/mysql3332/tmp --open_files_limit=65535 --server-id=31782647 --log_bin=/data/mysql/mysql3332/logs/mysql-bin --innodb_buffer_pool_size=100M --innodb_data_file_path=ibdata1:100M:autoextend --innodb_flush_log_at_trx_commit=1 --innodb_log_buffer_size=8M --innodb_log_file_size=100M --innodb_log_files_in_group=3 --innodb_max_dirty_pages_pct=50 --innodb_file_per_table=1 --innodb_io_capacity=2000 --innodb_flush_method=O_DIRECT --innodb_flush_log_at_trx_commit=1 --parallel=4
xtrabackup: recognized client arguments: --port=3332 --socket=/tmp/mysql3332.sock --user=zlm --password=* --target-dir=/data/backup/2022-05-12_16-07-12 --backup=1 --host=127.0.0.1 --port=3332 --check-privileges=1 --no-version-check=1 --lock-ddl-per-table=1
220512 16:07:12 Connecting to MySQL server host: 127.0.0.1, user: zlm, password: set, port: 3332, socket: /tmp/mysql3332.sock
Using server version 5.7.36-log
./xtrabackup version 2.4.20 based on MySQL server 5.7.26 Linux (x86_64) (revision id: c8b4056)
xtrabackup: uses posix_fadvise().
xtrabackup: cd to /data/mysql/mysql3332/data
xtrabackup: open files limit requested 65535, set to 65535
xtrabackup: using the following InnoDB configuration:
xtrabackup:   innodb_data_home_dir = .
xtrabackup:   innodb_data_file_path = ibdata1:100M:autoextend
xtrabackup:   innodb_log_group_home_dir = ./
xtrabackup:   innodb_log_files_in_group = 3
xtrabackup:   innodb_log_file_size = 104857600
xtrabackup: using O_DIRECT
InnoDB: Number of pools: 1
220512 16:07:13 >> log scanned up to (250920385317)
xtrabackup: Generating a list of tablespaces 
... 略
220512 16:07:14 Locking MDL for `sbtest`.`sbtest1`  ## 備份sbtest1表資料前，先加上MDL鎖
220512 16:07:14 [04] Copying ./sbtest/sbtest1.ibd to /data/backup/2022-05-12_16-07-12/sbtest/sbtest1.ibd
220512 16:07:14 [02] Copying ./sbtest/sbtest2.ibd to /data/backup/2022-05-12_16-07-12/sbtest/sbtest2.ibd
220512 16:07:15 >> log scanned up to (250920494721)
220512 16:07:16 >> log scanned up to (250920494832)
220512 16:07:17 >> log scanned up to (250920494841)
220512 16:07:18 >> log scanned up to (250920494841)
220512 16:07:19 [02]        ...done
220512 16:07:19 >> log scanned up to (250920494841)
220512 16:07:20 >> log scanned up to (250920494841)
220512 16:07:20 [01]        ...done
220512 16:07:20 [04]        ...done
220512 16:07:21 Starting to backup non-InnoDB tables and files
... 略
220512 16:07:21 [01] Copying ./sbtest/sbtest2.frm to /data/backup/2022-05-12_16-07-12/sbtest/sbtest2.frm
220512 16:07:21 [01]        ...done
220512 16:07:21 [01] Copying ./sbtest/sbtest1.frm to /data/backup/2022-05-12_16-07-12/sbtest/sbtest1.frm
220512 16:07:21 [01]        ...done
220512 16:07:21 Finished backing up non-InnoDB tables and files
220512 16:07:21 [00] Writing /data/backup/2022-05-12_16-07-12/xtrabackup_binlog_info
220512 16:07:21 [00]        ...done
220512 16:07:21 Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS...
xtrabackup: The latest check point (for incremental): '250920494832'
xtrabackup: Stopping log copying thread.
.220512 16:07:21 >> log scanned up to (250920494841)

220512 16:07:21 Executing UNLOCK TABLES
220512 16:07:21 All tables unlocked
220512 16:07:21 [00] Copying ib_buffer_pool to /data/backup/2022-05-12_16-07-12/ib_buffer_pool
220512 16:07:21 [00]        ...done
220512 16:07:21 Backup created in directory '/data/backup/2022-05-12_16-07-12/'
MySQL binlog position: filename 'mysql-bin.000012', position '472261259', GTID of the last change '0e795785-0ee5-11ec-9350-02000aba3c44:1-1619'
220512 16:07:21 [00] Writing /data/backup/2022-05-12_16-07-12/backup-my.cnf
220512 16:07:21 [00]        ...done
220512 16:07:21 [00] Writing /data/backup/2022-05-12_16-07-12/xtrabackup_info
220512 16:07:21 [00]        ...done
220512 16:07:21 Unlocking MDL for all tablesxtrabackup: Transaction log of lsn (250920249371) to (250920494841) was copied.  ## 釋放所有表上的MDL鎖
220512 16:07:22 completed OK!

終止指令碼

... 略
mysql: [Warning] Using a password on the command line interface can be insecure.
^C^C -- query aborted
ERROR 1317 (70100) at line 1: Query execution was interrupted
mysql: [Warning] Using a password on the command line interface can be insecure.
^C^C -- query aborted
ERROR 1317 (70100) at line 1: Query execution was interrupted
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1060 (42S21) at line 1: Duplicate column name 'sid'
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1060 (42S21) at line 1: Duplicate column name 'sid'

^C
dmp2 (master) ~/script#

小結

備份時使用 --lock-ddl-per-table 引數，會在拷貝每個表的 ibd 檔案前先加一個 MDL 鎖，用於阻塞該表上 DDL 語句的執行，直到備份完成後，DDL 才能繼續執行

場景4：備份時併發執行DDL（設定MySQL引數：old_alter_table=1）

關閉 ONLINE DDL 特性

zlm@10.186.60.68 [(none)]> set global old_alter_table=1;
Query OK, 0 rows affected (0.00 sec)

zlm@10.186.60.68 [(none)]> show variables like 'old_alter_table';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| old_alter_table | OFF   |
+-----------------+-------+
1 row in set (0.00 sec)

zlm@10.186.60.68 [(none)]> exit
Bye
dmp2 (master) ~/script# m3332 -Nse "show variables like 'old_alter_table';"
mysql: [Warning] Using a password on the command line interface can be insecure.
old_alter_table ON

執行指令碼

dmp2 (master) ~/script# while true; do bash batch_ddl.sh; done
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1060 (42S21) at line 1: Duplicate column name 'sid'
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1060 (42S21) at line 1: Duplicate column name 'sid'
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql: [Warning] Using a password on the command line interface can be insecure.
... 略

執行 Xtrabackup 備份

dmp2 /data/urman-agent/bin# ./xtrabackup --defaults-file=/data/mysql/mysql3332/my.cnf --user=zlm --password=zlm --target-dir=/data/backup/`date +%F_%H-%M-%S` --backup --host=127.0.0.1 --port=3332 --parallel 4 --check-privileges --no-version-check
xtrabackup: recognized server arguments: --datadir=/data/mysql/mysql3332/data --tmpdir=/data/mysql/mysql3332/tmp --open_files_limit=65535 --server-id=31782647 --log_bin=/data/mysql/mysql3332/logs/mysql-bin --innodb_buffer_pool_size=100M --innodb_data_file_path=ibdata1:100M:autoextend --innodb_flush_log_at_trx_commit=1 --innodb_log_buffer_size=8M --innodb_log_file_size=100M --innodb_log_files_in_group=3 --innodb_max_dirty_pages_pct=50 --innodb_file_per_table=1 --innodb_io_capacity=2000 --innodb_flush_method=O_DIRECT --innodb_flush_log_at_trx_commit=1 --parallel=4
xtrabackup: recognized client arguments: --port=3332 --socket=/tmp/mysql3332.sock --user=zlm --password=* --target-dir=/data/backup/2022-05-12_16-48-00 --backup=1 --host=127.0.0.1 --port=3332 --check-privileges=1 --no-version-check=1
220512 16:48:00 Connecting to MySQL server host: 127.0.0.1, user: zlm, password: set, port: 3332, socket: /tmp/mysql3332.sock
Using server version 5.7.36-log
./xtrabackup version 2.4.20 based on MySQL server 5.7.26 Linux (x86_64) (revision id: c8b4056)
xtrabackup: uses posix_fadvise().
xtrabackup: cd to /data/mysql/mysql3332/data
xtrabackup: open files limit requested 65535, set to 65535
xtrabackup: using the following InnoDB configuration:
xtrabackup:   innodb_data_home_dir = .
xtrabackup:   innodb_data_file_path = ibdata1:100M:autoextend
xtrabackup:   innodb_log_group_home_dir = ./
xtrabackup:   innodb_log_files_in_group = 3
xtrabackup:   innodb_log_file_size = 104857600
xtrabackup: using O_DIRECT
InnoDB: Number of pools: 1
220512 16:48:02 >> log scanned up to (251659945145)
xtrabackup: Generating a list of tablespaces
... 略
220512 16:48:03 [02] Copying ./zlm/t.ibd to /data/backup/2022-05-12_16-48-00/zlm/t.ibd
220512 16:48:03 [03]        ...done
220512 16:48:03 [02]        ...done
220512 16:48:03 [03] Copying ./sbtest/sbtest2.ibd to /data/backup/2022-05-12_16-48-00/sbtest/sbtest2.ibd
220512 16:48:03 [04] Copying ./zlm1/t1.ibd to /data/backup/2022-05-12_16-48-00/zlm1/t1.ibd
220512 16:48:03 [02] Copying ./sbtest/sbtest1.ibd to /data/backup/2022-05-12_16-48-00/sbtest/sbtest1.ibd
220512 16:48:03 [04]        ...done
220512 16:48:03 [04] Copying ./sbtest/#sql-c61_30133.ibd to /data/backup/2022-05-12_16-48-00/sbtest/#sql-c61_30133.ibd
220512 16:48:04 >> log scanned up to (251669706184)
220512 16:48:04 [04]        ...done
220512 16:48:05 >> log scanned up to (251672429294)
220512 16:48:06 >> log scanned up to (251675700622)
220512 16:48:07 >> log scanned up to (251678520383)
220512 16:48:08 >> log scanned up to (251681195105)
220512 16:48:09 [02]        ...done
220512 16:48:09 >> log scanned up to (251683273712)
220512 16:48:09 [01]        ...done
220512 16:48:10 >> log scanned up to (251694062575)
220512 16:48:11 >> log scanned up to (251707703017)
220512 16:48:12 [03]        ...done
220512 16:48:12 >> log scanned up to (251713134341)
220512 16:48:12 Executing FLUSH NO_WRITE_TO_BINLOG TABLES...
220512 16:48:13 >> log scanned up to (251721786959)
... 略
220512 16:48:38 [01] Copying ./sbtest/sbtest1.frm to /data/backup/2022-05-12_16-48-00/sbtest/sbtest1.frm
220512 16:48:38 [01]        ...done
220512 16:48:38 [01] Copying ./sbtest/sbtest2.frm to /data/backup/2022-05-12_16-48-00/sbtest/sbtest2.frm
220512 16:48:38 [01]        ...done
220512 16:48:38 Finished backing up non-InnoDB tables and files
220512 16:48:38 [00] Writing /data/backup/2022-05-12_16-48-00/xtrabackup_binlog_info
220512 16:48:38 [00]        ...done
220512 16:48:38 Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS...
xtrabackup: The latest check point (for incremental): '251977260800'
xtrabackup: Stopping log copying thread.
.220512 16:48:38 >> log scanned up to (252017389382)

220512 16:48:38 Executing UNLOCK TABLES
220512 16:48:38 All tables unlocked
220512 16:48:38 [00] Copying ib_buffer_pool to /data/backup/2022-05-12_16-48-00/ib_buffer_pool
220512 16:48:38 [00]        ...done
220512 16:48:38 Backup created in directory '/data/backup/2022-05-12_16-48-00/'
MySQL binlog position: filename 'mysql-bin.000012', position '472262307', GTID of the last change '0e795785-0ee5-11ec-9350-02000aba3c44:1-1625'
220512 16:48:38 [00] Writing /data/backup/2022-05-12_16-48-00/backup-my.cnf
220512 16:48:38 [00]        ...done
220512 16:48:38 [00] Writing /data/backup/2022-05-12_16-48-00/xtrabackup_info
220512 16:48:38 [00]        ...done
xtrabackup: Transaction log of lsn (251552488790) to (252017389382) was copied.
220512 16:48:38 completed OK!
dmp2 /data/urman-agent/bin#

## Xtrabackup備份日誌中沒有發現MDL鎖，備份也順利地執行完了，說明啟用old_alter_table也能夠解決備份失敗的問題

終止指令碼

... 略
mysql: [Warning] Using a password on the command line interface can be insecure.
^C^C -- query aborted
ERROR 1317 (70100) at line 1: Query execution was interrupted
mysql: [Warning] Using a password on the command line interface can be insecure.
^C^C -- query aborted
ERROR 1317 (70100) at line 1: Query execution was interrupted
^C
04:48 PM dmp2 (master) ~/script#

小結

當關閉 ONLINE DDL 特性時（old_alter_table=1），即執行 DDL 時強制其都採用 ALGORITHM=COPY 來代替 ALGORITHM=INPLACE ，此時即使備份時沒有使用 DDL 鎖的引數，也不會影響備份執行，但要注意的是，關閉 ONLINE DDL 特性會影響表上的 DML 併發，不推薦使用。

總結

DDL 業務變更操作應儘量避開備份視窗
Sorted Index Builds 對建立索引的優化與 PXB 2.4.x 備份存在衝突，需要替換為 PXB 高版本，並通過加備份鎖或 MDL 鎖的引數來避免備份失敗
MySQL 自身也可以通過關閉 ONLINE DDL 特性來解決備份失敗的問題，但是會影響 DML 併發，需權衡利弊

參考連結

https://dev.mysql.com/doc/ref...

故障分析 | DDL 導致的 Xtrabackup 備份失敗

背景

案例分析

場景測試

環境準備

建立測試表

準備指令碼（batch_ddl.sh）

場景1：備份時併發執行 DDL（無額外引數）

執行指令碼

驗證指令碼是否生效（觀察測試表的 sid 欄位，時而有，時而無）

執行 Xtrabackup 備份

終止指令碼

小結

場景2：備份時併發執行 DDL（加--lock-ddl引數）

執行指令碼

執行 Xtrabackup 備份

小結

場景3：備份時併發執行 DDL（加--lock-ddl-per-table引數）

執行指令碼

執行 Xtrabackup 備份

終止指令碼

小結

場景4：備份時併發執行DDL（設定MySQL引數：old_alter_table=1）

關閉 ONLINE DDL 特性

執行指令碼

執行 Xtrabackup 備份

終止指令碼

小結

總結

參考連結

相關文章