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

Introduction to MySQL MHA operation status monitoring

Table of contents 1. Project Description 1.1 Back...

Methods and techniques for quickly displaying web page images

1. Use .gifs rather than .jpgs. GIFs are smaller ...

Beginners learn some HTML tags (2)

Related article: Beginners learn some HTML tags (1...

How to use Docker Compose to implement nginx load balancing

Implement Nginx load balancing based on Docker ne...

MySQL column to row conversion and year-month grouping example

As shown below: SELECT count(DISTINCT(a.rect_id))...

The webpage cannot be opened because the div element lacks a closing tag

At first I thought it was a speed issue, so I late...

How to restore single table data using MySQL full database backup data

Preface When backing up the database, a full data...

18 killer JavaScript one-liners

Preface JavaScript continues to grow and prosper ...

js to achieve simple product screening function

This article example shares the specific code of ...

HTML5+CSS3 coding standards

The Golden Rule No matter how many people are wor...

Tutorial on installing jdk1.8 on ubuntu14.04

1. Download jdk download address我下載的是jdk-8u221-li...

Two ways to prohibit clearing the input text input cache in html

Most browsers will cache input values ​​by defaul...

Detailed explanation of grep and egrep commands in Linux

rep / egrep Syntax: grep [-cinvABC] 'word'...

Zabbix WEB monitoring implementation process diagram

Take zabbix's own WEB interface as an example...