mysql数据库主从同步

• 数据库版本
  主：5.7.34
  从：8.0.21

主数据库

1. 修改mysql配置文件 my.ini（windows）

• 查看配置文件存在位置

-- 查看配置文件存在位置
show variables like '%data%';
1
2

在Linux下叫my.cnf，该文件位于/etc/my.cnf

• 修改内容
  boo_db为同步的数据库名

	server-id=1
    log-bin=mysql-bin
    #目标数据库
    binlog_do_db=boo_db
1
2
3
4

2. 重启mysql服务
3. 创建指定从库才能访问的用户并赋权

create user 'slave'@'192.168.5.128' identified by '123456';
GRANT ALL PRIVILEGES ON *.* TO 'slave'@'192.168.5.128'; -- 给全部权限
FLUSH PRIVILEGES;

--修改连接的密码方式：
update mysql.user set plugin='mysql_native_password' where user='slave';
FLUSH PRIVILEGES;
1
2
3
4
5
6
7

从数据库

1. 在从机上创建从库：boo_db
2. 在从机上 用 'slave’账户连一下主数据库，看能不能连接上。能连接后面的操作才能成功。
3. 编辑从数据库的 my.ini 文件
   在 [mysqld] 节点中增加如下内容：

server-id=2 #指定唯一的ID，2至32，必须的，并且不能跟主数据库一样
replicate-do-db=boo_db # 指定要同步的数据库，必须的
replicate-ignore-db=cs_db# 指定不要同步的数据库。
1
2
3

4. 重启mysql服务
5. 在从机上连接主数据库

>> mysql -uslave -p123456 -h192.168.1.16
1

• 查询主服务器的状态信息，并且找到File 和 Position 的值记录下来；

 mysql> show master status;

+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000222|      1160| rpa-cloud    |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.01 sec)
1
2
3
4
5
6
7
8

6. 在从机启动slave
   6.1 重启数据库
   6.2 运行以下命令启动 slave

	mysql > change master to
		> master_host='192.168.1.16',
		> master_port=3306,
		> master_user='slave',
		> master_password='123456',
		> master_log_file='mysql-bin.000222',
		> master_log_pos=1160;

	mysql > start slave;
1
2
3
4
5
6
7
8
9

-- 确定需要同步的主机信息
change master to master_host='192.168.1.16',master_user='slave',master_password='123456',master_port=3306,master_log_file='mysql-bin.000222',master_log_pos=1160;

-- 开启 slave
start slave;

-- 关闭 slave
STOP slave;

-- 查看slave状态
show slave status;
1
2
3
4
5
6
7
8
9
10
11

master_host指向主机地址，
master_port指向主机端口
master_user是主库里面添加的只允许从库访问的用户
master_password是密码

7. 查看slave的运行状态：

	mysql > show slave status \G;
1

查看状态为：
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
时 slave 启动成功

8. 配置成功后可以在主库添加一个测试表格，看看从库是否发生了同样的变化。

问题解决

• Slave_IO_Running: NO

1. 查看 连接主数据库的 地址/密码是否正确，是否能正确连通
2. 配置没有加载上，重启从库 或 主库加载配置
3. 可以通过下面的方式查找异常的原因
   
   例如上图中显示的是The replication receiver thread cannot start because the master has GTID_MODE = ON and this server has GTID_MODE = OFF. 大致意思是 主库开启了 GTID 但是从库没有开启，这里开启从库的GTID就可以了，开启GTID的方法可以参考
   mysql 5.7在线开启/关闭GTIF

• Slave_SQL_Running: NO

4. 程序可能在slave上进行了写操作
5. 也可能是slave机器重起后，事务回滚造成的
6. 先关闭slave, 将数据库手动同步之后（完全同步）之后再启动slave, 怀疑为数据库不一致，当主库有操作同步到从库的时候找不到对应的结构或数据，导致出错，进而导致NO

一般是事务回滚造成的：
解决办法：

mysql> stop slave ;
mysql> set GLOBAL SQL_SLAVE_SKIP_COUNTER=1; -- 跳过这个错误
mysql> start slave ;
1
2
3

• Slave_IO_Running：Connecting
  问题原因：
  （1）网络不通
  （2）防火墙端口未开放
  （3）mysql账户密码错误
  （4）mysql主从机配置文件写错
  （5）配置从机连接语法错误
  （6）主机未开放账户连接权限