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

iFrame is a great way to use it as a popup layer to cover the background

I have been working on a project recently - Budou ...

A brief summary of basic web page performance optimization rules

Some optimization rules for browser web pages Pag...

Vue+axios sample code for uploading pictures and recognizing faces

Table of contents Axios Request Qs processing dat...

How to add Nginx to system services in CentOS7

Introduction After compiling, installing and solv...

Nginx configuration PC site mobile site separation to achieve redirection

Use nginx to configure the separation of PC site ...

5 Easy Ways to Free Up Space on Ubuntu

Preface Most people will probably perform this op...

MySQL 8 new features: Invisible Indexes

background Indexes are a double-edged sword. Whil...

Forever+nginx deployment method example of Node site

I recently bought the cheapest Tencent cloud serv...

Summary of Linux user groups and permissions

User Groups In Linux, every user must belong to a...

Using zabbix to monitor the ogg process (Windows platform)

This article introduces how to monitor the ogg pr...

Example sharing of anchor tag usage in HTML

Anchor tag usage: Linking to a specific location i...

JavaScript basics for loop and array

Table of contents Loop - for Basic use of for loo...

Advantages and disadvantages of conditional comments in IE

IE's conditional comments are a proprietary (...