MHA实现MySQL的高可用(2)

4.添加mha的管理账号，让管理节点远程连接到主机用来设置主从调整

[root@Master ~]# mysql -e "GRANT ALL ON *.* TO 'mhauser'@'192.168.73.%' IDENTIFIED BY 'centos';" 从节点配置

1.修改配置文件

[root@Slave1 ~]# vim /etc/my.cnf [mysqld] server-id=2 read-only log-bin relay_log_purge=0 skip_name_resolve

2.启动服务

[root@Slave1 ~]# systemctl start mariadb

3.配置CHANGE MASTER TO

MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST='192.168.73.110', MASTER_USER='repluser',MASTER_PASSWORD='centos',MASTER_PORT=3306,MASTER_LOG_FILE='mariadb-bin.000001',MASTER_LOG_POS=245; Query OK, 0 rows affected (0.00 sec)

4.启动线程

MariaDB [(none)]> START SLAVE; Query OK, 0 rows affected (0.00 sec)

在Slave2节点上也执行相同的操作，此处步骤省略，需要注意server-id需要修改为和其他主从节点不同

5.测试
主节点导入hellodb库

[root@Master ~]# mysql < hellodb_innodb.sql

从节点查看是否同步
slave1

[root@Slave1 ~]# mysql -e "SHOW DATABASES;" +--------------------+ | Database | +--------------------+ | information_schema | | hellodb | | mysql | | performance_schema | | test | +--------------------+

Slave2

[root@Slave2 ~]# mysql -e "SHOW DATABASES;" +--------------------+ | Database | +--------------------+ | information_schema | | hellodb | | mysql | | performance_schema | | test | +--------------------+ 二、配置管理节点及被管理节点

1.在管理节上安装mha4mysql-manager、mha4mysql-node，将两个包放在同一目录下

[root@Manager ~]# yum install *.rpm -y #这两个包有依赖管理需要一起安装

2.在所有被管理节点上安装mha4mysql-node

[root@Master ~]# yum install mha4mysql-node-0.56-0.el6.noarch.rpm -y [root@Slave1 ~]# yum install mha4mysql-node-0.56-0.el6.noarch.rpm -y [root@Slave2 ~]# yum install mha4mysql-node-0.56-0.el6.noarch.rpm -y

3.在管理节点上创建配置文件

[root@Manager ~]# vim /etc/mha/aap1.conf [server default] user=mhauser password=centos manager_workdir=/data/mastermha/app1/ manager_log=/data/mastermha/app1/manager.log remote_workdir=/data/mastermha/app1/ ssh_user=root repl_user=repluser repl_password=centos ping_interval=1 [server1] hostname=192.168.73.110 candidate_master=1 [server2] hostname=192.168.73.112 candidate_master=1 [server3] hostname=192.168.73.113 candidate_master=1

4.做检查
4.1检查ssh连接

[root@Manager ~]# masterha_check_ssh --conf=/etc/mha/aap1.conf

4.2检查主从复制

[root@Manager ~]# masterha_check_repl --conf=/etc/mha/aap1.conf

5.以上两项全部成功后启动程序
mha这个程序是跑在前台的，一次性的可以使用nohub或screen来解决跑在前台的问题

[root@Manager ~]# masterha_manager --conf=/etc/mha/aap1.conf 三、测试

1.在master上跑个存储过程，导入存储过程

[root@Master ~]# mysql hellodb < testlog.sql

2.调用存储过程

MariaDB [(none)]> USE hellodb 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 MariaDB [hellodb]> call pro_testlog;

3.另起一个主节点窗口将主节点断网

[root@Master ~]# ifdown ens33

4.manager端完成切换退出，查看日志，查看新的主节点是哪台slave

[root@Manager app1]# tail /data/mastermha/app1/manager.log Started automated(non-interactive) failover. The latest slave 192.168.73.112(192.168.73.112:3306) has all relay logs for recovery. Selected 192.168.73.112(192.168.73.112:3306) as a new master. 192.168.73.112(192.168.73.112:3306): OK: Applying all logs succeeded. 192.168.73.113(192.168.73.113:3306): This host has the latest relay log events. Generating relay diff files from the latest slave succeeded. 192.168.73.113(192.168.73.113:3306): OK: Applying all logs succeeded. Slave started, replicating from 192.168.73.112(192.168.73.112:3306) 192.168.73.112(192.168.73.112:3306): Resetting slave info succeeded. Master failover to 192.168.73.112(192.168.73.112:3306) completed successfully. #此处显示最新的主节点为192.168.73.112

由于从节点在配置文件中定义的为read-only，此时被提升为主能执行写操作时应为管理服务器上有管理账号，他将从节点的服务器全局变量read_only给关闭了

[root@Slave1 ~]# mysql -e "SELECT @@read_only;" +-------------+ | @@read_only | +-------------+ | 0 | +-------------+

为了防止服务服务重启再次变为read-only，此时需要对新主节点的配置文件进行修改将read-only行注释

[mysqld] server-id=2 #read-only log-bin relay_log_purge=0 skip_name_resolve 四、测试新的主节点编辑 HTML 源代码

1.对hellodb.teachers表插入数据

[root@Slave1 ~]# mysql -e "INSERT hellodb.teachers VALUES(5,'Tang San',30,'M');"