Implementation principle and configuration of MySql master-slave replication

Database read-write separation is an essential and important function for large systems or Internet applications with high traffic. For MySQL, the standard read-write separation is the master-slave mode, with a write node Master followed by multiple read nodes. The number of read nodes depends on the system pressure, and is usually configured with 1-3 read nodes. General read-write separation middleware, such as Mycat's read-write separation and automatic switching mechanism, requires the cooperation of MySQL's master-slave replication mechanism.

Things to note about master-slave configuration

1. The versions of the master DB server and slave DB server databases are consistent

2. The database data names of the master DB server and the slave DB server are consistent

3. The master DB server starts binary logging. The server_id of the master DB server and the slave DB server must be unique. MySQL master server configuration

Step 1: Modify the my.conf file:

Add the following under the [mysqld] section:

binlog-ignore-db=mysql

# Enable binary log log-bin=mysql-bin
//There are three formats of binary logs: statement/row/mixed
binlog_format=row
#The unique ID of the main server, usually the last segment of the IP address server-id=82

Step 2: Restart the MySQL service

service mysql restart

Step 3: Create an account and authorize the slave

mysql>GRANT FILE ON *.* TO 'slave'@'%' IDENTIFIED BY 'slavepw';
mysql>GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* to 'slave'@'%' identified by 'slavepw';

Generally, the root account is not used. “%” means that all clients can connect as long as the account and password are correct. The specific client IP address can be used here, such as 192.168.145.226, to enhance security.

Refresh permissions

mysql> FLUSH PRIVILEGES;

Step 4: Query the status of the master

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 | 881 | | mysql | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

MySQL slave server configuration

Step 1: Modify the my.conf file

[mysqld]# Unique ID from the server, usually the last segment of the IP address server-id=83

Step 2: Configure the slave server

mysql>change master to master_host='192.168.11.82',master_port=3306,master_user='slave',master_password='slavepw',master_log_file='mysql-bin.000001',master_log_pos=881;

Be careful not to disconnect the statement in the middle. master_port is the MySQL server port number (without quotes), master_user is the database account that performs the synchronization operation, and "881" has no single quotes (here 881 is the value of position seen in show master status, and here mysql-bin.000001 is the value corresponding to file).

Step 3: Start the replication function from the server

mysql>start slave;

Step 4: Check the status of the slave server replication function:

mysql> show slave status;

Slave_IO_Running: Yes //This status must be YES

Slave_SQL_Running: Yes //This status must be YES

Note: The Slave_IO and Slave_SQL processes must run normally, that is, the YES state, otherwise they are in an error state (for example, if one of them is NO, it is an error).

Verify

Create a table and insert data on the master node, and find that the slave node also creates a table and inserts data.

What is the principle of MySQL master-slave replication?

The master database writes the changes to the binlog log, and then after the slave database connects to the master database, the slave database has an IO thread that copies the master database's binlog log to its local computer and writes it to a relay log. Then, a SQL thread in the slave database will read the binlog from the relay log and execute the contents of the binlog, that is, execute the SQL again locally, so as to ensure that the data is the same as that of the master database.

There is a very important point here, that is, the process of synchronizing the data of the slave database with the master database is serialized, that is, the parallel operations on the master database will be executed serially on the slave database. So this is a very important point. Due to the characteristics of the slave database copying logs from the master database and serially executing SQL, in a high-concurrency scenario, the data in the slave database will definitely be slower than that in the master database, and there will be a delay. Therefore, it often happens that the data just written to the main database may not be readable, and it may take tens or even hundreds of milliseconds to read it.

And there is another problem here. If the master database suddenly goes down and the data has not been synchronized to the slave database, some data may not be available on the slave database, and some data may be lost.

So MySQL actually has two mechanisms in this area. One is semi-synchronous replication, which is used to solve the problem of data loss in the master database; the other is parallel replication, which is used to solve the problem of master-slave synchronization delay.

This so-called semi-synchronous replication, also called semi-sync replication, means that after the master database writes the binlog log, it will be forced to synchronize the data to the slave database immediately. After the slave database writes the log to its local relay log, it will return an ack to the master database. The master database will consider the write operation completed only after receiving at least one ack from the slave database.

The so-called parallel replication means starting multiple threads from the database, reading logs from different databases in the relay log in parallel, and then replaying logs from different databases in parallel. This is database-level parallelism.

MySQL master-slave synchronization delay issue

In the past, we have dealt with online bugs caused by master-slave synchronization delays, which were minor production accidents.

Is this the scene? A classmate wrote the code logic like this. First insert a piece of data, then check it out, and then update the data. During the peak period of the production environment, the write concurrency reached 2000/s. At this time, the master-slave replication delay was about tens of milliseconds. We will find that there is always some data every day, and we expect to update some important data status, but it is not updated during peak hours. Users give feedback to customer service, and customer service will give feedback to us.

We use the MySQL command:

show status

Looking at Seconds_Behind_Master , you can see that the slave database lags behind by several milliseconds in copying the data from the master database.

Generally speaking, if the master-slave delay is serious, there are the following solutions:

Database sharding, splitting a main database into multiple main databases, reduces the write concurrency of each main database by several times, and the master-slave delay can be ignored. Enable parallel replication supported by MySQL, and replicate multiple databases in parallel. If the write concurrency of a certain database is very high, and the write concurrency of a single database reaches 2000/s, parallel replication is still meaningless. Rewrite the code. Students who write code should be cautious. When inserting data, you may not be able to find it immediately. If it does exist and must be inserted first, it must be queried immediately, and then some operations must be performed in reverse immediately. Set a direct connection to the main database for this query. This method is not recommended. If you do this, the meaning of separating reading and writing will be lost. Enable parallel replication

To enable multi-threaded replication, there are two default key parameters:

mysql> show variables like 'slave_parallel_%';
+------------------------+----------+
| Variable_name | Value |
+------------------------+----------+
| slave_parallel_type | DATABASE |
| slave_parallel_workers | 0 |
+------------------------+----------+
2 rows in set (0.00 sec)

The default value of slave-parallel-type is database

slave-parallel-workers The default value is 0

Open:

mysql> stop slave sql_thread;

Query OK, 0 rows affected (0.05 sec)

mysql> set global slave_parallel_type='LOGICAL_CLOCK';

Query OK, 0 rows affected (0.00 sec)

mysql> set global slave_parallel_workers=4;

Query OK, 0 rows affected (0.00 sec)

mysql> start slave sql_thread;

Query OK, 0 rows affected (0.07 sec)

References:

https://www.jianshu.com/p/3932551e0221

https://github.com/doocs/advanced-java/blob/master/docs/high-concurrency/mysql-read-write-separation.md

The above is the full content of this article. I hope it will be helpful for everyone’s study. I also hope that everyone will support 123WORDPRESS.COM.