适用范围
MySQL5.7/8.0版本下，希望主库不停机不影响业务正常运行的情况下在线搭建从库。
方案概述
本方案主要论述在主库不停机的情况下使用MySQL Shell（新的备份工具包）在线进行从库搭建。
实施步骤
1. 主库MySQL服务器在线备份全量数据
对数据库进行全备，开启并行4个并行进行，实际根据主机CPU配置调整:
util.dumpInstance(’/mysqldata/backup/bk0322’,{threads:16})
[root@vehi_mondb1 bin]# /mysqldata/mysqlshell8032/bin/mysqlsh --uri=root@localhost:3306 -S /tmp/mysql.sock
Please provide the password for 'root@/tmp%2Fmysql.sock': *************
Save password for 'root@/tmp%2Fmysql.sock'? [Y]es/[N]o/Ne[v]er (default No): yes
MySQL Shell 8.0.32
Copyright (c) 2016, 2023, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its affiliates.
Other names may be trademarks of their respective owners.
Type '\help' or '\?' for help; '\quit' to exit.
Creating a session to 'root@/tmp%2Fmysql.sock'
Fetching schema names for auto-completion... Press ^C to stop.
Your MySQL connection id is 59776508
Server version: 5.7.39-enterprise-commercial-advanced-log MySQL Enterprise Server - Advanced Edition (Commercial)
No default schema selected; type \use <schema> to set one.
MySQL localhost JS > util.dumpInstance('/data/backup/bk0322',{threads:4})
Acquiring global read lock
Global read lock acquired
Initializing - done
1 out of 5 schemas will be dumped and within them 1 table, 0 views.
1 out of 4 users will be dumped.
Gathering information - done
All transactions have been started
Locking instance for backup
Global read lock has been released
Writing global DDL files
Writing users DDL
Running data dump using 4 threads.
NOTE: Progress information uses estimated values and may not be accurate.
Writing schema metadata - done
Writing DDL - done
Writing table metadata - done
Starting data dump
101% (200.00K rows / ~197.35K rows), 0.00 rows/s, 0.00 B/s uncompressed, 0.00 B/s compressed
Dump duration: 00:00:00s
Total duration: 00:00:00s
Schemas dumped: 1
Tables dumped: 1
Uncompressed data size: 38.61 MB
Compressed data size: 17.58 MB
Compression ratio: 2.2
Rows written: 200000
Bytes written: 17.58 MB
Average uncompressed throughput: 38.61 MB/s
Average compressed throughput: 17.58 MB/s
MySQL localhost JS > \q
Bye!
查看备份后的备份文件结构：本例为保护客户信息，使用自己试验环境数据展示：
[root@c1 bk0322]# ls -lhrt /data/backup/bk0322/
total 17M
-rw-r-----. 1 root root 240 Mar 22 16:37 @.sql
-rw-r-----. 1 root root 240 Mar 22 16:37 @.post.sql
-rw-r-----. 1 root root 820 Mar 22 16:37 @.json
-rw-r-----. 1 root root 1.9K Mar 22 16:37 @.users.sql
-rw-r-----. 1 root root 288 Mar 22 16:37 testdb.json
-rw-r-----. 1 root root 563 Mar 22 16:37 testdb.sql
-rw-r-----. 1 root root 637 Mar 22 16:37 testdb@sbtest1.json
-rw-r-----. 1 root root 859 Mar 22 16:37 testdb@sbtest1.sql
-rw-r-----. 1 root root 296 Mar 22 16:37 testdb@sbtest1@@0.tsv.zst.idx
-rw-r-----. 1 root root 17M Mar 22 16:37 testdb@sbtest1@@0.tsv.zst
-rw-r-----. 1 root root 233 Mar 22 16:37 @.done.json
[root@c1 bk0322]# cat @.json
{
"dumper": "mysqlsh Ver 8.0.32 for Linux on x86_64 - for MySQL 8.0.32 (MySQL Community Server (GPL))",
"version": "2.0.1",
"origin": "dumpInstance",
"options": {
"threads": 4
},
"schemas": [
"testdb"
],
"basenames": {
"testdb": "testdb"
},
"users": [
"'root'@'localhost'"
],
"defaultCharacterSet": "utf8mb4",
"tzUtc": true,
"bytesPerChunk": 64000000,
"user": "root",
"hostname": "c1",
"server": "c1",
"serverVersion": "8.0.32",
"binlogFile": "binlog.000002",
"binlogPosition": 38184121,
"gtidExecuted": "e96002f1-c877-11ed-a313-000c29ff0201:1-80",
"gtidExecutedInconsistent": false,
"consistent": true,
"compatibilityOptions": [],
"capabilities": [],
"begin": "2023-03-22 16:37:25"
}
[root@c1 bk0322]#
以上信息请注意，在备份文件夹中的 @.json中记录了数据库的备份信息和具体的GTID值，这在后面搭建从库尤为重要。
2.将备份数据库传送至从库服务器中
cd /mysqldata/backup
scp bk0322 192.168.10.113:/data/backup/
3.从库服务器初始化MySQL数据库
在从库安装一个单实例空库：
初始化：
/data/mysql/mysql8032/bin/mysqld --defaults-file=/data/mysql/mysql8032/my3832.cnf --initialize --user=mysql
启动：
/data/mysql/mysql8032/bin/mysqld_safe --defaults-file=/data/mysql/mysql8032/my3832.cnf --user=mysql &
登录：
/data/mysql/mysql8032/bin/mysql -uroot -p'!k?eu1s+ofI#' -S /lvdata/mysql/3832/run/mysql.sock
首次登录需要修改密码：
mysql> alter user root@localhost identified by 'password';
mysql> flush privileges;
mysql> exit;
4. 将主库数据库全量导入至从库中
本步骤需要注意，在导入数据时，我们目的是搭建从库，故在从库中需要保留主库的GTID信息：
默认不导入用户数据，也不带GTID信息，故需要将参数显示指出：
util.loadDump("/mysqldata/backup/bk0322",{loadUsers: true,threads:8,updateGtidSet:“replace”})
[root@c1 bk0322]# /lvdata/mysqlshell8032/bin/mysqlsh --uri=root@localhost:3833 -S /lvdata/mysql/3833/run/mysql.sock
Please provide the password for 'root@/lvdata%2Fmysql%2F3833%2Frun%2Fmysql.sock': *****
Save password for 'root@/lvdata%2Fmysql%2F3833%2Frun%2Fmysql.sock'? [Y]es/[N]o/Ne[v]er (default No): yes
MySQL Shell 8.0.32
Copyright (c) 2016, 2023, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its affiliates.
Other names may be trademarks of their respective owners.
Type '\help' or '\?' for help; '\quit' to exit.
Creating a Classic session to 'root@/lvdata%2Fmysql%2F3833%2Frun%2Fmysql.sock'
Fetching schema names for auto-completion... Press ^C to stop.
Your MySQL connection id is 9
Server version: 8.0.32 MySQL Community Server - GPL
MySQL localhost JS > util.loadDump("/data/backup/bk0322",{loadUsers: true,threads:8,updateGtidSet:"replace"})
ERROR: The 'local_infile' global system variable must be set to ON in the target server, after the server is verified to be trusted.
Util.loadDump: local_infile disabled in server (MYSQLSH 53025)
MySQL localhost JS >
8.0版本MySQL local_infile默认为OFF，需要开启该参数后才可以进行数据导入，登录从库设置：
root@localhost:(none) 04:56:03 >set global local_infile=ON;
Query OK, 0 rows affected (0.00 sec)
设置完毕后继续执行数据导入：
MySQL localhost JS > util.loadDump("/data/backup/bk0322",{loadUsers: true,threads:8,updateGtidSet:"replace"})
Loading DDL, Data and Users from '/data/backup/bk0322' using 8 threads.
Opening dump...
Target is MySQL 8.0.32. Dump was produced from MySQL 8.0.32
Scanning metadata - done
Checking for pre-existing objects...
Executing common preamble SQL
Executing DDL - done
Executing user accounts SQL...
NOTE: Skipping CREATE/ALTER USER statements for user 'root'@'localhost'
NOTE: Skipping GRANT statements for user 'root'@'localhost'
Executing view DDL - done
Starting data load
1 thds loading / 100% (38.61 MB / 38.61 MB), 15.06 MB/s, 0 / 1 tables done
Executing common postamble SQL
Resetting GTID_PURGED to dumped gtid set
Recreating indexes - done
1 chunks (200.00K rows, 38.61 MB) for 1 tables in 1 schemas were loaded in 3 sec (avg throughput 14.00 MB/s)
0 warnings were reported during the load.
MySQL localhost JS >
导入完毕后，查看从库信息，确认GTID：
root@localhost:(none) 05:00:48 >show global variables like '%gtid_executed';
+---------------+-------------------------------------------------------------------------------------+
| Variable_name | Value |
+---------------+-------------------------------------------------------------------------------------+
| gtid_executed | e96002f1-c877-11ed-a313-000c29ff0201:1-80,
fe0f400f-c877-11ed-a6b9-000c29ff0201:1-5 |
+---------------+-------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
root@localhost:(none) 05:00:54 >show global variables like '%gtid_purged%';
+---------------+-------------------------------------------+
| Variable_name | Value |
+---------------+-------------------------------------------+
| gtid_purged | e96002f1-c877-11ed-a313-000c29ff0201:1-80 |
+---------------+-------------------------------------------+
1 row in set (0.00 sec)
## 此时GTID_PURGED值与导出文件cat @.json中的gtidExecuted值保持一致，表明该数据已经执行过，当搭建主从复制时，将从此位置进行数据同步。
root@localhost:(none) 05:00:57 >show global variables like '%uuid%';
+---------------+--------------------------------------+
| Variable_name | Value |
+---------------+--------------------------------------+
| server_uuid | fe0f400f-c877-11ed-a6b9-000c29ff0201 |
+---------------+--------------------------------------+
1 row in set (0.00 sec)
root@localhost:(none) 05:01:09 >show master status;
+---------------+----------+--------------+------------------+-------------------------------------------------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------------------------------------------------------------------------+
| binlog.000002 | 38164549 | | | e96002f1-c877-11ed-a313-000c29ff0201:1-80,
fe0f400f-c877-11ed-a6b9-000c29ff0201:1-5 |
+---------------+----------+--------------+------------------+-------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
5. 建立主从复制关系
建立复制同步：
root@localhost:(none) 05:09:03 >CHANGE MASTER TO MASTER_USER='repluser', MASTER_PASSWORD='mysql' , MASTER_HOST='192.168.139.128',MASTER_PORT=3832,MASTER_AUTO_POSITION = 1;
Query OK, 0 rows affected, 8 warnings (0.02 sec)
启动复制同步：
root@localhost:(none) 05:09:51 >start replica;
Query OK, 0 rows affected (0.01 sec)
查看复制同步状态：
root@localhost:(none) 05:09:59 >show replica status\G
*************************** 1. row ***************************
Replica_IO_State: Waiting for source to send event
Source_Host: 192.168.139.128
Source_User: repluser
Source_Port: 3832
Connect_Retry: 60
Source_Log_File: binlog.000002
Read_Source_Log_Pos: 38184823
Relay_Log_File: relaylog.000002
Relay_Log_Pos: 1113
Relay_Source_Log_File: binlog.000002
Replica_IO_Running: Yes
Replica_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Source_Log_Pos: 38184823
Relay_Log_Space: 1316
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Source_SSL_Allowed: No
Source_SSL_CA_File:
Source_SSL_CA_Path:
Source_SSL_Cert:
Source_SSL_Cipher:
Source_SSL_Key:
Seconds_Behind_Source: 0
Source_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Source_Server_Id: 3832
Source_UUID: e96002f1-c877-11ed-a313-000c29ff0201
Source_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Replica_SQL_Running_State: Replica has read all relay log; waiting for more updates
Source_Retry_Count: 86400
Source_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Source_SSL_Crl:
Source_SSL_Crlpath:
Retrieved_Gtid_Set: e96002f1-c877-11ed-a313-000c29ff0201:81-83
Executed_Gtid_Set: e96002f1-c877-11ed-a313-000c29ff0201:1-83,
fe0f400f-c877-11ed-a6b9-000c29ff0201:1-5
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name:
Source_TLS_Version:
Source_public_key_path:
Get_Source_public_key: 0
Network_Namespace:
1 row in set (0.00 sec)
root@localhost:(none) 05:10:05 >
通过查看从库状态
Retrieved_Gtid_Set: e96002f1-c877-11ed-a313-000c29ff0201:81-83
可以得知，从库从GTID值为81开始同步数据（之前导入时当时数据库位置为e96002f1-c877-11ed-a313-000c29ff0201:1-80），目前主库有3个事物需要获取并应用。
至此，MySQL从库通过MySQL Shell工具搭建完成，复制状态正常。
参考文档
https://dev.mysql.com/doc/mysql-shell/8.0/en/mysql-shell-utilities.html
https://dev.mysql.com/doc/mysql-shell/8.0/en/mysql-shell-utilities-load-dump.html#mysql-shell-utilities-load-dump-opt-control
最后修改时间：2023-03-23 10:30:40
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
