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

桃扇骨 2022-06-17 05:39 199阅读 0赞

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 主从同步配置成功