MySQL8.0.45主从搭建传统方式以及使用mysql clone克隆方式搭建
MySQL8.0.45主从搭建传统方式
安装忽略
主库配置
# cat /etc/my.cnf [mysqld] basedir=/data/mysql-8.0.45 datadir=/data/mysql-8.0.45/data port=23306 socket=/data/mysql-8.0.45/mysql.sock log-error=/data/mysql-8.0.45/data/error_mysqld.log pid-file=/data/mysql-8.0.45/data/mysqld.pid default-time-zone = +08:00 lc-messages-dir = /data/mysql-8.0.45/share lc-messages = en_US server_id=208 log-bin=mysql-bin binlog_expire_logs_seconds = 604800 #为7天 max_binlog_size = 512M innodb_buffer_pool_size = 16G innodb_buffer_pool_instances = 8 # 多实例提高并发 innodb_file_per_table = ON innodb_max_dirty_pages_pct = 75 # 减少突发刷盘 innodb_log_buffer_size = 64M # 日志缓冲区大小 innodb_redo_log_capacity = 1G lower_case_table_names=1 max_connections=1000 wait_timeout=1800 interactive_timeout=1800 #单位s skip-name-resolve = ON #主从配置 gtid-mode=ON enforce-gtid-consistency log-replica-updates=ON # 密码复杂度 #validate_password.policy = 1 #validate_password.length = 10 #validate_password.number_count = 1 #validate_password.mixed_case_count = 1 #validate_password.special_char_count = 1 #validate_password.check_user_name = ON # 加载连接控制插件 #plugin-load-add=connection_control.so # 强制永久启用（无法卸载，重启丢失） #connection-control=FORCE_PLUS_PERMANENT #connection-control-failed-login-attempts=FORCE_PLUS_PERMANENT # 安全策略（等保推荐） #connection_control_failed_connections_threshold=5 # 失败5次触发延迟 #connection_control_min_connection_delay=1000 # 最小延迟1秒 #connection_control_max_connection_delay=60000 # 最大延迟60秒 # 锁相关优化 innodb_lock_wait_timeout = 50 # 锁等待超时时间 innodb_deadlock_detect = ON # 死锁检测 innodb_print_all_deadlocks = ON # 记录所有死锁信息 # 慢查询日志 slow_query_log = ON slow_query_log_file =/data/mysql/data/slow.log long_query_time = 2 # 临时表存储在内存（避免磁盘临时表） tmp_table_size = 64M max_heap_table_size = 64M innodb_temp_data_file_path=ibtmp1:12M:autoextend:max:12G [mysql] socket=/data/mysql/mysql.sock [client] socket=/data/mysql/mysql.sock从库配置
# cat /etc/my.cnf [mysqld] basedir=/data/mysql-8.0.45 datadir=/data/mysql-8.0.45/data port=23306 socket=/data/mysql-8.0.45/mysql.sock log-error=/data/mysql-8.0.45/data/error_mysqld.log pid-file=/data/mysql-8.0.45/data/mysqld.pid default-time-zone = +08:00 lc-messages-dir = /data/mysql-8.0.45/share lc-messages = en_US server_id=82 log-bin=mysql-bin binlog_expire_logs_seconds = 604800 #为7天 max_binlog_size = 512M innodb_buffer_pool_size = 16G innodb_buffer_pool_instances = 8 # 多实例提高并发 innodb_file_per_table = ON innodb_max_dirty_pages_pct = 75 # 减少突发刷盘 innodb_log_buffer_size = 64M # 日志缓冲区大小 innodb_redo_log_capacity = 1G lower_case_table_names=1 max_connections=1000 wait_timeout=1800 interactive_timeout=1800 #单位s skip-name-resolve = ON ##主从配置 gtid_mode=ON enforce-gtid-consistency=ON relay-log=relay-bin log-replica-updates=ON replicate_wild_ignore_table = mysql.% replicate_wild_ignore_table = sys.% replicate_wild_ignore_table = information_schema.% replicate_wild_ignore_table = performance_schema.% ##从库设置只读 #read_only = 1 #super_read_only = 1 # 密码复杂度 #validate_password.policy = 1 #validate_password.length = 10 #validate_password.number_count = 1 #validate_password.mixed_case_count = 1 #validate_password.special_char_count = 1 #validate_password.check_user_name = ON # 加载连接控制插件 #plugin-load-add=connection_control.so # 强制永久启用（无法卸载，重启丢失） #connection-control=FORCE_PLUS_PERMANENT #connection-control-failed-login-attempts=FORCE_PLUS_PERMANENT # 安全策略（等保推荐） #connection_control_failed_connections_threshold=5 # 失败5次触发延迟 #connection_control_min_connection_delay=1000 # 最小延迟1秒 #connection_control_max_connection_delay=60000 # 最大延迟60秒 # 锁相关优化 innodb_lock_wait_timeout = 50 # 锁等待超时时间 innodb_deadlock_detect = ON # 死锁检测 innodb_print_all_deadlocks = ON # 记录所有死锁信息 # 慢查询日志 slow_query_log = ON slow_query_log_file =/data/mysql-8.0.45/data/slow.log long_query_time = 2 # 临时表存储在内存（避免磁盘临时表） tmp_table_size = 64M max_heap_table_size = 64M innodb_temp_data_file_path=ibtmp1:12M:autoextend:max:12G [mysql] socket=/data/mysql-8.0.45/mysql.sock [client] socket=/data/mysql-8.0.45/mysql.sock主从库创建同步用户
SQL> create user repl@'%' identified with 'mysql_native_password' by 'repl@123'; SQL> grant replication slave on *.* to 'repl'@'%'; SQL> exit;从库执行
CHANGE REPLICATION SOURCE TO SOURCE_HOST='10.10.1.1', SOURCE_PORT=23306, SOURCE_USER='repl', SOURCE_PASSWORD='repl@123', SOURCE_AUTO_POSITION = 1, GET_SOURCE_PUBLIC_KEY=1; start replica; show replica status\GMySQL8.0.45 mysql clone克隆方式搭建主从
参数配置
cat > /etc/my.cnf <<EOF [mysqld] basedir=/data/mysql-8.0.45 datadir=/data/mysql-8.0.45/data port=23306 socket=/data/mysql-8.0.45/mysql.sock log-error=/data/mysql-8.0.45/data/error_mysqld.log pid-file=/data/mysql-8.0.45/data/mysqld.pid default-time-zone = +08:00 lc-messages-dir = /data/mysql-8.0.45/share lc-messages = en_US server_id=125 log-bin=mysql-bin binlog_expire_logs_seconds = 604800 #为7天 max_binlog_size = 512M innodb_buffer_pool_size = 2G innodb_buffer_pool_instances = 8 # 多实例提高并发 innodb_file_per_table = ON innodb_max_dirty_pages_pct = 75 # 减少突发刷盘 innodb_log_buffer_size = 64M # 日志缓冲区大小 innodb_redo_log_capacity = 1G lower_case_table_names=1 plugin-load-add=mysql_clone.so max_connections=1000 wait_timeout=1800 interactive_timeout=1800 #单位s skip-name-resolve = ON #主从配置 gtid-mode=ON enforce-gtid-consistency log-replica-updates=ON # 锁相关优化 innodb_lock_wait_timeout = 50 # 锁等待超时时间 innodb_deadlock_detect = ON # 死锁检测 innodb_print_all_deadlocks = ON # 记录所有死锁信息 # 慢查询日志 slow_query_log = ON slow_query_log_file = /data/mysql-8.0.45/data/slow.log long_query_time = 2 # 临时表存储在内存（避免磁盘临时表） tmp_table_size = 64M max_heap_table_size = 64M [mysql] socket=/data/mysql-8.0.45/mysql.sock [client] socket=/data/mysql-8.0.45/mysql.sock EOF主从库安装克隆插件
INSTALL PLUGIN clone SONAME ‘mysql_clone.so’;
编辑my.cnf
plugin-load-add=mysql_clone.so
检查插件是否安装成功
SELECT PLUGIN_NAME,PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME=‘clone’;
show plugins;
主库创建用户并授权
创建用户
CREATE USER ‘repl’@‘%’ IDENTIFIED BY ‘xld123’;
GRANT replication SLAVE ON.TO ‘repl’@‘%’;
GRANT REPLICATION SLAVE, REPLICATION CLIENT ON.TO ‘repl’@‘%’;
FLUSH PRIVILEGES;
ALTER USER ‘repl’@‘%’ IDENTIFIED WITH mysql_native_password BY ‘xld123’;
FLUSH PRIVILEGES;
创建克隆账号
CREATE USER ‘clone_user’@‘%’ IDENTIFIED BY ‘xld123’;
grant BACKUP_ADMIN on.to ‘clone_user’@‘%’;
grant CLONE_ADMIN on.to ‘clone_user’@‘%’;
从库执行克隆
在从库上执行克隆命令，如下：
– 从库配置参数
mysql -uroot -p’Gaa@mydb2026’ -P23306 -S /data/mysql-8.0.45/mysql.sock
SET GLOBAL clone_valid_donor_list = ‘192.168.56.123:23306’;
– 从库开始克隆
CLONE INSTANCE FROM ‘clone_user’@‘192.168.56.123’:23306 IDENTIFIED BY ‘xld123’;
日志记录
[root@db2 ~]# mysql -uroot -p'Gaa@mydb2026' -P23306 -S /data/mysql-8.0.45/mysql.sock mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 8 Server version: 8.0.45 MySQL Community Server - GPL Copyright (c) 2000, 2026, 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 '\h' for help. Type '\c' to clear the current input statement. mysql> mysql> mysql> SET GLOBAL clone_valid_donor_list = '192.168.56.123:23306'; Query OK, 0 rows affected (0.00 sec) mysql> CLONE INSTANCE FROM 'clone_user'@'192.168.56.123':23306 IDENTIFIED BY 'xld123'; Query OK, 0 rows affected (1.19 sec) mysql> exit [root@db2 ~]# mysql -uroot -p'Gaa@mydb2026' -P23306 -S /data/mysql-8.0.45/mysql.sock mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 8 Server version: 8.0.45 MySQL Community Server - GPL Copyright (c) 2000, 2026, 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 '\h' for help. Type '\c' to clear the current input statement. mysql> select * from performance_schema.clone_status\G; *************************** 1. row *************************** ID: 1 PID: 0 STATE: Completed BEGIN_TIME: 2026-08-24 23:09:28.033 END_TIME: 2026-08-24 23:09:34.207 SOURCE: 192.168.56.123:23306 DESTINATION: LOCAL INSTANCE ERROR_NO: 0 ERROR_MESSAGE: BINLOG_FILE: mysql-bin.000005 BINLOG_POSITION: 2306 GTID_EXECUTED: 3c600f2e-9fc8-11f1-bc54-0800271e45c7:1-9 1 row in set (0.01 sec) ERROR: No query specified mysql> CHANGE REPLICATION SOURCE TO -> SOURCE_HOST='192.168.56.123', -> SOURCE_PORT = 23306, -> SOURCE_USER='repl', -> SOURCE_PASSWORD='xld123', -> MASTER_AUTO_POSITION = 1; Query OK, 0 rows affected, 3 warnings (0.11 sec) mysql> start REPLICA; show replica status\G Query OK, 0 rows affected (0.06 sec) mysql> show replica status\G *************************** 1. row *************************** Replica_IO_State: Checking source version Source_Host: 192.168.56.123 Source_User: repl Source_Port: 23306 Connect_Retry: 60 Source_Log_File: Read_Source_Log_Pos: 4 Relay_Log_File: db2-relay-bin.000001 Relay_Log_Pos: 4 Relay_Source_Log_File: 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: 0 Relay_Log_Space: 157 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: 0 Source_UUID: 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: Executed_Gtid_Set: 3c600f2e-9fc8-11f1-bc54-0800271e45c7:1-9 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) mysql>