注意:后面由于填上来了脚本所有内容篇幅优点乱,读者可以参考复制
MHA的优势:
MHA(Master High Availability)目前在MySQL高可用方面是一个相对成熟的解决方案,它由日本DeNA公司
youshimaton(现就职于Facebook公司)开发,是一套优秀的作为MySQL高可用性环境下故障切换和主从提升的
高可用软件。在MySQL故障切换过程中,MHA能做到在0~30秒之内自动完成数据库的故障切换操作,并且在进行故
障切换的过程中,MHA能在最大程度上保证数据的一致性,以达到真正意义上的高可用。
Manager工具包主要包括以下几个工具:
masterha_check_ssh 检查MHA的SSH配置状况
masterha_check_repl 检查MySQL复制状况
masterha_manger 启动MHA
masterha_check_status 检测当前MHA运行状态
masterha_master_monitor 检测master是否宕机
masterha_master_switch 控制故障转移(自动或者手动)
masterha_conf_host 添加或删除配置的server信息
Node工具包(这些工具通常由MHA Manager的脚本触发,无需人为操作)主要包括以下几个工具:
save_binary_logs 保存和复制master的二进制日志
apply_diff_relay_logs 识别差异的中继日志事件并将其差异的事件应用于其他的slave
filter_mysqlbinlog 去除不必要的ROLLBACK事件(MHA已不再使用这个工具)
purge_relay_logs 清除中继日志(不会阻塞SQL线程)
实验环境:
**大家可以参考https://blog.csdn.net/aaaaaab_/article/details/81515111里面对实验环境的搭建
很具体,这里就不重复多唠叨了**
主:server1:172.25.38.1 MHA Manager(管理节点)
从:server2:172.25.38.2 MHA Node(数据节点)
从:server3:172.25.38.3 MHA Node(数据节点)
在主端server1还原数据库重新进行初始化:
[root@server1 ~]# /etc/init.d/mysqld stop
Stopping mysqld: OK
[root@server1 ~]# chkconfig mysqld off
[root@server1 ~]# ls
mysql-community-client-5.7.17-1.el6.x86_64.rpm
mysql-community-common-5.7.17-1.el6.x86_64.rpm
mysql-community-libs-5.7.17-1.el6.x86_64.rpm
mysql-community-libs-compat-5.7.17-1.el6.x86_64.rpm
mysql-community-server-5.7.17-1.el6.x86_64.rpm
[root@server1 ~]# rm -fr *
[root@server1 ~]# cd /var/lib/mysql
[root@server1 mysql]# ls
auto.cnf ib_buffer_pool mysql-bin.000001 private_key.pem test
ca-key.pem ibdata1 mysql-bin.000002 public_key.pem
ca.pem ib_logfile0 mysql-bin.000003 server-cert.pem
client-cert.pem ib_logfile1 mysql-bin.index server-key.pem
client-key.pem mysql performance_schema sys
[root@server1 mysql]# rm -fr *
[root@server1 mysql]# ls
由于从的配置文件只需要保证server-id不同即可:
[root@server1 mysql]# vim /etc/my.cnf 写入主配置文件GTID方式日志存放目录等
server-id=1
gtid_mode=ON
enforce-gtid-consistency=true
master_info_repository=TABLE
relay_log_info_repository=TABLE
log_slave_updates=ON
log_bin=binlog
binlog_format=ROW
[root@server1 mysql]# scp /etc/my.cnf server2:/etc/
[root@server1 mysql]# scp /etc/my.cnf server3:/etc/
进行主端节点server1的配置:
[root@server1 mysql]# /etc/init.d/mysqld start
Initializing MySQL database: [ OK ]
Installing validate password plugin: [ OK ]
Starting mysqld: [ OK ]
[root@server1 mysql]# grep password /var/log/mysqld.log 过滤密码
[root@server1 mysql]# mysql_secure_installation 初始化安装
[root@server1 mysql]# mysql -p
mysql> show master status;
+---------------+----------+--------------+------------------+------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+------------------------------------------+
| binlog.000003 | 750 | | | 7f6de4ab-9d07-11e8-adc4-525400a2bcb9:1-4 |
+---------------+----------+--------------+------------------+------------------------------------------+
1 row in set (0.00 sec)
mysql> grant replication slave on *.* to repl@'172.25.38.%' identified by 'Yakexi+007'; 给予slave端授权
Query OK, 0 rows affected, 1 warning (0.04 sec)
进行从端server2的配置还原数据库:
[root@server2 ~]# /etc/init.d/mysqld stop
Stopping mysqld: [ OK ]
[root@server2 ~]# chkconfig mysqld off
[root@server2 ~]# cd /var/log/mysqld.log
-bash: cd: /var/log/mysqld.log: Not a directory
[root@server2 ~]# cd /var/lib/mysql
[root@server2 mysql]# ls
auto.cnf mysql mysql-bin.index sys
ib_buffer_pool mysql-bin.000001 performance_schema test
ibdata1 mysql-bin.000002 server2-relay-bin.000010
ib_logfile0 mysql-bin.000003 server2-relay-bin.000011
ib_logfile1 mysql-bin.000004 server2-relay-bin.index
[root@server2 mysql]# rm -fr *
初始化数据库安装:
[root@server2 mysql]# vim /etc/my.cnf 更改了server-id即可
[root@server2 mysql]# /etc/init.d/mysqld start
Initializing MySQL database: [ OK ]
Installing validate password plugin: [ OK ]
Starting mysqld: [ OK ]
[root@server2 mysql]# grep password /var/log/mysqld.log 过滤密码
[root@server2 mysql]# mysql_secure_installation 初始化安装
[root@server2 mysql]# mysql -p
mysql> change master to master_host='172.25.38.1' , master_user='repl' , master_password='Yakexi+007' , master_auto_position=1; 连接
Query OK, 0 rows affected, 2 warnings (0.41 sec
mysql> start slave; 开启slave
Query OK, 0 rows affected (0.11 sec)
mysql> show slave status\G 显示slave状态
解决SQL为NO的情况:
在server1:
mysql> reset master; 删除master日志
Query OK, 0 rows affected (0.17 sec)
在server2:
mysql> stop slave; 停止slave
Query OK, 0 rows affected (0.07 sec)
mysql> reset slave; 删除slave日志
Query OK, 0 rows affected (0.20 sec)
mysql> start slave;删除master日志
Query OK, 0 rows affected (0.22 sec)
mysql> show slave status\G 保证数据同步
配置从端server3之前已经搭建好环境:
[root@server3 ~]# /etc/init.d/mysqld stop
Stopping mysqld: [ OK ]
[root@server3 ~]# chkconfig mysqld off 关闭开机启动
[root@server3 ~]