MySQL集群-双主实验

master1:192.168.13.19

master2:192.168.13.20

先做master1为主，master2为从的实验【一主一从】

hostnamectl set-hostname master1	192.168.13.19    //修改主机名
hostnamectl set-hostname master2	192.168.13.20    //修改主机名
vim /etc/hosts   //配置域名解析
192.168.13.19   master1
192.168.13.20   master2

首先在master1上插入数据（准备数据、做验证同步使用）

在生产环境中，数据库之前的数据是不会进行主从同步的，只有同步之后的数据才会在主从之间都拥有，所以，之前的数据备份还原，拷到从服务器上面。

mysql> create database masterdb;    //创库
mysql> create table masterdb.t1(name char(20));    //创表
mysql> insert into masterdb.t1 values (11111);    //插入数据
mysql> insert into masterdb.t1 values (222222);

开启二进制日志【在集群同步时，需要依赖主服务器的二进制日志文件来进行同步】

[root@master1 ~]# vim /etc/my.cnf
log_bin    //开启二进制日志
server-id=1    //server-ID（区分）
[root@master1 ~]# systemctl restart mysqld    //重启数据库，使其生效

 创建复制用户，专门用户进行主从同步使用的用户，并设置权限

#创建授权用户rep，密码为Yangyang@123
mysql> grant replication slave on *.* to 'rep'@'192.168.13.%' identified by 'Yangyang@123';
Query OK, 0 rows affected, 1 warning (0.01 sec)
#刷新
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

#将之前数据库插入的数据进行备份
[root@master1 ~]# mysqldump -p'Yangyang@123' --all-databases --single-transaction --master-data=2 --flush-logs > `date +%F`-mysql-all.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@master1 ~]# ls
2022-07-21-mysql-all.sql  anaconda-ks.cfg  mysql80-community-release-el7-6.noarch.rpm

 将备份文件拷贝给master2

 观察二进制日志分割点

[root@master2 ~]# vim /tmp/2022-07-21-mysql-all.sql 

 准备数据2（验证同步）

mysql> insert into masterdb.t1 values (33333);
Query OK, 1 row affected (0.01 sec)

mysql> insert into masterdb.t1 values (444444);
Query OK, 1 row affected (0.00 sec)

 master2（从配置）

测试rep用户是否可用

#验证master2是否可以登录测试用户rep
[root@master2 ~]# mysql -urep -p'Yangyang@123' -hmaster1
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.7.38-log MySQL Community Server (GPL)

Copyright (c) 2000, 2022, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.


mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
+--------------------+
1 row in set (0.00 sec)

[root@master2 ~]# vim /etc/my.cnf

server-id=2


[root@master2 ~]# systemctl restart mysqld    //重启数据库
[root@master2 ~]# mysql -uroot -p'Yangyang@123'    //测试数据库是否可用

手动同步数据

master2配置与测试
mysql> set sql_log_bin=0 ; //关闭二进制日志
Query OK, 0 rows affected (0.00 sec)

mysql> source /tmp/2022-07-21-mysql-all.sql;    //读取数据文件
mysql> select * from masterdb.t1;    //master1的原数据已经同步恢复到master2
+--------+
| name   |
+--------+
| 11111  |
| 222222 |
+--------+
2 rows in set (0.01 sec)

mysql> 

设置主服务器

mysql> change master to master_host='master1',
    -> master_user='rep',
    -> master_password='Yangyang@123',
    -> master_log_file='master1-bin.000003',
    -> master_log_pos=154;
Query OK, 0 rows affected, 2 warnings (0.03 sec)

mysql> start slave;    //启动从设备
Query OK, 0 rows affected (0.02 sec)

mysql> select  * from masterdb.t1;//在master2上可以看到master1的数据
+--------+
| name   |
+--------+
| 11111  |
| 222222 |
| 33333  |
| 444444 |
+--------+
4 rows in set (0.00 sec)

 在master1插入数据，可以同步到master2上，一主一从实验已完成。

下面开始双主双从的实验。（在一主一从的基础上进行）

之前是以设置master1为master2的主服务器，现在只需要将master2设置为master1的主。

在master1和master2上配置-自动同步

master1
vim /etc/my.cnf
log_bin
server-id=1
gtid_mode=ON
enforce_gtid_consistency=1

master2
vim /etc/my.cnf
log_bin
server-id=2
gtid_mode=ON
enforce_gtid_consistency=1

在master2上进行授权

master2的主配置
mysql> grant replication slave on *.* to 'rep'@'192.168.13.%' identified by 'Yangyang@123';
Query OK, 0 rows affected, 1 warning (0.03 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)

mysql> 

master1

change master to
master_host='master2',
master_user='rep',
master_password='Yangyang@123',
master_auto_position=1;

start slave;