mysql——mysql主从复制与读写分离

一、mysql主从复制原理

1、mysql的复制类型

基于语句的复制
基于行的复制
基于类型的复制

2、mysql主从复制的工作过程

二、mysql读写分离原理

只在主服务器上写，只在从服务器上读
主数据库处理事务性查询，从数据库处理select查询
数据库复制用于将事务性查询的变更同步到集群中的从数据库
读写分离方案
- 基于程序代码内部实现
- 基于中间代理层实现

1、主从开启权限，创建权限用户账户（主服务器）
amoeba变形虫，写权限，读取时，若同时存在，轮询读取
2、读写分离账号（主从服务器）
3、客户端访问amoeba账号
缺陷是如果主服务器宕机，则写操作就不存在。
解决方法（MHA）## 二级目录

三、读写分离试验

3.1 试验环境

一台主服务器192.168.200.90
两台从服务器192.168.200.80
192.168.200.110
一台amoeba服务器192.168.200.40
一台win10客户端

3.2 实验拓扑图

3.3 试验分析

先配置主从同步，再配置读写分离。主从同步是读写分离的前提

3.4 实验步骤

1、配置主从同步
主服务器

[root@localhost ~]# iptables -F
[root@localhost ~]# setenforce 0
[root@localhost ~]# hostnamectl set-hostname master
[root@localhost ~]# su
[root@master ~]# vim /etc/my.cnf
log-bin=master-bin          //二进制文件
server-id=11                     //身份id
log-slave-updates=true   //允许中继日志进行同步更新
[root@master ~]# systemctl restart mysqld
[root@master ~]# ls /usr/local/mysql/data/
auto.cnf        ibdata1      ib_logfile1  master-bin.000001  mysql               sys           //二进制文件
ib_buffer_pool  ib_logfile0  ibtmp1       master-bin.index   performance_schema  //日志索引位置文件
[root@master ~]# mysql -uroot -pabc123
mysql> grant replication slave on *.* to 'myslave'@'192.168.200.%' 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 |              |                  |                   |
+-------------------+----------+--------------+------------------+-------------------+
之后不进行任何操作

从服务器192.168.200.80

[root@localhost mysql]# iptables -F
[root@localhost mysql]# setenforce 0
[root@localhost mysql]# hostnamectl set-hostname slave1
[root@localhost mysql]# su
[root@slave1 mysql]# vim /etc/my.cnf
server-id=22
relay-log=relay-log-bin                                               //从主服务器上同步日志文件记录到本地
relay-log-index=slave-relay-bin.index                       //定义relay-log的位置和名称
[root@slave1 mysql]# systemctl restart mysqld
[root@slave1 mysql]# ls /usr/local/mysql/data/                         //还没启用所以无日志文件
auto.cnf        ibdata1      ib_logfile1  mysql               sys
ib_buffer_pool  ib_logfile0  ibtmp1       performance_schema
[root@slave1 mysql]# mysql -uroot -pabc123
mysql> change master to master_host='192.168.200.90',master_user='myslave',master_password='123456',master_log_file='master-bin.000001',master_log_pos=604;
mysql> start slave;
mysql> show slave status\G;
            Slave_IO_Running: Yes               //IO线程开启
            Slave_SQL_Running: Yes            //SQL线程开启

从服务器192.168.200.110

[root@localhost mysql]# iptables -F
[root@localhost mysql]# setenforce 0
[root@localhost mysql]# hostnamectl set-hostname slave2
[root@localhost mysql]# su
[root@slave2 mysql]# vim /etc/my.cnf
server-id=23
relay-log=relay-log-bin
relay-log-index=slave-relay-bin.index
[root@slave2 mysql]# systemctl restart mysqld
[root@slave2 mysql]# mysql -uroot -pabc123
mysql> change master to master_host='192.168.200.90',master_user='myslave',master_password='123456',master_log_file='master-bin.000001',master_log_pos=604;
mysql> start slave;
mysql> show slave status\G;
            Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

验证主从同步

主服务器
mysql> show databases;
mysql> create database school;
mysql> use school;
mysql> create table info (id int(4),name char(10));
mysql> insert into info values (1,'zhangsan'),(2,'lisi');
mysql> select * from info;
+------+----------+
| id   | name     |
+------+----------+
|    1 | zhangsan |
|    2 | lisi     |
+------+----------+
从服务器
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| school             |
| sys                |
+--------------------+
mysql> use school;
mysql> show tables;
+------------------+
| Tables_in_school |
+------------------+
| info             |
+------------------+
mysql> select * from info;
+------+----------+
| id   | name     |
+------+----------+
|    1 | zhangsan |
|    2 | lisi     |
+------+----------+

2、配置amoeba服务器

[root@localhost ~]# hostnamectl set-hostname amoeba
[root@localhost ~]# su
[root@amoeba ~]# iptables -F
[root@amoeba ~]# setenforce 0
[root@amoeba ~]# cd /opt/
[root@amoeba opt]# rz -E           //xshell拖入文件
rz waiting to receive.
[root@amoeba opt]# ls
amoeba-mysql-binary-2.2.0.tar.gz  jdk-6u14-linux-x64.bin  rh
[root@amoeba opt]# cp jdk-6u14-linux-x64.bin /usr/local/
[root@amoeba opt]# cd /usr/local/
[root@amoeba local]# chmod +x jdk-6u14-linux-x64.bin     //给执行权限
[root@amoeba local]# ls
bin  etc  games  include  jdk-6u14-linux-x64.bin  lib  lib64  libexec  sbin  share  src
[root@amoeba local]#./ jdk-6u14-linux-x64.bin
Do you agree to the above license terms? [yes or no]
yes
Press Enter to continue.....回车回车
[root@amoeba local]# mv jdk1.6.0_14/ jdk1.6
[root@amoeba local]# 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
[root@amoeba local]# source /etc/profile
[root@amoeba local]# mkdir /usr/local/amoeba    //创建目录
[root@amoeba local]# cd /opt/
[root@amoeba opt]# ls
amoeba-mysql-binary-2.2.0.tar.gz  jdk-6u14-linux-x64.bin  rh
[root@amoeba opt]# tar zxvf amoeba-mysql-binary-2.2.0.tar.gz -C /usr/local/amoeba/
[root@amoeba opt]# cd /usr/local/
[root@amoeba local]# ls amoeba/
benchmark  bin  changelogs.txt  conf  lib  LICENSE.txt  README.html
[root@amoeba local]# chmod -R 755 /usr/local/amoeba/
[root@amoeba local]# /usr/local/amoeba/bin/amoeba
amoeba start|stop                                   //说明安装成功

在主从三台服务器上面分别配置下面的命令，一台主服务器192.168.200.90两台从服务器192.168.200.80；192.168.200.110

mysql> grant all on *.* to test@'192.168.200.%' identified by '123.com';     //授权一个test账号可以登录主从服务器
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)

回到amoeba服务器

[root@amoeba local]# cd /usr/local/amoeba/
[root@amoeba amoeba]# ls
benchmark  bin  changelogs.txt  conf  lib  LICENSE.txt  README.html
[root@amoeba amoeba]# cd conf/
[root@amoeba conf]# ls
access_list.conf  dbserver.dtd   functionMap.xml  rule.dtd
amoeba.dtd        dbServers.xml  log4j.dtd        ruleFunctionMap.xml
amoeba.xml        function.dtd   log4j.xml        rule.xml
[root@amoeba conf]# vim amoeba.xml 
30                   <property name="user">amoeba</property>
31 
32                   <property name="password">123456</property>
115                 <property name="defaultPool">master</property>
116 
117                 
118                 <property name="writePool">master</property>             //读
119                 <property name="readPool">slaves</property>                //写
[root@amoeba conf]# vim dbServers.xml 
 23                         <property name="schema">mysql</property>   //一定要改成mysql，5.7没有test表了，5.5版本不要改
 26                         <property name="user">test</property>
 27 
 28                         <!--  mysql password -->
 29                         <property name="password">123.com</property>
 <dbServer name="master"  parent="abstractServer">             //确定master节点
 46                 <factoryConfig>
 47                         <!-- mysql ip -->
 48                         <property name="ipAddress">192.168.200.90</property>
 52         <dbServer name="slave1"  parent="abstractServer">
 53                 <factoryConfig>
 54                         <!-- mysql ip -->
 55                         <property name="ipAddress">192.168.200.80</property>
 56                 </factoryConfig>
 57         </dbServer>
 59          <dbServer name="slave2"  parent="abstractServer">     //复制粘贴slave1
60                  <factoryConfig>
61                          <!-- mysql ip -->
62                          <property name="ipAddress">192.168.200.110</property>
63                  </factoryConfig>
64          </dbServer>
66         <dbServer name="slaves" virtual="true">           //将slaves和slave1与slave2进行绑定，把slave1与slave2放入slaves地址池子
 67                 <poolConfig class="com.meidusa.amoeba.server.MultipleServerPool">
 68                         <!-- Load balancing strategy: 1=ROUNDROBIN , 2=WEIGHTBASED , 3=HA-->
 69                         <property name="loadbalance">1</property>
 70 
 71                         <!-- Separated by commas,such as: server1,server2,server1 -->
 72                         <property name="poolNames">slave1,slave2</property>
 [root@amoeba conf]# /usr/local/amoeba/bin/amoeba start&    //启动服务
[1] 70109
[root@amoeba conf]# log4j:WARN log4j config load completed from file:/usr/local/amoeba/conf/log4j.xml
2020-08-27 10:06:51,923 INFO  context.MysqlRuntimeContext - Amoeba for Mysql current versoin=5.1.45-mysql-amoeba-proxy-2.2.0
log4j:WARN ip access config load completed from file:/usr/local/amoeba/conf/access_list.conf
2020-08-27 10:06:57,180 INFO  net.ServerableConnectionManager - Amoeba for Mysql listening on 0.0.0.0/0.0.0.0:8066.            //监听8066端口
2020-08-27 10:06:57,182 INFO  net.ServerableConnectionManager - Amoeba Monitor Server listening on /127.0.0.1:61767.

重新用一个节点连接amoeba服务器，查看端口

[root@amoeba ~]# netstat -antp | grep java
tcp6       0      0 127.0.0.1:61767         :::*                    LISTEN      70109/java          
tcp6       0      0 :::8066                 :::*                    LISTEN      70109/java              //端口开启  
tcp6       0      0 192.168.200.40:47836    192.168.200.110:3306    ESTABLISHED 70109/java          
tcp6       0      0 192.168.200.40:47416    192.168.200.80:3306     ESTABLISHED 70109/java          
tcp6       0      0 192.168.200.40:39544    192.168.200.90:3306     ESTABLISHED 70109/java          
tcp6       0      0 192.168.200.40:47420    192.168.200.80:3306     ESTABLISHED 70109/java 

配置完成，开一台win10虚拟机验证试验

[root@localhost ~]# hostnamectl set-hostname client
[root@localhost ~]# su
[root@client ~]# iptables -F
[root@client ~]# setenforce 0
[root@client ~]# yum -y install mysql
[root@client ~]# mysql -u amoeba -p123456 -h 192.168.200.40 -P8066   //连接amoeba服务器
MySQL [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| school             |
| sys                |
+--------------------+
5 rows in set (0.01 sec)

验证搭建的正确性

客户机创建数据库
MySQL [(none)]> create database student;
Query OK, 1 row affected (0.00 sec)
MySQL [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| school             |
| student            |
| sys                |
+--------------------+
6 rows in set (0.00 sec)
master服务器查看，出现student库
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| school             |
| student            |
| sys                |
+--------------------+
6 rows in set (0.00 sec)
slave1,slave2服务器查看，出现student库
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| school             |
| student            |
| sys                |
+--------------------+
6 rows in set (0.00 sec)

关闭主从同步，验证读写分离

主服务器
mysql> use student;
mysql> create table zang (id int(10),name varchar(10),address varchar(20));
mysql> show tables;
+-------------------+
| Tables_in_student |
+-------------------+
| zang              |
+-------------------+
从服务器
mysql> stop slave;
mysql> show slave status\G;
 Slave_IO_Running: No
            Slave_SQL_Running: No
客户端
MySQL [(none)]> use student;
MySQL [student]> select * from zang;
Empty set (0.01 sec)
MySQL [student]> insert into zang values (1,'zhang','this is master');
Query OK, 1 row affected (0.00 sec)
MySQL [student]> select * from zang;    //读不到
Empty set (0.01 sec)
master服务器
mysql> select * from zang;
+------+-------+----------------+
| id   | name  | address        |
+------+-------+----------------+
|    1 | zhang | this is master |
+------+-------+----------------+
1 row in set (0.00 sec)
从1服务器
mysql> insert into zang values(2,'zhang','this is slave1');
从2服务器
mysql> insert into zang values(3,'zhang','this is slave2');
客户端服务器去读
轮询显示
MySQL [student]> select * from zang;
+------+-------+----------------+
| id   | name  | address        |
+------+-------+----------------+
|    2 | zhang | this is slave1 |
+------+-------+----------------+
MySQL [student]> select * from zang;
+------+-------+----------------+
| id   | name  | address        |
+------+-------+----------------+
|    3 | zhang | this is slave2 |
+------+-------+----------------+