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数据一致。