跳至主要內容

配置主从复制实践

张威大约 9 分钟mysqlmysql集群

配置主从复制实践

一、检测通信

查看master（centos7）和slave（win10）的ip地址，并检测是否可以相互通信

我自己用的是ubuntu 22.04(master，192.168.216.136)和centos7(slave，192.168.216.137)

到这里我们知道，master的ip为192.168.131.129，slave的ip为192.168.0.6，并且可以相互通信

如果物理机可以ping通虚拟机，而虚拟机无法ping通物理机，需要在控制面板的网络中心启用网络发现

虚拟机无法ping通物理机解决方案open in new window

sudo apt install firewalld
  • 查看防火墙状态
systemctl status firewalld.service
  • 临时手动启动、停止防火墙
systemctl start firewalld.service
systemctl stop firewalld.service
  • 持久打开、关闭防火墙（重启服务生效）
systemctl enable firewalld.service
systemctl disable firewalld.service
  • 开启防火墙3306端口
sudo firewall-cmd --zone=public --add-port=3306/tcp --permanent  # 开放3306端口
sudo firewall-cmd --reload                     # 重启防护墙
sudo firewall-cmd --list-port             # 查看当前开放的端口列表
  • 用哪个端口就开放哪个端口，直接关闭防火墙虽然方便，但是实际工作却不能这样
image-20240421225335827
image-20240421225335827

二、master配置

1. 开启二进制日志

sudo vi /etc/mysql/mysql.conf.d/mysqld.cnf

配置log_bin和，和slave区分开，不能配置成一样的（）

开启二进制日志后需要重启mysql服务

# 重启mysql，如果mysql报错，就改成mysqld
systemctl restart mysql
systemctl start mysql
systemctl stop mysql

# 查看mysql状态
systemctl status mysql

root@ubuntu-vm:/usr/bin# systemctl start mysql
root@ubuntu-vm:/usr/bin# systemctl status mysql
● mysql.service - MySQL Community Server
     Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: enabled)
     Active: active (running) since Wed 2022-11-16 16:50:56 CST; 5s ago
    Process: 8333 ExecStartPre=/usr/share/mysql/mysql-systemd-start pre (code=exited, status=0/SUCCESS)
   Main PID: 8341 (mysqld)
     Status: "Server is operational"
      Tasks: 39 (limit: 4582)
     Memory: 367.3M
     CGroup: /system.slice/mysql.service
             └─8341 /usr/sbin/mysqld      # 这是mysqld路径

11月 16 16:50:55 ubuntu-vm systemd[1]: Starting MySQL Community Server...
11月 16 16:50:56 ubuntu-vm systemd[1]: Started MySQL Community Server.
root@ubuntu-vm:/usr/bin# netstat -tanp | grep 3306
tcp        0      0 0.0.0.0:3306            0.0.0.0:*               LISTEN      9538/mysqld         
tcp        0      0 127.0.0.1:33060         0.0.0.0:*               LISTEN      9538/mysqld         
root@ubuntu-vm:/usr/bin#

2.创建一个用于主从库通信用的账号

从库要连接主库，涉及到账号登陆和身份验证，虽然可以继续使用root账号，但是我们选择重新创建一个新的账号

即在master中创建一个账号，用于slave登录master读取binlog

虽然我们在Linux上查看的ip地址是192.168.131.129，但我们创建账户登录时不写这个ip，写的是192.168.131.1。因为我这里虚拟机用的是NAT模式（桥接模式是虚拟机直连物理网络，和物理机的网络地位相同），虚拟机（master）和物理机（windows，slave）通信的时候，虚拟机先把数据发送到网关192.168.131.1（默认与VMnet8通信），192.168.131.1再转发到物理机，所以物理机接收到的是192.168.131.1的数据，故我们在master上为slave创建账户的时候，应该写192.168.131.1

如果给slave配置的不是网关192.168.131.1地址，错误日志（可在my.cnf中指定）中会有如下信息：

意思是从192.168.131.1的mslave用户权限不够，那是因为我们在master上配置的是允许从其他地址登录，并不是允许从192.168.131.1地址登录，这就导致权限不够。

由于master这边收到的就是来自192.168.131.1的请求，所以错误日志显示的是192.168.131.1

所以创建账户的命令应如下：

mysql> CREATE USER 'mslave'@'%' IDENTIFIED BY '14ds@EGBFV'; # “%”表示任意ip都可以通过这个账号登录
mysql> CREATE USER 'mslave'@'192.168.131.1' IDENTIFIED BY '14ds@EGBFV'; #为了权限最小化，权限收缩，我们指定ip才能登录
mysql> create user 'mslave'@'192.168.131.1' identified WITH mysql_native_password by '123456';#弱密码

mysql> GRANT REPLICATION SLAVE ON *.* to 'mslave'@'192.168.131.1' IDENTIFIED BY '14ds@EGBFV';  -- 给mslave用户主从同步的权限（REPLICATION SLAVE）mysql5.7
mysql> GRANT REPLICATION SLAVE ON *.* to 'mslave'@'192.168.131.1';#mysql8


mysql> FLUSH PRIVILEGES;                   -- 刷新权限，使权限生效

开启主从复制的权限，从库可以通过这个账户和密码，从这个IP来请求访问这个主库上的任意库，同步这个主库的任意库里的任意表

  • *.*表示任意库任意表，当然也可以指定库指定表，如school.user

（）

3.获取binlog文件名和position

查看当前二进制日志的名字，主库的更新是往哪个binlog写的，以及当前写日志的位置，font>

show master status;

三、slave配置

1. 配置全局唯一的server-id

找到my.ini

配置全局唯一的server-id

重启MySQL服务

2. 使用master创建的账户读取binlog同步数据

这一步配置主要是给IO线程读取binlog使用

mysql> CHANGE MASTER TO MASTER_HOST='192.168.131.129',
MASTER_PORT=3306,
MASTER_USER='mslave',
MASTER_PASSWORD='1qaz@WSX',
MASTER_LOG_FILE='mysql-bin.000007',
MASTER_LOG_POS=1262;

MASTER_HOST：指定master的ip MASTER_LOG_FILE：binlog文件名 MASTER_LOG_POS：binlog的position

3. 启动slave服务

mysql> start slave;
mysql> stop slave;

通过show slave status命令查看主从复制状态，show processlist查看master和salve相关线程的运行状态

自己配置的时候不知道为什么，slave总是连接不上master，重新在mysql.user表中设置一下密码，就能连接上了

四、配置中可能出现的问题

1. 网络连接问题

[mysql] 主从同步错误Last_IO_Errno: 2061_last_io_errno:2061-CSDN博客open in new window

Mysql主从同步配置字符集问题解决_mysqlbinlog: character set '#255' is not a compile-CSDN博客open in new window

技术分享 | 从 MySQL 8.0 复制到 MySQL 5.7 - 知乎 (zhihu.com)open in new window

MySQL 复制仅在连续主版本之间支持，并且仅从主低到从高之间支持。

master 5.7 - > slave 8.0 支持

master 8.0 - > slave 5.7

通过show slave status命令查看主从复制状态

连接connection错误，先考虑是否网络互通，ping一下

然后再检查从库里面的配置信息是否正确

如果都正确，还可以检查一下master的3306端口是否可以连接

telnet xxx.xxx.xxx.xxx 3306

最重要的是，自己玩的时候，如果虚拟机是NAT模式，则需要写成VMnet8网关ip。如果都是物理机通信，那直接写正确的ip即可

可以在MySQL数据库下的mysql库的user表中更改允许登录的ip

然后重新赋予权限

mysql> GRANT REPLICATION SLAVE ON *.* to 'mslave'@'xxx.xxx.xxx.xxx' IDENTIFIED BY '1qaz@WSX';

出现错误后还可以查看错误日志中提示的ip是否和自己允许slave登录的ip一致

2. binlog的position问题

在master中查看show master status一下binlog日志文件名以及position，然后用命令重新配置slave，比如：

mysql> CHANGE MASTER TO MASTER_HOST='192.168.131.129',MASTER_PORT=3306,MASTER_USER='mslave',MASTER_PASSWORD='1qaz@WSX',
MASTER_LOG_FILE='mysql-bin.000006',MASTER_LOG_POS=1262;

配置slave前需要stop slave，配置完成再start slave

3.SQL线程出错

问题发生原因如下：

首先配置主从复制的时候，slave的mytest库中没有user表，而master的mytest库已经有user表了

配置好主从复制后直接drop table mytest.user，这就会写到binlog里面，然后在通过dump线程和IO线程将这个操作发送到从库的relay log，然后从库的SQL线程从relay log里把drop table mytest.user捞出来在从库执行这个SQL，可从库的mytest根本就没有user表，这就是====

一般我们不会做这样的操作，一般都是主从配置以后，slave从数据开始增量进行同步，先做数据的增量，然后做数据的增删改查

不会配置好主从复制后，一开始就删主库的东西，如果真的出现这样的问题，随时可以在从库 show slave status，来查看主从同步的状态，有什么错误，就相应解决

mysql> stop slave;
mysql> set global sql_slave_skip_counter=1;
mysql> start slave;

可以通过show slave status查看以下标识，IO线程出错一般是，SQL线程出错一般是

总结

在网络互通的前提下，进行配置

master配置

  1. 配置文件配置server-id，开启binlog
  2. 创建slave账户并配置复制权限，用于登录主库取数据
  3. show master status; 查看当前binlog文件以及位置

slave配置

  1. 配置server-id
  2. 使用CHANGE MASTER TO 命令，将master的ip、port、账号、密码、binlog文件名、binlog位置告诉slave
  3. start slave

出错后可以查看错误日志

Copyright © 2024 张威