Explanation of Precautions for Mysql Master Slave Copy

1. Error error connecting to master 'x @ x. x. x. x: x'-retry-time: 60 retries: 86400

Today, build mysql master-slave copy, and report this error directly. I am in a virtual machine using multiple instances to create two different ports of the database, check for a long time, just solve.

1. Check the user name and password copied by master and slave;

2. Check MASTER_LOG_FILE and MASTER_LOG_POS.

Remember the command to configure the slave library, and these parameters should refer to the configuration of the master library:

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;

Query synchronization results:

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: 0
Master_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

There are three synchronization results: Slave_IO_Running (I/O thread state), Slave_SQL_Running (SQL thread state), Seconds_Behind_Master (the number of seconds delayed by the slave library compared with the master library during replication).

2. A conflict from the library cannot be copied. You can move the synchronization pointer down by 1, and repeat the operation if it is not synchronized many times.

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)

3. The current slave library also serves as the master library for other slave libraries. Add the following parameters to my. cnf of the slave library, and modify the configuration of server_id and log-bin (remove comments and modify the default configuration) to restart the service.

log-slave-updates
log-bin = /data/3309/data/mysql-bin # Write according to the actual situation 
expire_logs_days = 7 # Equivalent to find /data/3309/data -type f -name "mysql-bin.000* -mtime +7 | xargs rm -f"

Summarize