mysql8.0.23克隆插件的实践
mysql8.0.23克隆插件的实践
理论参考资料
https://www.jianshu.com/p/ed7e0796a3d8
备注：数据量越大，基于物理文件拷贝，越能体现出这种优势。
一、环境
| IP | 说明 |
| 10.100.19.216 | 数据源 |
| 10.100.19.215 | 目标库1 |
| 10.100.19.214 | 目标库2 |
二、查看是否已经安装克隆插件
mysql> INSTALL PLUGIN clone SONAME 'mysql_clone.so'; Query OK, 0 rows affected (0.16 sec) mysql> show PLUGINs; +---------------------------------+----------+--------------------+----------------+---------+ | Name | Status | Type | Library | License | +---------------------------------+----------+--------------------+----------------+---------+ | binlog | ACTIVE | STORAGE ENGINE | NULL | GPL | | mysql_native_password | ACTIVE | AUTHENTICATION | NULL | GPL | | sha256_password | ACTIVE | AUTHENTICATION | NULL | GPL | | caching_sha2_password | ACTIVE | AUTHENTICATION | NULL | GPL | | sha2_cache_cleaner | ACTIVE | AUDIT | NULL | GPL | | CSV | ACTIVE | STORAGE ENGINE | NULL | GPL | | MEMORY | ACTIVE | STORAGE ENGINE | NULL | GPL | | InnoDB | ACTIVE | STORAGE ENGINE | NULL | GPL | | INNODB_TRX | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_CMP | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_CMP_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_CMPMEM | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_CMPMEM_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_CMP_PER_INDEX | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_CMP_PER_INDEX_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_BUFFER_PAGE | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_BUFFER_PAGE_LRU | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_BUFFER_POOL_STATS | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_TEMP_TABLE_INFO | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_METRICS | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_FT_DEFAULT_STOPWORD | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_FT_DELETED | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_FT_BEING_DELETED | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_FT_CONFIG | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_FT_INDEX_CACHE | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_FT_INDEX_TABLE | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_TABLES | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_TABLESTATS | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_INDEXES | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_TABLESPACES | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_COLUMNS | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_VIRTUAL | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_CACHED_INDEXES | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_SESSION_TEMP_TABLESPACES | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | MyISAM | ACTIVE | STORAGE ENGINE | NULL | GPL | | MRG_MYISAM | ACTIVE | STORAGE ENGINE | NULL | GPL | | PERFORMANCE_SCHEMA | ACTIVE | STORAGE ENGINE | NULL | GPL | | TempTable | ACTIVE | STORAGE ENGINE | NULL | GPL | | ARCHIVE | ACTIVE | STORAGE ENGINE | NULL | GPL | | BLACKHOLE | ACTIVE | STORAGE ENGINE | NULL | GPL | | FEDERATED | DISABLED | STORAGE ENGINE | NULL | GPL | | ngram | ACTIVE | FTPARSER | NULL | GPL | | mysqlx_cache_cleaner | ACTIVE | AUDIT | NULL | GPL | | mysqlx | ACTIVE | DAEMON | NULL | GPL | | clone | ACTIVE | CLONE | mysql_clone.so | GPL | +---------------------------------+----------+--------------------+----------------+---------+ 45 rows in set (0.00 sec)
三、在各个节点创建专用账号
set sql_log_bin=0; create user 'recipient_clone_user'@'10.100.19.%' identified by 'recipient_clone_userpwd'; GRANT CLONE_ADMIN,BACKUP_ADMIN on *.* to 'recipient_clone_user'@'10.100.19.%'; set sql_log_bin=1;
四、添加数据贡献者以及克隆操作
#在215和214中操作，添加克隆提供方的ip列表与端口至clone_valid_donor_list mysql> SET GLOBAL clone_valid_donor_list = '10.100.19.216:3306'; Query OK, 0 rows affected (0.00 sec) # 214 mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | | testdb214 | +--------------------+ 5 rows in set (0.00 sec) # 215 mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | | testdb215 | +--------------------+ 5 rows in set (0.00 sec) # 远程克隆覆盖自身：（默认方式，提供方数据直接克隆到接收方，克隆后接收方实例重启） mysql> CLONE INSTANCE FROM 'recipient_clone_user'@'10.100.19.216':3306 IDENTIFIED BY 'recipient_clone_userpwd'; Query OK, 0 rows affected (23.91 sec) mysql> #具体的克隆流程可以查看performance_schema.clone_progress: mysql> SELECT STAGE, STATE, END_TIME FROM performance_schema.clone_progress; +-----------+-----------+----------------------------+ | STAGE | STATE | END_TIME | +-----------+-----------+----------------------------+ | DROP DATA | Completed | 2021-02-10 10:46:42.802132 | | FILE COPY | Completed | 2021-02-10 10:47:00.100939 | | PAGE COPY | Completed | 2021-02-10 10:47:00.331577 | | REDO COPY | Completed | 2021-02-10 10:47:00.460281 | | FILE SYNC | Completed | 2021-02-10 10:47:05.656917 | | RESTART | Completed | 2021-02-10 10:47:09.388017 | | RECOVERY | Completed | 2021-02-10 10:47:10.806868 | +-----------+-----------+----------------------------+ 7 rows in set (0.01 sec) # 注意，因为是克隆的，所以权限等于数据提供者保持了一致，所以需要修改密码文件，以便快捷方式能正常使用 mysql> show databases; # 214 +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | | testdb214 | | testdb215 | +--------------------+ 6 rows in set (0.03 sec) # 215 mysql> SELECT STAGE, STATE, END_TIME FROM performance_schema.clone_progress; +-----------+-------------+----------------------------+ | STAGE | STATE | END_TIME | +-----------+-------------+----------------------------+ | DROP DATA | Completed | 2021-02-10 10:50:52.940287 | | FILE COPY | Completed | 2021-02-10 10:51:10.128826 | | PAGE COPY | Completed | 2021-02-10 10:51:10.458558 | | REDO COPY | Completed | 2021-02-10 10:51:10.687318 | | FILE SYNC | In Progress | NULL | | RESTART | Not Started | NULL | | RECOVERY | Not Started | NULL | +-----------+-------------+----------------------------+ 7 rows in set (0.00 sec) mysql> SELECT STAGE, STATE, END_TIME FROM performance_schema.clone_progress; +-----------+-----------+----------------------------+ | STAGE | STATE | END_TIME | +-----------+-----------+----------------------------+ | DROP DATA | Completed | 2021-02-10 10:50:52.940287 | | FILE COPY | Completed | 2021-02-10 10:51:10.128826 | | PAGE COPY | Completed | 2021-02-10 10:51:10.458558 | | REDO COPY | Completed | 2021-02-10 10:51:10.687318 | | FILE SYNC | Completed | 2021-02-10 10:51:16.172598 | | RESTART | Completed | 2021-02-10 10:51:20.179976 | | RECOVERY | Completed | 2021-02-10 10:51:21.828740 | +-----------+-----------+----------------------------+ 7 rows in set (0.00 sec) mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | | testdb214 | | testdb215 | +--------------------+ 6 rows in set (0.02 sec)
# 经过比对，这个在各个节点是不一样的 # cat /data/mysql/mysql_3306/data/auto.cnf [auto] server-uuid=8d94b44b-645e-11eb-b232-fa163ee8385c
五、克隆方式部署主从复制环境
# 在主库上创建复制账号 create user 'repluser'@'10.100.19.%' identified by 'repluserpwd'; GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'repluser'@'10.100.19.%';
# 在从库中执行操作 CHANGE REPLICATION SOURCE TO SOURCE_HOST='10.100.19.216', SOURCE_USER='repluser', SOURCE_PASSWORD='repluserpwd', SOURCE_PORT=3306, SOURCE_AUTO_POSITION=1; start replica; show replica status\G; mysql> show replica status\G; *************************** 1. row *************************** Replica_IO_State: Waiting for master to send event Source_Host: 10.100.19.216 Source_User: repluser Source_Port: 3306 Connect_Retry: 60 Source_Log_File: mysql-binlog.000001 Read_Source_Log_Pos: 1401 Relay_Log_File: relay-log.000002 Relay_Log_Pos: 1622 Relay_Source_Log_File: mysql-binlog.000001 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: test.% Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Source_Log_Pos: 1401 Relay_Log_Space: 1825 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: 15402 Source_UUID: b7e35965-645f-11eb-a1e6-fa163e244eb1 Source_Info_File: mysql.slave_master_info SQL_Delay: 0 SQL_Remaining_Delay: NULL Replica_SQL_Running_State: Slave 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: b7e35965-645f-11eb-a1e6-fa163e244eb1:1-4 Executed_Gtid_Set: b7e35965-645f-11eb-a1e6-fa163e244eb1:1-4 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) ERROR: No query specified mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | | testdb214 | | testdb215 | +--------------------+ 6 rows in set (0.01 sec) mysql>
完毕！
