MySQL主从复制，读写分离

目的

实际生产环境中，一般通过主从复制同步数据，通过读写分离提高负载，两者配合使用提高数据库的并发性能。

主从复制

• 主从复制是实现读写分离的基础。MySQL支持的复制类型有三种：
  ①基于语句的复制：在主服务器上执行的sql语句，在从服务器上会执行同样的语句。Mysql默认采用基于语句的复制，效率比较高，但是有时不能实现精准复制。
  ② 基于行的复制：把改变的内容复制过去，而不是把命令在从服务器上执行一遍。
  ③混合类型的复制：默认采用基于语句的复制，一旦发现基于语句的复制不能精准复制时，就会采用基于行的复制。

• 主从复制过程：
  ①当进行insert、update、delete操作时，Master会按顺序将其写入到binlog中。
  ②Salve从库连接Master主库，Master有多少个Slave就会创建多少个binlog dump线程。
  ③当Master节点的binlog发生变化时，binlog dump 线程会通知所有的salve节点，并将相应的binlog内容推送给Slave节点。
  ④I/O线程接收到 binlog 内容后，将内容写入到本地的 relay-log（中继日志）。
  ⑤SQL线程读取I/O线程写入的relay-log，并且根据 relay-log 的内容对从数据库做对应的操作。
  

读写分离

读写分离：Master服务器负责写入数据，Salve服务器负责读取数据。

• 常见的MySQL读写分离有两种：
  ①基于程序代码内部实现。 在代码中根据select 、insert进行路由分类，这类方法也是目前生产环境下应用最广泛的。优点是性能较好，因为程序在代码中实现，不需要增加额外的硬件开支，缺点是需要开发人员来实现，运维人员无从下手。
  ②基于中间代理层实现。
  代理一般介于应用服务器和数据库服务器之间，代理数据库服务器接收到应用服务器的请求后根据判断转发到对应的后端数据库。
  常用代理：
  a. Mysql-proxy：其为mysql的开源项目，通过其自带的lua脚本进行sql判断，虽然是mysql官方产品，但是mysql官方并不建议其使用到生产环境中。缺点：仅为测试版本，不稳定，而且bug不少。
  b. Amoeba：由陈思儒开发，该程序由Java语言进行开发。这个软件致力于mysql的分布式数据库前端代理层，它主要为应用层访问mysql提供sql路由功能。Amoeba能够完成多数据源的高可用、负载均衡、数据切片等功能。
  c. mycat：MyCat是一个开源的分布式数据库系统，是一个实现了MySQL协议的服务器，可以看作是一个数据库代理。其核心功能是分表分库，即将一个大表水平分割为N个小表，存储在后端MySQL服务器里或者其他数据库里。可以用来实现读写分离。

测试

1. 环境
   ① linux CentOS-7 虚拟机3台（1主2从）
   主（192.168.11.224）
   从1（192.168.11.225）
   从2（192.168.11.226）
   Amoeba（192.168.11.227）
   ②MySQL 5.7

2. 准备工作
   ①配置静态IP
   ②安装MySQL5.7
   注意：数据库密码设为Asd@123，必须满足复杂度要求（有大小写字母、数字和符号），否则后续会踩很多坑。

主从复制

3. MySQL（主）
   ①校准服务器时间
   [root@localhost ~]# yum -y install ntp
   [root@localhost ~]# vi /etc/ntp.conf

   #Hosts on local network are less restricted.
   #restrict 192.168.1.0 mask 255.255.255.0 nomodify notrap
   server 192.168.11.227       //添加
   fudge 192.168.11.227 stratum 8  //添加
   

   [root@localhost ~]# service ntpd restart

   ②开端口（注意关闭防火墙或防火墙开好端口）
   [root@localhost ~]# iptables -I INPUT -p udp --dport 123 -j ACCEPT
   [root@localhost ~]# iptables -I INPUT -p udp --dport 3306 -j ACCEPT

   ③修改 /etc/my.cnf 配置文件
   [root@localhost ~]# vim /etc/my.cnf

   server-id       = 224        #mysql数据的唯一标示（不能重复）
   log-slave-updates=true      #允许连级复制   （增加）
   log-bin=master-bin      #二进制文件名（修改）
   

   [root@localhost ~]# service mysqld restart

   ④登陆mysql，授予其他服务器访问权限
   [root@localhost ~]# mysql -u root -p
   mysql> GRANT REPLICATION SLAVE ON . TO ‘root’@‘192.168.11.%’ IDENTIFIED BY ‘Asd@123’;
   mysql> flush privileges;
   mysql> show master status; （记住 | master-bin.000003 | 850 | ，后面从服务器配置时需要使用）
   ±------------------±---------±-------------±-----------------±------------------+
   | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
   ±------------------±---------±-------------±-----------------±------------------+
   | master-bin.000003 | 850 | | | |
   ±------------------±---------±-------------±-----------------±------------------+
   1 row in set (0.00 sec)

4. MySQL（从1，从2）
   注意：如果是虚拟机克隆的mysql环境，则需要删除UUID文件，保证所有数据库的UUID不同；rm -rf /var/lib/mysql/auto.cnf 。

   ①校准服务器时间（与主服务器校准）
   [root@localhost ~]# yum -y install ntpdate
   [root@localhost ~]# /usr/sbin/ntpdate 192.168.11.224

   ②修改mysql配置

   server-id       = 225                #不能与其他实例重复(从1:225；从2:226)
   log-bin=mysql-bin               #二进制日志文件名 修改
   relay-log=relay-log-bin             #复制过来的二进制文件名，增加
   relay-log-index=slave-relay-bin.index       #中继日志存放的文件名称，增加环境
   

   [root@localhost ~]# service mysqld restart

   ③登陆mysql配置同步（最好手敲，复制是命令会出现差异）
   mysql> change master to master_host=‘192.168.11.224’,master_user=‘root’,master_password=‘Asd@123’,master_log_file=‘master-bin.000003’, master_log_pos=850;
   mysql> start slave;

   ④检查配置
   mysql> show slave status\G （必须均为Yes，如不是，则查看后面的错误信息进行解决）
   

5. 验证主从复制
   ①主从服务器都查看当前数据库：
   mysql> show databases;
   ±-------------------+
   | Database |
   ±-------------------+
   | information_schema |
   | mysql |
   | performance_schema |
   | sys |
   | test_zc |
   ±-------------------+
   5 rows in set (0.00 sec)

   ②主服务器创建数据库，create database test;
   ③从服务器查看库的变化。正常情况下会同步成功的，不成功的请(stop slave)并重新进行同步配置。

读写分离

后续补充

踩坑：

1. linux环境下mysql数据库密码复杂度要达到要求，最好要携带大小写字母、特殊符号、数字，超过8位；
2. 虚拟机搭建时，如果是克隆的mysql环境，则需要删除UUID文件，保证其不同；
3. 防火墙注意开端口，保证集群内的其他服务器能够连到主服务器的mysql；
4. 检查配置时，两个值必须都是Yes；
5. 运行虚拟机，windows10蓝屏。解决先删除所有虚拟机，后删除VMWare，然后安装最新VMWare(15以上版本)；

配置参考：
https://www.cnblogs.com/luckcs/articles/2543607.html
https://blog.51cto.com/13555423/2068071（有错误）
https://blog.csdn.net/alitech2017/article/details/108241782
https://blog.csdn.net/afgasdg/article/details/79808458
https://blog.csdn.net/dsl59741/article/details/107361800