MySQL 主从相关

   2022-07-18 18:22:43

mysql

MySQL 主从相关

一、三种粒度

1.statement：会将对数据库的操作写到 binlog 中。

2.row：会将每一条数据的变化写到 binlog 中。

3.mixed：statement 和 row混合。Mysql觉得什么时候写statement 的binlog ，什么时候写row 格式的binlog

二、实现原理（三个线程，主一从二）

1、主库：

当master库发生变化，会按照事件顺序写到bin-log中，当Slave连接到Master后，Master 会为 Slave 开启bin-log dump线程，当Master的 binlog 发生变化，binlog dump会通知Slave，并将相应binlog发送给Slave。

2、从库：

I/O线程：该线程连接到Master，Master 的 bin-log dump线程会将binlog内容发送给I/O线程，I/O线程接受到binlog 再将内容写到本地的 relay log。

sql线程：该线程读取到I/O线程写入的relay log，根据relay log 的内容，对Slave做相应的操作。

三、主从复制的延迟查看

1、在服务器上执行 show slave satus; 可以看到很多同步的参数：

Master_Log_File： SLAVE中的I/O线程当前正在读取的主服务器二进制日志文件的名称

Read_Master_Log_Pos： 在当前的主服务器二进制日志中，SLAVE中的I/O线程已经读取的位置

Relay_Log_File： SQL线程当前正在读取和执行的中继日志文件的名称

Relay_Log_Pos： 在当前的中继日志中，SQL线程已读取和执行的位置

Relay_Master_Log_File： 由SQL线程执行的包含多数近期事件的主服务器二进制日志文件的名称

Slave_IO_Running： I/O线程是否被启动并成功地连接到主服务器上

Slave_SQL_Running： SQL线程是否被启动

Seconds_Behind_Master： 从属服务器SQL线程和从属服务器I/O线程之间的时间差距，单位以秒计。

从库同步延迟情况出现：

show slave status显示参数Seconds_Behind_Master不为0，这个数值可能会很大

show slave status显示参数Relay_Master_Log_File和Master_Log_File显示bin-log的编号相差很大，说明bin-log在从库上没有及时同步，所以近期执行的bin-log和当前IO线程所读的bin-log相差很大

mysql的从库数据目录下存在大量mysql-relay-log日志，该日志同步完成之后就会被系统自动删除，存在大量日志，说明主从同步延迟很厉害

2、Mysql主从同步的延迟原因：

主要原因：数据库在业务上读写压力太大，CPU计算负荷大，网卡负荷大，硬盘随机IO太高

次要原因：读写binlog带来的性能影响，网络传输延迟

四、MySql数据库从库同步的延迟解决方案：

1、架构方面：

业务的持久层采用分库架构，mysql服务能力水平扩展，分散压力

单个库读写分离，一主多从，主写读从，分散压力。这样从库比主库压力高，保护主库

服务在业务和DB之间加入memcache 和 redis 的cache层，降低读的压力

不同业务的mysql放在不同的物理机，降低压力

使用比主库更好的硬件设备，Mqsql压力小，延迟就减少了

2、硬件方面：

采用好服务器，比如4u比2u性能明显好，2u比1u性能明显好。

存储用ssd或者盘阵或者san，提升随机写的性能。

主从间保证处在同一个交换机下面，并且是万兆环境。

3、Mysql主从同步加速：

sync_binlog在Slave端设置为0.

log-slave-updates 从服务器从主服务器接受的更新日志不计入二进制日志

直接禁用 Slave 的binlog

Slave端，如果存储引擎是innodb，innodb_flush_log_at_trx_commit =2

同步参数调整主库是写，对数据安全性较高，比如sync_binlog=1，innodb_flush_log_at_trx_commit = 1 之类的设置是需要的而slave则不需要这么高的数据安全，完全可以讲sync_binlog设置为0或者关闭binlog，innodb_flushlog也可以设置为0来提高sql的执行效率

4、业务角度：

强制走主库方案：比如，在一个交易平台上，卖家发布商品以后，马上要返回主页面，看商品是否发布成功。那么，这个请求需要拿到最新的结果，就必须走主库。

sleep 方案：主库更新后，读从库之前先 sleep 一下。具体的方案就是，类似于执行一条 select sleep(1) 命令。这个方案的假设是，大多数情况下主备延迟在 1 秒之内，做一个 sleep 可以有很大概率拿到最新的数据。

判断主备无延迟方案：show slave status 结果里的 seconds_behind_master 参数的值，可以用来衡量主备延迟时间的长短。所以第一种确保主备无延迟的方法是，每次从库执行查询请求前，先判断 seconds_behind_master 是否已经等于 0。如果还不等于 0 ，那就必须等到这个参数变为 0 才能执行查询请求。