Rocky9MySQL主从复制、故障诊断和实现mysql读写分离

实验目的

掌握mysql主从复制，新主机加入到已经进行的mysql主机中，出现的一些主从配置的故障进行诊断，如果主节点已经运行了一段时间，且有大量数据时，如何配置并启动slave节点，通过备份恢复数据至从服务器，复制起始位置为备份时，二进制日志文件及其POS，掌握mysql的读写分离。

实验主要内容

1、mysql主机实现主从复制

2、新mysql主机加入到已有的主从主机中

3、进行主从配置故障诊断

4、mycat实现mysql读写分离

实验环境与准备

4台拍完快照配置好的linux主机，4台均为rocky9主机且安装mysql

四、实验分析与设计思路

建议基于画图、思维导图等模式，结合文字描述，写的清晰、明了、得体

主从复制基本原理

主节点负责数据的写操作，此时要求主节点启用二进制日志(bin-log)，此时数据库的更改写入到二进制日志中。

在MySQL主服务器上会生成开启dump Thread 将新生成的二进制日志读出来，同时通过网络发送给从节点。

从节点开启I/O线程（I/O Thread，用于接收dump Thread传送过来的二进制日志），使用relay-log存放二进制日志。然后从服务器开启一个单独的线程（SQL Thread，用于读取relay-log 然后执行修改数据库中的数据）

五、详细实验过程

1、mysql主机先实现一主一从复制

为当前节点设置一个全局惟一的ID号 ，因为主机位100，所以就设置为100吧，同时每个从节点mysql也要加入server-id，设置为他们对应的主机号

从节点110

然后主节点mysql创建用户repluser，并为其设置密码和授权

create user 'repluser'@'%' identified by '123456';

这里需要注意的是为用户设置密码时，需要使用旧版本native设置，否则后面连接的时候会出现报错连接不上

alter user 'repluser'@'%' identified with mysql_native_password by '123456';

GRANT REPLICATION SLAVE ON *.* TO 'repluser'@'%';

flush privileges;

将二进制日志文件进行删除，恢复到157的状态

在从节点使用有复制权限的用户账号连接至主服务器，并启动复制线程

mysql> change master to

-> master_host='192.168.10.100',

-> master_user='repluser',

-> master_password='123456',

-> master_port=3306,

-> master_log_file='mysql-bin.000001',

-> master_log_pos=157;

开启从节点主从同步线程 start slaves; / START REPLICA;(高版本)

show slave status\G 查看状态

在主节点中创建一个baby表，然后看看备机中会不会进行同步

在备机中查看确实已经有了创建的baby表，说明主从复制成功

2、新mysql主机加入到已有的主从主机中

vim test_2024-04-24_19-28-59.sql，将内容进行修改，使其指向100主节点

开启从节点主从同步线程 START REPLICA;

show slave status\G 可以看到新添加的主机已经与主机100进行主从复制

主节点插入字段内容，新连接的从复制器也产生了复制

3、进行主从配置故障诊断

进行主从配置故障的测试我们可以进行从节点主机进行写的操作，因为默认情况下mysql从服务器是不能进行写操作，但是由于mysql的root用户无视此规则，所以可以进行从服务器写入操作，因为主从复制是主机单向复制到从服务器，当从服务器写入的时候，就会产生主从配置故障，即可以模拟故障发生

在新加入的备机120中插入一个字段，同时查看主节点和备节点的表情况，下表为主节点

此表为备节点的表内容，可以看出主节点和备节点的表内容已经产生了区别

可以看到在412的时候出现了错误

从节点先停掉slave，忽略错误事件

stop slave;

set global sql_slave_skip_counter=1;

从节点修复错误，修改为和主节点一致的

delete from baby where id =3;

从节点重新启动，此时不在报错

START REPLICA;

SHOW REPLICA STATUS\G

主节点插入一条数据，然后再看从节点

从节点同样也已经产生了

4、mycat实现mysql读写分离

yum -y install java mysql java-11-openjdk-devel，将mycat压缩包移到130主机中，然后解压缩，添加环境变量，启动mycat服务，查看mycat端口，9066

将每个主备节点的通用日志全部打开，可以临时生效也可以永久生效，永久生效需要再配置文件中添加general_log

将两个配好的文件移到conf中，对这两个文件的权限进行修改

chmod 777 server.xml

chmod 777 schema.xml

vim /usr/local/mycat/conf/server.xml

vim /usr/local/mycat/conf/schema.xml

查看通用日志是否开启，1为开启

需要将主节点的数据库密码进行更改mysqladmin -u root flush-hosts -p否则会一直报错

连接到mycat主机的mysql上，mysql -u root -h 192.168.10.130 -p

可以看到已经处于轮询访问状态

验证读写分离性效果，在主节点开启通用日志查看写操作

tail -f /var/lib/mysql/node-1.log，可以看到没有什么操作

然后执行一次写操作，就会在日志中记录下来

mycat还可以实现主从健康状态监测，若读节点健康出问题，会将读操作转移到写节点运行。

将1台从节点进行强制挂机，再次进行读操作查看到读操作已经转移到了另一台从节点上，若两台都关闭则会转移到主节点

一台备节点关闭

两台均关闭

总结

不同的mysql版本可能会有不同的bug问题，比如在进行第2题的时候，从节点就自己出现问题了，也不知道为啥会出现这种结果，就恢复快照重新做了一遍，然后就好了，但是在第一次做的时候就是出不来结果，重新做就能出来，很奇怪，在以后得学习和工作中我们还会遇到各种各样奇葩的问题，别无他法，只能不断积累经验，才能更加游刃有余的解决问题。