MySQL 主从复制

在企业应用中，成熟的业务通常数据量都比较大
单台MySQL在安全性、高可用性和高并发方面都无法满足实际的需求
配置多台主从数据库服务器以实现读写分离

一、MySQL 主从复制原理

1.MySQL的复制类型

基于语句的复制(STATEMENT, MySQL默认类型)
基于行的复制(ROW)
混合类型的复制(MIXED)

2.mysq支持的复制类型

(1) STATEMENT:基于语句的复制。在服务器上执行sq1语句，在从服务器上执行同样的语句，mysql 默认采用基于语句的复制，
执行效率高。
(2) ROW: 基于行的复制。把改变的内容复制过去，而不是把命令在从服务器上执行一 遍。
(3)MIXED:混合类型的复制。默认采用基于语句的复制，一-旦发现基于语句无法精确复制时，就会采用基于行的复制。

3.MySQL主从复制的工作过程

(1)在每个事务更新数据完成之前，Master在二进制日志(Binary log)记录这些改变。写入二进制日志完成后，Master通知存储引擎提交事务。

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

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

二、MySQL主从复制延迟

1、master服务 器高并发，形成大量事务

2、网络延迟

3、主从硬件设备导致

cpu主频、内存io、硬盘io

4、本来就不是同步复制、而是异步复制

从库优化Mysql参数。比如增大innodb_buffer_pool_size，让更多操作在Mysql内存中完成，减少磁盘操作。
从库使用高性能主机。包括cpu强悍、内存加大。避免使用虚拟云主机，使用物理主机，这样提升了i/o方面性。
从库使用SSD磁盘
网络优化，避免跨机房实现同步

三、搭建 MySQL 主从复制

Master 服务器: 192.168.100.100        mysql5.7
Slave1 服务器: 192.168.100.110        mysql5.7
Slave2 服务器: 192.168.100.120        mysql5.7

1.Mysql主从服务器时间同步

主服务器设置

yum install ntp -y
vim /etc/ntp.conf

末尾添加
server 127.127.100.0              #设置本地是时钟源，注意修改网段
fudge 127.127.100.0 stratum 8     #设置时间层级为8 (限制在15内)

service ntpd start

从服务器设置

yum install ntp ntpdate - y
service ntpd start
/usr/sbin/ntpdate 192.168.100.100

#进行时间同步
crontab -e
*/10 * * * * /usr/sbin/ntpdate 192.168.100.100

主服务器的mysql配置
vim /etc/my.cnf

[mysqld]
server-id = 11
log-bin=master-bin        #添加，主服务器开启二进制日志
binlog_format = MIXED
log-slave-updates=true    #添加，允许从服务器更新二进制日志

systemctl restart mysqld

mysql -u root -pabc123
GRANT REPLICATION SLAVE ON *.* TO 'myslave'@'192.168.100.%' IDENTIFIED BY '123456';

给从服务器授权
FLUSH PRIVILEGES;
show master status;

从服务器的mysql配置
vim /etc/my.cnf
server-id = 22                             #修改，注意id与Master的不同，两个Slave的id也要不同
relay-log=relay-log-bin                    #添加，开启中继日志，从主服务器上同步日志文件记录到本地
relay-log-index=slave-relay-bin.index      #添加，定义中继日志文件的位置和名称

systemctl restart mysqld
mysql -u root -pabc123

CHANGE master to master_host='192.168.100.100',master_user='myslave',
master_password='123456',master_log_file='master-bin.000001',master_log_pos=604;
#配置同步，注意master_log_file和master_log_pos的值要与Master查询的一致

start slave;                        #启动同步，如有报错执行reset slave;
show slave status\G                 #查看Slave 状态

确保I0和SQL线程都是Yes，代表同步正常。
Slave_IO_Running:Yes       #负责与主机的io通信
Slave_SQL_Running:Yes      #负责自己的slave_mysql进程

2.验证主从复制

总结

主从复制
MySQL主服务器master将事件写入自己的二进制日志中（binary log） 这时从服务器slave通过I/O线程读取master二进制日志，如同步则进入睡眠并等待master事件更新，I/O线程会将master二进制日志写入到自己的中继日志（存在缓存中），SQL线程将重放中继日志中的事件，写入到slave的数据库中，从而使得slave与master数据一致。