利用mha实现mysql的主从自动切换

ssh免密连接：

[root@localhost etc]# ssh-keygen
[root@localhost etc]# ssh-copy-id -i /root/.ssh/id_rsa.pub root@192.168.127.37  #免密登录对方的ssh  这的ip是对方的ip
[root@localhost etc]# vim /etc/ssh/ssh_config  #关闭yes的询问 
StrictHostKeyChecking no

**时间同步服务器：**随意找一台服务器作为时间的服务器，时间正确与错误本次实验没有要求，但是要一致

[root@localhost etc]# vim /etc/ntp.conf   #时间服务器
restrict 127.0.0.1
restrict ::1
restrict 192.168.127.0 mask 255.255.255.0
server 127.127.1.0

时间客户端：参考

 [root@localhost etc]# vim /etc/ntp.conf    #该设置在下次启动才生效
     server 192.168.153.7 iburst     
[root@localhost etc]# ntpdate 192.168.127.7  

**主服务器的配置：**172.18.251.133

[root@localhost etc]# /vim/etc/my.cnf

[mysqld]
server_id=2
datadir=/mysql/data
log_bin=/mysql/logbin/log
innodb_file_per_table
binlog_format=row
skip_name_resolve=1
socket=/var/lib/mysql/mysql.sock

MariaDB [(none)]> show master status;
+------------+----------+--------------+------------------+
| File       | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------+----------+--------------+------------------+
| log.000003 |      245 |              |                  |
+------------+----------+--------------+------------------+
1 row in set (0.00 sec)
#建立主从同步的账号
MariaDB [(none)]> grant replication slave on *.* to repluser@'%' identified by 'centos';
Query OK, 0 rows affected (0.00 sec)

MariaDB [mysql]>  grant all on *.* to mhauser@'%' identified by 'centos';
Query OK, 0 rows affected (0.00 sec)

**配置从服务器：**172.18.254.228 172.18.254.174

[mysqld]
server_id=24
datadir=/mysql/data
log_bin=/mysql/logbin/log
innodb_file_per_table
read_only=1
relay_log_purge=0         
skip_name_resolve=1 
socket=/var/lib/mysql/mysql.sock

MariaDB [(none)]> CHANGE MASTER TO
    ->   MASTER_HOST='172.18.251.133',
    ->   MASTER_USER='repluser',
    ->   MASTER_PASSWORD='centos',
    ->   MASTER_PORT=3306,
    ->   MASTER_LOG_FILE='log.000003',
    ->   MASTER_LOG_POS=245,
    ->   MASTER_CONNECT_RETRY=10;
Query OK, 0 rows affected (0.24 sec)
MariaDB [(none)]> start slave;

**mha服务器：**server#这个顺序决定这从服务器变为主的次序

[root@localhost etc]# vim /etc/mha/app1.conf

[server default]
user=mhauser      #mha的账号
password=centos
manager_workdir=/data/mastermha/app1/  
manager_log=/data/mastermha/app1/manager.log  
master_binlog_dir=/mysql/logbin  设置master 保存binlog的位置，以便MHA可以找到master的日志        
remote_workdir=/data/mastermha/app1/  
ssh_user=root
repl_user=repluser  
repl_password=centos
ping_interval=1

[server1]  
hostname=192.168.153.17
candidate_master=1  #设置为候选master，如果设置该参数以后，发生主从切换以后将会将此从库提升为主库，即使这个主库不是集群中事件最新的slave
[server2]  
hostname=192.168.153.27
candidate_master=1  
[server3]  
hostname=192.168.153.37

mha服务器测试：

[root@localhost etc]# masterha_check_ssh --conf=/etc/mha/app1.conf
.......
Wed Oct 17 09:55:42 2018 - [info] All SSH connection tests passed successfully.
[root@localhost etc]# masterha_check_repl --conf=/etc/mha/app1.conf
......
MySQL Replication Health is OK.

当主服务器损坏时，从服务器会自动变为主，read_only也会关闭，当主服务器再次修好时，会有主变为从其配置如下：

[root@localhost etc]# vim /etc/my.cnf
#增加以下两行
read_only=ON
relay_log_purge=0

[root@localhost etc]# systemctl restart mariadb.service

Master [(none)]> CHANGE MASTER TO
    ->   MASTER_HOST='172.15.254.228',
    ->   MASTER_USER='repluser',
    ->   MASTER_PASSWORD='centos',
    ->   MASTER_PORT=3306,
    ->   MASTER_LOG_FILE='slave-log.000003',
    ->   MASTER_LOG_POS=245,
    ->   MASTER_CONNECT_RETRY=10;
Query OK, 0 rows affected (0.05 sec)

Master [(none)]> start slave;
Query OK, 0 rows affected (0.00 sec)