MySQL8.0.45主從搭建傳統(tǒng)方式安裝忽略主庫配置# 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 # 多實例提高并發(fā) innodb_file_per_table ON innodb_max_dirty_pages_pct 75 # 減少突發(fā)刷盤 innodb_log_buffer_size 64M # 日志緩沖區(qū)大小 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 # 密碼復(fù)雜度 #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次觸發(fā)延遲 #connection_control_min_connection_delay1000 # 最小延遲1秒 #connection_control_max_connection_delay60000 # 最大延遲60秒 # 鎖相關(guān)優(yōu)化 innodb_lock_wait_timeout 50 # 鎖等待超時時間 innodb_deadlock_detect ON # 死鎖檢測 innodb_print_all_deadlocks ON # 記錄所有死鎖信息 # 慢查詢?nèi)罩?slow_query_log ON slow_query_log_file /data/mysql/data/slow.log long_query_time 2 # 臨時表存儲在內(nèi)存避免磁盤臨時表 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 # 多實例提高并發(fā) innodb_file_per_table ON innodb_max_dirty_pages_pct 75 # 減少突發(fā)刷盤 innodb_log_buffer_size 64M # 日志緩沖區(qū)大小 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.% ##從庫設(shè)置只讀 #read_only 1 #super_read_only 1 # 密碼復(fù)雜度 #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次觸發(fā)延遲 #connection_control_min_connection_delay1000 # 最小延遲1秒 #connection_control_max_connection_delay60000 # 最大延遲60秒 # 鎖相關(guān)優(yōu)化 innodb_lock_wait_timeout 50 # 鎖等待超時時間 innodb_deadlock_detect ON # 死鎖檢測 innodb_print_all_deadlocks ON # 記錄所有死鎖信息 # 慢查詢?nèi)罩?slow_query_log ON slow_query_log_file /data/mysql-8.0.45/data/slow.log long_query_time 2 # 臨時表存儲在內(nèi)存避免磁盤臨時表 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主從庫創(chuàng)建同步用戶SQL create user repl% identified with mysql_native_password by repl123; SQL grant replication slave on *.* to repl%; SQL exit;從庫執(zhí)行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克隆方式搭建主從參數(shù)配置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 # 多實例提高并發(fā) innodb_file_per_table ON innodb_max_dirty_pages_pct 75 # 減少突發(fā)刷盤 innodb_log_buffer_size 64M # 日志緩沖區(qū)大小 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 # 鎖相關(guān)優(yōu)化 innodb_lock_wait_timeout 50 # 鎖等待超時時間 innodb_deadlock_detect ON # 死鎖檢測 innodb_print_all_deadlocks ON # 記錄所有死鎖信息 # 慢查詢?nèi)罩?slow_query_log ON slow_query_log_file /data/mysql-8.0.45/data/slow.log long_query_time 2 # 臨時表存儲在內(nèi)存避免磁盤臨時表 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;主庫創(chuàng)建用戶并授權(quán)創(chuàng)建用戶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;創(chuàng)建克隆賬號CREATE USER ‘clone_user’‘%’ IDENTIFIED BY ‘xld123’;grant BACKUP_ADMIN on.to ‘clone_user’‘%’;grant CLONE_ADMIN on.to ‘clone_user’‘%’;從庫執(zhí)行克隆在從庫上執(zhí)行克隆命令如下– 從庫配置參數(shù)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