Master-slave replication and read-write separation of MySQL database

In the actual production environment, if the read and write to the database in the same database server operation, whether it is security, high availability, or high concurrency and other aspects are completely unable to meet the actual demand, therefore, in general, the master-slave replication (Master-slave) way to synchronize data, And then through the read and write separation to improve the database of the concurrency load of such a scenario for deployment and implementation.

MySQL's master-slave replication and read-write separation are closely related, first of all to deploy master-slave replication, in order to base on the data read and write separation.

• Replication type for MySQL master-slave replication
  1) statement-based data. Executes the SQL statement on the primary server, executing the same statement from the server. 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. The default is statement-based replication, which takes row-based replication once it is found that a statement cannot be accurately replicated.

• MySQL Read and write separation principle
  In simple terms, read-write separation is only written on the primary server and 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 slave servers in the cluster.

Deployment environment

Host	Operating System	IP Address	main software
Master	CentOS 7.4 x86_64	192.168.100.200	Mysql-5.7.17.tar.gz, NTP, boost_1_59_0.tar.gz
Slave1	CentOS 7.4 x86_64	192.168.100.201	Mysql-5.7.17.tar.gz, NTP, boost_1_59_0.tar.gz
Slave2	CentOS 7.4 x86_64	192.168.200.202	Mysql-5.7.17.tar.gz, NTP, boost_1_59_0.tar.gz
Amoeba	CentOS 7.4 x86_64	192.168.200.203	Mysql-5.7.17.tar.gz, NTP, boost_1_59_0.tar.gz, amoeba-mysql-binary-2.2.0.tar.gz, Jdk-6u14-linux-x64.bin
Client	CentOS 7.4 x86_64	192.168.200.204	Mysql-5.7.17.tar.gz

Build MySQL master-slave replication

• Set up a time synchronization environment to build a time synchronization server on the master node

  systemctl stop firewalld.servicesetenforce 0yum -y install ntp    #一般系统自带，没有的话yum安装vim /etc/ntp.conf    #添加下面的行server 127.127.100.0    #本地为时钟源fudge 127.127.100.0 stratum 8    #设置时间层级为8（一般限制15内）systemctl restart ntpd    #重启服务

• Time synchronization from the server

  systemctl stop firewalld.servicesetenforce 0yum -y install ntpdate/usr/sbin/ntpdate 192.168.100.200     #进行时间同步

• Installing the MySQL database on 4 hosts
  This option is the 5.7 version, directly to the simple compilation installation.

• Configure Master master server

  # vim /etc/my.cnf     server-id       = 11           #修改     log-bin=master-bin        #主服务器日志文件     log-slave-updates=true      #从服务器更新二进制日志# systemctl restart mysqld.service# mysql -u root -p123456  #登录Mysql 给从服务器授权mysql> GRANT REPLICATION SLAVE ON *.* TO ‘myslave‘@‘192.168.100.%‘ IDENTIFIED BY ‘123456‘;mysql> FLUSH PRIVILEGES;mysql> show master status;

• Configure Slave from server (Slave1, Slave2 configuration Same)

  # vim /etc/my.cnfserver-id       = 22        #另一台从服务器也是22(不能和 Master 服务器相同)relay-log=relay-log-bin         #从主服务器上同步日志文件记录到本地relay-log-index=slave-relay-bin.index      #定义relay-log的位置和名称# systemctl restart mysqld.service

• Log in to the Slave server and change the parameters of Master_log_file and Master_log_pos in the following command as per the Master server results

  # mysql -u root -p123456mysql> change master to master_host=‘192.168.100.200‘,master_user=‘myslave‘,master_password=‘123456‘,master_log_file=‘master-bin.000001‘,master_log_pos=604;mysql> start slave;      #启动同步mysql> show slave status\G;         #查看状态

• Verifying master-slave copy effects
  Create a test table on the Master server to view from the server.

Building MySQL database for read and write separation

The Amoeba (amoeba) project Open source framework released a amoeba for MySQL software in 2008. This software is dedicated to MySQL's distributed database front-end agent layer, which serves as a SQL route for the application layer when it accesses MySQL, and has load balancing, high availability, SQL filtering, read and write separation, routable correlation to the target database, and multiple databases that can be requested concurrently. The amoeba has been used in many enterprises ' production lines and its version is available for download on the website.

• Installing the Java environment on the amoeba server

  systemctl stop firewalld.servicesetenforce 0cp jdk-6u14-linux-x64.bin /usr/local/cd /usr/local./jdk-6u14-linux-x64.bin   #回车，输入"yes"，回车mv jdk1.6.0_14/ /usr/local/jdk1.6

• modifying environment variables

  # vim /etc/profile    #在文件末尾添加下面的行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# source /etc/profile    #刷新环境变量

• Installing and configuring Amoeba software

  # mkdir /usr/local/amoeba# tar zxvf amoeba-mysql-binary-2.2.0.tar.gz -C /usr/local/amoeba/# chmod -R 755 /usr/local/amoeba/# /usr/local/amoeba/bin/amoebaamoeba start|stop    #显示此内容说明 Amoeba 安装成功

• Open permissions to amoeba access on three master-slave MySQL servers (three servers are added)

  grant all on *.* to [email protected]‘192.168.100.%‘ identified by ‘123456‘;

• Edit the Amoeba.xml configuration file on the amoeba server

  # vim /usr/local/amoeba/conf/amoeba.xml--30行--<property name="user">amoeba</property>    #用户访问amoeba服务器的身份验证--32行-- <property name="password">123456</property>--115行--<property name="defaultPool">master</property>    #默认权限--117-去掉注释-<property name="writePool">master</property>      #赋予master写权限<property name="readPool">slaves</property>        #赋予slaves读权限

  # vi conf/dbServers.xml-23行注释掉  作用：默认进入test库 以防mysql中没有test库时，会报错<!-- <property name="schema">test</property> -->--26--29--去掉注释--    #赋予amoeba用户权限<property name="user">test</property><property name="password">123456</property>-----42-主服务器地址---<dbServer name="master"  parent="abstractServer"><property name="ipAddress">192.168.100.200</property>--52-从服务器主机名-<dbServer name="slave1"  parent="abstractServer">--55-从服务器1地址-<property name="ipAddress">192.168.100.201</property>--（这里上面6行复制修改）从服务器2地址<dbServer name="slave2"  parent="abstractServer"><property name="ipAddress">192.168.100.202</property>--64行--<dbServer name="slaves" virtual="true">--70行--<property name="poolNames">slave1,slave2</property>

• Open service

  /usr/local/amoeba/bin/amoeba start&netstat -anpt | grep java

• Test read/write separation

• Client

  systemctl stop firewalld.servicesetenforce 0mysql -u amoeba -p123456 -h 192.168.100.203 -P8066    #使用amoeba远程登录MySQL

• Master Server

  mysql -u root -pmysql> create database wang;mysql> use wzn;mysql> create table wzn (id int(10),name varchar(10),address varchar(20));

• Slave Server

  mysql -u root -pmysql> stop slave;mysql> use wang;mysql> use wzn;# Slave1 服务器mysql> insert into zang values(‘2‘,‘wzn‘,‘this_is_slave1‘);# Slave2 服务器mysq> insert into zang values(‘3‘,‘wzn‘,‘this_is_slave2‘);

• At this point, we use client access, and we select * from wzn; can see the data created separately on the two Slave servers.

• We insert a piece of data on the client, at this time look at the table WZN, do not see the newly written data, because the data is written on the master server , the synchronization is not open , but we log in to the master database can see the data write success . Try to turn on synchronization, we can see that after the data is synchronized to the Slave database , we can see the new data.
  This confirms that the data is written on the Master server, and the data is read on the Slave server.

Master-slave replication and read-write separation of MySQL database