mysql主从复制延迟解决

àì夳堔傛蜴生んèń 2023-09-25 09:58 29阅读 0赞

## mysql主从配置成功，但是过段时间再操作master,发现slave没有同步更新 ##

复制有延迟，slave想要尽可能及时跟上master的进度，可以尝试采用以下几种方法：

1、采用MariaDB发行版，它实现了相对真正意义上的并行复制，其效果远比ORACLEMySQL好的很多。在我的场景中，采用MariaDB作为slave的实例，几乎总是能及时跟上master。如果不想用这个版本的话，那就老实等待官方5.7大版本发布吧；

关于MariaDB的ParallelReplication具体请参考：ReplicationandBinaryLogServerSystemVariables\#slave\_parallel\_threads–MariaDBKnowledgeBase

2、每个表都要显式指定主键，如果没有指定主键的话，会导致在row模式下，每次修改都要全表扫描，尤其是大表就非常可怕了，延迟会更严重，甚至导致整个slave库都被挂起，可参考案例：mysql主键的缺少导致备库hang；

3、应用程序端多做些事，让MySQL端少做事，尤其是和IO相关的活动，例如：前端通过内存CACHE或者本地写队列等，合并多次读写为一次，甚至消除一些写请求；

4、进行合适的分库、分表策略，减小单库单表复制压力，避免由于单库单表的的压力导致整个实例的复制延迟；

其他提高IOPS性能的几种方法，根据效果优劣，我做了个简单排序：

1、更换成SSD，或者PCIeSSD等IO设备，其IOPS能力的提升是普通15KSAS盘的数以百倍、万倍，甚至几十万倍计；

2、加大物理内存，相应提高InnoDBBufferPool大小，让更多热数据放在内存中，降低发生物理IO的频率；

3、调整文件系统为XFS或ReiserFS，相比ext3可以极大程度提高IOPS能力。在高IOPS压力下，相比ext4有更稳健的IOPS表现(有人认为XFS在特别的场景下会有很大的问题，但我们除了剩余磁盘空间少于10%时引发丢数据外，其他的尚未遇到)；

4、调整RAID级别为raid1+0，它相比raid1、raid5等更能提高IOPS性能。如果已经全部是SSD设备了，可以2块盘做成RAID1，或者多快盘做成RAID5(并且可以设置全局热备盘，提高阵列容错性)，甚至有些土豪用户直接将多块SSD盘组成RAID50；

5、调整RAID的写cache策略为WB或FORCEWB，详情请参考：常用PC服务器阵列卡、硬盘健康监控以及PC服务器阵列卡管理简易手册；

6、调整内核的ioscheduler，优先使用deadline，如果是SSD，则可以使用noop策略，相比默认的cfq，个别情况下对IOPS的性能提升至少是数倍的。

其他更多方法，欢迎大家帮忙补充：)

![mysql主从复制延迟解决\_mysql主从复制配置][mysql_mysql]

## mysql的主从复制能配置远程mysql吗 ##

long\_query\_time=1

\#slow\_query\_log

slow\_query\_log=1

\#slow\_query\_log\_file=var/lib/mysql/slow-query.log

\#以前版本的参数格式跟5.6的不一致

slow\_query\_log\_file=var/lib/mysql/slow-query.log

\#将所有没有使用带索引的查询语句全部写到慢查询日志中

log\_queries\_not\_using\_indexes=1

本回答由提问者推荐

## 请问：1，mysql主从复制是什么概念，什么场合下用，最好举例说明； ##

1 主从复制，是用来建立一个和主数据库完全一样的数据库环境，称为从数据库；主数据库一般是实时的业务数据库，从数据库的作用和使用场合一般有几个：

一是作为后备数据库，主数据库服务器故障后，可切换到从数据库继续工作；

二是可在从数据库作备份、数据统计等工作，这样不影响主数据库的性能；

2 读写分离，是指读与写分别使用不同的数据库，当然一般是在不同服务器上的；在同一台服务器上的读写环境，估计只是用来测试吧。

一般读写的数据库环境配置为，一个写入的数据库，一个或多个读的数据库，各个数据库分别位于不同的服务器上，充分利用服务器性能和数据库性能；当然，其中会涉及到如何保证读写数据库的数据一致，这个就可以利用主从复制技术来完成。

一般应用场合为：业务吞吐量很大，读数据库(可简单理解为select语句的 比例和影响)的负载较大；

官方的mysql-proxy就是一个实现了读写分离、负载均衡等多个功能的软件。主从复制，是用来建立一个和主数据库完全一样的数据库环境，称为从数据库；主数据库一般是实时的业务数据库

1. MySQL是一个关系型数据库管理系统，由瑞典MySQL AB 公司开发，目前属于 Oracle 旗下产品。MySQL 最流行的关系型数据库管理系统，在 WEB 应用方面MySQL是最好的 RDBMS (Relational Database Management System，关系数据库管理系统) 应用软件之一。

2. MySQL是一种关联数据库管理系统，关联数据库将数据保存在不同的表中，而不是将所有数据放在一个大仓库内，这样就增加了速度并提高了灵活性。

3. 与其他的大型数据库例如Oracle、DB2、SQL Server等相比，MySQL 自有它的不足之处，但是这丝毫也没有减少它受欢迎的程度。

4. 对于一般的个人使用者和中小型企业来说，MySQL提供的功能已经绰绰有余，而且由于 MySQ L是开放源码软件，因此可以大大降低总体拥有成本。

![mysql主从复制延迟解决\_mysql主从复制配置\_02][mysql_mysql_02]

## 如何添加新数据库到mysql主从复制列表 ##

首先，我们大概罗列一下主从复制的基本步骤，(MySQL主从首先需要在各自服务器配置好)。

1. 复制数据库。

mysqldump --master-data --single-transaction -R --databases \[db\_name\] | gzip -9 - | pv > all-db-with-master-data.sql.gz

注意：innodb用 –single-transaction, myisam需要用 –lock-all-tables。

2. 复制，导入数据。

pv < all-db-with-master-data.sql.gz | zcat | mysql

3. 启动slave数据库。

slave start

注意：切换到主的语句已经在导出的sql语句里面了，注意查看。change master to master\_log\_file=’(binlog name in relay\_master\_log\_file)’, master\_log\_pos=(exec\_master\_log\_pos number)。

那么，在现有的主从复制结构中，如何增加一个新的数据库进去？比如我们要增加一个数据库在master服务器上，比如，名为newdb的数据库。

具体操作如下：

1. 从服务上，停掉slave数据库。

stop slave;

2. 主服务器上，导出新数据库。

mysqldump --master-data --single-transaction -R --databases newdb > newdb.sql

3. 主服务器上，修改my.cnf文件，添加新库到binlog-do-db参数，重启mysql。

4. 在导出的newdb.sql里面查找当前的日志文件以及位置(change master to …)

然后让slave服务器执行到这个位置。

start slave until MASTER\_LOG\_FILE="mysql-bin.000001", MASTER\_LOG\_POS=1222220;

其中MASTER\_LOG\_FILE以及MASTER\_LOG\_POS在导出的数据库newdb.sql顶部位置查找。

4. 导入新库到从服务器上。

mysql < newdb.sql

5. start slave

其中比较重要的是在主服务器上导出新库时的日志位置(position A)，这个点很重要，以这个点做为分界线，导入新库。

这种方法也同样适用于某个数据库或者某个数据表不同步的情况，比如主从数据库有一个表由于某些原因数据不一致，那么上面的方法只需要去掉重启数据库一步，其他的操作基本

[mysql_mysql]: https://img-blog.csdnimg.cn/img_convert/55477fa10233025f3235c7995e254eda.jpeg
[mysql_mysql_02]: https://img0.baidu.com/it/u=2097499227,508070524&fm=26&fmt=auto