Explanation of the precautions for Mysql master-slave replication

Explanation of the precautions for Mysql master-slave replication

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

Today I set up MySQL master-slave replication and kept reporting this error. I used multiple instances to create two databases with different ports on a virtual machine. It took me a long time to figure it out.

1. Check the username and password of master-slave replication;

2. Check MASTER_LOG_FILE and MASTER_LOG_POS.

Remember to configure the slave database commands. These parameters should refer to the configuration of the master database:

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

The synchronization results are shown in three items: Slave_IO_Running (I/O thread status), Slave_SQL_Running (SQL thread status), and Seconds_Behind_Master (the number of seconds the slave database is delayed compared to the master database during the replication process).

2. A conflict occurs in the slave database and replication cannot be performed. You can move the synchronization pointer down by one. If it is out of sync multiple times, repeat the operation.

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 database also serves as the master database for other slave databases. Add the following parameters to the my.cnf file of the slave library, modify the configuration of server_id and log-bin (remove comments and modify the default configuration), and restart the service.

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

Summarize

The above is the full content of this article. I hope that the content of this article will have certain reference learning value for your study or work. Thank you for your support of 123WORDPRESS.COM. If you want to learn more about this, please check out the following links

You may also be interested in:
  • Detailed explanation of the role and working principle of MySQL master-slave replication
  • Solution to the long delay of MySQL database master-slave replication
  • Summary of MYSQL full backup, master-slave replication, cascading replication, and semi-synchronization
  • In-depth understanding of MySQL master-slave replication thread state transition
  • MySQL configuration SSL master-slave replication
  • MYSQL master-slave replication knowledge points summary

<<:  VMware installation of Centos8 system tutorial diagram (Chinese graphical mode)

>>:  Analysis of the principle implementation from the source code of the mini-program developer tool

Recommend

Detailed example of changing Linux account password

Change personal account password If ordinary user...

5 things to note when writing React components using hooks

Table of contents 01. Use useState when render is...

Learn about CSS label display mode in one article

Tag type (display mode) HTML tags are generally d...

Docker+selenium method to realize automatic health reporting

This article takes the health reporting system of...

How to solve the high concurrency problem in MySQL database

Preface We all know that startups initially use m...

Vue dynamic menu, dynamic route loading and refresh pitfalls

Table of contents need: Ideas: lesson: Share the ...

Vue implements the full selection function

This article example shares the specific code of ...

How to cancel the background color of the a tag when it is clicked in H5

1. Cancel the blue color of the a tag when it is ...

Summary of Mysql high performance optimization skills

Database Command Specification All database objec...

Use the Linux seq command to generate a sequence of numbers (recommended)

The Linux seq command can generate lists of numbe...

How to use the dig/nslookup command to view DNS resolution steps

dig - DNS lookup utility When a domain name acces...

Best way to replace the key in json object

JSON (JavaScript Object Notation, JS Object Notat...