MySQL主从复制与读写分离

MySQL主从复制与读写分离

实验介绍

在实际生产环境中，如果对数据库的读和写都在同一个数据库上操作，无论是在安全性、高可用性还是高并发性等各方面都是完全不能满足实际需求的，所以一般来说都是通过主从复制来同步数据，再通过读写分离来提升数据库的并发负载能力这样的方案进行部署和实施。

MySQL主从复制原理

1、在每个事务更新数据前，Master服务器在二进制日志中记录这些改变。写入二进制文件完成后，Master服务器通知存储引擎提交事务。

2、Slave服务器将Master的Binary log复制到其中继日志（Relay log）。首先Slave开始一个工作线程--I/O线程，I/O线程在Master上打开一个普通的连接，然后开始Binary log dump process。Binary log dump process从Master的二进制日志中读取时间，如果已经跟上Master，它会睡眠等待Master产生新的事件。I/O线程将这些事件写入中继日志中。

3、SQL slave thread（SQL从线程）处理最后一步。SQL线程从中继日志中读取事件，并重放其中的事件从而更新Slave的数据，使其与Master中的数据一致。只要该线程与I/O线程保持一致，中继日志通常会位于系统的缓存中，所以中继日志开销很小。

MySQL读写分离原理

读写分离就是在主服务器上写，只在从服务器上读。基本原理是让主数据库处理事务性查询，而从数据库处理select查询。数据库复制被用来把事务性查询导致的变更同步到群集中的数据库。

基于中间代理层实现：代理一般位于客户端和服务端之间，代理服务器接到客户端请求通过判断转发到后端数据库，这部分通过Amoeba实现。

案例环境

如下图所示

实验过程

搭建MySQL主从复制

1、建立时间同步环境，在主节点上搭建时间源服务器。

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

2、在两台从节点上分别进行时间同步。

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

3、安装MySQL，这步在前面讲过，就省略掉了。

4、配置MySQL Master主服务器。

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

5、登录MySQL服务，给从服务器授权。

[root@promote ~]# 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、配置MySQL Slave从服务器,在两台从服务器上都执行相同的操作，只有server-id不同

[root@localhost ~]# vim /etc/my.cnfserver-id       = 22                        #设置server-id，三台服务器不能一样relay-log=relay-log-bin                     #从主服务器上同步日志文件记录到本地relay-log-index=slave-relay-bin.index       #定义relay-log的位置和名称[root@localhost ~]# service mysqld restart[root@localhost ~]# 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;   #这条命令就是用来指定主服务器，master_log_file和master_log_pos参数和上面对应。mysql> start slave;    #开启同步mysql> show slave status\G; #查看slave状态，确保 Slave_IO_Running，Slave_SQL_Running都是yex*************************** 1. row ***************************               Slave_IO_State: Waiting for master to send event                  Master_Host: 192.168.58.131                  Master_User: myslave                  Master_Port: 3306                Connect_Retry: 60              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_Condition: None               Until_Log_File:                 Until_Log_Pos: 0           Master_SSL_Allowed: No           Master_SSL_CA_File:            Master_SSL_CA_Path:               Master_SSL_Cert:             Master_SSL_Cipher:                Master_SSL_Key:         Seconds_Behind_Master: 0

7、验证主从同步效果。通过在主服务器上创建一个新数据库，然后查看是否同步成功来判断。

主服务器中的数据库

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

从服务器中的数据库

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

下面，在在主服务器上创建一个新的数据库test02，进而查看是否同步成功。

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)

查看从服务器的数据库。同步成功！

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

搭建MySQL读写分离

1、在Amoeba代理服务器上安装Java环境，因为Amoeba服务是基于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、安装并配置Amoeba软件。

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

3、配置Amoeba读写分离，两个Slave读负载均衡。

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

4、回到Amoeba服务器上，首先配置amoeba.xml配置文件。

[root@promote ~]# vim /usr/local/amoeba/conf/amoeba.xml                                     amoeba #客户端用来登录Amoeba服务器的用户名                        123456 #客户端用来登录Amoeba服务器的密码                                                                                ${amoeba.home}/conf/access_list.conf                                                                

                master#默认服务器池                master#master服务器用于写                slaves#slaves服务器用于读

配置dbServers.xml文件

[root@promote ~]# vim /usr/local/amoeba/conf/dbServers.xml                                                                         test #修改为用于登录服务器池的用户名                        123.com #修改为用于登录服务器池的密码

 #配置主服务器                                                                192.168.58.131#主服务器的IP                                #从服务器1的mysql服务器                                                                192.168.58.144 #从服务器1的mysql服务器IP                                #从服务器2的mysql服务器                                                                192.168.58.145 #从服务器2的mysql服务器IP                                #定义从服务器池                                                                1                                                slave1,slave2#定义从服务器池中有两台服务器                        

5、配置好后，启动Amoeba软件，其默认端口为tcp 8066.

[root@promote ~]# /usr/local/amoeba/bin/amoeba start &[1] 69919[root@promote ~]# 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 completed 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[root@promote ~]# netstat -ntap | grep java #8066端口已经开启tcp6       0      0 127.0.0.1:54818         :::*                    LISTEN      69919/java          tcp6       0      0 :::8066                 :::*                    LISTEN      69919/java          tcp6       0      0 192.168.58.136:41992    192.168.58.145:3306     ESTABLISHED 69919/java          tcp6       0      0 192.168.58.136:33236    192.168.58.144:3306     ESTABLISHED 69919/java          tcp6       0      0 192.168.58.136:48134    192.168.58.131:3306     ESTABLISHED 69919/java 

6、到client主机上进行测试。

[root@yx 桌面]# mysql -u amoeba -p123456 -h 192.168.58.136 -P8066mysql> show databases;+--------------------+| Database           |+--------------------+| information_schema || mysql              || performance_schema || school             || sys                || test02             || test1              |+--------------------+7 rows in set (0.22 sec)mysql> 

在Master上创建一个表，同步到各从服务器。

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

关闭两台从服务器的同步功能。

mysql> stop slave;

----在主服务器上---内容不会同步use test1insert into zang values('1','zhang','this_is_master');----从服务器1----use test1;insert into zang values('2','zhang','this_is_slave1');----从服务器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)