MySQL主从复制与读写分离

实际的生产环境中，如果对数据库的读和写都在同一个数据库服务器中操作，无论是在安全性、高可用性还是高并发等各个方面都是完全不能满足实际需求的

因此，一般来说都是通过主从复制(Master-Slave)来同步数据，再通过读写分离来提升数据库并发负载能力的方案来进行部署与实施

• 一台主 MVSQL服务器带两台从 MVSQL服务器做数据复制，前端应用在进行数据库写操作时，对主服务器进行操作，在进行数据库读操作时，对两台从服务器进行操作，这样大量减轻了对主服务器的压力

• MySQL主从复制原理

  • MySQL的主从复制和 MySQL的读写分离两者有着紧密联系，首先要部署主从复制，只有主从复制完成了，才能在此基础上进行数据的读写分离
  • MySQL 支持的复制类型
    • 基于语句的复制。在主服务器上执行的 SQL语句，在从服务器上执行同样的语句基于语句的复制。在主服务器上执行的 SQL语句，在从服务器上执行同样的语句。MySQL 默认采用基于语句的复制，效率比较高
    • 基于行的复制。把改变的内容复制过去，而不是把命令在从服务器上执行一遍基于行的复制。把改变的内容复制过去，而不是把命令在从服务器上执行一遍
    • 混合类型的复制。默认采用基于语句的复制，一旦发现基于语句无法精确复制时，就会采用基于行的复制
• 复制工作过程

1. 在每个事务更新数据完成之前，Master将这些改变记录进二进制日志。写入二进制日志完成后，Master通知存储引擎提交事务
2. Slave 将 Master 的 Binary log 复制到其中继日志(Relay log)。首先，Slave 开始一个工作线程--1/0 线程，I/O 线程在 Master 上打开一个普通的连接，然后开始 Binlogdump process。Binlog dump process 从 Master 的二进制日志中读取事件，如果已经跟上Master，它会睡眠并等待 Master 产生新的事件。I/0 线程将这些事件写入中继日志
3. SQL slave thread(SQL 从线程)处理该过程的最后一步。SQL 线程从中继日志读取事件，并重放其中的事件而更新 Slave 数据，使其与 Master 中的数据保持一致。只要该线程与 I/O 线程保持一致，中继日志通常会位于 OS 的缓存中，所以中继日志的开销很小

• 复制过程有一个很重要的限制，即复制在 Slave 上是串行化的，也就是说 Master 上的并行更新操作不能在 Slave ,上并行操作

1. 基于程序代码内部实现
   1. 在代码中根据 select、insert 进行路由分类,这类方法也是目前生产环境应用的优点是性能较好，因为在程序代码中实现，不需要增加额外的设备作为硬件开支;缺点是需要开发人员来实现，运维人员无从下手
2. 基于中间代理层实现
   1. 代理一般位于客户端和服务器之间,代理服务器接到客户端请求后通过判断后转发到后端数据库，有两个代表性程序

搭建MySQL主从复制

主机	操作系统	IP 地址	主要软件
Master	CentOS 7.3 x86 64	192.168.1.101	boost_1_59_0.tar.gz, mysql-5.7.28.tar.gz, cmake-3.16.2.tar.g2
Slave1	CentOS 7.3 x86 64	192.168.1.102	boost_1_59_0.tar.gz, mysql-5.7.28.tar.gz, cmake-3.16.2.tar.g2
Slave2	CentOS 7.3 x86 64	192.168.1.103	boost_1_59_0.tar.gz, mysql-5.7.28.tar.gz cmake-3.16.2.tar.g2
Amoeba	CentOS 7.3 x86 64	192.168.1.110	amoeba-mysql-binary-2.2.0.tar.gz, idk-6u14-linux-x64.bin
客户端	CentOS 7.3 x86 64	192.168.1.111

1：关闭所有服务器的firewalld

[root@localhost ~]# setenforce 0
[root@localhost ~]# systemctl stop firewalld

2：建立时间同步环境

[root@localhost ~]# yum -y install ntp
[root@localhost ~]# vim /etc/ntp.conf
添加：
server 127.127.1.0
fudge 127.127.1.0 stratum 8
[root@localhost ~]# systemctl restart ntpd
[root@localhost ~]# systemctl enable ntpd

3：在从节点上进行时间同步

[root@localhost ~]# yum -y install ntp
[root@localhost ~]# ntpdate 192.168.10.102

4：在所有服务器上安装mysql数据库

5：配置mysql master主服务器

[root@localhost ~]# vi /etc/my.cnf
在[mysqld]模块中修改或添加：server-id=11           ##修改
log-bin=master-bin      ##修改
log-slave-updates=true    ##添加    （可不用添加）#Slave可以是其他 Slave 的 Master，从而扩散 Master 的更新
binlog-format = MIXED//1：基于语句的复制。 在主服务器上执行的 SQL 语句，在从服务器上执行同样的语句。配置：
binlog_format = STATEMENT
//2：基于行的复制。把改变的内容复制过去，而不是把命令在从服务器上执行一遍，从 MySQL 5.0开始支持，配置：
binlog_format = ROW
//3：混合类型的复制。默认采用基于语句的复制，一旦发现基于语句的无法精确的复制时，就会采用基于行的复制,配置：
binlog_format = MIXED
//以下都可以配置
//binlog-ignore-db=test      #不记录指定的数据库的二进制日志
//replicate-ignore-db=test    #设置不需要同步的库
//binlog_cache_size = 1M    #日志缓存的大小
//expire_logs_days=3       #自动过期清理日志的天数[root@localhost ~]# systemctl restart mysqld
[root@localhost ~]# mysql -u root -p
mysql> grant replication slave on *.* to 'myslave'@'192.168.10.%' identified by '123456' ;
mysql> flush privileges;
mysql> show master status;

6：从服务器的配置

[root@localhost ~]# vi /etc/my.cnf
在[mysqld]模块中修改或添加：server-id  = 22            ##修改，值不能和其他mysql服务器重复
relay-log=relay-log-bin            ##添加（可不指定）
relay-log-index=slave-relay-bin.index    ##添加（可不指定）
//--relay-log=name    中继日志的文件的名字 
//--relay-log-index=name.index      MySQL slave 在启动时需要检查relay-log-index 文件中的relay-log信息，此处定义该索引文件的名字[root@localhost ~]# systemctl restart mysqld
[root@localhost ~]# mysql -u root -p
mysql> change master to master_host='192.168.10.101',master_user='myslave',master_password='123456',master_log_file=
'master-bin.000001',master_log_pos=337;//Slave 的 IO 线程接收到信息后，将接收到的日志内容依次写入到 Slave 端的Relay Log文件(relay-log-bin.xxxxxx)的最末端，
并将读取到的Master端的master-bin的文件名和位置记录到master- info文件中，
以便在下一次读取的时候能够清楚的告诉Master“我需要从某个master-bin的哪个位置开始往后的日志内容，请发给我mysql> start slave;
mysql> show slave status\G           ##注意后面不要加分号

7：验证主从复制

（1）在主从服务器上分别查询数据库
mysql> show databases;
（2）在主服务器上创建数据库
mysql> create database db_test;
Query OK, 1 row affected (0.00 sec)
（3）在从服务器上再次查询数据库，可以看到从服务器上也有了db_test数据库了
mysql> show databases;

扩展：主主复制

1）：在slave1上修改my.cnf，在[mysqld]模块添加

server-id=11
log-bin=master-bin
log-slave-updates=true        //原先主服务器也可以添加
并重启mysql

2）：在slave1上执行以下命令创建一个授权用户，用于在slave2上链接slave1

mysql> grant replication slave on *.* to 'myslave'@'192.168.10.%' identified by '123456' ;
mysql> flush privileges;
mysql> show master status;

3）：show出来的信息做为slave2上连接slave1时的参数

重启Mysql服务不会影响主从关系

搭建Mysql读写分离

1：在主机amoeba上安装java环境

[root@localhost ~]# chmod +x jdk-6u14-linux-x64.bin
[root@localhost ~]# ./jdk-6u14-linux-x64.bin
[root@localhost ~]# mv jdk1.6.0_14/ /usr/local/jdk1.6
[root@localhost ~]# vi /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:$JAVA_HOME/bin
export AMOEBA_HOME=/usr/local/amoeba/
export PATH=$PATH:$AMOEBA_HOME/bin[root@localhost local]# source /etc/profile
[root@localhost local]# java -version            ##查询版本，确定java安装成功

2：安装并配置amoeba

[root@localhost local]# mkdir /usr/local/amoeba
[root@localhost ~]# tar zxf amoeba-mysql-binary-2.2.0.tar.gz -C /usr/local/amoeba/
[root@localhost ~]# chmod -R 755 /usr/local/amoeba/
[root@localhost ~]# /usr/local/amoeba/bin/amoeba
amoeba start|stop       ##有此提示表示成功

3：配置amoeba读写分离

（1）在三个mysql服务器中开放权限给amoeba访问（只在master中即可，会复制到slave中）
mysql> grant all on *.* to test@'192.168.10.%' identified by '123.com';（2）在amoeba上配置amoeba.xml文件
[root@localhost amoeba]# systemctl stop firewalld[root@localhost ~]# cd /usr/local/amoeba/conf
[root@localhost conf]# vi amoeba.xml
修改部分，此处设置的是mysql客户端连接amoeba时用的账号和密码...<property name="user">amoeba</property>                 ##30行<property name="password">123456</property>               ##32行...<property name="defaultPool">master</property>             ##115行<property name="writePool">master</property>             ##118行<property name="readPool">slaves</property>    ##119行此处的注释去掉（3）编辑dbServer.xml文件
[root@localhost conf]# vi dbServers.xml<property name="user">test</property>         ##26行<property name="password">123.com</property>  ##29行，去掉注释符...<dbServer name="master"  parent="abstractServer">         ##45行<factoryConfig><!-- mysql ip --><property name="ipAddress">192.168.1.101</property>            ##48行</factoryConfig></dbServer><dbServer name="slave1"  parent="abstractServer">    ##52行<factoryConfig><!-- mysql ip --><property name="ipAddress">192.168.1.102</property>               ##55行</factoryConfig></dbServer>    <dbServer name="slave2"  parent="abstractServer"><factoryConfig><!-- mysql ip --><property name="ipAddress">192.168.1.103</property></factoryConfig></dbServer><dbServer name="slaves" virtual="true">                    ##59行<poolConfig class="com.meidusa.amoeba.server.MultipleServerPool"><!-- Load balancing strategy: 1=ROUNDROBIN , 2=WEIGHTBASED , 3=HA--><property name="loadbalance">1</property><!-- Separated by commas,such as: server1,server2,server1 --><property name="poolNames">slave1,slave2</property>          ##65行</poolConfig></dbServer>     
（4）启动amoeba软件
[root@localhost ~]# cd /usr/local/amoeba/
[root@localhost amoeba]# bin/amoeba start&
注：当在前台运行某个作业时,终端被该作业占据;而在后台运行作业时,它不会占据终端。可以使用&命令把作业放到后台执行
[root@localhost amoeba]# netstat -anpt | grep java
如果能看到8066和3306两个端口号，证明amoeba是正常开启的                                   

4：测试

（1）：在client上
[root@localhost yum.repos.d]# yum -y install mariadb
[root@localhost yum.repos.d]# mysql -u amoeba -p 123456 -h 192.168.10.104 -P 8066
Enter password:            ##密码：123456（2）：在master服务器上创建表
mysql> stop slave;
MySQL [test]> use auth
MySQL [auth]> create table users (id int(10),name char(20));
（3）：在两个slave服务器上
mysql> stop slave;（4）：在master服务器上
mysql> insert into users values ('1','zhangsan');（5）：在slave1上
mysql> use auth;
mysql>insert into zang values ('2','zhangsan');（6）：在slave2上
mysql> use auth;
mysql>insert into zang values ('3','zhangsan);（7）：在client上查询三次
mysql> use auth;
mysql> select * from users;
对比三次的输出，验证读操作，发现没有在master写入的数据，而slave上写的能查到（8）：在client上
mysql> use auth;
mysql>insert into users values ('4','zhangsan');
mysql> select * from users;      ##发现在client上查询不到自己写的数据（9）：在master上
mysql> select * from users;      ##能查到在client上写入的数据，说明写操作在master上（10）：在slave上
mysql> select * from users;       ##发现没有数据，说明写入的操作是在master上导出导入数据库：
[root@localhost ~]# mysqldump -u root -p --database auth > /opt/auth.sql       
[root@localhost data]# mysql -u root -p < /opt/ auth.sql