mysql主从同步

拓扑结构
	master51
	|
	|
	______________________________________________________
	| || | |
	slave52slave53 slave54 slave55 mgm56
	Manager
	公共配置：
	在所有主机上安装软件软件包
	]#cd mha-soft-student
	]#ls perl-*.rpm
	perl-Config-Tiny-2.14-7.el7.noarch.rpm
	perl-Mail-Sender-0.8.23-1.el7.noarch.rpm
	perl-MIME-Types-1.38-2.el7.noarch.rpm
	perl-Email-Date-Format-1.002-15.el7.noarch.rpm
	perl-Mail-Sendmail-0.79-21.el7.art.noarch.rpm
	perl-Parallel-ForkManager-1.18-2.el7.noarch.rpm
	perl-Log-Dispatch-2.41-1.el7.1.noarch.rpm
	perl-MIME-Lite-3.030-1.el7.noarch.rpm
	[root@db108share]# yum -y install perl-*.rpm
	一、配置所有数据节点主机之间可以互相以ssh密钥对方式认证登陆
	二、配置manager56主机无密码ssh登录所有数据节点主机
	三、配置主从同步,要求如下：
	51主库开半同步复制
	52从库（备用主库）开半同步复制
	53从库（备用主库）开半同步复制
	54从库不做备用主库所以不用开半同步复制
	55从库不做备用主库所以不用开半同步复制
	配置一主多从
	3.1、master51配置：
	vim/etc/my.cnf
	[mysqld]
	plugin-load="rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"
	rpl-semi-sync-master-enabled= 1
	rpl-semi-sync-slave-enabled= 1
	server_id=51
	log-bin=master51
	binlog-format="mixed"
	:wq
	[root@db107~]# systemctl restart mysqld
	[root@db107~]# ls /var/lib/mysql/master51.*
	/var/lib/mysql/master51.000001/var/lib/mysql/master51.index
	[root@db107~]# mysql -uroot -p123456
	mysql>grant replication slave on *.* to repluser@"%"identified by "123456";
	QueryOK, 0 rows affected, 1 warning (10.04 sec)
	mysql>set global relay_log_purge=off;
	QueryOK, 0 rows affected (0.15 sec)
	mysql>show master status;
	+-----------------+----------+--------------+------------------+-------------------+
	|File | Position | Binlog_Do_DB | Binlog_Ignore_DB |Executed_Gtid_Set |
	+-----------------+----------+--------------+------------------+-------------------+
	|master51.000001 | 441 | | | |
	+-----------------+----------+--------------+------------------+-------------------+
	mysql>quit;
	3.2、备用master52的配置
	vim/etc/my.cnf
	[mysqld]
	plugin-load="rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"
	rpl-semi-sync-master-enabled= 1
	rpl-semi-sync-slave-enabled= 1
	server_id=52
	log-bin=master52
	binlog-format="mixed"
	]#systemctl restart mysqld
	[root@db108~]# ls /var/lib/mysql/master52.*
	/var/lib/mysql/master52.000001/var/lib/mysql/master52.index
	[root@db108~]# mysql -uroot -p123456
	mysql>set global relay_log_purge=off;
	QueryOK, 0 rows affected (0.13 sec)
	mysql>change master to
	->master_host="192.168.4.51",
	->master_user="repluser",
	->master_password="123456",
	->master_log_file="master51.000001",
	->master_log_pos=441;
	QueryOK, 0 rows affected, 2 warnings (0.04 sec)
	mysql>start slave;
	QueryOK, 0 rows affected (0.01 sec)
	[root@db108~]# mysql -uroot -p123456 -e "show slave status\G" |grep -i YES
	mysql:[Warning] Using a password on the command line interface can beinsecure.
	Slave_IO_Running:Yes
	Slave_SQL_Running:Yes
	[root@db108~]#
	3.3、备用master53的配置
	]#vim /etc/my.cnf
	[mysqld]
	plugin-load="rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"
	rpl-semi-sync-master-enabled= 1
	rpl-semi-sync-slave-enabled= 1
	server_id=53
	log-bin=master53
	binlog-format="mixed"
	:wq
	[root@db109~]# systemctl restart mysqld
	[root@db109~]# ls /var/lib/mysql/master53.*
	/var/lib/mysql/master53.000001/var/lib/mysql/master53.index
	[root@db109~]#
	[root@db109~]# mysql -uroot -p123456
	mysql>set global relay_log_purge=off;
	QueryOK, 0 rows affected (0.14 sec)
	mysql>change master tomaster_host="192.168.4.51",master_user="repluser",master_password="123456",master_log_file="master51.000001",master_log_pos=441;
	QueryOK, 0 rows affected, 2 warnings (0.05 sec)
	mysql>start slave;
	QueryOK, 0 rows affected (0.00 sec)
	mysql>
	[root@db109~]# mysql -uroot -p123456 -e "show slave status\G" |grep -i yes
	mysql:[Warning] Using a password on the command line interface can beinsecure.
	Slave_IO_Running:Yes
	Slave_SQL_Running:Yes
	[root@db109~]#
	3.4、配置从服务器54
	[root@db111~]# vim /etc/my.cnf
	[mysqld]
	server_id=54
	:wq
	[root@db111~]# systemctl restart mysqld
	[root@db111~]# mysql -uroot -p123456
	mysql>change master tomaster_host="192.168.4.51",master_user="repluser",master_password="123456",master_log_file="master51.000001",master_log_pos=441;
	QueryOK, 0 rows affected, 2 warnings (0.09 sec)
	mysql>start slave;
	QueryOK, 0 rows affected (0.00 sec)
	mysql>quit;
	Bye
	[root@db111~]#
	[root@db111~]# mysql -uroot -p123456 -e "show slave status\G" |grep -i yes
	mysql:[Warning] Using a password on the command line interface can beinsecure.
	Slave_IO_Running:Yes
	Slave_SQL_Running:Yes
	[root@db111~]#
	3.5、配置从服务器55
	[root@db111~]# vim /etc/my.cnf
	[mysqld]
	server_id=55
	:wq
	[root@db111~]# systemctl restart mysqld
	[root@db111~]# mysql -uroot -p123456
	mysql>change master tomaster_host="192.168.4.51",master_user="repluser",master_password="123456",master_log_file="master51.000001",master_log_pos=441;
	QueryOK, 0 rows affected, 2 warnings (0.09 sec)
	mysql>start slave;
	QueryOK, 0 rows affected (0.00 sec)
	mysql>quit;
	Bye
	[root@db111~]#
	[root@db111~]# mysql -uroot -p123456 -e "show slave status\G" |grep -i yes
	mysql:[Warning] Using a password on the command line interface can beinsecure.
	Slave_IO_Running:Yes
	Slave_SQL_Running:Yes
	[root@db111~]#
	3.6、在客户端测试主从同步配置
	3.6.1在主库51上添加访问数据的授权用户
	[root@db107~]# mysql -uroot -p123456
	mysql>grant all on gamedb.* to admin@"%" identified by"123456";
	3.6.2在客户端主机连接主库51建库表记录
	mysql>create database gamedb;
	QueryOK, 1 row affected (0.01 sec)
	mysql>create table gamedb.t1 (id int);
	QueryOK, 0 rows affected (0.04 sec)
	mysql>insert into gamedb.t1 values(999);
	QueryOK, 1 row affected (0.15 sec)
	mysql>insert into gamedb.t1 values(999);
	QueryOK, 1 row affected (0.05 sec)
	mysql>select * from gamedb.t1;
	+------+
	| id|
	+------+
	|999 |
	|999 |
	+------+
	2rows in set (0.00 sec)
	mysql>
	3.6.3在客户端使用授权用户连接从库52-55,也能看到同样的库表及记录
	[root@host114~]# mysql -h从库IP地址-uadmin-p123456
	mysql>select * from gamedb.t1;
	+------+
	| id|
	+------+
	|999 |
	|999 |
	+------+