MySQL master-Slave synchronization setup and troubleshooting

the definition of master-slave synchronization:is a structured pattern for storing data.

Primary: The database server accessed by the client is the primary library server master

from: connect the main library server, automatically synchronize all the data of the main library to the native slave

1. What is MySQL master-slave synchronization?

When the Master (Master) library data changes, the changes are synchronized to the slave (from) library in real time.

2. What are the advantages of master-slave synchronization?

Scale the load capacity of the database horizontally.

Fault tolerant, high availability. Failover (failed switchover)/high availability

Data backup.

3. Specific content and purpose of master-slave synchronization

Whether delete, update, insert, or create functions, stored procedures, all operations are on master. When Master has an operation, slave will quickly receive these operations, thus synchronizing.

4. Basic structure:

One-way replication: Main---from

Extend your App

Chained copy:-----from

Bidirectional replication: Master <--> Slave

Radial replication: From <--to Main----from

|

From

5. Principle of implementation

On the master machine, the master-slave synchronization event will be written to a special log file (Binary-log), on the Slave machine, slave read the master-slave synchronization events, and according to the changes in the Read event, the slave library to make the corresponding changes, the master-slave synchronization is realized!

6. There are 3 modes of master-Slave synchronization events :  

statement, row, mixed

Statement : Writes SQL statements for database operations to Binlog.

Row : Each data change is written to Binlog.

Mixed : The statement is mixed with row. MySQL decides when to write the statement format, and when to write the Binlog in the row format.

Realize:

7. Purpose

When the data on master changes, the event (INSERT, UPDATE, delete) changes are written sequentially to Binlog.

MySQL master-Slave synchronization setup steps:

Mainly divided into "Main library configuration" and "From Library Configuration"

Example Machine IP and assignment:Master 192.168.4.52

Slave 192.168.4.53

Configuration steps for the main library:

1. Enable Database Binlog log

# VIM/ETC/MY.CNF

[Mysqld]

server_id=52 //Database ID "Main library is not the same as from library"

Log-bin=master//Enable Binlog log and set the path

binlog-format= "Mixed"//Specify Binlog usage mode

2. Authorized Users

mysql> grant replication Slave on *. *

→

-e [email protected] "192.168.4.53"

-Identified by "123456";

Update Database Permissions

Mysql>flush privileges;

3. View the status of master

# Show Master status;

From the library configuration step (slave):

1. See if you can log in to the database

Mysql-h192.168.4.52-u tom-p123456

Show grants; View Permissions

2. Specify the database ID number (SERVER_ID)

Vim/etc/my.cnf

[MySQL]

server_id=53

Systemctl Restar mysqld

3. Administrator specifies information about the database server

Mysql> Change Master to

-master_host= "192.168.4.52",//Specify the IP address of the main library

-Master_user= "Tom",//Specify the authorized user name

-master_password= "123456",

-master_log_file= "master.000001",//when specifying the main log, you can go to the main library to view the fill

master_log_pos=447;

4. Enable the Slave process

mysql> start slave; Open the slave process

Mysql> show Slave status\g; View the slave process

Slave_io_running:yes//See if the thread is running

Slave_sql_running:yes

• I/O threads . The thread thread attached to the master machine, and the Binlog dump thread on the master machine sends the Binlog content to the I/O thread. After the I/O thread receives the Binlog content, the content is then written to the local relay log.

• SQL Thread . The thread reads the relay log written by the I/O thread. And according to relay log content to the slave database to do the corresponding operation.

Note: The slave_io_running and slave_sql_running processes must be running normally, that is, the Yes state, otherwise the synchronization fails. These two can be used to determine whether master-slave synchronization is successful

5. Testing

Mysql>create database Ceshi; Master Master Library Create test Library

Mysql>show databases; Slave from the gallery to see if updates are automatic

Frequently Asked Questions:

If the thread is not in the Yes state when the process is enabled, you can check the error message below to debug

Common causes:

One: The same database UUID causes the conflict

Workaround: Vim/var/lib/mysql/auto.cnf Modify the UUID to make it different

Restart database

Restarting the slave process

"If you can't sync then you need to re-specify the database information and restart the slave process."

Two: Database ID conflict, reset ID and restart service

Three: See if the command is incorrectly written

Four: Delete the effect of Binlog log

" If there is a slave secondary server that will be synchronized, the server needs to read one of the logs that you are deleting, and the synchronization will not execute and generate an error, if the slave subordinate server is off (or Master-slave master-slave relationship is off), If one of the logs to be read is cleaned, the slave slave server cannot be synchronized when it is started, and when the subordinate server is replicating, deleting the Binlog log has no effect, and no need to stop the master-slave service "

MySQL master-Slave synchronization setup and troubleshooting