MySQL8.0.45主从搭建传统方式安装忽略主库配置# cat /etc/my.cnf [mysqld] basedir/data/mysql-8.0.45 datadir/data/mysql-8.0.45/data port23306 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_id208 log-binmysql-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_names1 max_connections1000 wait_timeout1800 interactive_timeout1800 #单位s skip-name-resolve ON #主从配置 gtid-modeON enforce-gtid-consistency log-replica-updatesON # 密码复杂度 #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-addconnection_control.so # 强制永久启用无法卸载重启丢失 #connection-controlFORCE_PLUS_PERMANENT #connection-control-failed-login-attemptsFORCE_PLUS_PERMANENT # 安全策略等保推荐 #connection_control_failed_connections_threshold5 # 失败5次触发延迟 #connection_control_min_connection_delay1000 # 最小延迟1秒 #connection_control_max_connection_delay60000 # 最大延迟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_pathibtmp1: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 port23306 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_id82 log-binmysql-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_names1 max_connections1000 wait_timeout1800 interactive_timeout1800 #单位s skip-name-resolve ON ##主从配置 gtid_modeON enforce-gtid-consistencyON relay-logrelay-bin log-replica-updatesON 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-addconnection_control.so # 强制永久启用无法卸载重启丢失 #connection-controlFORCE_PLUS_PERMANENT #connection-control-failed-login-attemptsFORCE_PLUS_PERMANENT # 安全策略等保推荐 #connection_control_failed_connections_threshold5 # 失败5次触发延迟 #connection_control_min_connection_delay1000 # 最小延迟1秒 #connection_control_max_connection_delay60000 # 最大延迟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_pathibtmp1: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 repl123; SQL grant replication slave on *.* to repl%; SQL exit;从库执行CHANGE REPLICATION SOURCE TO SOURCE_HOST10.10.1.1, SOURCE_PORT23306, SOURCE_USERrepl, SOURCE_PASSWORDrepl123, SOURCE_AUTO_POSITION 1, GET_SOURCE_PUBLIC_KEY1; 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 port23306 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_id125 log-binmysql-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_names1 plugin-load-addmysql_clone.so max_connections1000 wait_timeout1800 interactive_timeout1800 #单位s skip-name-resolve ON #主从配置 gtid-modeON enforce-gtid-consistency log-replica-updatesON # 锁相关优化 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.cnfplugin-load-addmysql_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’Gaamydb2026’ -P23306 -S /data/mysql-8.0.45/mysql.sockSET GLOBAL clone_valid_donor_list ‘192.168.56.123:23306’;– 从库开始克隆CLONE INSTANCE FROM ‘clone_user’‘192.168.56.123’:23306 IDENTIFIED BY ‘xld123’;日志记录[rootdb2 ~]# mysql -uroot -pGaamydb2026 -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_user192.168.56.123:23306 IDENTIFIED BY xld123; Query OK, 0 rows affected (1.19 sec) mysql exit [rootdb2 ~]# mysql -uroot -pGaamydb2026 -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_HOST192.168.56.123, - SOURCE_PORT 23306, - SOURCE_USERrepl, - SOURCE_PASSWORDxld123, - 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