Shulou(Shulou.com)05/31 Report--

This article mainly introduces how to configure master-slave replication in mysql, which has a certain reference value, and interested friends can refer to it. I hope you will gain a lot after reading this article.

For the current system, in the system with complex business, the database is often the bottleneck of the application, and the stand-alone is often unable to withstand the concurrent pressure of the large-scale system, so it is necessary to solve the bottleneck from the database, for example, the sql statement needs to lock the table, resulting in temporarily unable to use the read service, then it will affect the running business. After using the master and slave, the read operation of the slave library is not affected.

First, why do you need master-slave replication?

1. Use master-slave replication, and let the master database be responsible for writing and the slave database for reading. In this way, even if the master database locks the table, the read-slave database can ensure the normal operation of the business.

2, the extension of the architecture. Due to the increasing volume of business, the access frequency of Iamp O is too high to be satisfied by a single machine. At this time, the storage of multiple databases can be done to reduce the access frequency of disk Iamp O and improve the performance of single machine.

3, master and slave multiple servers, can also be used as data backup.

What is the master-slave replication of mysql?

MySQL master-slave replication means that data can be replicated from one MySQL database server master node to one or more slave nodes. MySQL uses asynchronous replication by default, so that the slave node does not have to access the master server all the time to update its own data. The data update can be carried out on the remote connection, and the slave node can copy all databases or specific databases or tables in the master database.

Third, the principle of mysql replication

(1) the master server records the changes of the data in the binary binlog log. When the data on the master changes, the changes are written to the binary log.

(2) the slave server will detect whether the master binary log has changed within a certain time interval, and if so, start an I Thread request master binary event.

(3) at the same time, the master node starts a dump thread for each I/OThread O thread, which is used to send binary events to it and save it to the local relay log of the slave node. The slave node will start SQL thread to read the binary log from the relay log and replay it locally, so that its data is consistent with that of the master node. Finally SQL and SQL will go to sleep and wait for the next time to wake up.

That is to say, two threads will be generated from the library, one is the Icano thread, and the other is the SQL thread; the IWeiO thread will request the binlog of the master database and write the resulting binlog to the local relay-log (Relay Log) file; the master library will generate a log dump thread, which will be used to pass the binlog;SQL thread to the slave Icano thread, read the log in the relay log file, and parse it into sql statements one by one.

Note:

Master records the operation statement in the binlog log, and then grants slave permission to connect remotely (master must enable binlog binary log function; usually for data security reasons, slave also enables binlog feature).

Slave starts two threads: the IO thread and the SQL thread. The IO thread is responsible for reading the binlog content of the master to the relay log relay log; the SQL thread is responsible for reading the binlog content from the relay log log and updating it to the slave database, so as to ensure that the slave data is consistent with the master data.

Mysql replication requires at least two Mysql services. Of course, Mysql services can be distributed on different servers, or multiple services can be started on one server.

Mysql replication is best to ensure that the version of Mysql on the master and slave servers is the same (if the version cannot be met, then the version of the master master node is lower than that of the slave slave node)

The time between master and slave nodes needs to be synchronized.

The figure is from "High performance mysql".

Specific steps:

1. The slave library connects to the master database by executing change master to statements manually, providing all the conditions of the connected users (user, password, port, ip), and letting the slave library know the starting position of the binary log (file name position number); start slave

2. Establish a connection between the IO thread of the slave library and the dump thread of the master library.

3. According to the file name and position number provided by the change master to statement, the IO thread initiates the binlog request to the master database.

4. The master library dump thread sends the local binlog to the slave library IO thread in the form of events according to the request of the slave library.

5. Receive the binlog events from the library IO thread and store it in the local relay-log, and the transmitted information will be recorded in the master.info.

6. Apply relay-log from the SQL thread of the library, and record the applied relay to relay-log.info. By default, the applied relay will be cleaned automatically.

Fourth, mysql master-slave synchronization delay analysis.

The master-slave replication of mysql is a single-thread operation, and the master library writes all the logs generated by DDL and DML into binlog. Because binlog is written sequentially, it is very efficient. The sql thread thread of slave replays the DDL and DML operation events of the master library in slave. The IO operations of DML and DDL are random, not sequential, so the cost is much higher. On the other hand, because sql thread is also single-threaded, when the concurrency of the main library is high, the number of DML generated exceeds the speed that slave's SQL thread can handle, or when a large query statement in slave produces lock waiting, then the delay occurs.

Solution:

1. The implementation of the persistence layer of the business adopts a sub-library architecture, and mysql services can be expanded in parallel to disperse the pressure.

two。 A single library is separated from reading and writing, with one master and multiple followers, and the main writer is read from each other, dispersing the pressure. In this way, the pressure of the slave library is higher than that of the master library, which protects the master library.

3. The infrastructure of the service adds the cache layer of memcache or redis between the business and the mysql. Reduce the reading pressure of mysql.

4. The mysql of different businesses is physically placed on different machines to disperse the pressure.

5. Using a better hardware device than the main library as a slave,mysql is less stressful, and the latency is naturally smaller.

6. Use more powerful hardware.

After mysql5.7, we use MTS parallel replication technology to permanently solve the problem of replication delay. I'll talk about it later.

V. configuration of master-slave replication

1. Basic setup preparation

The version of this test mysql is 5. 7. Relatively poor, two mysql instances are installed on one machine, and you can modify different ports. Of course, it is also possible to install two mysql if you have the money to prepare two machines that can access each other.

In the test phase, the two mysql instances IP are both native (ip=127.0.0.1). Under the distinction, the master is node1 and the slave is node2, and the ports are different. I used 3306 and 3307 for actual testing.)

2. Install mysql database

For many examples on the Internet, I will not repeat them here. Please use Baidu / google (the result is that the database can be used normally). After the two mysql are completed, we will begin to configure master-slave replication.

3. Create a database in two databases respectively

Note that both of them must be executed, and the databases of both must keep the same create database test.

4. Configure the main (node1) server as follows:

# modify the configuration file, execute the following command to open the mysql configuration file vi / etc/my.cnf#, add the following configuration information to the mysqld module: log-bin=master-bin # binary file name # binary log format, there are row, statement, mixed three formats, binlog-format=ROW server-id=1 # requires that the id of each server must be different binlog-do-db=test # synchronized database name

The binary log format has three formats: row, statement, and mixed; row refers to copying the changed content, rather than executing the command on the slave server; statement refers to the SQL statement executed on the master server, which executes the same statement on the slave server. MySQL uses statement-based replication by default, which is more efficient; mixed refers to statement-based replication by default, and row-based replication is used once it is found that statement-based replication cannot be accurately replicated.

5. Configure the account authorization to log in to the master server from the (node2) server

-- authorize the operation of set global validate_password_policy=0;set global validate_password_length=1;grant replication slave on *. * to 'root'@'%' identified by' 123456-refresh permission flush privileges

6. Configuration of slave (node2) server

# modify the configuration file, execute the following command to open the mysql configuration file vi / etc/my.cnf# and add the following configuration information to the mysqld module: log-bin=master-bin # name of the binary file binlog-format=ROW # format of the binary file server-id=2 # id of the server

7. Restart the mysqld service of the master server

# restart mysql Service service mysqld restart# Log in to mysql Database mysql-uroot-p # View the status of master show master status

8. Restart the slave server and configure it

# restart mysql service service mysqld restart# login mysqlmysql-uroot-p# connect to the master server (master_host is the primary IP address, I test locally) change master to master_host='127.0.0.1',master_user='root',master_password='123456',master_port=3306,master_log_file='master-bin.000001',master_log_pos=154;# launch slavestart slave# to check the status of slave show slave status\ G # (note that there is no semicolon)

At this point, the configuration of the master and slave has been completed. At this time, you can add and delete the relevant data on the master server, and check the relevant status from the server to see if the corresponding data has changed.

Thank you for reading this article carefully. I hope the article "how to configure Master-Slave replication in mysql" shared by the editor will be helpful to everyone. At the same time, I also hope you will support us and pay attention to the industry information channel. More related knowledge is waiting for you to learn!

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.