配置多主一从 目前是四主一从,并且主主互通
数据库版本 8版本
一、配置测试环境
Ip | 跨域账户 | 跨域账户密码 | 跨域账户通道名称(CHANNEL) | |
主库 | 192.168.1.100 | repl | repl | M_192_168_1_100_3306 |
192.168.1.181 | repl | repl | M_192_168_1_181_3306 | |
192.168.1.177 | repl | repl | M_192_168_1_177_3306 | |
192.168.1.116 | repl | repl | M_192_168_1_116_3306 | |
从库 | 192.168.1.109 | ----------------------------------------------------------------------------------------- |
注意1:库与主库的mysql 安装目录下 的data中的auto.cnf文件中的server-uuid要确保不一样,若一样,则随便更改一下即可
注意2:主机和从机中,数据表格以及数据需要一致,统计出目前最大的自增id为多少
二、配置文件:
1.主机配置文件:
|
2.从机配置文件:
|
- 命令
各自服务器分别通过cmd(需要管理员权限)登录mysql
打开cmd(需要用管理员权限打开)
进入mysql安装目录下的bin目录中
net stop mysql (该命令为停止数据库服务)
net start mysql (该命令为启动数据库服务)
分别使用命令登入mysql:mysql -u 用户名 -P 数据库端口号 -p
然后输入密码即可登入mysql
- 主机创造从机可以登录的跨域账户,以保证从机可以登录mysql的数据库(所有主机都需要执行)
主机 | 命令 |
192.168.1.100 | 进入mysql表中使用命令:Use mysql; 添加用户使用命令:create user ‘repl’@’%’ identified with mysql_native_password by ‘repl’; |
192.168.1.181 | |
192.168.1.177 | |
192.168.1.116 |
- 主机给创建好的用户授权(所有主机都需要执行)
主机 | 命令 |
192.168.1.100 | grant replication slave on *.* to 'repl'@'%'; |
192.168.1.181 | |
192.168.1.177 | |
192.168.1.116 |
- 主机给创建好的用户赋予相关权限(所有主机都需要执行)
主机 | 命令 |
192.168.1.100 | grant REPLICATION CLIENT ON *.* TO repl; grant REPLICATION SLAVE ON *.* TO repl; grant SUPER ON *.* TO repl; grant reload on *.* to repl; FLUSH PRIVILEGES; (FLUSH PRIVILEGES;是刷新权限的意思) |
192.168.1.181 | |
192.168.1.177 | |
192.168.1.116 |
- 主机和从机都配置以下主从复制关系(所有主机和从机都需要执行)
主机/从机 | 命令(每个里边都有两个命令,第二行的set 和@@后边的是一行命令) |
192.168.1.100 | RESET MASTER; SET @@GLOBAL.GTID_PURGED='0008c013-afb2-11ea-9fa9-005056b7a90a:1-4,1f7133df-aa0b-11e9-af7d-005056b7a90a:1-8158244:8158247-8199883,6b7f2891-5c90-11eb-b9a5-005056b7a90a:1-38,b012f5ec-5c8f-11eb-94e2-005056b7a90a:1-38,bca9ac2e-aa0a-11e9-ac0c-005056b71124:1-11393066,e308e300-a63c-11ea-ae39-005056b71124:1-2'; |
192.168.1.181 | |
192.168.1.177 | |
192.168.1.116 | |
192.168.1.109(从机也需要配置) |
- 从机配置复制关系(每个主机都执行对应的命令,对应各自的ip哦)
主机和从机 | 命令 |
192.168.1.109 (从机) | CHANGE MASTER TO MASTER_HOST='192.168.1.100', MASTER_USER='repl', MASTER_PORT=3306, MASTER_PASSWORD='repl', MASTER_AUTO_POSITION = 1 FOR CHANNEL 'M_192_168_1_100_3306'; |
CHANGE MASTER TO MASTER_HOST='192.168.1.177', MASTER_USER='repl', MASTER_PORT=3306, MASTER_PASSWORD='repl', MASTER_AUTO_POSITION = 1 FOR CHANNEL 'M_192_168_1_177_3306'; | |
CHANGE MASTER TO MASTER_HOST='192.168.1.181', MASTER_USER='repl', MASTER_PORT=3306, MASTER_PASSWORD='repl', MASTER_AUTO_POSITION = 1 FOR CHANNEL 'M_192_168_1_181_3306'; | |
CHANGE MASTER TO MASTER_HOST='192.168.1.116', MASTER_USER='repl', MASTER_PORT=3306, MASTER_PASSWORD='repl', MASTER_AUTO_POSITION = 1 FOR CHANNEL 'M_192_168_1_116_3306'; | |
192.168.1.100 (主机) | CHANGE MASTER TO MASTER_HOST='192.168.1.177', MASTER_USER='repl', MASTER_PORT=3306, MASTER_PASSWORD='repl', MASTER_AUTO_POSITION = 1 FOR CHANNEL 'M_192_168_1_177_3306'; |
CHANGE MASTER TO MASTER_HOST='192.168.1.181', MASTER_USER='repl', MASTER_PORT=3306, MASTER_PASSWORD='repl', MASTER_AUTO_POSITION = 1 FOR CHANNEL 'M_192_168_1_181_3306'; | |
CHANGE MASTER TO MASTER_HOST='192.168.1.116', MASTER_USER='repl', MASTER_PORT=3306, MASTER_PASSWORD='repl', MASTER_AUTO_POSITION = 1 FOR CHANNEL 'M_192_168_1_116_3306'; | |
192.168.1.177 (主机) | CHANGE MASTER TO MASTER_HOST='192.168.1.100', MASTER_USER='repl', MASTER_PORT=3306, MASTER_PASSWORD='repl', MASTER_AUTO_POSITION = 1 FOR CHANNEL 'M_192_168_1_100_3306'; |
CHANGE MASTER TO MASTER_HOST='192.168.1.181', MASTER_USER='repl', MASTER_PORT=3306, MASTER_PASSWORD='repl', MASTER_AUTO_POSITION = 1 FOR CHANNEL 'M_192_168_1_181_3306'; | |
CHANGE MASTER TO MASTER_HOST='192.168.1.116', MASTER_USER='repl', MASTER_PORT=3306, MASTER_PASSWORD='repl', MASTER_AUTO_POSITION = 1 FOR CHANNEL 'M_192_168_1_116_3306'; | |
192.168.1.181 (主机) | CHANGE MASTER TO MASTER_HOST='192.168.1.100', MASTER_USER='repl', MASTER_PORT=3306, MASTER_PASSWORD='repl', MASTER_AUTO_POSITION = 1 FOR CHANNEL 'M_192_168_1_100_3306'; |
CHANGE MASTER TO MASTER_HOST='192.168.1.177', MASTER_USER='repl', MASTER_PORT=3306, MASTER_PASSWORD='repl', MASTER_AUTO_POSITION = 1 FOR CHANNEL 'M_192_168_1_177_3306'; | |
CHANGE MASTER TO MASTER_HOST='192.168.1.116', MASTER_USER='repl', MASTER_PORT=3306, MASTER_PASSWORD='repl', MASTER_AUTO_POSITION = 1 FOR CHANNEL 'M_192_168_1_116_3306'; | |
192.168.1.116 (主机) | CHANGE MASTER TO MASTER_HOST='192.168.1.100', MASTER_USER='repl', MASTER_PORT=3306, MASTER_PASSWORD='repl', MASTER_AUTO_POSITION = 1 FOR CHANNEL 'M_192_168_1_100_3306'; |
CHANGE MASTER TO MASTER_HOST='192.168.1.181', MASTER_USER='repl', MASTER_PORT=3306, MASTER_PASSWORD='repl', MASTER_AUTO_POSITION = 1 FOR CHANNEL 'M_192_168_1_181_3306'; | |
CHANGE MASTER TO MASTER_HOST='192.168.1.177', MASTER_USER='repl', MASTER_PORT=3306, MASTER_PASSWORD='repl', MASTER_AUTO_POSITION = 1 FOR CHANNEL 'M_192_168_1_177_3306'; |
若执行“配置复制关系”报错GTID为OFF时,执行下方开启GTID命令,即可配置成功
1.在所有主从实例结构中执行: set global ENFORCE_GTID_CONSISTENCY = WARN; ##说明,这是一个警告模式,如果有还没有执行完的sql 2.如果没有警告,所有实例中执行: set global ENFORCE_GTID_CONSISTENCY = ON; 3.在所有主从实例中执行: set global GTID_MODE = OFF_PERMISSIVE; 4.在所有主从实例中执行: set global GTID_MODE = ON_PERMISSIVE; 5,在所有主从实例中执行,检查是否还有事务没有结束: SHOW STATUS LIKE 'ONGOING_ANONYMOUS_TRANSACTION_COUNT'; #注意value一定要为0 6.检查slave(从库)的binlog点位,如果还没有应用完binlog,则需等待 show slave status\G; 在所有主从实例中执行: set global GTID_MODE = ON; 已经升级为GTID模式了。 最后记得更改所有主从服务中的my.cnf文件: gtid_mode = on enforce_gtid_consistency = on |
配置完毕后,先在主库和从库中输入停止服务命令 (以防万一,有备无患嘛,嘿嘿)
主库和从库 | 停止“从库复制”命令 |
192.168.1.100 (主库) | stop SLAVE FOR CHANNEL "M_192_168_1_177_3306"; stop SLAVE FOR CHANNEL "M_192_168_1_181_3306"; stop SLAVE FOR CHANNEL "M_192_168_1_116_3306"; |
192.168.1.177 (主库) | stop SLAVE FOR CHANNEL "M_192_168_1_100_3306"; stop SLAVE FOR CHANNEL "M_192_168_1_181_3306"; stop SLAVE FOR CHANNEL "M_192_168_1_116_3306"; |
192.168.1.181 (主库) | stop SLAVE FOR CHANNEL "M_192_168_1_100_3306"; stop SLAVE FOR CHANNEL "M_192_168_1_177_3306"; stop SLAVE FOR CHANNEL "M_192_168_1_116_3306"; |
192.168.1.116 (主库) | stop SLAVE FOR CHANNEL "M_192_168_1_100_3306"; stop SLAVE FOR CHANNEL "M_192_168_1_177_3306"; stop SLAVE FOR CHANNEL "M_192_168_1_181_3306"; |
192.168.1.109 (从库) | stop SLAVE FOR CHANNEL "M_192_168_1_100_3306"; stop SLAVE FOR CHANNEL "M_192_168_1_177_3306"; stop SLAVE FOR CHANNEL "M_192_168_1_181_3306"; stop SLAVE FOR CHANNEL "M_192_168_1_116_3306"; |
然后依次在主库和从库上执行上方第4个表格配置复制关系中的命令即可,例如:
CHANGE MASTER TO MASTER_HOST='192.168.1.100', MASTER_USER='repl', MASTER_PORT=3306, MASTER_PASSWORD='repl', MASTER_AUTO_POSITION = 1 FOR CHANNEL 'M_192_168_1_100_3306';
将从库和所有主库的mysql服务重启一下,然后再做以下操作
在主库和从库中都输入以下命令锁定master服务
FLUSH TABLE WITH READ LOCK;
依次执行完毕后,即可在主库和从库中开启从库复制啦:
主库和从库 | 开启从库复制命令 |
192.168.1.100 (主库) | start SLAVE FOR CHANNEL "M_192_168_1_177_3306"; start SLAVE FOR CHANNEL "M_192_168_1_181_3306"; start SLAVE FOR CHANNEL "M_192_168_1_116_3306"; |
192.168.1.177 (主库) | start SLAVE FOR CHANNEL "M_192_168_1_100_3306"; start SLAVE FOR CHANNEL "M_192_168_1_181_3306"; start SLAVE FOR CHANNEL "M_192_168_1_116_3306"; |
192.168.1.181 (主库) | start SLAVE FOR CHANNEL "M_192_168_1_100_3306"; start SLAVE FOR CHANNEL "M_192_168_1_177_3306"; start SLAVE FOR CHANNEL "M_192_168_1_116_3306"; |
192.168.1.116 (主库) | start SLAVE FOR CHANNEL "M_192_168_1_100_3306"; start SLAVE FOR CHANNEL "M_192_168_1_177_3306"; start SLAVE FOR CHANNEL "M_192_168_1_181_3306"; |
192.168.1.109 (从库) | start SLAVE FOR CHANNEL "M_192_168_1_100_3306"; start SLAVE FOR CHANNEL "M_192_168_1_177_3306"; start SLAVE FOR CHANNEL "M_192_168_1_181_3306"; start SLAVE FOR CHANNEL "M_192_168_1_116_3306"; |
启动成功后,在所有主库和从库中都输入以下命令对master服务解锁:
UNLOCK TABLES;
解锁后,在主库和从库中 查看show slave status\G 以下两个状态都为yes即可
Slave_IO_Running: YES
Slave_SQL_Running: YES
若Slave_IO_Running为no时,可查看 Last_IO_Error后边带的错误日志,若是以下错误,则可将主机和从机中的复制命令停止后,将log_bin中指定的日志目录下所有的日志删除,然后锁定msater服务,然后启动slave复制命令,最后再将master服务解锁即可
错误信息 | Got fatal error 1236 from master when reading data from binary log: 'Cannot replicate because the master purged required binary logs. Replicate the missing transactions from elsewhere, or provision a new slave from backup. Consider increasing the master's binary log expiration period. The GTID sets and the missing purged transactions are too long to print in this message. For more infor' |
后期调配停止启动slave从库复制时,可在cmd(管理员全权限的)中,通过以下命令远程链接各个服务器,此处是在从库服务器上远程连接其余四个主机服务器
第一步:进入从库的Mysql安装目录下的bin目录下,输入命令mysql -h 192.168.1.100 -u root -p,然后输入密码即可登录
第二步:在主库和从库停止了slave从库复制后,在从机和主机中都输入锁定master服务命令,
第三步:从库和主库都输入exit;命令,退出当前登录,然后即可对各个服务器中的数据库表格做操作,此时就可以将my.ini配置文件中的log_bin中指定的日志目录下所有的日志删除,防止后期“日志读取报错”,随后将mysql服务进行重启;
第四步:数据库表格操作完之后,再登录进去各个mysql服务中,先将主库和从库中的多个对应的slave从库服务开启,再输入解锁master命令,从库的解锁之后,再依次将各个主库的msater解锁即可
接下来就可以测试数据是否互通啦。