mysql主从复制与读写分离

一张图安排明明白白—>系好安全带上高速:

一、主从复制概念理解:

主从复制分为：半同步复制和异步复制，主从复制默认是异步复制
主从复制原理：
MySQL的主从复制是一个异步的复制过程（虽然一般情况下感觉是实时的），数据将从一个MySQL数据库（Master）复制到另一个MySQL数据库（Slave），在Master和Slave之间实现整个主从复制的过程是由三个线程参与完成的。其中两个线程（SQL线程和IO线程）在Slave端，另一个线程（I/O线程）在Master端。
要实现MySQL的主从复制，首先必须打开Master端的binlog记录功能，否则就无法实现。binlog: binary log，是主库中保存所有更新事件日志的二进制文件。因为整个复制过程实际上就是Slave从Master端获取binlog日志，然后在Slave上以相同顺序执行获取的binlog日志中的记录的各种SQL操作。
主从同步延迟
主库有数据写入之后，同时也写入在binlog(二进制日志文件)中，从库是通过binlog文件来同步数据的，这期间会有一定时间的延迟，可能是1秒，如果同时有大量数据写入的话，时间可能更长。
这会导致什么问题呢？比如有一个付款操作，你付款了，主库是已经写入数据，但是查询是到从库查，从库里还没有你的付款记录，所以页面上查询的时候你还没付款。那可不急眼了啊，吞钱了这还了得！打电话给客服投诉！
所以为了解决主从同步延迟的问题有以下几个方法：
1、二次读取
二次读取的意思就是读从库没读到之后再去主库读一下，只要通过对数据库访问的API进行封装就能实现这个功能。很简单，并且和业务之间没有耦合。但是有个问题，如果有很多二次读取相当于压力还是回到了主库身上，等于读写分离白分了。而且如有人恶意攻击，就一直访问没有的数据，那主库就可能爆了。
2、写之后的马上的读操作访问主库
也就是写操作之后，立马的读操作指定访问主库，之后的读操作采取访问从库。这就等于写死了，和业务强耦合了。
3、关键业务读写都由主库承担，非关键业务读写分离
类似付钱的这种业务，读写都到主库，避免延迟的问题，但是例如改个头像啊，个人签名这种比较不重要的就读写分离，查询都去从库查，毕竟延迟一下影响也不大，不会立马打客服电话哈哈。
分配机制的考虑
分配机制的考虑也就是怎么制定写操作是去主库写，读操作是去从库读。
一般有两种方式：代码封装、数据库中间件。
1、代码封装 代码封装的实现很简单，就是抽出一个中间层，让这个中间层来实现读写分离和数据库连接。讲白点就是搞个provider封装了save,select等通常数据库操作，内部save操作的dataSource是主库的，select操作的dataSource是从库的。
优点：就是实现简单，并且可以根据业务定制化变化，随心所欲。
缺点：就是是如果哪个数据库宕机了，发生主从切换了之后，就得修改配置重启。并且如果你的系统很大，一个业务可能包含多个子系统，一个子系统是java写的一个子系统用go写的，这样的话得分别为不同语言实现一套中间层，重复开发。 

2、数据库中间件 就是有一个独立的系统，专门来实现读写分离和数据库连接管理，业务服务器和数据库中间件之间是通过标准的SQL协议交流的，所以在业务服务器看来数据库中间件其实就是个数据库。
优点：因为是通过sql协议的所以可以兼容不同的语言不需要单独写一套，并且有中间件来实现主从切换，业务服务器不需要关心这点。
缺点：多了一个系统其实就等于多了一个关心。。如果数据库中间件挂了的话对吧，而且多了一个系统就等于多了一个瓶颈，所以对中间件的性能要求也高，并且所有的数据库操作都要经过它。并且中间件实现很复杂，难度比代码封装高多了。
但是有开源的数据库中间件例如Mysql Proxy,Mysql Route,Atlas,Mycat。

二、主从复制实验:

三台服务器
Master 192.168.254.240
Slave1 192.168.254.241
Slave2 192.168.254.242
A、关闭防火墙，selinux,配置hosts文件解析
B、 下载Mysql源
wget https://dev.mysql.com/get/mysql57-community-release-el7-9.noarch.rpm
C、repo安装 rpm -ivh mysql57-community-release-el7-9.noarch.rpm 
D、删除旧的mysql   rpm  -qa |grep mysql ,如果有就删除 rpm -e xxxxx
E、开始安装Mysql
yum -y install mysql-community-server
D、三个节点都启动mysql
[root@master ~]# systemctl start mysqld
[root@master ~]# systemctl status mysqld

E、查看mysql版本

F、设置新密码(重要一步)登陆每一个节点进行

出现上面这种不要慌张，我们修改配置文件，进行免密登陆
[root@master ~]# vi /etc/my.cnf
添加：skip-grant-tables=1

[root@master ~]# systemctl restart mysqld

看上图操作
注释掉免密登陆，从启动mysql,在验证登陆。

如果时间不同步，数据是不会同步的。
G、配置master主服务器

[root@master ~]# tail -3 /etc/my.cnf
server-id = 11
log-bin=master-bin
log-slave-updates=true
从新启动mysql
[root@master ~]# systemctl restart mysqld
登陆mysql服务器授权

上图出现授权时候密码简单，我们做以下配置
mysql> set global validate_password_length=4;
Query OK, 0 rows affected (0.00 sec)
mysql> set global validate_password_policy=0;
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
授权设置密码:
mysql> grant replication slave on *.* to 'myslave'@'192.168.254.%' identified by '000000';
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
查看主服务器的状态:

file列显示的日志名，position 列显示偏移量
H、配置slave1/2从服务器

[root@slave1 ~]# tail -3 /etc/my.cnf
server-id = 22   ## 这里的ID不能与主服务器相同
relay-log=relay-log-bin  ## 从主服务器上同步日志到本地
relay-log-index=slave-relay-bin.index  ## 定义relay-log的位置和名称
从新启动两台从服务器mysqld
systemctl restart mysqld
登陆mysql服务器，进行根据主服务器的结果来更改下面的master_log_file 和master_log_post 的参数

mysql> change master to master_host='192.168.254.240',master_user='myslave',master_password='000000',master_log_file='master-bin.000001',master_log_pos=756;
Query OK, 0 rows affected, 2 warnings (0.02 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
启动同步
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
查看slave值确保两个值都为yes

I、验证主从复制功能
在主服务器创建数据库，查看两个从服务器是否同步

三、读写分离实验：

这里使用mysql-proxy,新增一台服务器 192.168.254.243
A、关闭防火墙，selinux、hosts映射，全部一致

B、官网选择mysql-proxy版本

yum install wget -y
[root@proxy ~]# wget https://downloads.mysql.com/archives/get/p/21/file/mysql-proxy-0.8.5-linux-glibc2.3-x86-64bit.tar.gz
[root@proxy ~]# mkdir -p /usr/local/mysql-proxy
[root@proxy ~]# tar -zxvf mysql-proxy-0.8.5-linux-glibc2.3-x86-64bit.tar.gz -C /usr/local/mysql-proxy
[root@proxy ~]# cd /usr/local/mysql-proxy/mysql-proxy-0.8.5-linux-glibc2.3-x86-64bit
[root@proxy mysql-proxy-0.8.5-linux-glibc2.3-x86-64bit]# mkdir lua   #创建脚本存放目录
[root@proxy mysql-proxy-0.8.5-linux-glibc2.3-x86-64bit]# mkdir logs #创建日志目录
[root@proxy mysql-proxy-0.8.5-linux-glibc2.3-x86-64bit]# cp share/doc/mysql-proxy/rw-splitting.lua ./lua #复制读写分离的文件
[root@proxy mysql-proxy-0.8.5-linux-glibc2.3-x86-64bit]# cp /lib/mysql-proxy/lua/admin.lua ./lua #复制管理脚本
[root@proxy ~]# vi /etc/mysql-proxy.cnf #创建配置文件
[mysql-proxy]
user=root #运行mysql-proxy用户
admin-username=admin #主从mysql共有的用户   
admin-password=admin #用户的密码
proxy-address=192.168.254.243:4040 #mysql-proxy运行ip和端口，不加端口，默认4040
admin-address = 192.168.254.243:4041 #管理端口
proxy-read-only-backend-addresses=192.168.254.241:3306,192.168.254.242:3306 #指定后端从slave读取数据
proxy-backend-addresses=192.168.254.240:3306 #指定后端主master写入数据
proxy-lua-script=/usr/local/mysql-proxy/mysql-proxy-0.8.5-linux-glibc2.3-x86-64bit/lua/rw-splitting.lua #指定读写分离配置文
件位置
admin-lua-script=/usr/local/mysql-proxy/mysql-proxy-0.8.5-linux-glibc2.3-x86-64bit/lua/admin.lua #指定管理脚本
log-file=/usr/local/mysql-proxy/mysql-proxy-0.8.5-linux-glibc2.3-x86-64bit/logs/mysql-proxy.log #日志位置
log-level=info #定义log日志级别，由高到低分别有(error|warning|info|message|debug)
daemon=true    #以守护进程方式运行
keepalive=true #mysql-proxy崩溃时，尝试重启
[root@proxy ~]# chmod 660 /etc/mysql-proxy.cnf 
[root@proxy ~]# vi /usr/local/mysql-proxy/mysql-proxy-0.8.5-linux-glibc2.3-x86-64bit/lua/rw-splitting.lua 

启动mysql-proxy
[root@proxy ~]# /usr/local/mysql-proxy/mysql-proxy-0.8.5-linux-glibc2.3-x86-64bit/bin/mysql-proxy --defaults-file=/etc/mysql-proxy.cnf 

测试读写分离：
在主服务器创建proxy用户用于mysql-proxy使用，从服务器也会同步这个操作
mysql> grant all on *.* to 'proxy'@'192.168.254.243' identified by '000000';
使用客户端连接mysql-proxy

在主机上下载一个mysql客户端做测试：
 yum -y install mysql
客服端连接mysql-proxy
[root@proxy ~]# mysql -uproxy -p000000 -h192.168.254.243 --port=4040

登陆管理端口查看:

执行语句测试，在master,slave1,slave2
yum install tcpdump -y 
[root@master ~]# tcpdump -i ens33 -nn -XX ip dst 192.168.254.240 and tcp dst port 3306  
[root@slave1 ~]# tcpdump -i ens33 -nn -XX ip dst 192.168.254.241 and tcp dst port 3306 
[root@slave2 ~]# tcpdump -i ens33 -nn -XX ip dst 192.168.254.242 and tcp dst port 3306 
目的:抓包查看数据
在mysql-proxy主机上分别登陆master,slave1,slave2
[root@proxy ~]# mysql -uproxy -p000000 -h192.168.254.240
[root@proxy ~]# mysql -uproxy -p000000 -h192.168.254.241
[root@proxy ~]# mysql -uproxy -p000000 -h192.168.254.242
都执行sql语句，观察抓包变化

在mysql-proxy多执行几遍执行sql语句在登陆查看状态
[root@proxy ~]# mysql -uproxy -p000000 -h192.168.254.243 --port=4040 -e "select * from mysql.user;"

五、实验结束，主从复制读写分离配置完成。后续在进行优化。