MYSQL数据库之主从复制及读写分离
一、MySQL的主从复制
1、概述
MySQL Replication俗称MySQL AB复制或主从复制，是MySQL官方推荐的数据同步技术。数据同步基本过程：从库会实时去读取主库的二进制日志文件，按照日志中记录对从座进行同样的操作，以达到数据同步效果。
2、MySQL Replication优点
通过增加从服务器来提高数据库平台的可靠性能。在主服务器上执行写入和更新，在从服务器上向外提供读功能，可以动态地调整从服务器的数量，从而调整数据库平台的高性能。
提高数据安全性，因为数据已复制到从服务器，主数据库数据异常时，可以将从服务器复制进程终止来达到保护数据完整性的特点。
在主服务器上生成实时数据，而在从服务器上分析这些数据，从而缓解主服务器的性能。
3、复制类型
1）异步复制
MysQL默认的复制即是异步的，主库在执行完客户端提交的事务后会立即将结果返给给客户端，并不关心从库是否已经接收并处理。
这样就会有一个问题，主库如果down掉了，此时主上已经提交的事务可能并没有传到从座服务器上，如果此时，强行将从提升为主，可能会导致新主上的数据不完整。
默认情况下MySQL5.5/5.6/5.7和mariaDB10.0/10.1的复制功能是异步的。
2）全同步复制
指当主库执行完一个事务，所有的从库都执行了该事务才返回给客户端。
因为需要等待所有从库都行完该事务才能返回，所以全同步复制的性能必然会收到严重的影响，返回客户端的响应速度也会被拖慢
3）半同步复制
MySQL5.5由Google贡献的补丁才开始支持半同步复制（semi Replication）模式，介于异步复制和全同步复制之间，主库在执行完客户端提交的事务后不是立刻返回给客户端，而是等待至少一个从库接收到并写到relaylog中才返回给客户端。
相对于异步复制，半同步复制提高了数据的安全性，同时它也造成了一定程度的延迟，这个延迟最少是一个TCP/IP往返的时间。
所以，半同步复制最好在低延时的网络中使用。当出现超时情况时，源主服务器会暂时切换到异步复制模式，直到至少有一台设置为半同步复制模式的从服务器及时收到信息为止。
半同步复制模式在主服 务器和从服务器同时启用，否则主服务器默认使用异步复制模式。
半同步复制的潜在问题 ：
客户端事务在存储引擎层提交后，在得到从库确认的过程中，主库宕机了。此时可能的情况有两种：
（1）事务还没发送到从库上
此时，客户端会收到事务提交失败的信息，客户端会重新提交该事务到新的主上，当宕机的主库重新启动后，以从库的身份重新加入到该主从结构中，会发现，该事务在从库中被提交了两次，一次是之前作为主的时候，一次是被新主同步过来的。
（2）事务已经发送到从库上
此时，从库已经收到并应用了该事务，但是客户端仍然会收到事务提交失败的信息，重新提交该事务到新的主上。
无数据丢失的半同步复制：
针对上述潜在问题，MySQL 5.7引入了一种新的半同步方案：Loss-Less半同步复制。
先同步到从库，同步成功主库在提交；
当然，之前的半同步方案同样支持，MySQL 5.7.2引入了一个新的参数进行控制r见lsemi_sync_master wait point。
rplsemi sync master_wait_point 有两种取值：
AFTER_SYNC：这个即新的半同步方案
AFTER_COMMIT：老的半同步方案
4、MySQL支持的复制方式
（1）基于SQl语句的复制（statement-based replication，SBR）：在主服务器上执行的sQL语句，在从服务器上执行同样的SQl语句，效率比较高。。
（2）基于行的复制（row-based replication，RBR）：主服务器把表的行变化作为事件写入到二进制日志中，主服务器把代表了行变化的事件复制到从服务中。
（2）混合模式复制（mixed-based replication，MBR）：先采用基于语句的复制，一旦发现基于语句无法精确复制时，再采用行。
5、复制方式的优缺点
1）RBR
优点：
（1）任何情况都可以被复制，这对复制数据来说是最安全可靠的。
（2）更少的行级锁表
（3）和其他大多数数据库系统的复制技术一样多数情况下
（4）从服务器上的表如果有主键的话，复制就会快很多
缺点：
（1）Binlog文件较大。
（2）复杂的回滚时binlog中会包含大量的数据
（3）主服务器上执行多个UPDATE语句时，所有发生变化的记录都会写到binlog中，而且只写为一个操作事物，这会导致频繁发生binlog的并发写问题
（4）不能通过查看日志来审计执行过的sql语句，不过可以通过使用mysqlbinlog
-base64-output=decode-rows--verbose 来查看数据的变动
2）SBR
优点：
（1）历史悠久，技术成熟，binlog文件较小。
（2）binlog中包含了所有数据库更改信息，可以据此来审核数据库的安全等情况
（3）binlog.可以用于实时的还原，而不仅仅用于复制。
（4）主从版本可以不一样，从服务器版本可以比主服务器版本高
缺点：
（1）不是所有的UPDATE 语句都能被复制，尤其是包含不确定操作的时候。
（2）复制需要进行全表扫描（WHERE语句中没有使用到索引）的UPDATE时，需要比RBR请求更多的行级锁
（3）对于一些复杂的语句，在从服务器上的耗资源情况会更严重
（4）如下函数的语句不能被正确地复制：load file()，uuid()，uuid short（）；user（）；found_rows（）；sysdate（）；get lock（）；is_free_lock（）；is_used_lock（）；master_pos wait（）；rand（）；release_lock（）；sleep（）；version（）；
（5）在日志中出现如下警告信息的不能正确地复制：[Warning]Statement is not safe to log in statement format.或者在客户端中出现show warnings
（6）Insert卻elect 语句会执行大量的行级锁表
6、主从复制的原理
（1）首先主开启二进制日志，为从授权；从开启中继日志
（2）从库开启一个IO线程，与主的IO线程连接，时刻监视主的二进制日志有无改动
（3）如果有变动，复制主的二进制日志，写入从的中继日志，
（4）从会开启一个sql线程，将中继日志中的内容写入从库
7、复制过程的限制
（1）MySQL5.6之前的版本复制操作在Slave上执行是串行化的，Master上的并行更新会导致数据复制延迟。
（2）所有MYSQL 服务器的版本都要高于3.2，还有一个基本的原则就是从服务器的数据库版本可以高于主服务器数据库的版本，但是不可以低于主服务器的数据库版本。
二、MySQL的读写分离
1、概念
通过主从复制（Master-Slave）的方式来同步数据，再通过读写分离来提升数据的高并发负载能力这样的方案来进行部署。
读写分离就是只在主服务器上写，只在从服务器上读、基本的原理是让主数据库处理事务性查询，而从数据库处理select 查询，数据库复制被用来把事务性查询导致的改变更新同步到集群中的从数据库。
2、分离方案
1）基于程序代码内部实现
在代码中根据select，insert进行路由分类，这类方法也是目前生产环境应用最广泛的，优点是性能好，因为在程序代码中实现，不需要曾加额外的设备作为硬件开支，缺点是需要开发人员来实现，运维人员无从下手。
2）基于中间代理
代理一般位于客户端和服务器之间，代理服务器接到客户端请求后通过判断后转发到后端数据库，代表性程序：
（1）mysql-proxy 为mysgl开发早期开源项目，通过其自带的lua脚本进行sQL判断，虽然是mysql的官方产品，但是mysql官方不建议将其应用到生产环境。
（2）Amoeba（变形虫）由陈思儒开发，曾就职与阿里巴巴，该程序由java语言进行开发，阿里巴巴将其应用于生成环境，它不支持事物和存储过程。
三、主从复制及读写分离实验
数据库内执行 xshell内执行 配置文件的修改
1、五台虚拟机
192.168.200.111 MySQL主
192.168.200.112 MySQL从
192.168.200.113 MySQL从
192.168.200.114 amoeba
192.168.200.115 测试机
2、安装Mariadb
111、112、113：
yum -y install mariadb mariadb-server
115：
yum -y install mariadb
3、修改MySQL的配置文件
111：
vim /etc/my.cnf
[mysqld]
server-id=1 #服务描述标号，三台虚拟机不能相同
log-bin=mysql-binlog #开启二进制日志
log-slave-updates=true #允许从复制
systemctl restart mariadb
112、113：
vim /etc/my.cnf
[mysqld]
server-id=2 #服务描述标号，三台虚拟机不能相同
relay-log=relay-log-bin #开启中继日志
relay-log-index=slave-relay-bin.index #索引文件
systemctl restart mariadb
4、备份主库原有数据
如果主库之前有数据，为了保持数据的一致性，先备份主的数据，同步到从上。
111：
mysqldump -uroot -p123123 --all-databases > /root/allbackup.sql
scp /root/allbackup.sql root@192.168.200.112:/root/
scp /root/allbackup.sql root@192.168.200.113:/root/
112、113：
mysqldump -uroot -p123123 < /root/allbackup.sql
5、配置主从复制
111：
mysql #进入数据库
grant replication slave on *.* to 'myslave'@'192.168.200.11_' identified by '123123'; #为从授权
grant all on *.* to 'admin'@'192.168.200.11_' identified by '123123'; #为amoeba授权
flush privileges; #刷新授权表
show master status; #查看日志及偏移量
+---------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+---------------------+----------+--------------+------------------+
| mysql-binlog.000001 | 479 | | |
+---------------------+----------+--------------+------------------+
112、113：
mysql
stop slave; #停止从的功能
CHANGE MASTER TO
MASTER_HOST='192.168.200.111', #主的地址
MASTER_USER='myslave', #主授权的用户名
MASTER_PASSWORD='123123', #主授权的密码
MASTER_LOG_FILE='mysql-binlog.000001', #主的日志
MASTER_LOG_POS=479; #主的日志的偏移量
start slave; #开启从的功能
show slave status\G #查看从的服务
Slave_IO_Running: Yes #代表连接成功
Slave_SQL_Running: Yes #代表连接成功
grant all on *.* to 'admin'@'192.168.200.11_' identified by '123123'; #为amoeba授权
flush privileges; #刷新授权表 _单个字符，%表示多个字符
6、安装amoeba
114：
由于amoeba由Java开发，需要先安装jdk，建议安装1.5/1.6
tar xf jdk-7u65-linux-x64.gz
mv jdk1.7.0_65/ /usr/local/java
vi /etc/profile
export JAVA_HOME=/usr/local/java
export PATH=$PATH:$JAVA_HOME/bin
source /etc/profile
tar xf amoeba-mysql-binary-2.2.0.tar.gz -C /usr/local/amoeba
chmod -R 755 /usr/local/amoeba/
/usr/local/amoeba/bin/amoeba
amoeba start|stop #正常
The stack size specified is too small, Specify at least 228k #错误
Error: Could not create the Java Virtual Machine.
如果出现上面红色的报错，修改amoeba的启动文件
/usr/local/amoeba/bin/amoeba
58 DEFAULT_OPTS="-server -Xms256m -Xmx256m -Xss256k"
amoeba启动文件正常，然后修改配置文件
cp /usr/local/amoeba/conf/amoeba.xml /usr/local/amoeba/conf/amoeba.xml.bak #备份
vim /usr/local/amoeba/conf/amoeba.xml
30
32
115
118
119
cp /usr/local/amoeba/conf/dbServers.xml /usr/local/amoeba/conf/dbServers.xml.bak #备份
vim /usr/local/amoeba/conf/dbServers.xml
19
20
22
26
28
29
45
46
48
49
50
52
53
55
56
57
58
59
61
62
63
65
66
68
71
72
73
/usr/local/amoeba/bin/amoeba start & #启动amoeba
netstat -lnpt | grep :8066 #出现8066端口即为成功
tcp6 0 0 :::8066 :::* LISTEN 20196/java
7、测试
115：
mysql -uadmin -p123123 -h192.168.200.114 -P8066
create database asd;
create table user(id int,name char(20));
111、112、113：
show databases;
112、113：
stop slave;
111：
use asd;
insert into user values (1,'a');
112：
use asd;
insert into user values (2,'b');
113：
use asd;
insert into user values (3,'c');
115：
select * from asd.user;
查看结果为2b、3c轮替，读写分离成功
