Mysql主从复制注意事项的讲解

一、报错error connecting to master 'x@x.x.x.x:x' - retry-time: 60 retries: 86400

今天搭建mysql主从复制，一直报这个错。我是在一台虚拟机上使用多实例创建的2个不同端口的数据库，查了很久，才解决。

1.检查主从复制的用户名密码；

2.检查MASTER_LOG_FILE和MASTER_LOG_POS。

记住配置从库的命令，这些参数都要参考主库的配置：

mysql> CHANGE MASTER TO MASTER_HOST='127.0.0.1', MASTER_PORT=3308, MASTER_USER='root', MASTER_PASSWORD='oldboy123', MASTER_LOG_FILE='mysql-bin.000007', MASTER_LOG_POS=797;

mysql> show slave status \G*************************** 1. row ***************************        Slave_IO_State: Waiting for master to send event         Master_Host: 127.0.0.1         Master_User: root         Master_Port: 3308        Connect_Retry: 60       Master_Log_File: mysql-bin.000007     Read_Master_Log_Pos: 797        Relay_Log_File: mysql-relay-bin.000002        Relay_Log_Pos: 253    Relay_Master_Log_File: mysql-bin.000007       Slave_IO_Running: Yes      Slave_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_Master_Log_Pos: 797       Relay_Log_Space: 409       Until_Condition: None        Until_Log_File:         Until_Log_Pos: 0      Master_SSL_Allowed: No      Master_SSL_CA_File:       Master_SSL_CA_Path:        Master_SSL_Cert:       Master_SSL_Cipher:         Master_SSL_Key:     Seconds_Behind_Master: 0Master_SSL_Verify_Server_Cert: No        Last_IO_Errno: 0        Last_IO_Error:         Last_SQL_Errno: 0        Last_SQL_Error:  Replicate_Ignore_Server_Ids:        Master_Server_Id: 3308

二、从库出现冲突无法复制。可以将同步指针向下移动一个，如果多次不同步，重复操作。

mysql> stop slave;Query OK, 0 rows affected (0.02 sec)mysql> set global sql_slave_skip_counter=1;Query OK, 0 rows affected (0.00 sec)mysql> start slave;Query OK, 0 rows affected (0.01 sec)

log-slave-updateslog-bin = /data/3309/data/mysql-bin #根据实际情况写expire_logs_days = 7 #相当于find /data/3309/data -type f -name "mysql-bin.000* -mtime +7 | xargs rm -f"

更多相关文章

随机推荐