Mysql master-slave replication principle and configuration, mysql master-slave Principle

Mysql master-slave replication principle and configuration, mysql master-slave Principle

Mysql master-slave replication principle and Configuration

1.Overview
The built-in replication function of Mysql is the basis for building large and high-performance applications. Distribution of Mysql Data to multiple systems. This distribution mechanism isBy copying data from one Mysql host to another Server Load balancer,And execute it again. During the replication process, one server acts as the master server, and one or more other servers act as slave servers. The master server writes updates to the binary log file and maintains an index of the file to track log loops. These logs can record updates sent to the slave server. When a slave server connects to the master server, it notifies the master server of the last successful update location read from the server in the log. The slave server receives any updates from that time, blocks them, and waits for the master server to notify you of new updates.

Note that when you perform a copy, all updates to the tables in the copy must be performed on the master server.Otherwise, you must be careful to avoid conflicts between updates to tables on the master server and those on the slave server.(So the slave server is usually set to read-only.Read_only = 1)

1.1 mysql supports the following replication types:

1) Statement-based replication: SQL statements executed on the master server and the same statements executed on the slave server. MySQL uses statement-based replication by default, which is highly efficient. If you find that you cannot perform exact replication, row-based replication is automatically selected.
2): Row-based replication: Copies the changed content instead of executing the command on the slave server. It is supported from mysql5.0.
3): Hybrid replication: Statement-based replication is used by default. Once a statement-based replication fails, row-based replication is used.

1.2. Problems solved by Replication

MySQL replication technology has the following features:
(1) Data distribution)
(2) load balancing)
(3) backup (Backups)
(4) High availability and fault tolerance High availability and failover

1.3 How replication works >Replication principle:

(1) The master will record changes to binary logs (these records are called binary log events and binary log events );
(2) slave copies the binary log events of the master to its relay log );
(3) slave retries the events in the relay log and applies the changes to its own data.

Describes the replication process:

Slave replication process:

Step 1: The master node records binary logs. The master binary log records these changes before each transaction updates data. MySQL writes transactions into binary logs in sequence, even if the statements in the transaction are executed in a crossover manner. After the event is written to the binary log, the master notifies the storage engine to submit the transaction.
Step 2: slave uses I/O thread to read the binary log of the master and copy it to its own relay log. First, slave starts a working thread-I/O thread. The I/O thread opens a normal connection on the master and starts the binlog dump process. The Binlog dump process reads the event from the binary log of the master. If it has already followed the master, it will sleep and wait for the master to generate a new event. The I/O thread writes these events to the relay log.
Step 3: The last step for SQL slave thread to process the process. The SQL thread reads the event from the relay log and executes the event again to update the slave data so that it is consistent with the data in the master. This thread is consistent with the I/O thread,Relay logs are usually stored in the OS cache, so the overhead of relay logs is very small.

Note: The data updated by the I/O thread is recorded in master.info. (/application/mysql/data)
In addition, there is also a working thread in the master: Like other MySQL connections, opening a connection in the master will also enable the master to start a thread.There is an important limitation in the replication process-replication is serialized on the slave, that is, the parallel update operations on the master cannot be performed in parallel on the slave.In the actual production process, synchronization is not real-time, but asynchronous.

2.Master/Slave practicesConfiguration

Prepare two MySQL database servers: Master and slave. Master is the Master server, and slave is the slave server. In the initial state, the data information in Master and slave is the same. If the data is different, you should first export the master data in full backup and then import it to slave to ensure data consistency between the master database and slave database. (multiple port instances can also be used)

Key points:
　　The media responsible for transmitting various modification actions on the master and slave servers is the binary change log of the master server, which records the various modification actions to be transmitted to the slave server. Therefore, the master server must activate the binary log function. The slave server must have the permission to connect to the master server and request the master server to transmit the binary change log to it.
Environment:
The Master version 5.1.17 and slave versions are the same as 5.5.34 (in fact, the best version is the same, but some functions may not be able to be copied)
Operating System: centos 6.9

2.1 create a replication account

1. Create a backup account in the Master database: each slave uses the standard MySQL user name and password to connect to the master.The user who performs the copy operation will grantReplication slavePermission. The username and password are stored in the master.info text file.

The command is as follows:
Mysql>Grant replication slave,Reload, super, On*.*To 'BackuP'@ '192.168.52.220'Identified'201756';

Create an account backup and only Allow logon from 192.168.52.220. The password is 123456.Here @ the ip address is the slave ip Address

2.2 copy data

(If you have completely installed the mysql Master/Slave server, this step is not required. Because the newly installed master and slave have the same data)

If not, you need:Shut down the Master server and copy the data in the Master to server B,Make sure that the data in the Master and slave is synchronized, and that the write operations on the Master and slave servers are prohibited before the end of all settings, so that the data in the two databases must be the same!

In this exampleWhen the master database is fully standby, use the -- master-data = 1 parameter for backup as follows:

[Root @ localhost] # mysqldump-uroot-p123456-A-B-F -- master-data = 1-x -- events | gzip> Backup file.

With this parameter, when the master node is synchronized from the master node, you do not need to specify the mysql-bin location and file separately when you execute the change master node, because it is already in full backup, and the statement is executed, recovery has been completed during full backup recovery from the database. and specify related files.

2.3 configure master

Next, configure the master, including opening the binary log and specifying a unique servr ID. For example, add the following values to the configuration file:

Server-id = 1#ID of master server
Log-bin = mysql-bin#Binary change log

Restart masterRun show master status. If the output is as follows, the binary log is successfully enabled:

Mysql> show master status;

+ ------------------ + ---------- + -------------- + ------------------ +

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |

+ ------------------ + ---------- + -------------- + ------------------ +

| Mysql-bin.000002 | 106 |

+ ------------------ + ---------- + -------------- + ------------------ +

1 row in set (0.00 sec)

 

2.4 configure slave

The Slave configuration is similar to that of the master. You also need to restart the slave MySQL. As follows:
Log_bin = mysql-bin
Server_id = 2
Relay_log = mysql-relay-bin
Log_slave_updates = 1
Read_only = 1
1 ):Server_id is required and unique.

2 ):Slave does not need to enable binary logs, but in some cases, it must be set. For example, if slave is another slave master, bin_log must be set. Here, we enable binary logs and display the name (the default name is hostname, but if the hostname is changed, the problem will occur ).
3 ):Relay_log configures the relay log. log_slave_updates indicates that slave writes the replication event into its own binary log (which will be useful later ).
Some people have enabled the slave binary log, but have not set log_slave_updates, and then check whether the slave Data has changed. This is an incorrect configuration.
4 ):Use read_only whenever possible, which prevents data changes (except for special threads ).But the application read_only that needs to create a table on slave is very practical.

The configuration also takes effect after being restarted.

2.5 start slave

At this point, let slave connect to the master and start to redo the events in the master binary log.Instead of using the configuration file for this operation, you should use the change master to statement. This statement can completely replace the modification TO the configuration file, and it can specify different masters for slave, you do not need to stop the server.As follows:

Mysql> change master to MASTER_HOST = 'host ip ',

-> MASTER_USER = 'rep ',

-> MASTER_PASSWORD = '123 ',

-> MASTER_LOG_FILE = 'mysql-bin.000001 ',# You do not need to specify -- master-data = 1 for Backup recovery. It is automatically specified in the backup.

-> MASTER_LOG_POS = 0 ;## You do not need to specify -- master-data = 1 for Backup recovery. It is automatically specified in the backup.

The value of MASTER_LOG_POS is 0 because it is the start position of the log.

 

If you do not use -- master-data, you can log on to the database and use show master status.MASTER_LOG_FILE

AndMASTER_LOG_POS.

 

Mysql> show master status;

+ ------------------ + ---------- + -------------- + ------------------ +

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |

+ ------------------ + ---------- + -------------- + ------------------ +

| Mysql-bin.000002 | 106 |

+ ------------------ + ---------- + -------------- + ------------------ +

1 row in set (0.00 sec)

You can use the show slave status statement to check whether the slave settings are correct:

Mysql> show slave status \ G

* *************************** 1. row ***************************

Slave_IO_State:

Master_Host: 192.168.52.220

Master_User: rep

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000001

Read_Master_Log_Pos: 4

Relay_Log_File: mysql-relay-bin.000001

Relay_Log_Pos: 4

Relay_Master_Log_File: mysql-bin.000001

Slave_IO_Running: No

Slave_ SQL _Running: No

... Omitted...

Seconds_Behind_Master: NULL

Slave_IO_State, Slave_IO_Running, and Slave_ SQL _Running are No

It indicates that the replication process has not started. The log location is 4 rather than 0, because 0 is the start position of the log file, not the log location. In fact, MySQL knows that the first event is located at 4.

 

Start copying. You can run:

Mysql> start slave;

Run show slave status to view the output result:

Mysql> show slave status \ G

* *************************** 1. row ***************************

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.52.220

Master_User: rep

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000001

Read_Master_Log_Pos: 164

Relay_Log_File: mysql-relay-bin.000001

Relay_Log_Pos: 164

Relay_Master_Log_File: mysql-bin.000001

Slave_IO_Running: Yes

Slave_ SQL _Running: Yes

... Omitted...

Seconds_Behind_Master: 0

Here we mainly look:
Slave_IO_Running = Yes
Slave_ SQL _Running = Yes

Slave's I/O and SQL threads have started to run, and Seconds_Behind_Master is no longer NULL. The log location is increased, meaning that some events are obtained and executed.

Verification:If you areMasterYou canSlaveYou can also see the changes in the location of various log files.

You can view the status of the master and slave threads. On the master, you can see the connection created by the slave I/O thread:

Enter show processlist \ G on the master;

Mysql> show processlist \ G

* *************************** 1. row ***************************

Id: 1

User: root

Host: localhost: 2096

Db: test

Command: Query

Time: 0

State: NULL

Info: show processlist

* *************************** 2. row ***************************

Id: 2

User: repl

Host: localhost: 2144

Db: NULL

Command: Binlog Dump

Time: 1838

State: Has sent all binlog to slave; waiting for binlog to be updated

Info: NULL

2 rows in set (0.00 sec)

Row 2 is the connection to the slave I/O thread.

Run this statement on the slave server:

Mysql> show processlist \ G

* *************************** 1. row ***************************

Id: 1

User: system user

Host:

Db: NULL

Command: Connect

Time: 2291

State: Waiting for master to send event

Info: NULL

* *************************** 2. row ***************************

Id: 2

User: system user

Host:

Db: NULL

Command: Connect

Time: 1852

State: Has read all relay log; waiting for the slave I/O thread to update it

Info: NULL

* *************************** 3. row ***************************

Id: 5

User: root

Host: localhost: 2152

Db: test

Command: Query

Time: 0

State: NULL

Info: show processlist

3 rows in set (0.00 sec)

Row 1 is the I/O thread state, and row 2 is the SQL thread state.

From network arrangement