Build MySQL master-slave copy and read/write separation on CentOS7

MySQL master-slave replication principle

MySQL master-slave replication and MySQL read-write separation of the two have a close connection, first of all to deploy master-slave replication, only the master-slave replication is completed, the basis for the data read and write separation.

(1) MySQL supports the type of replication.

1) statement-based replication. MySQL uses statement-based replication by default and is more efficient.

2) row-based replication. Copy the changes to the past, rather than executing the commands from the server again.

3) mixed-type replication. Statement-based replication is used by default, and row-based replication is used when a statement cannot be accurately replicated based on the discovery.

(2) The working process of MySQL replication.

1) master records these changes in the binary log before each transaction updates the data. After the write binary log is complete, Master notifies the storage engine to commit the transaction.

2) Slave Copy the binary log of master to the secondary log. First, slave starts a worker thread--i/o thread, the I/O thread opens a normal link on master, and then starts Binlog dump process. Binlog dump process reads the event from the binary log of master, and if it has been followed by master, it sleeps and waits for master to produce a new event. The I/O thread writes these events to the relay log.

3) SQL slave thred (SQL slave thread) handles the last step of the process. The SQL thread reads events from the log and replays the events in them to update the slave data so that it is consistent with the data in master. As long as the thread is consistent with the I/O thread, the trunk log is typically located in the OS cache, so the overhead of the trunk log is minimal.

One important limitation in the replication process is that replication is serialized on slave, meaning that parallel update operations on Master cannot operate concurrently on slave.

MySQL Read and write separation principle

In simple terms, read-write separation (see figure) is only written on the primary server and is read only from the server. The rationale is to have the primary database process transactional queries and to process select queries from the database. Database replication is used to synchronize changes caused by transactional queries to the from database in the cluster.

Based on the intermediary agent layer implementation: The agent is generally located between the client and the server, the proxy server to the client request by the judgment to forward to the backend database.

Experimental environment

Use five server simulations to build a specific topology:

Lab Environment Table:

Implementation steps

1. Build MySQL master-slave replication.

(1) Set up a time synchronization environment to build a time synchronization server on the master node.

1) Install NTP.

[[email protected] ~]# yum install ntp -y

2) Configure NTP.

[[email protected] ~]# vim /etc/ntp.conf server 127.127.126.0                    //本地是时钟源//fudge 127.127.126.0 stratum 8          //设置时间层级为8（限制在15内）//

3) Restart the service.

[[email protected] ~]# systemctl restart ntpd.service

(2) Time synchronization is performed from the node server.

[[email protected] ~]# yum install ntpdate -y[[email protected] ~]# /usr/sbin/ntpdate 192.168.126.138  //同步主服务器的时间//

(3) Turn off the FIREWALLD firewall on each server.

[[email protected] ~]# systemctl stop firewalld.service   //关闭防火墙//[[email protected] ~]# setenforce 0

(4) Install MySQL database. Install on Master, Slave1, Slave2, I use the database is MySQL5.7.17 here installation finished no longer demo.

(5) Configure the MySQL Master master server.

1) Modify or add the following in the/ETC/MY.CNF.

[[email protected] mysql]# vim /etc/my.cnfserver-id = 11log-bin=master-bin             //主服务器日志文件//log-slave-updates=true      //从服务器更新二进制日志//

2) Restart the MySQL service.

[[email protected] ~]# systemctl restart mysqld.service

3) Log in to the MySQL program and authorize the server.

[[email protected] ~]# mysql -uroot -pmysql> GRANT REPLICATION SLAVE ON *.* TO ‘myslave‘@‘192.168.126.%‘ IDENTIFIED BY ‘123456‘;      //授权//mysql> FLUSH PRIVILEGES;mysql> show master status;+-------------------+----------+--------------+------------------+-------------------+| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |+-------------------+----------+--------------+------------------+-------------------+| master-bin.000001 |      604 |              |                  |                   |+-------------------+----------+--------------+------------------+-------------------+1 row in set (0.00 sec)其中File列显示日志名，position列显示偏移量。

(6) Configure the slave server.
1) Modify or add the following in the/ETC/MY.CNF.

[[email protected] ~]# vim /etc/my.cnfserver-id = 22relay-log=relay-log-bin             //从主服务器上同步日志文件记录到本地//relay-log-index=slave-relay-bin.index   //定义relay-log的位置和名称//

Note here that Server-id cannot be the same as the primary server.

2) Restart the MySQL service.

3) log in to MySQL and configure synchronization.

Change the parameters of Master_log_file and Master_log_pos in the following command by the master server results.

[[email protected] ~]# mysql -u root -pmysql> change master to master_host=‘192.168.126.138‘,master_user=‘myslave‘,master_password=‘123456‘,master_log_file=‘master-bin.000001‘,master_log_pos=604;

4) Start the synchronization.

mysql> start slave;

5) Check the slave status to ensure that the following two values are yes.

mysql> show slave status\G;*************************** 1. row ***************************               Slave_IO_State: Waiting for master to send event                  Master_Host: 192.168.126.138                  Master_User: myslave                  Master_Port: 3306                Connect_Retry: 60              Master_Log_File: master-bin.000001          Read_Master_Log_Pos: 604               Relay_Log_File: relay-log-bin.000002                Relay_Log_Pos: 321        Relay_Master_Log_File: master-bin.000001             Slave_IO_Running: Yes                       Slave_SQL_Running: Yes              Replicate_Do_DB:           

(7) Verify the master-slave copy effect.

1) Create a new database db_test on the primary server.

mysql>  create database db_test;

2) in the main, from the server to view the database, the same as the database, the master-slave replication succeeds.

mysql> show databases;      //主服务器//+--------------------+| Database           |+--------------------+| information_schema || db_test            || mysql              || performance_schema || school             || sys                |+--------------------+6 rows in set (0.03 sec)mysql> show databases;    //从服务器//+--------------------+| Database           |+--------------------+| information_schema || db_test            || kgc                || mysql              || performance_schema || sys                |+--------------------+6 rows in set (0.05 sec)

Build MySQL read-write separation

Amoeba (amoeba), this software is dedicated to MySQL's distributed database front-end agent layer, which is mainly for the application layer to access MySQL as a SQL route, and has load balancing, high availability, SQL filtering, read and write separation, routable related to the target database, can concurrently request multiple databases. The ability of high availability, load balancing and data slicing of multiple data sources can be accomplished through amoeba.

(1) Install the Java environment on the host amoeba.

Amoeba is developed based on jdk1.5, so the official recommendation is to use the jdk1.5 or 1.6 version, which is not recommended for high versions.

[[email protected] ~]# systemctl stop firewalld.service [[email protected] ~]# setenforce 0[[email protected] tomcat]# cp jdk-6u14-linux-x64.bin /usr/local/[[email protected] local]# ./jdk-6u14-linux-x64.bin    //根据提示按Enter键完成即可//[[email protected] local]# mv jdk1.6.0_14/ /usr/local/jdk1.6[[email protected] local]# vim /etc/profile

Add the following configuration

export JAVA_HOME=/usr/local/jdk1.6export CLASSPATH=$CLASSPATH:$JAVA_HOME/lib:$JAVA_HOME/jre/libexport PATH=$JAVA_HOME/lib:$JAVA_HOME/jre/bin/:$PATH:$HOME/binexport AMOEBA_HOME=/usr/local/amoebaexport PATH=$PATH:$AMOEBA_HOME/bin[[email protected] local]# source /etc/profile   //启动//

The Java environment has been configured successfully.

(2) Installing and configuring Amoeba software

[[email protected] local]# mkdir /usr/local/amoeba  创建工作路径//[[email protected] tomcat]# tar zxvf amoeba-mysql-binary-2.2.0.tar.gz -C /usr/local/amoeba   //解压安装包//[[email protected] tomcat]# chmod -R 755 /usr/local/amoeba/  //提示amoeba权限//[[email protected] tomcat]# /usr/local/amoeba/bin/amoebaamoeba start|stop   //显示此内容说明Amoeba安装成功//

(3) Configure amoeba read/write separation, two slave read load balancer.
1) Master, Slave1, Slave2 Open permissions to Amoeba access.

grant all on *.* to [email protected]‘192.168.126.%‘ identified by ‘123.com‘;

2) Edit the Amoeba.xml configuration file.

[[email protected] tomcat]# cd /usr/local/amoeba/[[email protected] amoeba]# vim conf/amoeba.xml ---30行-- <property name="user">amoeba</property>----32行--------- <property name="password">123456</property>---117-去掉注释- <property name="defaultPool">master</property> <property name="writePool">master</property> <property name="readPool">slaves</property>

3) Edit the Dbservers.xml configuration file.

vi conf/dbServers.xml--26-29--去掉注释--      //行// <property name="user">test</property>     <property name="password">123.com</property>-----42-主服务器地址---<dbServer name="master"  parent="abstractServer"> <property name="ipAddress">192.168.126.138</property>--52-从服务器主机名-<dbServer name="slave1"  parent="abstractServer">--55-从服务器地址- <property name="ipAddress">192.168.126.162</property> 从服务器slave2      <dbServer name="slave2"  parent="abstractServer">      //添加// <property name="ipAddress">192.168.126.232</property> <dbServer name="slaves" virtual="true"> <poolConfig class="com.meidusa.amoeba.server.MultipleServerPool">--末尾--<property name="poolNames">slave1,slave2</property>    //修改// </poolConfig>

4) When configured correctly, you can start the Amoeba software with the default port of TCP 8066.

[[email protected] amoeba]# /usr/local/amoeba/bin/amoeba start&[[email protected] amoeba]#  netstat -anpt | grep javatcp6       0      0 127.0.0.1:28750         :::*                    LISTEN      3370/java           tcp6       0      0 :::8066                 :::*                    LISTEN      3370/java .....//省略//

(4) test
1) on the client host.

[[email protected] ~]# yum install mysql -y

You can access MySQL by proxy:

[[email protected] ~]# mysql -u amoeba -p123456 -h 192.168.126.132 -P8066..... //省略//MySQL [(none)]>

2) Create a table on master, synchronize to each slave server, then close the slave function of each slave server, and then insert the difference statement.

mysql> use db_test;Database changedmysql> create table zang (id int(10),name varchar(10),address varchar(20)); //创建表//Query OK, 0 rows affected (0.06 sec)

To turn off the slave feature on two servers, respectively:

mysql> stop slave;

Then insert the difference statement on the primary server:

mysql> insert into zang values(‘1‘,‘zhang‘,‘this_is_master‘);  //插入数据//

3) Manually insert additional content from the server.

slave1:mysql> use db_test;mysql> insert into zang values(‘2‘,‘zhang‘,‘this_is_slave1‘);   //插入数据//Query OK, 1 row affected (0.03 sec)slave2:mysql> use db_test;mysql> insert into zang values(‘3‘,‘zhang‘,‘this_is_slave2‘); //插入数据//Query OK, 1 row affected (0.03 sec)

4) test read operation
The results of the first query on the client host are as follows:

MySQL [db_test]> select * from zang;+------+-------+----------------+| id   | name  | address        |+------+-------+----------------+|    3 | zhang | this_is_slave2 |+------+-------+----------------+1 row in set (0.01 sec)

The results of the second query are as follows:

MySQL [db_test]> select * from zang;+------+-------+----------------+| id   | name  | address        |+------+-------+----------------+|    2 | zhang | this_is_slave1 |+------+-------+----------------+1 row in set (0.01 sec)

Third query Result:

MySQL [db_test]> select * from zang;+------+-------+----------------+| id   | name  | address        |+------+-------+----------------+|    3 | zhang | this_is_slave2 |+------+-------+----------------+1 row in set (0.01 sec)

5) test the write operation.
Insert a statement on the client host:

MySQL [db_test]> insert into zang values(‘5‘,‘zhang‘,‘write_test‘);Query OK, 1 row affected (0.02 sec)

However, in the client can not be queried, and ultimately only on master to see the content of this statement, indicating that the operation is written on the master server.

mysql> select * from zang;+------+-------+----------------+| id   | name  | address        |+------+-------+----------------+|    1 | zhang | this_is_master ||    5 | zhang | write_test     |+------+-------+----------------+2 rows in set (0.01 sec)

This verifies that MySQL read-write separation has been achieved, all of the current write operations on the Master master server, to avoid the data of different steps, all read operations are allocated to the slave from the server, to share the pressure of the database.

Build MySQL master-slave copy and read/write separation on CentOS7