当前位置：首页 > article >正文

MySQL-日志与主从复制（包含如何中途加入从节点）

article 2025/1/23 22:26:20

错误日志

SHOW VARIABLES LIKE '%log_error%';

二进制日志

show variables like '%log_bin%';
show variables like '%binlog_format%';

show variables like '%dir%';
# 找到 data 目录

cd /var/log/mysql
mysqlbinlog -v mysql-bin.000002

调整日志格式为 statement

vim /etc/my.cnf
binlog_format = statement

# 删除 000002 之前(不包括2)的所有日志 -> 000001 被删除
purge master logs to 'mysql-bin.000002';

show variables like '%binlog_expire_logs_second%';

# binlog_expire_logs_seconds | 2592000 默认三十天

主从复制

概述

原理

主库配置

双主双从

docker run -d \
-p 4306:3306 \
-v /root/mysql/master/conf:/etc/mysql/conf.d \
-v /root/mysql/master/data:/var/lib/mysql \
-e MYSQL_ROOT_PASSWORD=123456 \
--name atguigu-mysql-master \
mysql:8.0.29

vim /root/mysql/master/conf/my.cnf

[mysqld]
# 服务器唯一id，默认值1
server-id=1
# 设置日志格式，默认值ROW
# binlog_format=STATEMENT
# 是否只读，1 代表只读，0 代表读写
read_only=0
# 二进制日志名，默认binlog
# log-bin=binlog
# 设置需要复制的数据库，默认复制全部数据库
#binlog-do-db=mytestdb
# 设置不需要复制的数据库
#binlog-ignore-db=mysql
#binlog-ignore-db=infomation_schema

#进入容器：env LANG=C.UTF-8 避免容器中显示中文乱码
docker exec -it atguigu-mysql-master env LANG=C.UTF-8 /bin/bash
#进入容器内的mysql命令行
mysql -uroot -p

#修改默认密码校验方式
ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY '123456';
-- 创建slave用户
CREATE USER 'atguigu_slave'@'%';
-- 设置密码
ALTER USER 'atguigu_slave'@'%' IDENTIFIED WITH mysql_native_password BY '123456';
-- 授予复制权限
GRANT REPLICATION SLAVE ON *.* TO 'atguigu_slave'@'%';
-- 刷新权限
FLUSH PRIVILEGES;

SHOW MASTER STATUS;
# 记下`File`和`Position`的值。执行完此步骤后不要再操作主服务器MYSQL，防止主服务器状态值变化

# 如果有历史数据需要做这一步
# 导出历史数据
mysqldump --master-data=2 -uroot -p123456 --single-transaction --all-databases > data-for-upgrade.sql
head -n 50 data-for-upgrade.sql # 找到binlog中止点，后续配置binlog复制开始点需要用到
# --
# -- Position to start replication or point-in-time recovery from
# --

# -- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000004', MASTER_LOG_POS=1039;
# 还原
mysql -uroot -p123456 -hlocalhost -P3306 < data-for-upgrade.sql
# 或者sql窗口
source data-for-upgrade.sql

docker run -d -p 4309:3306 -v /root/mysql/master1/conf:/etc/mysql/conf.d -v /root/mysql/master1/data:/var/lib/mysql -e MYSQL_ROOT_PASSWORD=123456 --name atguigu-mysql-master1 mysql:8.0.29
#进入容器：
docker exec -it atguigu-mysql-master1 env LANG=C.UTF-8 /bin/bash
#进入容器内的mysql命令行
mysql -uroot -p
#修改默认密码校验方式
ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY '123456';

CHANGE MASTER TO MASTER_HOST='192.168.139.30', 
MASTER_USER='atguigu_slave',MASTER_PASSWORD='123456', MASTER_PORT=4309,
MASTER_LOG_FILE='binlog.000002',MASTER_LOG_POS=3548042;

START SLAVE;
-- 查看状态（不需要分号）
SHOW SLAVE STATUS\G

从库配置

docker run -d \
-p 4307:3306 \
-v /root/mysql/slave1/conf:/etc/mysql/conf.d \
-v /root/mysql/slave1/data:/var/lib/mysql \
-e MYSQL_ROOT_PASSWORD=123456 \
--name atguigu-mysql-slave1 \
mysql:8.0.29

[mysqld]
# 服务器唯一id，每台服务器的id必须不同，如果配置其他从机，注意修改id
server-id=2
# 是否只读，1 代表只读，0 代表读写
read_only=1
# 中继日志名，默认xxxxxxxxxxxx-relay-bin
#relay-log=relay-bin

#进入容器：
docker exec -it atguigu-mysql-slave1 env LANG=C.UTF-8 /bin/bash
#进入容器内的mysql命令行
mysql -uroot -p
#修改默认密码校验方式
ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY '123456';

CHANGE MASTER TO MASTER_HOST='192.168.139.30', 
MASTER_USER='atguigu_slave',MASTER_PASSWORD='123456', MASTER_PORT=4306,
MASTER_LOG_FILE='binlog.000002',MASTER_LOG_POS=1357;

START SLAVE;
-- 查看状态（不需要分号）
SHOW SLAVE STATUS\G

docker run -d \
-p 4308:3306 \
-v /root/mysql/slave2/conf:/etc/mysql/conf.d \
-v /root/mysql/slave2/data:/var/lib/mysql \
-e MYSQL_ROOT_PASSWORD=123456 \
--name atguigu-mysql-slave2 \
mysql:8.0.29

测试

主库中创建表并且插入记录，查看从库

常见问题

典型的错误例如：<font style="color:rgb(51, 51, 51);background-color:rgb(243, 244, 244);">Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'Client requested master to start replication from position > file size'</font>

-- 在从机停止slave
SLAVE STOP;

-- 在主机查看mater状态
SHOW MASTER STATUS;
-- 在主机刷新日志
FLUSH LOGS;
-- 再次在主机查看mater状态（会发现File和Position发生了变化）
SHOW MASTER STATUS;
-- 修改从机连接主机的SQL，并重新连接即可