本文共 6968 字,大约阅读时间需要 23 分钟。
0~30秒内自动故障切换。切换过程中保证数据一致性(高可用)
组成:
工作过程
192.168.4.51----55mysql服务器,
192.168.4.56作为管理,
192.168.4.50作为客户端
所有主机yum安装perl-*依赖包
他们之间无密码ssh登陆 ssh-keygen -f /root/.ssh/id_rsa -N '' ; ssh-copy-id ip地址
mysq> grant all on *.* to root@"%" identified by "123456";
]# cd mha-soft-student
]#yum mha4mysql-node-0.56-0.el6.noarch.rpm ]# tar -zxvf mha4mysql-manager-0.56.tar.gz ]# cd mha4mysql-manager-0.56 ]# perl Makefile.PL]# make && make install
]# cd mha-soft-student
]# yum mha4mysql-node-0.56-0.el6.noarch.rpm
配置mysql主从同步(一主多从)
配置主服务器51
# vim /etc/my.cnf
[mysqld] server_id=51 log-bin=master51 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 relay_log_purge=off #不自动删除本机的中继日志文件]# systemctl restart mysqld
]# mysql -uroot -p密码
mysql> grant replication slave on *.* to plj@"%" identified by "123qqq...A";mysql> show master status;
mysql> set global relay_log_purge=off;]# vim /etc/my.cnf
[mysqld] server_id=52 log-bin=master52 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 relay_log_purge=off]# systemctl restart mysqld
[root@mysql52 ~]# mysqll -uroot -p密码指定master信息
mysql> change master to master_host="192.168.4.51",master_user="plj",master_password="123qqq...A",master_log_file="master51.000001",master_log_pos=436;
Query OK, 0 rows affected, 2 warnings (0.28 sec)
mysql> start slave;
Query OK, 0 rows affected (0.02 sec)mysql> show slave status\G;
Master_Host: 192.168.4.51 Slave_IO_Running: Yes Slave_SQL_Running: Yesrelay_log_purge=off
:wq ]# systemctl restart mysqld ]# mysql -uroot -p密码mysql> change master to master_host="192.168.4.51",master_user="plj",master_password="123qqq...A",master_log_file="master51.000001",master_log_pos=436;
mysql> start slave;
Query OK, 0 rows affected (0.02 sec) mysql> show slave status\G; Master_Host: 192.168.4.51 Slave_IO_Running: Yes Slave_SQL_Running: Yes
]# systemctl restart mysqld
]# mysql -uroot -p密码 mysql> change master to master_host="192.168.4.51",master_user="plj",master_password="123qqq...A",master_log_file="master51.000001",master_log_pos=436; mysql> start slave; mysql> show slave status\G;
manager_workdir=/etc/mha_manager
manager_log=/etc/mha_manager/manager.log master_ip_failover_script=/etc/mha_manager/master_ip_failover ssh_user=root ssh_port=22 repl_user=plj #主从同步用户名 repl_password=123qqq...A user=root #数据库用户名 password=123qqq...A [server1] hostname=192.168.4.51 candidate_master=1 #设置为候选master [server2] hostname=192.168.4.52 candidate_master=1 [server3] hostname=192.168.4.53 candidate_master=1 [server4] hostname=192.168.4.54 no_master=1 #不竞选master [server5] hostname=192.168.4.55 no_master=1
[root@mysql51 ~]# ifconfig eth0
eth0: flags=4163<UP,BROADCAST,RUNNING,MULTICAST> mtu 1500 inet 192.168.4.51 netmask 255.255.255.0 broadcast 192.168.4.255 inet6 fe80::5c9:7cc1:9663:574 prefixlen 64 scopeid 0x20<link> ether 74:52:09:07:51:01 txqueuelen 1000 (Ethernet) RX packets 676632 bytes 1050900208 (1002.2 MiB) RX errors 0 dropped 60 overruns 0 frame 0 TX packets 128153 bytes 31563247 (30.1 MiB) TX errors 0 dropped 0 overruns 0 carrier 0 collisions 0
52mysql> grant replication slave on *.* to plj@"%"
identified by "123qqq...A"; 53mysql> grant replication slave on *.* to plj@"%" identified by "123qqq...A";51mysql> grant all on *.* to root@'%'
identified by "123qqq...A";
[root@host56 ~]# masterha_check_ssh --conf=/etc/mha_manager/app1.cnf
Wed May 15 17:56:13 2019 - [info] All SSH connection tests passed successfully.[root@host56 ~]# masterha_check_repl --conf=/etc/mha_manager/app1.cnf
MySQL Replication Health is OK.
host56 ~]# masterha_manager --conf=/etc/mha_manager/app1.cnf \
--remove_dead_master_conf --ignore_last_failover注:--remove_dead_master_conf 删除宕机主库配置; --ignore_last_failover 忽略xxx.health文件
host56 ~]# masterha_check_status --conf=/etc/mha_manager/app1.cnf #查看状态
# 停止服务masterha_stop --conf=/etc/mha/app1.cnf
host56 ~]# ls /etc/mha_manager
3.4.1 在主数据库51上添加访问数据的连接用户yaya99 123qqq...A
mysql> create database db9; mysql> create table db9.t1 (id int); mysql> grant select ,insert on db9.* to yaya99@"%" identified by "123qqq...A";3.4.2 在客户端50 连接vip地址访问数据库服务
50]# mysql -h192.168.4.100 -uyaya99 -p123qqq...A mysql> insert into db9.t1 values(100),(200),(300); mysql> select * from db9.t1; 在51的所有从服务器上都可以看到同样的数据3.4.3 停止主数据库51 的数据库服务,50主机依然可以访问到数据
51]# systemctl stop mysqld注:当主库宕机后用 ip addr show | grep vip地址 查看一下
思考:
2.1 启动51 和 52 主机的数据库服务
2.2 把51 和 52 主机 配置为的当前主服务器的从服务器 2.3 把主机51 和 52 添加app1.cnf文件里 ]#vim /etc/mha_manager/app1.cnf [server1] candidate_master=1 hostname=192.168.4.51[server2]
candidate_master=1 hostname=192.168.4.52 2.4 在主机56上运行管理服务,并查看服务状态 ]# masterha_check_repl --conf=/etc/mha_manager/app1.cnf ]# masterha_manager --conf=/etc/mha_manager/app1.cnf
排错参考
转载地址:http://peiqi.baihongyu.com/