- GreatSQL社區原創內容未經授權不得隨意使用，轉載請聯系小編并注明來源，
- GreatSQL是MySQL的國產分支版本，使用上與MySQL一致，
- 作者：王權富貴
- 文章來源：GreatSQL社區原創
1.概述
本文通過 XtraBackup 備份單個資料庫，然后恢復到另一個實體，用于快速遷移大資料量，使用的軟體版本為：
| 軟體名 | 版本 |
|---|---|
| MySQL | mysql-5.7.39-linux-glibc2.12-x86_64.tar.gz |
| XtraBackup | percona-xtrabackup-24-2.4.26-1.el7.x86_64.rpm |
在2臺主機分別安裝一個MySQL實體：
| 主機 | IP地址 | 埠號 |
|---|---|---|
| MySQL-A | 192.168.100.10 | 5508 |
| MySQL-B | 192.168.100.11 | 5508 |
2.在MySQL-A初始化資料
通過sysbench 初始化資料，10張表，每張表10000條記錄
shell> sysbench src/lua/oltp_read_write.lua --mysql-db=sysbench --mysql-host=192.168.100.10 --mysql-port=5508 --mysql-user=greatsql --mysql-password='greatsql' --tables=10 --table_size=10000 --report-interval=2 --threads=50 --time=1000 --mysql_storage_engine=innodb --partitions=3 --skip_trx=0 --mysql-ignore-errors=all prepare
3.通過 xtrabackup 備份 MySQL-A 的 sysbench 庫
shell> innobackupex --defaults-file=/mysql/conf/my5508.cnf --user greatsql --password greatsql -H127.0.0.1 -P5508 --databases sysbench /mysql/dbbackup
...
...
221229 10:11:17 Executing UNLOCK TABLES
221229 10:11:17 All tables unlocked
221229 10:11:17 [00] Copying ib_buffer_pool to /mysql/dbbackup/2022-12-29_10-11-07/ib_buffer_pool
221229 10:11:17 [00] ...done
221229 10:11:17 Backup created in directory '/mysql/dbbackup/2022-12-29_10-11-07/'
MySQL binlog position: filename 'binlog.000005', position '190', GTID of the last change '3e362a47-8683-11ed-92e1-00163ed294ac:1-87'
221229 10:11:17 [00] Writing /mysql/dbbackup/2022-12-29_10-11-07/backup-my.cnf
221229 10:11:17 [00] ...done
221229 10:11:17 [00] Writing /mysql/dbbackup/2022-12-29_10-11-07/xtrabackup_info
221229 10:11:17 [00] ...done
xtrabackup: Transaction log of lsn (1123405728) to (1123405737) was copied.
221229 10:11:17 completed OK!
4.備份 MySQL-A sysbench 庫的表結構
shell> /mysql/svr/mysql/bin/mysqldump -ugreatsql -pgreatsql -h127.0.0.1 -P5508 --single-transaction --set-gtid-purged=OFF --master-data=https://www.cnblogs.com/greatsql/p/2 -d -B sysbench > sysbench.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
5.將備份的 sysbench 庫的表結構匯入 MySQL-B
shell> /mysql/svr/mysql/bin/mysql -ugreatsql -pgreatsql -h192.168.100.11 -P5508 < sysbench.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
6.登錄 MySQL-B 生成丟棄/匯入表空間的 SQL 陳述句
# 生成丟棄表空間的SQL
mysql> select concat('alter table ',table_schema,'.',TABLE_NAME , ' discard tablespace', ';') from information_schema.tables where TABLE_SCHEMA = 'sysbench' into outfile '/tmp/discard.sql';
Query OK, 10 rows affected (0.00 sec)
# 生成匯入表空間的SQL
mysql> select concat('alter table ',table_schema,'.',TABLE_NAME , ' import tablespace', ';') from information_schema.tables where TABLE_SCHEMA = 'sysbench' into outfile '/tmp/import.sql';
Query OK, 10 rows affected (0.01 sec)
7.登錄 MySQL-B 執行丟棄表空間的 SQL 陳述句
mysql> source /tmp/discard.sql;
8.查看 MySQL-B 底層資料檔案
shell> ll -h /mysql/dbdata/data5508/data/sysbench/
total 124K
-rw-r----- 1 mysql mysql 67 Dec 29 10:25 db.opt
-rw-r----- 1 mysql mysql 8.5K Dec 29 10:25 sbtest10.frm
-rw-r----- 1 mysql mysql 8.5K Dec 29 10:25 sbtest1.frm
-rw-r----- 1 mysql mysql 8.5K Dec 29 10:25 sbtest2.frm
-rw-r----- 1 mysql mysql 8.5K Dec 29 10:26 sbtest3.frm
-rw-r----- 1 mysql mysql 8.5K Dec 29 10:26 sbtest4.frm
-rw-r----- 1 mysql mysql 8.5K Dec 29 10:26 sbtest5.frm
-rw-r----- 1 mysql mysql 8.5K Dec 29 10:26 sbtest6.frm
-rw-r----- 1 mysql mysql 8.5K Dec 29 10:26 sbtest7.frm
-rw-r----- 1 mysql mysql 8.5K Dec 29 10:26 sbtest8.frm
-rw-r----- 1 mysql mysql 8.5K Dec 29 10:26 sbtest9.frm
可以看到ibd檔案已被丟棄
9.prepare 備份檔案
注意這里需要加 --export 選項，它允許匯出單個表以進行匯入到另一個服務器
shell> innobackupex --apply-log --export /mysql/dbbackup/2022-12-29_10-11-07
xtrabackup: recognized server arguments: --innodb_checksum_algorithm=crc32 --innodb_log_checksum_algorithm=strict_crc32 --innodb_data_file_path=ibdata1:12M:autoextend --innodb_log_files_in_group=4 --innodb_log_file_size=33554432 --innodb_fast_checksum=0 --innodb_page_size=16384 --innodb_log_block_size=512 --innodb_undo_directory=/mysql/dbdata/data5508/log --innodb_undo_tablespaces=0 --server-id=2 --redo-log-version=1
xtrabackup: recognized client arguments:
221229 10:56:58 innobackupex: Starting the apply-log operation
IMPORTANT: Please check that the apply-log run completes successfully.
At the end of a successful apply-log run innobackupex
prints "completed OK!".
...
...
xtrabackup: starting shutdown with innodb_fast_shutdown = 0
InnoDB: FTS optimize thread exiting.
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 1123519528
221229 10:57:13 completed OK!
10.查看備份檔案目錄
shell> ll -h /mysql/dbbackup/2022-12-29_10-11-07/sysbench/
total 271M
-rw-r----- 1 root root 67 Dec 29 10:11 db.opt
-rw-r----- 1 root root 8.5K Dec 29 10:11 sbtest10.frm
-rw-r--r-- 1 root root 578 Dec 29 10:57 sbtest10#P#p0.cfg
-rw-r----- 1 root root 16K Dec 29 10:57 sbtest10#P#p0.exp
-rw-r----- 1 root root 9.0M Dec 29 10:11 sbtest10#P#p0.ibd
-rw-r--r-- 1 root root 578 Dec 29 10:57 sbtest10#P#p1.cfg
-rw-r----- 1 root root 16K Dec 29 10:57 sbtest10#P#p1.exp
-rw-r----- 1 root root 9.0M Dec 29 10:11 sbtest10#P#p1.ibd
-rw-r--r-- 1 root root 578 Dec 29 10:57 sbtest10#P#p2.cfg
...
可以看到prepare備份檔案后，多了 cfg,exp結尾的檔案
11.傳輸備份檔案
將準備好的備份檔案中后綴名為cfg，ibd，exp的檔案傳輸到MySQL-B實體的sysbench庫下
shell> scp -r /mysql/dbbackup/2022-12-29_10-11-07/sysbench/*.ibd /mysql/dbbackup/2022-12-29_10-11-07/sysbench/*.cfg /mysql/dbbackup/2022-12-29_10-11-07/sysbench/*.exp [email protected]:/mysql/dbdata/data5508/data/sysbench/
12.修改傳輸過來的檔案的屬主屬組
shell> chown -R mysql.mysql /mysql
13.登錄 MySQL-B 執行匯入表空間的 SQL 陳述句
mysql> source /tmp/import.sql;
14.登錄 MySQL-B 檢查一張表是否正常
mysql> select count(*) from sysbench.sbtest1;
+----------+
| count(*) |
+----------+
| 10000 |
+----------+
1 row in set (0.01 sec)
15.使用 sysbench 進行壓測 MySQL-B
shell> sysbench src/lua/oltp_read_write.lua --mysql-db=sysbench --mysql-host=192.168.100.11 --mysql-port=5508 --mysql-user=greatsql --mysql-password='greatsql' --tables=10 --table_size=10000 --report-interval=2 --threads=50 --time=300 --mysql_storage_engine=innodb --partitions=3 --skip_trx=0 --mysql-ignore-errors=all run
sysbench 1.1.0-df89d34 (using bundled LuaJIT 2.1.0-beta3)
...
...
SQL statistics:
queries performed:
read: 1702400
write: 486400
other: 243200
total: 2432000
transactions: 121600 (405.25 per sec.)
queries: 2432000 (8105.04 per sec.)
ignored errors: 0 (0.00 per sec.)
reconnects: 0 (0.00 per sec.)
Throughput:
events/s (eps): 405.2522
time elapsed: 300.0601s
total number of events: 121600
Latency (ms):
min: 9.04
avg: 123.36
max: 1512.19
95th percentile: 590.56
sum: 15000942.35
Threads fairness:
events (avg/stddev): 2432.0000/58.48
execution time (avg/stddev): 300.0188/0.01
壓測正常
16.重啟MySQL-B并查詢一張表
mysql> shutdown;
shell> /mysql/svr/mysql/bin/mysqld_safe --defaults-file=/mysql/conf/my5508.cnf --user=mysql &
mysql> select count(*) from sysbench.sbtest2;
+----------+
| count(*) |
+----------+
| 10000 |
+----------+
1 row in set (0.04 sec)
總結
通過上述方法可以快速備份恢復大資料量的庫，也可以同時指定多個庫，單張表或多張表進行恢復，可參考官方檔案 Partial Backups - Percona XtraBackup
Enjoy GreatSQL ??
關于 GreatSQL
GreatSQL是由萬里資料庫維護的MySQL分支，專注于提升MGR可靠性及性能，支持InnoDB并行查詢特性，是適用于金融級應用的MySQL分支版本，
相關鏈接： GreatSQL社區 Gitee GitHub Bilibili
GreatSQL社區：
社區博客有獎征稿詳情：https://greatsql.cn/thread-100-1-1.html
技術交流群：
微信：掃碼添加
GreatSQL社區助手微信好友，發送驗證資訊加群，
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/541778.html
標籤：MySQL
下一篇：Mysql頁分裂