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

导出主库数据

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

创建一个用户 并授予Replication Client和Replication 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_Running和Slave_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字段无更新
所以猜测到索引失效问题大概是出在这里了
通过搜索得知mysql8上information_schema.TABLES的数据 并不准确 会被缓存 导致mysql执行查询时 先去获取表的元数据 发现无索引 所以导致无法利用索引加快查询速度 最终导致查询结果缓慢
解决方案有两种
第一种 如果表不多 可以手动执行以下语句 来刷新表的元数据
ANALYZE TABLE 表名
第二种 mysql8新增了一个配置information_schema_stats_expiry多久刷新一次表的元数据 默认是一天 86400s
我们可以在mysql的my.ini配置里增加 使元数据不被缓存 就跟mysql8以下的版本使用起来一致
information_schema_stats_expiry=0
查了一天问题，一个配置项，查吐了！！！
所以其实不止是新增从库，如果直接新增一张表，在mysql8上，其实也会出现这种情况，但是新增表的时候，我们数据通常不会增长的很快，用不用到索引，感觉不是很明显，当数据量过多的时候，实际上大概率也过了默认的一天时间，会使得查询正常走索引
所以大部分这种坑，应该都踩到新增从库，并且需要立马投入使用的情况下

版权属于：本文是原创文章，版权归 吾梦小站 所有。

本文链接：https://nikm.cn/archives/64.html

本站所有原创文章采用 知识共享署名-非商业性使用 4.0 国际许可协议 进行许可。
您可以自由地转载和修改，但请务必注明文章来源并且不可用于商业目的。

最后修改：2022 年 07 月 18 日 03 : 30 PM
© 允许规范转载
如果觉得我的文章对你有用，请随意赞赏