MySQL master-slave replication, read-write separation theoretical analysis + practical demonstration

1. Principle

• Read write separation is to write only on the master server and read only on the slave server
• The basic principle is to let the master database handle transactional queries and the slave database handle select queries. Database replication is used to synchronize changes caused by transactional queries on the master database to the slave database in the cluster

Why separate reading and writing?
Because it may take 3 minutes to write 10000 data in the database, the operation is time-consuming, but it may only take 5 seconds to read 10000 data in the database, so the reading and writing are separated. The solution is that the writing of the database affects the efficiency of query.

• The process of read-write separation is shown in the following figure
  

2. Read write separation

• The database does not have to be read-write separated. If the program uses more databases, less updates and more queries, it will be considered.
• The use of database master-slave synchronization, and then through read-write separation, can share the pressure of the database and improve the performance

3. Implementation mode

• There are two types of read-write separation:

3.1 internal implementation based on program code

• Routing is classified according to select and insert in the code. This kind of method is also the most widely used in production environment at present.
• The advantage is better performance, because it is implemented in program code, and there is no need to add additional equipment for hardware expenditure; The disadvantage is that it needs developers to implement it, and the operation and maintenance personnel have no way to start.
• However, not all applications are suitable for realizing read-write separation in program code. For example, some large and complex Java applications, if reading-write separation is realized in program code, the code will be greatly changed.

3.2 implementation based on intermediate agent layer

The proxy is generally located between the client and the server. After receiving the client request, the proxy server forwards it to the back-end database through judgment. There are the following representative programs:

• MySQL proxy: MySQL proxy is an open source project of MySQL. It uses its own lua script to judge the SOL.
• Atlas: it is a data middle tier project based on MySQL protocol developed and maintained by the infrastructure team of Qihoo 360's Web Platform Department. It optimizes mysql-proxy0.8.2 and adds some new features. The MySQL service run by atlas in 360 carries billions of read and write requests every day. Support things and stored procedures.
• Amoeba: developed by Chen Siru. The author once worked for Alibaba. The program is developed by Jaya language and Alibaba uses it in the production environment. However, it does not support transactions and stored procedures.

Because a large number of Lua scripts need to be written to use MySQL Proxy, these Luas are not ready-made, but need to be written by themselves. This is very difficult for people who are not familiar with MySQL Proxy built-in variables and MySQL Protocol.
Amoeba is a very easy to use and portable software. Therefore, it is widely used in the agent layer of database in production environment.

1. Server configuration

2. Environmental preparation

#Turn off firewall and security enhancement system systemctl stop firewalld && systemctl disable firewalld setenforce 0 setenforce: SELinux is disabled

3. mysql master-slave server time synchronization

• Main server: 192.168.8.14

[root@master ~]#yum install -y ntp [root@master ~]#vim /etc/ntp.conf #Add the following at the end of the line server 127.127.8.0 #Set the local clock source. Pay attention to modifying your own network segment fudge 127.127.8.0 stratum 8 #Set the time level to 8 (limited to 15) [root@master ~]#systemctl start ntpd

• slave1 : 192.168.8.16

[root@slave1 ~]#yum install -y ntp ntpdate [root@slave1 ~]#systemctl start ntpd [root@slave1 ~]#/usr/sbin/ntpdate 192.168.8.14 #time synchronization 1 Nov 11:16:58 ntpdate[31306]: the NTP socket is in use, exiting [root@slave1 ~]#crontab -e #Set scheduled tasks */30 * * * * /usr/sbin/ntpdate 192.168.8.14

• slave2 : 192.168.8.15

[root@slave2 ~]#yum install -y ntp ntpdate [root@slave2 ~]#service ntpd start [root@slave2 ~]#crontab -e */30 * * * * /usr/sbin/ntpdate 192.168.8.14

4. mysql configuration of master server

[root@master ~]#vim /etc/my.cnf #Add the following configuration server-id = 1 #Define the server ID. each host cannot be the same log-bin=master-bin #The primary server turns on the binary log binlog_format = MIXED #MIXED mode is used this time log-slave-updates=true #Allow binary logs to be updated from the server systemctl restart mysqld.service #Restart service #Set the slave server account and authorize mysql -uroot -p123456 #Authorize slave servers mysql> GRANT REPLICATION SLAVE ON *.* TO 'myslave'@'192.168.8.%' IDENTIFIED BY '123456'; mysql> flush privileges; mysql> use mysql; mysql> select user,host,authentication_string from user; +-----------+-------------+-------------------------------------------+ | user | host | authentication_string | +-----------+-------------+-------------------------------------------+ | root | localhost | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | | mysql.sys | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | | myslave | 192.168.8.% | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | +-----------+-------------+-------------------------------------------+ 3 rows in set (0.00 sec) mysql> show master status; +-------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +-------------------+----------+--------------+------------------+-------------------+ | master-bin.000001 | 862 | | | | +-------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec) #The File column displays the log name and the Position column displays the offset

5. mysql configuration from the server

5.1 slave1 configuration

vim /etc/my.cnf server-id = 2 #Note that the id cannot be the same as other hosts relay-log=relay-log-bin #Turn on the relay log and synchronize the log file records from the primary server to the local server relay-log-index=slave-relay-bin.index #Define the location and name of the relay log file relay_log_recovery = 1 #Options #After the slave goes down from the database, if the relay log is damaged, resulting in some relay logs not being processed, all unexecuted relay logs will be automatically discarded, And re from master Get logs on the, which ensures relay-log Integrity of. This function is off by default and will relay_log_recovery When the value of is set to 1, you can slave It is recommended to enable this function from the library. systemctl restart mysqld [root@slave1 ~]#mysql -uroot -p123456 #To configure synchronization, pay attention to the master_log_file and Master_ log_ The value of POS should be consistent with that of Master query mysql> CHANGE master to master_host='192.168.10.8.14',master_user='myslave',master_password='123456',master_log_file='master-bin.000001',master_log_pos=603; mysql> start slave; #Start synchronization, and execute reset slave in case of error; mysql> show slave status\G; #Check the Slave status and ensure that both IO and SQL threads are Yes, which means that the synchronization is normal

5.2 slave2 configuration

vim /etc/my.cnf server-id = 3 #Note that it cannot be the same as master and slave1 relay-log=relay-log-bin relay-log-index=slave-relay-bin.index relay_log_recovery = 1 systemctl restart mysqld [root@slave2 ~]#mysql -uroot -p123456 mysql> CHANGE master to master_host='192.168.8.14',master_user='myslave',master_password='123456',master_log_file='master-bin.000001',master_log_pos=603; mysql> start slave; mysql> show slave status\G;

5.3 general slave_ IO_ Running: possibility of no

• The network is blocked
• There is a problem with my.cnf configuration
• Incorrect password, file name, pos offset
• The firewall is not turned off

6. Verify master-slave replication

#master server mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | | test | | test1 | +--------------------+ 6 rows in set (0.00 sec) mysql> create database test; Query OK, 1 row affected (0.00 sec) mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | | test | +--------------------+ 7 rows in set (0.00 sec)

#slave1 mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | test | | mysql | | performance_schema | | sys | +--------------------+ 5 rows in set (0.00 sec) ----------------------------------------------------------------------------------------------- #slave2 mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | test | | mysql | | performance_schema | | sys | +--------------------+ 5 rows in set (0.00 sec)

1. amoeba server installation configuration

• Installing the Java environment

Because amoeba is developed based on jdk1.5, it is officially recommended to use jdk1.5 or 1.6, and the higher version is not recommended.

[root@localhost ~]#cd /opt/ [root@localhost /opt]#ls amoeba-mysql-binary-2.2.0.tar.gz jdk-6u14-linux-x64.bin rh cp jdk-6u14-linux-x64.bin /usr/local/ cd /usr/local/ chmod +x jdk-6u14-linux-x64.bin ./jdk-6u14-linux-x64.bin #Press and hold Enter, prompt for YES, wait for completion and Enter mv jdk1.6.0_14/ /usr/local/jdk1.6 #Edit the global configuration file and add the following configuration on the last line vim /etc/profile export JAVA_HOME=/usr/local/jdk1.6 export CLASSPATH=$CLASSPATH:$JAVA_HOME/lib:$JAVA_HOME/jre/lib export PATH=$JAVA_HOME/lib:$JAVA_HOME/jre/bin/:$PATH:$HOME/bin export AMOEBA_HOME=/usr/local/amoeba export PATH=$PATH:$AMOEBA_HOME/bin #Output defines the working directory of Java #Output the specified java type #Add java path environment variable #Output definition amoeba working directory #Add path environment variable [root@localhost /usr/local]#source /etc/profile #Execute the modified global profile [root@localhost /usr/local]#java -version #Check the java version information to see if the installation was successful java version "1.6.0_14" Java(TM) SE Runtime Environment (build 1.6.0_14-b08) Java HotSpot(TM) 64-Bit Server VM (build 14.0-b16, mixed mode)

• Install Amoeba

[root@localhost /usr/local]#mkdir /usr/local/amoeba [root@localhost /usr/local]#tar zxvf /opt/amoeba-mysql-binary-2.2.0.tar.gz -C /usr/local/amoeba/ [root@localhost /usr/local]#chmod -R 755 /usr/local/amoeba/ #Give permissions to the directory [root@localhost /usr/local]#/usr/local/amoeba/bin/amoeba #Open amoeba amoeba start|stop #The prompt stop indicates that the installation is successful

2. Configure amoeba read / write separation and load balancing between two slave reads

• Open permissions to amoeba on mysql of master, slave1 and slave2

mysql> grant all on *.* to test@'192.168.8.%' identified by '123456'; mysql> select user,host,authentication_string from mysql.user;

• Amoeba server configuration amoeba service

#Backup first cd /usr/local/amoeba/conf/ cp amoeba.xml amoeba.xml.bak cp dbServers.xml dbServers.xml.bak vim /usr/local/amoeba/conf/amoeba.xml #Modify amoeba configuration file #--30 lines-- <property name="user">amoeba</property> #--32 lines-- <property name="password">123456</property> #--115 lines-- <property name="defaultPool">master</property> #--Lines 117 - 120, uncomment and modify <property name="writePool">master</property> <property name="readPool">slaves</property> ...... ------------------------------------------------------------------------------------------- vim /usr/local/amoeba/conf/dbServers.xml #Modify database configuration file #--Line 23 -- comment out the function: enter the test library by default to prevent an error when there is no test library in mysql <!-- <property name="schema">test</property> --> #--26 -- modify and use the previously created authorized user <property name="user">test</property> #--29 -- remove the comment, and the password is the authorized user password created previously <property name="password">123456</property> #--45 -- modify and set the master name of the master server <dbServer name="master" parent="abstractServer"> #--48 -- modify and set the address of the master server <property name="ipAddress">192.168.10.20</property> #--52 -- modify and set slave server name slave1 <dbServer name="slave1" parent="abstractServer"> #--55 -- modify and set the address of slave server 1 <property name="ipAddress">192.168.10.30</property> #--58 -- copy and paste the above six lines, and set the slave 2 name and address from server 2 <dbServer name="slave2" parent="abstractServer"> <property name="ipAddress">192.168.10.40</property> #--Line 65 -- modify <dbServer name="slaves" virtual="true"> #--Line 71 -- modify <property name="poolNames">slave1,slave2</property> ...... /usr/local/amoeba/bin/amoeba start & #Start amoeba software and press ctrl+c to return netstat -anpt | grep java #Check whether port 8066 is enabled. The default port is TCP 8066 tcp6 0 0 127.0.0.1:61721 :::* LISTEN 4346/java tcp6 0 0 :::8066 :::* LISTEN 4346/java tcp6 0 0 192.168.8.17:58290 192.168.8.15:3306 ESTABLISHED 4346/java tcp6 0 0 192.168.8.17:58300 192.168.8.14:3306 ESTABLISHED 4346/java tcp6 0 0 192.168.8.17:56808 192.168.8.16:3306 ESTABLISHED 4346/java

3. Client test read / write separation

client: 192.168.8.13, MySQL service installed

• client

[root@client ~]#mysql -uamoeba -p123456 -h 192.168.8.17 -P8066 mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | ggll | | mysql | | performance_schema | | sys | | test | | test1 | +--------------------+ mysql> use test; mysql> create table test(id int,name char(20)); mysql> show tables; +----------------+ | Tables_in_test | +----------------+ | test | +----------------+ 1 row in set (0.00 sec)

• master server

mysql> use test; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> show tables; +----------------+ | Tables_in_test | +----------------+ | test | +----------------+ 1 row in set (0.00 sec)

• slave 1 server

mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | | test | +--------------------+ mysql> use test; mysql> show tables; +----------------+ | Tables_in_test | +----------------+ | test | +----------------+ 1 row in set (0.00 sec)

• slave 2 server

mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | | test | +--------------------+ mysql> use test; mysql> show tables; +----------------+ | Tables_in_test | +----------------+ | test | +----------------+

You can see the changes made in the client, which can be seen on all servers.

4. Insert data in the master-slave server after turning off the slave function

• slave 1 server

mysql> stop slave; Query OK, 0 rows affected (0.00 sec) mysql> insert into test values(1,'slave1'); Query OK, 1 row affected (0.01 sec) mysql> select * from test; +------+--------+ | id | name | +------+--------+ | 1 | slave1 | +------+--------+ 1 row in set (0.00 sec)

• slave 2 server

mysql> stop slave; Query OK, 0 rows affected (0.01 sec) mysql> insert into test values(2,'slave2'); Query OK, 1 row affected (0.01 sec) mysql> select * from test; +------+--------+ | id | name | +------+--------+ | 2 | slave2 | +------+--------+ 1 row in set (0.00 sec)

• master server

mysql> show tables; +----------------+ | Tables_in_test | +----------------+ | test | +----------------+ 1 row in set (0.00 sec) mysql> insert into test values(3,'master'); Query OK, 1 row affected (0.01 sec) mysql> select * from test; +------+--------+ | id | name | +------+--------+ | 3 | master | +------+--------+ 1 row in set (0.00 sec)

• client server

mysql> select * from test; +------+--------+ | id | name | +------+--------+ | 2 | slave2 | +------+--------+ 1 row in set (0.01 sec) mysql> select * from test; +------+--------+ | id | name | +------+--------+ | 1 | slave1 | +------+--------+ 1 row in set (0.00 sec) mysql> select * from test; +------+--------+ | id | name | +------+--------+ | 2 | slave2 | +------+--------+ 1 row in set (0.00 sec) mysql> select * from test; +------+--------+ | id | name | +------+--------+ | 1 | slave1 | +------+--------+ 1 row in set (0.00 sec)

In the above output results, the client polls slave1 and slave2 to view data. Since slave functions are turned off from both slave servers, modifications in the master server cannot be viewed.

5. After the slave function is turned off, the client server writes data

• Client server

mysql> insert into test values(4,'client1'); Query OK, 1 row affected (0.00 sec) mysql> select * from test; +------+--------+ | id | name | +------+--------+ | 2 | slave2 | +------+--------+ 1 row in set (0.01 sec) mysql> select * from test; +------+--------+ | id | name | +------+--------+ | 1 | slave1 | +------+--------+ 1 row in set (0.00 sec)

• master server

mysql> select * from test; +------+---------+ | id | name | +------+---------+ | 3 | master | | 4 | client1 | +------+---------+ 2 rows in set (0.00 sec)

• slave 1 server

mysql> select * from test; +------+--------+ | id | name | +------+--------+ | 1 | slave1 | +------+--------+ 1 row in set (0.00 sec)

• slave 2 server

mysql> select * from test; +------+--------+ | id | name | +------+--------+ | 2 | slave2 | +------+--------+ 1 row in set (0.00 sec)

Result: the modification of the client server is performed on the master server, so the insert operation of the client server can be seen on the master server.
Since the select operation of the client server is performed on the slave server, and the slave server turns off the slave function and cannot obtain updates, the client server itself and the slave server cannot view the update operation.

6. Start the slave service from the server

• slave 1 server

mysql> start slave; Query OK, 0 rows affected (0.00 sec) mysql> select * from test; +------+---------+ | id | name | +------+---------+ | 1 | slave1 | | 3 | master | | 4 | client1 | +------+---------+ 3 rows in set (0.00 sec)

• slave 2 server

mysql> start slave; Query OK, 0 rows affected (0.01 sec) mysql> select * from test; +------+---------+ | id | name | +------+---------+ | 2 | slave2 | | 3 | master | | 4 | client1 | +------+---------+ 3 rows in set (0.00 sec)

• master server

mysql> select * from test; +------+---------+ | id | name | +------+---------+ | 3 | master | | 4 | client1 | +------+---------+ 2 rows in set (0.00 sec)

• Client server

mysql> select * from test; +------+---------+ | id | name | +------+---------+ | 2 | slave2 | | 3 | master | | 4 | client1 | +------+---------+ 3 rows in set (0.00 sec) mysql> select * from test; +------+---------+ | id | name | +------+---------+ | 1 | slave1 | | 3 | master | | 4 | client1 | +------+---------+ 3 rows in set (0.00 sec)

Result: after starting the slave service from the server, you can get updates from the master server, but the updates from the server will not be seen by the master server.

7. The client server inserts data again

• Client server

mysql> insert into test values(5,'client2'); Query OK, 1 row affected (0.00 sec) mysql> select * from test; +------+---------+ | id | name | +------+---------+ | 2 | slave2 | | 3 | master | | 4 | client1 | | 5 | client2 | +------+---------+ 4 rows in set (0.00 sec)

• master server

mysql> select * from test; +------+---------+ | id | name | +------+---------+ | 3 | master | | 4 | client1 | | 5 | client2 | +------+---------+ 3 rows in set (0.00 sec)

• slave1 server

mysql> select * from test; +------+---------+ | id | name | +------+---------+ | 1 | slave1 | | 3 | master | | 4 | client1 | | 5 | client2 | +------+---------+ 4 rows in set (0.00 sec)

• slave2 server

mysql> select * from test; +------+---------+ | id | name | +------+---------+ | 2 | slave2 | | 3 | master | | 4 | client1 | | 5 | client2 | +------+---------+ 4 rows in set (0.00 sec)

Result: the data modification on the client server will be synchronized to all servers.

After the user adds, deletes, changes and checks the committed transactions, the MySQL master server will write them to the binlog in sequence. The slave server starts the I/O thread and requests the contents of the binlog. When the binlog log is updated, the dump thread reads the contents and pushes them to the I/O thread.
Connect the primary server database from the server. After receiving the binlog log content, the I/O thread will put the data into the local relay log. The SQL thread will read the content in the relay log and operate the server according to the content.

Add new comment

0 comments