Mysql-MHA高可用实验测试-主从复制搭建

 Mysql-MHA高可用实验测试-主从复制搭建
环境信息：
OS:Centos6.8
192.168.180.55  monistor //mha-manager管理端
192.168.180.56  master  //现有主库
192.168.180.57  slave1  //即将做双主的数据库
192.168.180.58  slave2  //从库


1.安装mysql

这里就不再介绍mysql的安装，具体的可参考：http://blog.csdn.net/shiyu1157758655/article/details/70226036

2.配置mysql主从

a）配置my.cnf
 master上操作：
[root@master mysql]# vi /etc/my.cnf 
添加如下：
server_id =1
log-bin=mysql-bin
binlog_format=mixed


slave1上操作
[root@slave1 ~]# vi /etc/my.cnf 
添加如下：
server_id =2
log-bin=mysql-bin
binlog_format=mixed
relay_log_purge=0


slave2上操作
添加如下：
[root@slave2 ~]# vi /etc/my.cnf 
server_id =3
log-bin=mysql-bin
relay_log=mysql-relay-bin
max_relay_log_size=2G
log_slave_updates=1
read_only=1
relay_log_purge=0


注意：以上配置完之后要重启数据库
b）搭建
master上操作：
mysql> grant replication slave on *.* to mysql@'192.168.180.%' identified by 'mysql';
mysql> flush privileges;
mysql> stop slave;
#设置复制权限账号
mysql> grant all privileges on *.* to 'mha'@'192.168.180.%' identified by 'mysql';
mysql> reset master;
mysql> show master status\G;
*************************** 1. row ***************************
             File: mysql-bin.000001
         Position: 154
     Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: 
1 row in set (0.00 sec)


ERROR: 
No query specified


slave1 上操作：
mysql> grant replication slave on *.* to mysql@'192.168.180.%' identified by 'mysql';
mysql> grant all privileges on *.* to 'mha'@'192.168.180.%' identified by 'mysql';
mysql> flush privileges;
mysql> reset master;
mysql> stop slave;
mysql> change master to master_host='192.168.180.56',master_port=3306,master_user='mysql',master_password='mysql';
mysql> start slave;
mysql> show slave status\G;


slave2上操作:
mysql> reset master;
mysql> stop slave;
mysql> grant all privileges on *.* to 'mha'@'192.168.180.%' identified by 'mysql';
mysql> flush privileges;
mysql> change master to master_host='192.168.180.56',master_port=3306,master_user='mysql',master_password='mysql';
mysql> start slave;
mysql> show slave status\G;


3.测试：
在master上创建一个test数据库，在test里创建一个test的表


master上操作
ysql> create database test;
Query OK, 1 row affected (0.14 sec)


mysql> use test;
Database changed
mysql> show tables;
Empty set (0.00 sec)


mysql> create table test(id int);
Query OK, 0 rows affected (0.04 sec)


mysql> insert into test values(1);
Query OK, 1 row affected (0.01 sec)




slave1，slave2上查看是否同步过去
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+
5 rows in set (0.00 sec)


mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A


Database changed
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| test           |
+----------------+
1 row in set (0.00 sec)


mysql> select * from test;
+------+
| id   |
+------+
|    1 |
+------+
1 row in set (0.00 sec)


mysql> 


至此mha 主从同步配置成功