环境Ubuntu 22.04 · MySQL 8.0两台虚拟机从装系统到主从同步跑通含全部踩坑记录一、为什么要做主从复制面试被问烂的问题但确实有用读写分离主库写、从库读减轻主库压力数据冗余主库挂了还有从库顶着备份不影响业务在从库上做 mysqldump主库该干嘛干嘛二、环境准备机器清单角色主机名可选操作也可以直接用IP静态IP根据自己情况配置Mastermysql-master192.168.91.1011核2GSlavemysql-slave192.168.91.1021核2GOS 都是 Ubuntu Server 22.04 LTSMySQL 8.0。配置 hosts可选操作# 两台都执行 echo 192.168.91.101 mysql-master /etc/hosts echo 192.168.91.102 mysql-slave /etc/hosts # 验证互通 ping -c 3 mysql-master ping -c 3 mysql-slave如果不想麻烦直接跳过这一步后续MASTER_HOSTmysql-master,配置中直接填IP即可三、安装 MySQL两台机器一模一样的操作apt update apt install mysql-server -y systemctl start mysql systemctl enable mysql systemctl status mysql --no-pager -l看到active (running)就对了。四、配置主库Master4.1 修改 MySQL 配置文件vim /etc/mysql/mysql.conf.d/mysqld.cnf在[mysqld]段下追加server-id 1 log_bin /var/log/mysql/mysql-bin.log binlog_format ROW解释一下这三个参数参数作用为什么这么配server-id1集群内唯一标识主从不一致就出问题log_bin开启二进制日志主从复制的根本——所有写操作记在这个日志里binlog_formatROW记录每行数据变更比 STATEMENT 更精确数据一致性最好4.2 修改 bind-address如果修改主机名需加上此操作# 查看当前值 cat /etc/mysql/mysql.conf.d/mysqld.cnf | grep bind-address # 默认是 127.0.0.1改为 0.0.0.0 sed -i s/127.0.0.1/0.0.0.0/ /etc/mysql/mysql.conf.d/mysqld.cnf默认 MySQL 只监听本地127.0.0.1不改的话从库连不上。4.3 重启 MySQLsystemctl restart mysql4.4 创建复制用户注意 MySQL 8.0 的坑直接看下面mysql -u root -p CREATE USER replica% IDENTIFIED WITH mysql_native_password BY 你的密码; GRANT REPLICATION SLAVE ON *.* TO replica%; FLUSH PRIVILEGES;为什么必须写WITH mysql_native_passwordMySQL 8.0 默认的caching_sha2_password要求从库走 SSL 连接否则报这个错Authentication requires secure connection改成mysql_native_password一步解决练习环境够用了生产环境可以配 SSL。4.5 查看主库状态SHOW MASTER STATUS;记下输出例如---------------------------- | File | Position | ---------------------------- | mysql-bin.000001 | 157 | ----------------------------这俩值从库配置时必须用错一个都不行。五、配置从库Slave5.1 修改 MySQL 配置文件vim /etc/mysql/mysql.conf.d/mysqld.cnfserver-id 2 log_bin /var/log/mysql/mysql-bin.log relay_log /var/log/mysql/mysql-relay-bin.log read_only 1relay_log 是什么从库从主库拉回来的 binlog 先存到 relay log再重放。可以理解为中转日志。5.2 重启 MySQLsystemctl restart mysql5.3 配置连接主库mysql -u root -p CHANGE MASTER TO MASTER_HOSTmysql-master未修改改主机名直接填IP即可, MASTER_USERreplica, MASTER_PASSWORD你的密码, MASTER_LOG_FILEmysql-bin.000001, MASTER_LOG_POS157;MASTER_LOG_FILE 和 MASTER_LOG_POS 的值来自主库SHOW MASTER STATUS必须完全一致。5.4 启动复制并检查START SLAVE; SHOW SLAVE STATUS \G;找到这两行Slave_IO_Running: Yes Slave_SQL_Running: Yes两个都是Yes就成功了。六、踩坑记录实操中遇到的坑1Slave_IO_Running: Connecting持续处于连接中的状态没报错也没成功。排查清单主库bind-address是不是 127.0.0.1→ 改为 0.0.0.0主库防火墙放行了 3306 没→ufw allow 3306/tcpreplica 用户密码对不对→ 主库 SELECT user,host FROM mysql.user 查一下从库能 telnet 主库 3306 吗→telnet mysql-master 3306坑2Authentication requires secure connectionMySQL 8.0 默认认证插件的问题。解决方案创建用户时加上IDENTIFIED WITH mysql_native_password BY 密码。如果已经建好了就 ALTERALTER USER replica% IDENTIFIED WITH mysql_native_password BY 你的密码; FLUSH PRIVILEGES;坑3Slave_SQL_Running: No错误码 1396主库之前执行过 ALTER USER/DROP USER 等操作binlog 传到从库后重放失败从库没有对应的用户。解决方案跳过这条错误STOP SLAVE; SET GLOBAL SQL_SLAVE_SKIP_COUNTER 1; START SLAVE;注意线上不要随便跳要查清楚根本原因。练习环境跳过无妨。坑4read_only1 没拦住写入原因是 MySQL 的 read_only 对 super 用户包括 root无效。验证只读要用普通用户连。生产环境还需要super_read_onlyON。七、验证同步主库写数据CREATE DATABASE demo_db; USE demo_db; CREATE TABLE users ( id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(50) ); INSERT INTO users(name) VALUES(张三),(李四); SELECT * FROM users;从库查数据USE demo_db; SELECT * FROM users;如果能看到张三和李四主从复制就彻底跑通了。八、面试常问主从复制原理一句话主库写 binlog从库拉 binlog 回来重放三个线程干完。三个线程线程在哪干什么Dump 线程主库binlog 有更新就通知从库IO 线程从库连主库拉 binlog 写 relay logSQL 线程从库读 relay log逐条重放两种日志日志用途binlog二进制日志主库记录所有数据变更relay log中继日志从库存放从主库拉回来的数据主从延迟怎么看SHOW SLAVE STATUS \G;看Seconds_Behind_Master字段单位秒。为 0 表示没有延迟。主库挂了怎么办从库提升为主STOP SLAVE; RESET MASTER;然后应用改配置连新的主库。三种复制模式模式特点丢数据风险异步主库写完 binlog 就返回有半同步至少一个从库确认才返回无或少全同步所有从库确认才返回无但慢到没法用九、常用命令速查# 主库 SHOW MASTER STATUS; # 看当前 binlog 位置 # 从库 STOP SLAVE; # 停止复制 CHANGE MASTER TO MASTER_HOST...; # 重新配置 START SLAVE; # 启动复制 SHOW SLAVE STATUS \G; # 看两个 Yes # 跳过错误 SET GLOBAL SQL_SLAVE_SKIP_COUNTER 1;十、总结主从复制搭起来不难难的是理解它背后的流程。抓住一句话主写 binlog从拉 binlog 重放三个线程干完。做运维面试问到主从能把这个流程讲清楚、能说出那三个线程的名字和职责、能说出几种复制模式的区别就已经及格了。下一步可以继续玩什么写一个 Shell 脚本crontab 定时从库上 mysqldump 备份加一主两从搞读写分离试试半同步复制
MySQL 主从复制从零搭建实战
环境Ubuntu 22.04 · MySQL 8.0两台虚拟机从装系统到主从同步跑通含全部踩坑记录一、为什么要做主从复制面试被问烂的问题但确实有用读写分离主库写、从库读减轻主库压力数据冗余主库挂了还有从库顶着备份不影响业务在从库上做 mysqldump主库该干嘛干嘛二、环境准备机器清单角色主机名可选操作也可以直接用IP静态IP根据自己情况配置Mastermysql-master192.168.91.1011核2GSlavemysql-slave192.168.91.1021核2GOS 都是 Ubuntu Server 22.04 LTSMySQL 8.0。配置 hosts可选操作# 两台都执行 echo 192.168.91.101 mysql-master /etc/hosts echo 192.168.91.102 mysql-slave /etc/hosts # 验证互通 ping -c 3 mysql-master ping -c 3 mysql-slave如果不想麻烦直接跳过这一步后续MASTER_HOSTmysql-master,配置中直接填IP即可三、安装 MySQL两台机器一模一样的操作apt update apt install mysql-server -y systemctl start mysql systemctl enable mysql systemctl status mysql --no-pager -l看到active (running)就对了。四、配置主库Master4.1 修改 MySQL 配置文件vim /etc/mysql/mysql.conf.d/mysqld.cnf在[mysqld]段下追加server-id 1 log_bin /var/log/mysql/mysql-bin.log binlog_format ROW解释一下这三个参数参数作用为什么这么配server-id1集群内唯一标识主从不一致就出问题log_bin开启二进制日志主从复制的根本——所有写操作记在这个日志里binlog_formatROW记录每行数据变更比 STATEMENT 更精确数据一致性最好4.2 修改 bind-address如果修改主机名需加上此操作# 查看当前值 cat /etc/mysql/mysql.conf.d/mysqld.cnf | grep bind-address # 默认是 127.0.0.1改为 0.0.0.0 sed -i s/127.0.0.1/0.0.0.0/ /etc/mysql/mysql.conf.d/mysqld.cnf默认 MySQL 只监听本地127.0.0.1不改的话从库连不上。4.3 重启 MySQLsystemctl restart mysql4.4 创建复制用户注意 MySQL 8.0 的坑直接看下面mysql -u root -p CREATE USER replica% IDENTIFIED WITH mysql_native_password BY 你的密码; GRANT REPLICATION SLAVE ON *.* TO replica%; FLUSH PRIVILEGES;为什么必须写WITH mysql_native_passwordMySQL 8.0 默认的caching_sha2_password要求从库走 SSL 连接否则报这个错Authentication requires secure connection改成mysql_native_password一步解决练习环境够用了生产环境可以配 SSL。4.5 查看主库状态SHOW MASTER STATUS;记下输出例如---------------------------- | File | Position | ---------------------------- | mysql-bin.000001 | 157 | ----------------------------这俩值从库配置时必须用错一个都不行。五、配置从库Slave5.1 修改 MySQL 配置文件vim /etc/mysql/mysql.conf.d/mysqld.cnfserver-id 2 log_bin /var/log/mysql/mysql-bin.log relay_log /var/log/mysql/mysql-relay-bin.log read_only 1relay_log 是什么从库从主库拉回来的 binlog 先存到 relay log再重放。可以理解为中转日志。5.2 重启 MySQLsystemctl restart mysql5.3 配置连接主库mysql -u root -p CHANGE MASTER TO MASTER_HOSTmysql-master未修改改主机名直接填IP即可, MASTER_USERreplica, MASTER_PASSWORD你的密码, MASTER_LOG_FILEmysql-bin.000001, MASTER_LOG_POS157;MASTER_LOG_FILE 和 MASTER_LOG_POS 的值来自主库SHOW MASTER STATUS必须完全一致。5.4 启动复制并检查START SLAVE; SHOW SLAVE STATUS \G;找到这两行Slave_IO_Running: Yes Slave_SQL_Running: Yes两个都是Yes就成功了。六、踩坑记录实操中遇到的坑1Slave_IO_Running: Connecting持续处于连接中的状态没报错也没成功。排查清单主库bind-address是不是 127.0.0.1→ 改为 0.0.0.0主库防火墙放行了 3306 没→ufw allow 3306/tcpreplica 用户密码对不对→ 主库 SELECT user,host FROM mysql.user 查一下从库能 telnet 主库 3306 吗→telnet mysql-master 3306坑2Authentication requires secure connectionMySQL 8.0 默认认证插件的问题。解决方案创建用户时加上IDENTIFIED WITH mysql_native_password BY 密码。如果已经建好了就 ALTERALTER USER replica% IDENTIFIED WITH mysql_native_password BY 你的密码; FLUSH PRIVILEGES;坑3Slave_SQL_Running: No错误码 1396主库之前执行过 ALTER USER/DROP USER 等操作binlog 传到从库后重放失败从库没有对应的用户。解决方案跳过这条错误STOP SLAVE; SET GLOBAL SQL_SLAVE_SKIP_COUNTER 1; START SLAVE;注意线上不要随便跳要查清楚根本原因。练习环境跳过无妨。坑4read_only1 没拦住写入原因是 MySQL 的 read_only 对 super 用户包括 root无效。验证只读要用普通用户连。生产环境还需要super_read_onlyON。七、验证同步主库写数据CREATE DATABASE demo_db; USE demo_db; CREATE TABLE users ( id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(50) ); INSERT INTO users(name) VALUES(张三),(李四); SELECT * FROM users;从库查数据USE demo_db; SELECT * FROM users;如果能看到张三和李四主从复制就彻底跑通了。八、面试常问主从复制原理一句话主库写 binlog从库拉 binlog 回来重放三个线程干完。三个线程线程在哪干什么Dump 线程主库binlog 有更新就通知从库IO 线程从库连主库拉 binlog 写 relay logSQL 线程从库读 relay log逐条重放两种日志日志用途binlog二进制日志主库记录所有数据变更relay log中继日志从库存放从主库拉回来的数据主从延迟怎么看SHOW SLAVE STATUS \G;看Seconds_Behind_Master字段单位秒。为 0 表示没有延迟。主库挂了怎么办从库提升为主STOP SLAVE; RESET MASTER;然后应用改配置连新的主库。三种复制模式模式特点丢数据风险异步主库写完 binlog 就返回有半同步至少一个从库确认才返回无或少全同步所有从库确认才返回无但慢到没法用九、常用命令速查# 主库 SHOW MASTER STATUS; # 看当前 binlog 位置 # 从库 STOP SLAVE; # 停止复制 CHANGE MASTER TO MASTER_HOST...; # 重新配置 START SLAVE; # 启动复制 SHOW SLAVE STATUS \G; # 看两个 Yes # 跳过错误 SET GLOBAL SQL_SLAVE_SKIP_COUNTER 1;十、总结主从复制搭起来不难难的是理解它背后的流程。抓住一句话主写 binlog从拉 binlog 重放三个线程干完。做运维面试问到主从能把这个流程讲清楚、能说出那三个线程的名字和职责、能说出几种复制模式的区别就已经及格了。下一步可以继续玩什么写一个 Shell 脚本crontab 定时从库上 mysqldump 备份加一主两从搞读写分离试试半同步复制
