MySQL master-slave replication and read/write separation

MySQL master-slave copy and read-Write separation experiment Introduction

In the actual production environment, if the database read and write on the same database operation, whether in terms of security, high availability or high concurrency and so on all aspects are completely unable to meet the actual demand, so generally through the master-slave replication to synchronize the data, The deployment and implementation of a scheme such as the concurrent load capability of the database can be improved by the read-write separation.

MySQL master-slave replication principle

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

2. The slave server copies the binary log of master to the secondary log (Relay log). First slave starts a worker thread--i/o thread, the I/O thread opens a normal connection on master, and then starts binary log dump process. Binary log dump process reads the time from master's binary logs, and if it has been followed by master, it will sleep waiting for master to produce new events. The I/O thread writes these events to the relay log.

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

MySQL Read and write separation principle

The read-write separation is 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 databases in the cluster.

Based on the intermediary agent layer implementation: The agent is generally located between the client and the server, the Proxy client request is forwarded to the backend database through judgment, this part is implemented by amoeba.

Case Environment

As shown

Build MySQL master-slave replication during the experiment

1, establish the time synchronization environment, on the main node to build a time source server.

[[email protected] ~]# yum install ntp -y[[email protected] ~]# vim /etc/ntp.conf                  #在配置文件最后加两行server 127.127.58.0                                  #设置本地时钟源fudge 127.127.58.0 stratum 8                         #设置时间层级为8（限制在15内）[[email protected] ~]# service ntpd restart               #重启服务[[email protected] ~]# systemctl stop firewalld.service   #关闭防火墙[[email protected] ~]# setenforce 0                       #关闭增强安全功能

2. Synchronize the time between the two nodes separately.

[[email protected] ~]# yum install ntp -y[[email protected] ~]# /usr/sbin/ntpdate 192.168.58.131      #和时间源服务器同步10 Jul 11:07:16 ntpdate[28695]: the NTP socket is in use, exiting[[email protected] ~]# systemctl stop firewalld.service   #关闭防火墙[[email protected] ~]# setenforce 0                       #关闭增强安全功能

3, install MySQL, this step in the front, it is omitted.

4. Configure MySQL Master master server.

[[email protected] ~]# vim /etc/my.cnfserver-id = 11                                           #修改server-id，注意三台服务器id不能重复log-bin=master-bin                                       #修改主服务器日志文件log-slave-updates=true                                   #增加开启主从同步功能[[email protected] ~]# systemctl restart mysqld.service       #重启MySQL服务器

5, log in to the MySQL service, to the license from the server.

[[email protected] ~]# mysql -u root -pEnter password: mysql> GRANT REPLICATION SLAVE ON *.* TO ‘myslave‘@‘192.168.58.%‘ IDENTIFIED BY ‘123456‘;    #授予192.168.58.0网段的主机分别以myslave，123456为用户名，密码的用户REPLICATION SLAVE权限。mysql> FLUSH PRIVILEGES;   #刷新权限设置mysql> show master status; #查看主服务器状态，file,position两个值很重要，后面要用到。+-------------------+----------+--------------+------------------+-------------------+| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |+-------------------+----------+--------------+------------------+-------------------+| master-bin.000002 |     2614 |              |                  |                   |+-------------------+----------+--------------+------------------+-------------------+

6, configure MySQL slave from the server, both from the server to perform the same operation, only Server-id different

[[email protected] ~]# Vim/etc/my.cnfserver-id = #设置server-id, three servers can not be the same relay-log=rel Ay-log-bin #从主服务器上同步日志文件记录到本地relay-log-index=slave-relay-bin.index #定义relay the location and name of the-log [[Email&nbs  P;protected] ~]# service mysqld restart[[email protected] ~]# mysql-u root-penter password:mysql> Change Master To master_host= ' 192.168.58.131 ', master_user= ' myslave ', master_password= ' 123456 ', master_log_file= '   Master-bin.000002 ', master_log_pos=2614; #这条命令就是用来指定主服务器, the master_log_file and Master_log_pos parameters correspond to the above.    mysql> start slave; #开启同步mysql > Show slave status\g; #查看slave状态, make sure the slave_io_running,slave_sql_running are yex*************************** 1. Row *************************** slave_io_state:waiting for master to send event Master_hos t:192.168.58.131 Master_user:myslave master_port:3306 Connect_retry:6 0 master_log_file:master-bin.000002         read_master_log_pos:2614 relay_log_file:relay-log-bin.000005 relay_log_pos:1955              relay_master_log_file:master-bin.000002 Slave_io_running:yes Slave_sql_running:yes       Replicate_Do_DB:Replicate_Ignore_DB:Replicate_Do_Table:Replicate_Ignore_Table:                  replicate_wild_do_table:replicate_wild_ignore_table:last_errno:0 Last_error: skip_counter:0 exec_master_log_pos:2614 relay_log_space:2535 Until_con Dition:none until_log_file:until_log_pos:0 Master_ssl_allowed:no Mas                Ter_SSL_CA_File:Master_SSL_CA_Path:Master_SSL_Cert:Master_SSL_Cipher: master_ssl_key:seconds_behind_master:0

7, verify the master-slave synchronization effect. Judge by creating a new database on the primary server and then checking to see if the synchronization was successful.

Databases in the primary server

mysql> show databases;+--------------------+| Database           |+--------------------+| information_schema || mysql              || performance_schema || school             || sys                || test1              |+--------------------+6 rows in set (0.23 sec)

From the database in the server

mysql> show databases;+--------------------+| Database           |+--------------------+| information_schema || mysql              || performance_schema || school             || sys                || test1              || wq                 || yxxx               |+--------------------+8 rows in set (0.31 sec)

Below, create a new database test02 on the primary server to see if the synchronization was successful.

mysql> create database test02;Query OK, 1 row affected (3.86 sec)mysql> show databases;+--------------------+| Database           |+--------------------+| information_schema || mysql              || performance_schema || school             || sys                || test02             || test1              |+--------------------+7 rows in set (0.01 sec)

View the database from the server. Sync succeeded!

mysql> show databases;+--------------------+| Database           |+--------------------+| information_schema || mysql              || performance_schema || school             || sys                || test02             || test1              || wq                 || yxxx               |+--------------------+9 rows in set (0.15 sec)

Build MySQL read-write separation

1. Install the Java environment on the Amoeba Proxy server because the amoeba service is developed based on Java1.5.

systemctl stop firewalld.service               #关闭防火墙        setenforce 0                                   #关闭增强性安全功能cp jdk-6u14-linux-x64.bin /usr/local/          #将软件包复制到指定目录./jdk-6u14-linux-x64.bin                       #执行安装脚本mv jdk1.6.0_14/ /usr/local/jdk1.6              #为了方便，修改文件名vim /etc/profile                               #将Java添加到环境变量中export JAVA_HOME=/usr/local/jdk1.6             #将下面几行插入到文件中export 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/binsource /etc/profile                            #刷新文件，使改动生效

2. Install and configure the Amoeba software.

[[email protected] ~]#mkdir /usr/local/amoeba                        #为Amoeba创建工作目录[[email protected] ~]#tar zxvf amoeba-mysql-binary-2.2.0.tar.gz -C /usr/local/amoeba/   #解压[[email protected] ~]#chmod -R 755 /usr/local/amoeba/                #修改文件权限[[email protected] ~]#/usr/local/amoeba/bin/amoeba   #执行Amoeba服务amoeba start|stop说明amoeba安装成功

3, configuration amoeba read and write separation, two slave read load balancing.

mysql> grant all on *.* to [email protected]‘192.168.58.%‘ identified by ‘123.com‘;#在三台mysql服务器上添加权限开放给amoeba访问

4. Go back to the amoeba server and configure the Amoeba.xml configuration file first.

  [[email protected] ~]# vim/usr/local/amoeba/conf/amoeba.xml <property name= "Authenticator" > <bean class= "Com.meidusa.amoeba.mysql.server.MysqlClientAuthenticator" > <property name = "User" >amoeba</property> #客户端用来登录Amoeba服务器的用户名 <property name= "password" >123456< /property> #客户端用来登录Amoeba服务器的密码 <property name= "Filter" > <be An class= "Com.meidusa.amoeba.server.IPAccessController" > <property name= "ipfile" >${amo Eba.home}/conf/access_list.conf</property> </bean> </prop Erty> </bean></property>  

                <property name="defaultPool">master</property>#默认服务器池                <property name="writePool">master</property>#master服务器用于写                <property name="readPool">slaves</property>#slaves服务器用于读

Configuring the Dbservers.xml File

[[email protected] ~]# vim /usr/local/amoeba/conf/dbServers.xml                         <!-- mysql schema                         <property name="schema">test</property>#将这句话注释掉，否则无法用客户端通过amoeba代理服务器访问mysql服务器池                        -->                        <!-- mysql user -->                        <property name="user">test</property> #修改为用于登录服务器池的用户名                        <property name="password">123.com</property> #修改为用于登录服务器池的密码

 <dbserver name= "Master" parent= "Abstractserver" > #配置主服务器 <factoryConfig>                <!--mysql IP--<property name= "ipAddress" >192.168.58.131</property> #主服务器的IP </factoryConfig> </dbServer> <dbserver name= "slave1" parent= "Abstractserver" &                        GT, #从服务器1的mysql服务器 <factoryConfig> <!--mysql IP-- <property name= "ipAddress" >192.168.58.144</property> #从服务器1的mysql服务器IP &LT;/FACTORYCONFIG&G        T </dbServer> <dbserver name= "slave2" parent= "Abstractserver" > #从服务器2的mysql服务器 <factory config> <!--mysql IP--<property name= "IpAddress" >192.168. 58.145</property> #从服务器2的mysql服务器IP </factoryConfig> </dbServer> <dbserv ER name= "SlaveS "virtual=" true "> #定义从服务器池 <poolconfig class=" Com.meidusa.amoeba.server.MultipleServerPool "> <!--Load balancing Strategy:1=roundrobin, 2=weightbased, 3=ha--> &LT;PR Operty name= "LoadBalance" >1</property> <!--separated by Commas,such As:server1,server                2,server1--<property name= "Poolnames" >slave1,slave2</property> #定义从服务器池中有两台服务器 </poolConfig> </dbServer>

5, when configured, start the Amoeba software with the default port of TCP 8066.

[[email protected] ~]#/usr/local/amoeba/bin/amoeba start &[1] 69919[[email protected] ~]# Log4j:WARN log4j config load completed from file:/usr/local/amoeba/conf/log4j.xml2018-07-10 16:53:55,472 INFO context. Mysqlruntimecontext-amoeba for Mysql current versoin=5.1.45-mysql-amoeba-proxy-2.2.0log4j:warn IP access config load Co mpleted from file:/usr/local/amoeba/conf/access_list.conf2018-07-10 16:53:55,925 INFO net. Serverableconnectionmanager-amoeba for Mysql listening on 0.0.0.0/0.0.0.0:8066.2018-07-10 16:53:55,925 INFO net. Serverableconnectionmanager-amoeba Monitor Server listening on/127.0.0.1:54818.^c[[email protected] ~]# netstat -ntap | grep java #8066端口已经开启tcp6 0 0 127.0.0.1:54818:::* LISTEN 69919/java t CP6 0 0::: 8066:::* LISTEN 69919/java tcp6 0 0 192          .168.58.136:41992 192.168.58.145:3306 established 69919/javaTCP6 0 0 192.168.58.136:33236 192.168.58.144:3306 established 69919/java tcp6 0 0 19  2.168.58.136:48134 192.168.58.131:3306 established 69919/java

6, to the client host on the test.

[[email protected] 桌面]# mysql -u amoeba -p123456 -h 192.168.58.136 -P8066mysql> show databases;+--------------------+| Database           |+--------------------+| information_schema || mysql              || performance_schema || school             || sys                || test02             || test1              

Create a table on master that synchronizes to each slave server.

mysql> use test1;Database changedmysql> create table zang (id int(10),name varchar(10),address varchar(20));

Turn off the two sync from server feature.

mysql> stop slave;

 -------Content on the primary server is not synchronized with use Test1insert to Zang values (' 1 ', ' Zhang ', ' this_is_master ')----from server 1----use Test1 INSERT into Zang values (' 2 ', ' Zhang ', ' this_is_slave1 '),----from server 2----use Test1;insert into Zang values (' 3 ', ' Zhang ', ' This_is_slave2 ');  

------在客户端上测试----第一次会向从服务器1读数据-第二次会向从服务器2读取mysql> select * from test1.zang;------+-------+----------------+| id   | name  | address        |+------+-------+----------------+|    3 | zhang | this_is_slave2 |+------+-------+----------------+3 rows in set (0.03 sec)mysql> select * from test1.zang;+------+-------+----------------+| id   | name  | address        |+------+-------+----------------+|    2 | zhang | this_is_slave1 |+------+-------+----------------+3 rows in set (0.25 sec)------在通过客户端连接数据库后写入的数据只有主服务器会记录mysql> insert into zang values(‘5‘,‘zhang‘,‘write_test‘);到主服务器中查看表，会发现有两条记录，而从服务器中只有一条记录，说明实现了读写分离+------+-------+----------------+| id   | name  | address        |+------+-------+----------------+|    1 | zhang | this_is_master ||    5 | zhang | write_test     |+------+-------+----------------+3 rows in set (0.01 sec)

MySQL master-slave replication and read/write separation