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

What does mysql master-slave synchronization mean? This problem may be often seen in our daily study or work. I hope you can gain a lot from this question. The following is the reference content that the editor brings to you, let's take a look at it!

Mysql master-slave synchronization means backup, and the master library (Master) synchronizes the writes in its own library to its own slave library (Slave). When some unpredictable conditions occur in the master database, resulting in the failure of the entire server, because there is also a piece of data in the slave database, the data can be quickly restored without causing or reducing data loss.

What is mysql master-slave synchronization?

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

Data is a vital part of an application. For the purpose, master-slave synchronization means a little backup. The master library (Master) synchronizes the writes in its own library to its slave database (Slave). When some unpredictable conditions occur in the master database, resulting in the failure of the entire server, because there is also a piece of data in the slave database, the data can be quickly restored without causing or reducing data loss.

Of course, this is only the first level, and if the role of master and slave libraries is limited to this, then I personally do not think it is necessary to divide the database into two databases. It is only necessary to send the database content to another server as a snapshot on a regular basis, or not to send the write content to another server in real time for each write, which can not only save resources, but also serve the purpose of disaster recovery backup.

Of course, the role of master-slave synchronization can never be limited to this, once we have configured the master-slave structure, we usually do not let the slave node only as a backup database, we should also configure read-write separation accordingly (you can use MyCat or other middleware, you can find out for yourself, about MyCat I will say this in the next blog, it may be a bit long, so write another article).

In the actual environment, the number of read operations to the database is much greater than the write operations to the database, so we can let Master only provide write functions, and then move all read operations to the slave database, which is what we often call read-write separation, which can not only reduce the pressure on Master, but also do disaster recovery backup, killing two birds with one stone.

What are the benefits of master-slave synchronization?

Expand the load capacity of the database horizontally.

Fault tolerance, high availability. Failover (failed switching) / High Availability

Data backup.

The principle of master-slave synchronization in MySQL

After talking about the concept of master-slave synchronization, let's talk about the principle of master-slave synchronization. In fact, the principle is also very simple, not as many concepts as Redis cluster.

In fact, when we configure master and slave in MySQL, as long as we write to the Master node, this operation will be saved to the binary-log (bin-log) log of MySQL. When slave connects to master, the master machine will open binlog dump threads for slave. When the binlog of master changes, the dump thread of Master notifies slave and sends the corresponding binlog content to Slave. When the master-slave synchronization is turned on, the Slave node will create two threads, one is an Imacure O thread and the other is a SQL thread, which we can see with our own eyes in the later building.

IO 0 thread: this thread is linked to the master machine, and when the binlog of the master machine is sent to slave, the IO thread writes the contents of the log in the local relay log (Relay log).

SQL thread: this thread reads the contents of the relay log and acts on the Slave database according to the contents of the relay log.

Possible problems: in the case of quite a large number of write requests, there may be inconsistency between Slave data and Master data, which is due to a short delay in log transmission, or a large number of write commands, and the system speed mismatch.

This is roughly the principle of MySQL master-slave synchronization, in which the real role is actually these two log files, binlog and relay logs.

Manually set up MySQL master-slave synchronization environment preparation

This time, build a master-slave synchronization environment: CentOS 7, MySQL 8.0.18 (installed using binary package).

Scene introduction

This time, master-slave synchronization of MySQL will be built, including one Master and two Slave.

Master:IP: 192.168.43.201 Port:3306Slave1:IP:192.168.43.202 Port:3306Slave2:IP:192.168.43.203 Port:3306 begins to build

Modify the configuration file

After we install MySQL, there will be a my.cnf file in the / etc/ directory. Open the file and add the following (don't forget to make a backup before you modify it):

X

# the configuration server-id=201 # Server id must be different for each MySQL # indicates that binlog log expire _ logs_days=10 # log expiration time max_binlog_size=200M # log maximum capacity binlog_ignore_db=mysql # ignore the mysql library, indicating that the library is not synchronized

Y

# this configuration is the configuration of Slave, and the second Slave is also configured in the same way, but modify the cache time of server-idserver-id=202expire_logs_days=10 # log max_binlog_size=200M # maximum size of log replicate _ ignore_db=mysql # ignore the synchronized database

New Slave users

Open the client of the Master node with the mysql-u root-p password

Create user create user 'Slave'@'%' identified by' 123456'

Empower newly created users: grant replication slave on'*. *'to 'Slave'@'%'

View Master node status

After all the above operations are all right, we enter show master status in the client to view the binlog log of master.

Configure two Slave nodes

Open two Slave node clients and enter the following command in our other two Slave nodes:

Change master to master_user='Slave',master_password='123456',master_host='192.168.43.201',master_log_file='mysql-bin.000005',master_log_pos=155,get_master_public_key=1;# note that the master_log_file here is the file name of the binlog. Enter the mysql-bin.000005 in the figure above, and everyone's may be different. # Note that the master_log_pos here is the binlog offset. Enter 155in the figure above, and everyone's may be different.

When the configuration is complete, type start slave; to enable the slave node, and then enter show slave status\ G to view the slave node status

As you can see, in the state of the two Slave, we can see with our own eyes the running status of IO thread and SQL thread. Both threads must be yes before the configuration is completed.

The construction is complete

Through the above steps, the master-slave synchronization of MySQL is completed, and the configuration of MySQL is quite simple compared with Redis. Now we can test it.

First take a look at the database status of the three MySQL: SHOW DATABASES

You can see that the databases are now in the initial default state, and there are no additional libraries.

Create a database in the Master node, and the library name can be set by yourself.

CREATE DATABASE testcluster

As you can see, the database created in Master also appears in Slave, indicating that there is nothing wrong with our configuration, and the master and slave are successfully built. Instead of creating tables here, you can try for yourself, create tables and insert data into the tables, and there will be no problem.

Matters needing attention

If the IO thread is in the Connecting state all the time, you can see if the three machines cannot connect to each other. If you can connect to each other, it is possible that the password of the Slave account has been misspelled. Just turn off Slave again and enter the above configuration command to open Slave.

If the SQL thread is in the NO state, it may be caused by the data inconsistency between the slave database and the master database, or the transaction rollback. If it is the latter, close Slave first, then check master's binlog and position first, then enter the configuration command, and then enter set GLOBAL SQL_SLAVE_SKIP_COUNTER=1;, before re-start slave. Then, if the former is adopted, check whether there is a table that is not synchronized, whether there is a table that exists in the master database but not in the slave database, and then synchronize it and reconfigure it again.

Conclusion

Before writing this article, I was frightened by some nouns in the computer field. I believe many students have the same experience. When they encounter some high-end nouns, such as "distributed", "cluster" and so on, even before they come into contact with nginx, even words such as "load balance" and "reverse agent" make people think that it must be very difficult for such high-end words. But in fact, after learning about nginx, ribbon, etc., I found that it was just like that, and it was not as difficult as I imagined.

Therefore, the original intention of writing this article is not to make people feel deterred from clustering or distributed or other technologies or solutions (it feels that words in the computer field have such a characteristic, the vocabulary is high-end, but in fact, the idea is relatively easy to understand), in fact, it is not that difficult to manually configure a simple cluster.

It will be easier to configure after learning docker, but I hope it is not limited to configuration. It can only be said that you can configure something, but under this layer of configuration, our predecessors have done quite a lot of work to enable us to achieve some functions through simple configuration. We should go deep into the bottom and understand the working principle of the configuration below. This is the most important thing. It is also a place that reflects the level of a programmer.

Thank you for reading! After reading the above, do you have a general understanding of what mysql master-slave synchronization refers to? I hope the content of the article will be helpful to all of you. If you want to know more about the relevant articles, you are welcome to follow the industry information channel.

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.