Master slave synchronization settings of MySQL database cluster series

Time:2020-10-10

  

  Recently, I have been sorting out the related operations of database cluster. Now I will take some time to sort out the operation summary of MySQL database cluster. It happens that you are looking at this part again for reference. The end of this series includes the following contents: multi database installation, MYCAT deployment and installation, separation of read and write of database, master-slave replication of database, double master and multiple database, and database sub database and table. Each point may correspond to one or more articles. As you have to continue to work, this series of sharing is expected to last about 10 days. If you are interested, you can continue to pay attention to it. I am a rookie, if the writing is not good, hope to give more advice and forgiveness.

OK, let’s go straight to the topic of this time: there are many ways of data clustering in MYCAT, such as master-slave replication, multiple master-slave replication, and so on. Let’s start with the simplest master-slave replication.

Master slave replication, in fact, I also collated an article some time ago, which is in the Linux environment settings, interested can understand https://www.cnblogs.com/xiaoXuZhi/p/xyh_ mysqlsynchro.html 。 In fact, the master-slave settings of Linux and windows MySQL are essentially the same, but slightly different. This time, it is mainly aimed at a complete process construction of Windows environment, so we are also sorting out the master-slave replication.

1、 Introduction to master slave replication of MySQL

Principle:

  • The master server records the changes of data in the binary binlog log log. When the data on the master server changes, the changes are written to the binary log;

  • The slave server will detect whether the master binary log has changed within a certain time interval. If it changes, it will start an I / othread request master binary event

  • At the same time, the master node starts a dump thread for each I / O thread, which is used to send binary events to it and save them to the local relay log of the slave node. The slave node will start the SQL thread to read the binary log from the relay log and replay it locally to make its data consistent with that of the master node. Finally, I / othread and sqlthread will enter sleep state and wait for the next wake-up 。

  • The slave library will generate two threads, one I / O thread and one SQL thread;

  • The I / O thread will request the binlog of the main database and write the resulting binlog to the local relay log file;

  • The master database will generate a log dump thread to transfer binlog to the I / O thread of the slave library;

  • The SQL thread will read the log in the relay log file and parse it into SQL statements and execute them one by one;

summary:

  • The slave library will generate two threads, one I / O thread and one SQL thread;

  • The I / O thread will request the binlog of the main database and write the resulting binlog to the local relay log file;

  • The master database will generate a log dump thread to transfer binlog to the I / O thread of the slave library;

  • The SQL thread will read the log in the relay log file and parse it into SQL statements and execute them one by one;

2、 Preliminary configuration file preparation

Server description:

I have started several database instances in this machine, and each instance is as follows:

  • Main database: 192.168.1.88:3306

  • ? from database 1:192.168.1.88:3307

  • From database 2: 192.168.1.88:3308

Through the previous introduction of master-slave replication, we know that master-slave replication actually interacts through log files. How to open the specific log files? How to set the specific synchronization policy? The following will be a brief introduction, step by step.

The configuration files of master-slave synchronization are in the directory of database installation my.ini (liux is my.cnf )File, which was also mentioned in the previous article about MySQL multi instance installation. This time, you can directly add the configuration above. The specific configuration file is as follows. The specific configuration parameter values can be modified according to actual needs

In 3306 of the main database my.ini The configuration file is as follows:

 

[Client]
port = 3306
[mysqld]
#Set 3306 port
port = 3306
#Set MySQL installation directory
basedir=C:\Program Files (x86)\MySQL\MySQL Server 5.7
#Set the storage directory of MySQL database data
datadir=C:\Program Files (x86)\MySQL\MySQL Server 5.7\data
#Maximum number of connections allowed
max_connections=200
#The character set used by the server is the 8-bit Latin1 character set by default
character-set-server=utf8
#The default storage engine that will be used when creating a new table
default-storage-engine=INNODB
#Master slave replication settings
#Set the service ID and pay attention to keep the ID value unique in the same database cluster
server-id = 3306
#Write disk policy. The valid values of this parameter are 0, 1, 2
#0: the redo log buffer is not written to disk when the transaction is committed. Therefore, if MySQL goes down, some transactions may be lost.
#1: when the transaction is committed, the redo log buffer is written to disk and flushed immediately (fsync()). Ensure that it is persistent to the hard disk.
#2: when the transaction is committed, redo log buffer will be written to disk, but the refresh operation will not be performed immediately. At this time, if the operating system goes down, some data may be lost.
#As you can see, only 1 can really guarantee the transaction persistence, but because the refresh operation fsync() is blocked, the performance will be significantly reduced. If you don't care about transaction loss, 0 and 2 can get better performance.
#The default value is 1. In the actual use process, for the sake of efficiency, we usually set it to 2
innodb_flush_log_at_trx_commit=2  
#Enable binlog log log synchronization function
#This parameter directly affects the performance and integrity of MySQL.
# sync_ Binlog = 0. After the transaction is committed, MySQL only changes the binlog_ The data in the cache is written to the binlog file, but fsync and other disks are not executed. The synchronization instruction informs the file system to refresh the cache to the disk, while the filesystem decides when to synchronize. This is the best performance.
# sync_ Binlog = n, after n transactions are committed, MySQL will execute a disk synchronization instruction such as fsync to inform the file system to refresh the binlog file cache to the disk.
#The default setting in MySQL is sync_ Binlog = 0, that is, do not do any mandatory disk refresh instructions. At this time, the performance is the best, but the risk is also the greatest. Once the system crashes, all binlog information in the file system cache is lost.
#It can be set flexibly according to the data requirements of the project
sync_binlog=0
#Binary log retention days
expire_logs_days=180
#Binlog log log file name (can be arbitrarily named)
log-bin=mysql-bin

#There are two ways to set up a synchronous database
# binlog_ Do_ DB: set the database to be synchronized
#Binlog ignore DB: setting up databases that do not need to be synchronized
#Only one of them needs to be set. According to our usual project situation, binlog ignore DB is set to exclude some system level data that does not need to be synchronized
#This setting has an advantage: if the business database is newly added, it does not need to update the configuration again
#The two setting modes can have multiple data
#If the database is synchronized, other databases are not synchronized (choose one from binlog ignore dB below)
# binlog_do_db=testdb 
#Out of sync database, all other
binlog-ignore-db = information_schema
binlog-ignore-db = mysql
binlog-ignore-db = performance_schema
binlog-ignore-db = sys
 
[mysql]
#Setting the default character set of MySQL client
default-character-set=utf8

 

 

From the database my.ini The configuration is very simple. You only need to configure server ID, binlog do dB and binlog ignore dB.

The server ID must be configured

Binlog do dB and binlog ignore DB can be set according to actual needs. If the master database is synchronized to the slave database, the two values are unnecessary to be set. If the slave only synchronizes some databases of the master, it needs to be set. In the data project, most of them do not need to be set.

 

From the database’s 3307 my.ini The configuration file is as follows:

 

[Client]
port = 3307
 
[mysqld]
#Set 3307 port
port = 3307
server-id = 3307
#Set MySQL installation directory
basedir=C:\Program Files (x86)\MySQL\MySQL Server 5.7.2
#Set the storage directory of MySQL database data
datadir=C:\Program Files (x86)\MySQL\MySQL Server 5.7.2\data
#Maximum number of connections allowed
max_connections=200
#The character set used by the server is the 8-bit Latin1 character set by default
character-set-server=utf8
#The default storage engine that will be used when creating a new table
default-storage-engine=INNODB
 
[mysql]
#Setting the default character set of MySQL client
default-character-set=utf8

 

From the database’s 3308 my.ini The configuration file is as follows:

[Client]
port = 3308

[mysqld]
#Set 3308 port
port = 3308
server-id = 3308
#Set MySQL installation directory
basedir=C:\Program Files (x86)\MySQL\MySQL Server 5.7.2
#Set the storage directory of MySQL database data
datadir=C:\Program Files (x86)\MySQL\MySQL Server 5.7.2\data
#Maximum number of connections allowed
max_connections=200
#The character set used by the server is the 8-bit Latin1 character set by default
character-set-server=utf8
#The default storage engine that will be used when creating a new table
default-storage-engine=INNODB
 
[mysql]
#Setting the default character set of MySQL client
default-character-set=utf8

 

3、 Create synchronization account in master database

Before processing, since the configuration file of the database has just been set, in order to take effect, the first step is to restart the main database. You only need to execute the following two commands:

  net stop mysql

  net start mysql

 

Create an account for master data users to use synchronously:

  • Login database: MySQL – uroot – pxuyuanhong – P 3306

  • To create a data synchronization user slavedata:

Create user ‘slavedata’ @ ‘%’ identified by ‘user login password’;

  • Set permissions:

Grant replication slave on *. * to ‘slavedata’ @ ‘%’ identified by ‘user login password’;

  • Refresh permissions: flush privileges;
  • View the host status: show master status;

 

4、 Synchronize settings from database

Precondition: set synchronization from the database. Take database 3307 as an example to illustrate:

Before setting slave data, it is still the same as master data setting, and restart it first. You only need to execute the following two commands:

   net stop mysql3307

  net start mysql3307

Login from database:

  mysql -uroot -pxuyuanhong -P 3307

Execute the manual synchronization command:

  CHANGE MASTER TO MASTER_HOST=’192.168.3.205′,MASTER_PORT=3306,MASTER_USER=’slaveData’,MASTER_PASSWORD=’xuyuanhong’,MASTER_LOG_FILE=’mysql-bin.000001′,MASTER_LOG_POS=844;

 

The parameter values are as follows:

  • master_ Host: IP address of main database

  • master_ User: primary database connection user name

  • master_ Password: master data connection password

  • master_ Port: primary data port

  • master_ log_ File: log name of the primary database to start synchronization, and show master status of the primary database

  • Flie column data of the queue

  • master_ log_ Pos: the starting position of the primary database log to start synchronization, and the master database shows master status

  • Position column data of the queue

Start master slave synchronization:

  start slave;

View master slave synchronization status

show slave status\G;

The result of master-slave synchronization status is as follows: if slave_ IO_ Running: Yes,Slave_ SQL_ Running: Yes indicates that the two threads have been started and the master-slave replication configuration is successful.

 

 

How to handle the failure of master slave synchronization setting

Result slave of master-slave state_ IO_ Running: Yes,Slave_ SQL_ If running is not true, you need to reset it according to the prompt result. The process of resetting is as follows:

  • Stop Slave: stop slave;

  • Reset Slave: reset slave;

  • Repeat the above: execute the manual synchronization command to start the process

5、 After SQL synchronization fails, it will not be synchronized

In the actual use process, if the master-slave synchronization is not handled well, it may lead to synchronization failure. For example, the slave database is also doing new operations. If there is an auto increment primary key constraint, it may lead to synchronization failure. Because the ultimate goal of master-slave synchronization is to achieve data consistency, when a certain item fails to synchronize, post synchronization will not be performed. So what should I do if there is a synchronization failure?

In the actual processing process, there are probably two processing methods:

1. The difference between master and slave data is small, and regardless of the consistency of the final data, you can set the log that directly skips the synchronization travel and continues to synchronize the subsequent data. The specific operation commands are as follows:

Step 1: stop master slave synchronization

  stop slave;
Step 2: skip the wrong log location and continue the subsequent synchronization. The following numbers can be changed

  set global
sql_slave_skip_counter =1;

Step 3: continue to turn on master-slave synchronization

  start slave;

2. If the master-slave data differ greatly, and the master-slave data are required to be completely consistent, then it is necessary to do the master-slave again and fully synchronize.

Step 1: delete the slave database and backup the master database to the slave database (ensure that the master and slave data are completely consistent)

Step 2: according to the above process, reconfigure the master-slave synchronization

6、 Some self summary

 1. Master slave synchronous database, must pay attention to read-write separation. Read from the database, do not do write operations, it is best to directly only give read from the database permissions, not to write permissions

2. When setting master-slave synchronization account in database, it is better to create a special account for master-slave synchronization

The master-slave synchronization of MySQL is shared here. The next article will share myc with youAt’s multi installation configuration, master-slave synchronization, read-write separation and other knowledge points, interested partners, can continue to pay attention, thank you!

END
In order to communicate more, you are welcome to pay attention to my official account.

Recommended Today

PHP 12th week function learning record

sha1() effect sha1()Function to evaluate the value of a stringSHA-1Hash. usage sha1(string,raw) case <?php $str = “Hello”; echo sha1($str); ?> result f7ff9e8b7bb2e09b70935a5d785e0cc5d9d0abf0 sha1_file() effect sha1_file()Function calculation fileSHA-1Hash. usage sha1_file(file,raw) case <?php $filename = “test.txt”; $sha1file = sha1_file($filename); echo $sha1file; ?> result aaf4c61ddcc5e8a2dabede0f3b482cd9aea9434d similar_text() effect similar_text()Function to calculate the similarity between two strings. usage similar_text(string1,string2,percent) case […]