Linux - MySQL搭建一主一从、多主一从

首先安装MySQL，请参考Linux - MySQL安装，多主一从主要是公司小项目比较多，大部分还没有备份，多主一从是MySQL5.7出现的策略，使用多主一从可以将多个数据库进行异步复制，进行一个备份的作用，也可以进行统计一系列的分析

准备

测试机	作用
192.168.1.11	从机
192.168.1.12	主机
192.168.1.13	主机

三台机器都安装MySQL，安装后进行远程测试看看每一台机器能不能通，能通之后剩下的都好说

12号机
12号机创建数据库和表

create database student;
use student
create table student_info(
id int,
name varchar(20) not null comment '姓名',
phone varchar(20) comment '手机号',
primary key(id)
);
insert into student_info values(1,'张三','123');
insert into student_info values(2,'李四','234');
# 此处可以创建一个复制用的用户，当然也可以使用root用户
create user '用户名'@'从机IP' identified by '密码';  # 此账户指定只有从机IP可以使用
grant REPLICATION SLAVE on *.* to '用户名'@'从机IP';  # 给这个账户复制的全部权限
flush privileges; # 刷新
show grants for '用户名'@'从机IP'; # 查看权限

13号机
13号机创建数据库和表

create database teacher;
use teacher
create table teacher_info(
id int,
name varchar(20) not null comment '姓名',
phone varchar(20) comment '手机号',
primary key(id)
);
insert into teacher_info values(1,'老师1','345');
insert into teacher_info values(2,'老师2','456');
# 同上，这个主机也可以创建复制用户

配置

主从复制需要在/etc/my.cnf进行新的配置

主机配置

需要注意的是server_id在主从复制中每台机器保证唯一，下方为12号机的配置（主机），13号机配置只有server_id是不同的其他一样

[mysqld]
basedir=/usr/local/mysql/mysql8
datadir=/usr/local/mysql/data
log-error=/usr/local/mysql/log/mysql.log
character-set-server=utf8mb4
server_id=12
gtid_mode=on
enforce_gtid_consistency=on       
binlog_format=row
log-slave-updates=1
log-bin=mysqlbin-log          
default_authentication_plugin='mysql_native_password'

从机配置

由于我们搭建的是多主一从(多源配置)，从机需要添加两个配置其他都一样，下方为11号机的配置（从机）

[mysqld]
basedir=/usr/local/mysql/mysql8              # MySQL安装目录
datadir=/usr/local/mysql/data                # MySQL数据地址
log-error=/usr/local/mysql/log/mysql.log     # MySQL日志地址
character-set-server=utf8mb4         # 编码
master_info_repository    = TABLE    # 配置多源时需要配置，多源使用了channel
relay_log_info_repository = TABLE    # 配置多源时需要配置，多源使用了channel                      
server_id=11                         # 唯一值，搭建的主从复制中每一台都不一样
gtid_mode=on                         # 开启gtid方式进行复制，另一种为log日志复制
enforce_gtid_consistency=on          # 使用gtid方式需要开启的                
binlog_format=row                    # 复制的方式 三种 自行了解
log-slave-updates=1                  # 开启可实现三级级联同步
log-bin=mysqlbin-log                 # 启动log_bin   
default_authentication_plugin='mysql_native_password'   # 默认” mysql_native_password “认证方式

将这三台机器配置好后重启一下MySQL使配置生效

一主一从

搭建多主一从时我们先搭建一个一主一从，然后再进行扩展

以下操作为11号机从机执行，我MySQL安装在了/usr/local/mysql下面，不会安装的参考我之前的文件

mkdir /usr/local/mysql/tem   # 创建一个存放其他主机数据库数据的文件夹
/usr/local/mysql/mysql8/bin/mysqldump -h 192.168.1.12 -u root -p -R -E --single-transaction --source-data --databases student > /usr/local/mysql/tem/student.sql     # 导出12号主机的student库的数据，会提示一个警告，忽略即可
grep -w  "GTID_PURGED" /usr/local/mysql/tem/student.sql   # 获取导出的库的GTID_PURGED，留着下面用
# SET @@GLOBAL.GTID_PURGED=/*!80000 '+'*/ '3bb2a5eb-92be-11ec-937d-d0946676a3f3:1-10'; 数值一 这个是我自己的gtid
MySQL：create database student      # 在从库创建一个student库
mysql -u root -p  student < /usr/local/mysql/tem/student.sql    # 输入本机MySQL的密码将主库的数据导进来
mysql -uroot -p    # 进入从库的MySQL中，下面的操作MySQL中执行

问题：导出的数据中有10条，再从库没有连接主库时，主库的数据增加到了100条，当从库使用10条的gtid时，会把剩余的90条同步过来吗？
会，已测试！

下面的操作均在11号机从库的MySQL中执行，执行中如有异常请参考文章最好的异常解决

SET @@GLOBAL.GTID_PURGED='3bb2a5eb-92be-11ec-937d-d0946676a3f3:1-10';    # 设置gtid值，上面说数值一
change master to master_host='192.168.1.12',master_port=3306,master_user='root',master_password='M$5mN8bVp',master_auto_position=1 for channel 'master1';   # 连接12号主库创建管道名为msater1
start slave for channel 'master1';   # 单独启动master1进行复制，可使用start slave;
show slave status\G   # 查看从库的状态，状态中Slave_IO_Running与Slave_SQL_Running同为yes时正常，不然就是异常
show master status\G  # 观察自己这个管道的gtid，记录下来,为之后的多主做准备，此处应该先停止slave，然后查看master的gtid，然后清空master
# 3bb2a5eb-92be-11ec-937d-d0946676a3f3:1-11 数值二  可能会有多个值，观察自己有几个管道，然后去看每个管道的Master_UUID然后对比获取
stop slave;  reset master;  reset slave all;   # 停止复制slave，清除master
create database teacher  # 创建13号机的数据库，准备

然后就可以自行进行测试，从主库中添加数据观察从库是否进行了异步复制。

多主一从

多主一从和上面的操作大部分相同，只有部分不一样

以下操作为11号机从机执行，在搭建多主一从的时候注意异常，有问题看文章最后的异常解决

/usr/local/mysql/mysql8/bin/mysqldump -h 192.168.1.13 -u root -p -R -E --single-transaction --source-data --databases teacher > /usr/local/mysql/tem/teacher.sql   # 导出13号主机的teacher库的数据，会提示一个警告，忽略即可
grep -w  "GTID_PURGED" /usr/local/mysql/tem/teacher.sql  # 获取13号机MySQL数据的gtid
# SET @@GLOBAL.GTID_PURGED=/*!80000 '+'*/ '3bc79f22-92be-11ec-abd3-d0946676a3c3:1-10'; 数值三 记录好等会使用
mysql -u root -p  teacher < /usr/local/mysql/tem/teacher.sql  # 将13号机的数据库导入到11号从机上面
mysql -uroot -p    # 进入MySQL

下面的操作均在11号机从库的MySQL中执行，执行中如有异常请参考文章最好的异常解决

SET @@GLOBAL.GTID_PURGED='3bb2a5eb-92be-11ec-937d-d0946676a3f3:1-11,3bc79f22-92be-11ec-abd3-d0946676a3c3:1-10';  # 此处的值为数值二和数值三，将上方的gtid一起设置进去
change master to master_host='192.168.1.12',master_port=3306,master_user='root',master_password='M$5mN8bVp',master_auto_position=1 for channel 'master1';   # 连接主库12号机
change master to master_host='192.168.1.13',master_port=3306,master_user='root',master_password='M$5mN8bVk',master_auto_position=1 for channel 'master2';   # 连接主库13号机
start slave for channel 'master1';    # 启动管道1
start slave for channel 'master2';    # 启动管道2
show slave status\G   # 查看从库的状态，此时从库已经是有两个master了

测试两个主库添加修改，看从库的效果
根据上面添加的思路，再多添加几个主库都是一样的思路
同时一个MySQL可能需要同步多个数据库，这个时候一样可以参考上面的方案，也可以使用MySQL过滤复制（网上搜索）

异常解决

Host ‘192.168.1.11’ is blocked because of many connection errors;

主从库都要执行下面的命令
/usr/local/mysql/mysql8/bin/mysqladmin flush-hosts -uroot -p

Multiple channels exist on the slave. Please provide channel name as an argument

需要将从机的master清空（本机连接的有master的也要清空）
stop slave; reset slave all; reset master;

@@GLOBAL.GTID_PURGED cannot be changed: the added gtid set must not overlap with @@GLOBAL.GTID_EXECUTED

reset master;

@@GLOBAL.GTID_PURGED cannot be changed: the new value must be a superset of the old value

之前已经存在gtid了想再需要配置一个多个的
SET @@GLOBAL.GTID_PURGED=‘以前的gtid…,新的gtid’;

结尾

至此MySQL的多主一从已经全部搭建好了，如果有问题或者有其他好的方案望提议，共同学习。