Shulou(Shulou.com)06/01 Report--

Principle and configuration of Mysql master-slave replication

Command entered

Blue fonts: comment

Background: important

Subtitle: White words on blue background

1. Overview of replication

Mysql's built-in replication function is the basis for building large, high-performance applications. Distribute the data of Mysql to multiple systems. The mechanism of this distribution is to copy the data of one host of Mysql to another host (slaves) and execute it again. During replication, one server acts as the master server, while one or more other servers act as the slave server. The primary server writes updates to the binary log file and maintains an index of the file to track the log cycle. These logs can record updates sent to the slave server. When a slave server connects to the master server, it informs the master server of the location of the last successful update read by the slave server in the log. Receive any updates that have occurred since then from the server, then block and wait for the primary server to notify the new update.

Please note that when you replicate, all updates to the replicated tables must be made on the primary server. Otherwise, you must be careful to avoid conflicts between user updates to the tables on the master server and updates to the tables on the slave server. (so the slave server is generally set to read-only read_only= 1)

1.1 types of replication supported by mysql:

1): statement-based replication: a SQL statement executed on the master server and the same statement executed on the slave server. MySQL uses statement-based replication by default, which is more efficient. Row-based replication is automatically selected when it is found that exact replication is not possible.

2): line-based replication: copy the changes instead of executing the command from the server. Support starts with mysql5.0

3): mixed-type replication: statement-based replication is used by default, and row-based replication is used once it is found that statement-based replication cannot be accurately replicated.

1.2. Problems solved by replication

MySQL replication technology has the following characteristics:

(1) data distribution (Data distribution)

(2) load balancing (load balancing)

(3) backup (Backups)

(4) High availability and fault tolerant line High availability and failover

1.3 how replication works > principles of replication:

(1) master records changes in binary log (binary log) (these records are called binary log events, binary log events)

(2) slave copies the binary log events of master to its relay log (relay log)

(3) slave redoes events in the relay log to apply changes to its own data.

The following figure describes the replication process:

Slave replication process:

Step 1: master records binary logs. The master binary log records these changes before each transaction updates the data. MySQL writes transactions serially to the binary log, even if the statements in the transaction are executed across each other. After the event is written to the binary log, master notifies the storage engine to commit the transaction.

Step 2: slave reads and copies the binary log of the master to its own relay log using Imax O thread. First, slave starts a worker thread-- the Imax O thread. The iUnip O thread opens a normal connection on master and then starts binlog dump process. Binlog dump process reads events from master's binary log, and if it has caught up with master, it sleeps and waits for master to generate new events. The Icano thread writes these events to the relay log.

Step 3: SQL slave thread (SQL from the thread) handles the last step of the process. The SQL thread reads events from the relay log and re-executes the events in it to update the data in slave to make it consistent with the data in master. This thread is consistent with the Imax O thread, and the relay log is usually in the cache of OS, so the overhead of the relay log is very small.

Note: the data updated by the I / O thread is recorded in master.info. (/ application/mysql/data)

In addition, there is a worker thread in master: like other MySQL connections, slave opening a connection in master causes master to start a thread. There is an important limitation to the replication process-replication is serialized on slave, which means that parallel update operations on master cannot operate in parallel on slave. In the actual production process, synchronization is not real-time, but asynchronous.

2. Master-slave practical configuration

Prepare two MySQL database servers, Master and slave,Master, and slave as the slave server. In the initial state, the data information in Master and slave is the same. If the data are different, you should first export all the master data, and then import it into slave. This is to ensure that the slave database data of the master database is consistent. (it can also be done with multi-port instances)

Main points:

The medium responsible for transmitting various modification actions in the master and slave server is the binary change log of the master server, which records the various modification actions that need to be transmitted to the slave server. Therefore, the primary server must activate the binary logging feature. The slave server must have sufficient permissions to connect to the master server and request the master server to transfer the binary change log to it.

Environment:

Master version 5.1.17 and slave version 5.5.34 (in fact, the best version is the same, some functions may not be copied)

Operating system: centos 6.9

2.1. Create a replication account

1. Set up a backup account in Master's database: each slave connects to master with a standard MySQL username and password. The user performing the replication operation is granted replication slave permission. The password of the user name is stored in the text file master.info

The command is as follows:

Mysql > grant replication slave,reload,super, on *. * to 'backup'@' 192.168.52.220' identified by '123456'

Set up an account backup and only allow login from the address 192.168.52.220 with a password of 123456. The ip address after @ here is the ip of slave.

2.2. Copy data

If you completely newly install the mysql master and slave server, this step is not required. Because the newly installed master and slave data are exactly the same)

If not, you need to shut down the Master server, copy the data in Master to server B, synchronize the data in Master and slave, and make sure that writes are prohibited in Master and slave servers before all setup operations are over, so that the data in the two databases must be the same!

When you complete the master database, use the parameter-- master-data=1 to back up as follows:

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

With this parameter, when performing master-slave synchronization, the execution of change master does not have to specify the location and file of the mysql-bin separately, because it is already in the complete file, and the statement is executed, and the recovery has been completed during the full recovery from the library. And the designation of related documents.

2.3.Config master

Next, configure 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 value of primary server A

Log-bin=mysql-bin # binary change date value

Restart master and run SHOW MASTER STATUS. The output below indicates that the binary log has been opened successfully:

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.Configuring slave

The configuration of Slave is similar to that of master, and you also need to restart MySQL for slave. 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 necessary and unique.

2): slave does not need to turn on binary logging, but in some cases, it must be set, for example, if slave is the master of another slave, bin_log must be set. Here, we turn on the binary log and display the name (the default name is hostname, but there will be a problem if the hostname changes).

3): relay_log configures relay logs, and log_slave_updates indicates that slave writes replication events to its own binary log (you'll see its usefulness later).

Some people open the binary log of slave, but do not set log_slave_updates, and then check to see if the data of slave has changed, which is a misconfiguration.

4): use read_only whenever possible, which prevents data from being changed (except for special threads). But the application read_only that needs to create tables on slave is not very practical.

The configuration also needs to be rebooted to take effect.

2.5.Starting slave

At this point, the next step is to have slave connect to master and start redoing the events in the master binary log. Instead of doing this with a configuration file, you should use the CHANGE MASTER TO statement, which completely replaces changes to the configuration file, and it can specify a different master for the slave without stopping the server. As follows:

Mysql > CHANGE MASTER TO MASTER_HOST=' host IP'

-> MASTER_USER='rep'

-> MASTER_PASSWORD='123456'

-> MASTER_LOG_FILE='mysql-bin.000001',# recovery backup-- master-data=1 does not need to be specified. Automatically specify in backup

-> MASTER_LOG_POS=0;## recovery backup-- master-data=1 does not need to be specified. Automatically specify in backup

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

If it doesn't work-- master-data, you can log in to the database and use show master status for MASTER_LOG_FILE

And MASTER_LOG_POS 's search.

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 see if the slave setting is 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

Indicates that slave has not started the replication process. The location of the log is 4 instead of 0, because 0 is the starting location of the log file, not the log location. In fact, the location of the first event that MySQL knows is 4.

To start copying, you can run:

Mysql > START SLAVE

Run SHOW SLAVE STATUS to view the output:

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

The main purpose here is to see:

Slave_IO_Running=Yes

Slave_SQL_Running=Yes

Both slave's Icano and SQL threads are running, and Seconds_Behind_Master is no longer a NULL. The location of the log has increased, which means that some events have been obtained and executed.

Verification: if you make changes on master, you can see changes in the location of various log files on slave, as well as changes in data in the database.

You can check the status of threads on master and slave. On master, you can see the connections created by slave's I / O thread:

Enter show processlist\ G on 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)

Line 2 is the connection that handles the I / O thread of the slave.

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)

Line 1 is the Imax O thread state, and line 2 is the SQL thread state.

Come from network finishing.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.