MySQL主从复制（原理/主从延迟/配置）
2021-02-26 15:44:54 2 举报AI智能生成
MySQL主从复制（原理/主从延迟/配置）
作者其他创作
大纲/内容
阅读导航
MySQL 语法 👉
MySQL 索引 👉
MySQL 事务 👉
MySQL MVCC 👉
MySQL 锁机制 👉
MySQL 调优 👉
主从复制+慢查询
原理
从库连接到主库后，从库有个IO线程，将主库的binlog日志拷贝到自己本地，写入relay日志中
接着从库中有一个SQL线程会从relay日志中读取，然后执行其中的内容，使主从数据保持一致
主备延迟怎么解决？
分库，把一个主库拆分成多个主库，降低主库的写并发，使得延迟时间可以忽略不计
不查询，插入数据成功后直接修改数据，不要查询（因为修改数据在主库，查询在从库）
还可以直连主库，不推荐，失去读写分离的意义
配置
修改配置
一主一丛
主机：vim/etc/my.cnf
如有必要可关闭防火墙
如有必要可关闭防火墙
主服务器唯一ID：server-id=1（双主切忌冲突）
启用二进制日志：log-bin=mysql-bin
设置不要复制的数据库
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
设置需要复制的数据库：binlog-do-db=testdb（自定义）
设置binlog格式：binlog_format=STATEMENT
从机：vim/etc/my.cnf
从服务器唯一ID：server-id=2
启用中继日志：relay-log=mysql-relay
双主双从
两台主机需额外配置
（一主一从请忽略）
（一主一从请忽略）
在作为从库时，有写入操作也要更新二进制日志：log-slave-updates
设置自增长字段每次递增的步长：auto-increment-increment=2
设置自增长字段的起始值：auto-increment-offset=1 和 2
双主相互复制，分别执行
CHANGE MASTER TO MASTER_HOST='另一台主机地址',
MASTER_USER='slave',
MASTER_PASSWORD='123456',
MASTER_LOG_FILE='MYSQL-BIN.000001',
MASTER_LOG_POS=154;
MASTER_USER='slave',
MASTER_PASSWORD='123456',
MASTER_LOG_FILE='MYSQL-BIN.000001',
MASTER_LOG_POS=154;
start slave;
show slave status\G;
重启服务使配置生效：systemctl restart mysqld
设置权限
主机
mysql -uroot -proot
mysql> ：GRANT REPLICATION SLAVE ON *.* TO 'slave'@'%' IDENTIFIED BY '123456';
mysql> ：show master status;
查看File(日志名)和Position(切入点)，用来配置从机
从机
CHANGE MASTER TO MASTER_HOST='10.211.55.6',
MASTER_USER='slave',
MASTER_PASSWORD='123456',
MASTER_LOG_FILE='MYSQL-BIN.000001',
MASTER_LOG_POS=154;
MASTER_USER='slave',
MASTER_PASSWORD='123456',
MASTER_LOG_FILE='MYSQL-BIN.000001',
MASTER_LOG_POS=154;
start slave;
show slave status\G;
Slave_IO_Running：Yes
Slave_SQL_Running：Yes
如之前配置过主从，需重置
mysql> ：stop slave;
停止从服务器的复制功能
mysql> ：reset master;
重新配置主从
慢查询
查看
mysql> show variables like 'slow_query_log%';
查看慢查询日志的开启状态和慢查询日志保存位置
mysql> show variables like 'long_query_time';
查看慢SQL阈值，默认值是10s，可根据需求调整
日志分析工具
mysqldumpslow
[root@lishaojie]# mysqldumpslow /var/lib/mysql/mysql-slow.log
使用
mysqldumpslow --help
显示返回记录集最多的10个SQL
显示访问次数最多的10个SQL
显示按照时间排序的前10条里面含有左连接的查询语句
建议在使用这些命令时结合 | more 使用，否则有可能出现爆屏情况
mysqlsla（需安装）
关于作者
我的博客 👉
微信公众号 👉
GitHub 主页 👉
0 条评论
下一页
