正常做个mysql主从这种没什么好说的,不用过多的篇幅去做说明

导出主库数据

mysqldump -u root -p --routines --single-transaction --master-data=2 --databases 库1 库2 > /var/lib/mysql/fullbak.sql

创建一个用户 并授予Replication ClientReplication Slave权限 用于从库读取主库binlog日志 同步数据

查找导出的sql文件内CHANGE MASTER开头的内容 标明了同步需指向的日志文件及指针位置

CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000433', MASTER_LOG_POS=216240109;

scp传递导出的主库数据到从库机器上

scr -P ssh端口 /var/lib/mysql/fullbak.sql root@ip:/sql文件保存目录

在新的从库上导入数据

mysql -p < /var/lib/mysql/fullbak.sql

设置主从连接信息 并开启同步

CHANGE MASTER TO MASTER_HOST='主库ip', MASTER_PORT=主库端口, MASTER_USER='用户名', MASTER_PASSWORD='密码', MASTER_CONNECT_RETRY=30, MASTER_RETRY_COUNT=86400, MASTER_LOG_FILE='从哪个binlog文件开始复制', MASTER_LOG_POS=从binlog文件的哪个位置开始复制;
    START SLAVE;

查看同步状态 并检查Slave_IO_RunningSlave_SQL_Running是否都为yes 如果是 则说明在正常同步

  • Master_Log_File 主库目前binlog文件
  • Read_Master_Log_Pos 主库目前pos位置
  • Relay_Master_Log_File 目前同步进度在主库哪个binlog文件
  • Exec_Master_Log_Pos 目前同步进度在哪个pos位置
show slave status
在生产环境配置完以上操作以后 我就开始将新的从库加入mysql中间件的读库负载配置里
然后没过几分钟生产环境部分用户开始打开页面缓慢 立马查看了新的从库cpu负载,发现是满载,并且几乎大部分sql执行都是慢sql 并被慢sql日志记录下来
复制sql到之前老的从库查询 查询结果很快0.02s而已 慢sql日志显示新的从库 一样的sql执行了20s+
于是先移除新的从库负载配置
然后再慢慢排查问题
通过EXPLAIN查询计划 并与老的从库比对 发现mysql索引走的字段 和 老的完全不一致 并且扫描行数都是1或者null
查看表里面的索引字段都在,但是表的数据容量和索引容量 占用都是很小的16k 和一张空表一样 但是查询数据又有,并且行数与老的从库一致
SELECT
    table_name AS '表名',
    table_rows AS '记录数',
    TRUNCATE ( data_length / 1024 / 1024, 2 ) AS '数据容量(MB)',
    TRUNCATE ( index_length / 1024 / 1024, 2 ) AS '索引容量(MB)',
    UPDATE_TIME AS '最后修改时间' 
FROM
    information_schema.TABLES 
WHERE
    table_schema = 'duoshop' 
ORDER BY
    data_length DESC,
    index_length DESC;
information_schema库里的TABLES 是表的一些元数据
里面包含了行数 数据容量 索引容量等等数据
通过查询发现 一张几百万的大表 行数正常 但是数据和索引容量都是0.01mb
并且如果我们插入几百条条数据的话,那么数据和索引容量都应该会增加一点 测试插入以后 无变化 并且UPDATE_TIME字段无更新
所以猜测到索引失效问题大概是出在这里了
通过搜索得知mysql8information_schema.TABLES的数据 并不准确 会被缓存 导致mysql执行查询时 先去获取表的元数据 发现无索引 所以导致无法利用索引加快查询速度 最终导致查询结果缓慢
解决方案有两种
第一种 如果表不多 可以手动执行以下语句 来刷新表的元数据
ANALYZE TABLE 表名
第二种 mysql8新增了一个配置information_schema_stats_expiry多久刷新一次表的元数据 默认是一天 86400s
我们可以在mysqlmy.ini配置里增加 使元数据不被缓存 就跟mysql8以下的版本使用起来一致
information_schema_stats_expiry=0
查了一天问题,一个配置项,查吐了!!!
所以其实不止是新增从库,如果直接新增一张表,在mysql8上,其实也会出现这种情况,但是新增表的时候,我们数据通常不会增长的很快,用不用到索引,感觉不是很明显,当数据量过多的时候,实际上大概率也过了默认的一天时间,会使得查询正常走索引
所以大部分这种坑,应该都踩到新增从库,并且需要立马投入使用的情况下
最后修改:2022 年 07 月 18 日 03 : 30 PM
如果觉得我的文章对你有用,请随意赞赏