1）规则：
1） db-51 192.168.4.51 Mysql 5.7
2） db-52 192.168.4.52 Mysql 5.7
vip： 192.168.4.50
2）创建Mysql互为主从：
1、在192.168.4.51 （db-51）安装Mysql，创建同步复制用户（用于192.168.4.52（db-52）主机同步数据）
1 [[email protected]51 ~]#tar -xf mysql-5.7.17.tar -C mysql 2 [[email protected]51 ~]#yum -y install net-tools perl-Data-Dumper perl-JSON 3 [[email protected]51 mysql]#cd mysql 4 [[email protected]51 mysql]#rpm -Uvh mysql-community-*.rpm 5 [[email protected]51 mysql]#systemctl start mysqld 6 [[email protected]51 mysql]#grep password /var/log/mysqld.log 7 [[email protected]51 mysql]#mysql -uroot -p‘y8glj!u=ldQ%‘ 8 > set global validate_password_policy=0; 9 > set global validate_password_length=6; 10 > set password=‘123456‘; //5.7开始设置mysql数据root用户密码 11 > exit 12 [[email protected]51 mysql]# vim /etc/my.cnf 13 validate_password_policy=0 14 validate_password_length=6 15 16 server_id=51 //设置mysql的server id 17 log_bin=/var/lib/mysql/mysql-master-51 18 binlog_format=mixed 19 20 21 [[email protected]51 mysql]#systemctl stop firewalld 22 [[email protected]51 mysql]#systemctl disable firewalld 23 [[email protected]51 mysql]#systemctl restart mysqld 24 [[email protected]51 mysql]#mysql -uroot -p 25 > grant replication slave on *.* to [email protected]‘%‘ identified by ‘123456‘; //创建主从复制用户 26 > show warnings; 27 > show grants for [email protected]‘%‘; //查看用户权限 28 > flush table with read lock; //给服务加读写锁，注意：关闭本连接session后就自动解锁了。 29 > show master status; //查看本机作为主库状态信息。
2、在192.168.4.52 （db-52）安装Mysql ，设置同步主库信息，同时创建同步复制用户（用于192.168.4.51 （db-51）主机同步数据）。
1 [[email protected]52 ~]#tar -xf mysql-5.7.17.tar -C mysql 2 [[email protected]52 ~]# yum -y install net-tools perl-Data-Dumper perl-JSON 3 [[email protected]52 ~]#cd mysql 4 [[email protected]52 mysql]#rpm -Uvh mysql-community-*.rpm 5 [[email protected]52 mysql]#cd ~ 6 [[email protected]52 ~]#systemctl start mysqld 7 [[email protected]52 ~]#grep password /var/log/mysqld.log 8 [[email protected]52 ~]#mysql -uroot -p‘k:sv!HWtV7d>‘ 9 > set global validate_password_policy=0; 10 > set global validate_password_length=6; 11 > set password=‘123456‘; //5.7开始 12 > exit 13 [[email protected]52 ~]#vim /etc/my.cnf 14 [mysqld] 15 validate_password_policy=0 16 validate_password_length=6 17 18 server_id=52 19 log_bin=/var/lib/mysql/mysql-master-52 20 binlog_format=mixed 21 22 [[email protected]52 ~]#systemctl stop firewalld 23 [[email protected]52 ~]#systemctl disable firewalld 24 [[email protected]52 ~]#systemctl restart mysqld 25 [[email protected]52 ~]#mysql -uroot -p 26 > reset slave; //重置从库服务，即清空slave服务 27 > show slave status; 28 > change master to //配置主库 29 -> master_host=‘192.168.4.51‘, 30 -> master_user=‘repl‘, 31 -> master_password=‘123456‘, 32 -> master_log_file=‘mysql-master-51.000001‘, 33 -> master_log_pos=437 34 -> ; 35 > start slave; //启动slave服务 36 > show slave statusG 37 > create user [email protected]‘%‘ identified by ‘123456‘; //创建主从复制用户 38 > grant replication slave on *.* to [email protected]‘%‘; //给用户授权 39 > show grants for [email protected]‘%‘; //查看用户权限 40 > show master status; //查看本机作为主库信息。
3、在192.168.4.51 （db-51）上Mysql中设置同步主库信息，创建表及记录查看是否同步。
1 > unlock tables; //释放锁 2 > reset slave //重置slave 3 > show slave statusG 4 > change master to 5 -> master_host=‘192.168.4.52‘, 6 -> master_user=‘repl‘, 7 -> master_password=‘123456‘, 8 -> master_log_file=‘mysql-master-52.000001‘, 9 -> master_log_pos=595; 10 > start slave; //开启slave从库同步服务 11 > show slave statusG 12 > show databases; 13 > create database mygame; 14 > use mygame 15 > create table t1( 16 -> id int(4) not null auto_increment primary key, 17 -> name char(10) 18 -> ); 19 > insert into t1(name) values("Tom"),("Jave");
4、在192.168.4.52 （db-52）上Mysql中 创建表及记录查看是否同步。(检查操作)
[[email protected]52 ~]#mysql -uroot -p > show databases; > use mygame > insert into t1(name) values("Bom"),("Baly"); > select * from t1; > create table t2 select name from t1 where false; //复制表结构 > desc t2; > insert into t2 values("tom"),("poly"); > select * from t2;
5、在192.168.4.51 （db-51）上Mysql中 创建表及记录查看是否同步。(检查操作)
1 [[email protected]51 ~]mysql -uroot -p 2 > show databases; 3 > use mygame 4 > desc t2; 5 > insert into t2 values("tom"),("Jam"); 6 > select * from t2;
注1：让互为主从库，有各自的自增变量：
1 #主库1配置my.cnf： 2 [[email protected]1 ~]#vim /etc/my.cnf 3 auto_increment_increment = 2 4 auto_increment_offset = 2 5 log-bin =/mysql_multi_case/3306/mysqld-bin 6 log-slave-updates = 1 //级联服务 7 8 主库2配置my.cnf： 9 [[email protected]2 ~]#vim /etc/my.cnf 10 auto_increment_increment = 2 11 auto_increment_offset = 1 12 log-bin =/mysql_multi_case/3307/mysqld-bin 13 log-slave-updates = 1 //级联服务 14 15 16 17 注意： 18 auto_increment_increment 控制列中的值的增量值，也就是步长。 19 auto_increment_offset 确定AUTO_INCREMENT列值的起点，也就是初始值。 20 21 上述两个变量：可以在全局以及session级别设置这2个变量 。
参考：
https://blog.51cto.com/superpcm/2094958
https://blog.51cto.com/superpcm/2095731
https://www.cnblogs.com/DBArtist/p/auto_increment.html
https://blog.csdn.net/leshami/article/details/39779509#