mariadb 高可用集群

目录

1.相同操作：修改hosts文件 把四台机IP写进去

2.  www 管理机

4.管理

---

简介：

MHA（Master High Availability）目前在MySQL高可用方面是一个相对成熟的解决方案，它由日本DeNA公司youshimaton（现就职于Facebook公司）开发，是一套优秀的作为MySQL高可用性环境下故障切换和主从提升的高可用软件。在MySQL故障切换过程中，MHA能做到在0~30秒之内自动完成数据库的故障切换操作，并且在进行故障切换的过程中，MHA能在最大程度上保证数据的一致性，以达到真正意义上的高可用。

1.环境：qqq主机  www管理机   eee从   rrr从

1.相同操作：修改hosts文件 把四台机IP写进去

 

 生成密钥：ssh-keygen -t rsa

复制密钥 所有机必须复制管理机 另保证全部可以相互登陆

ssh-copy-id -i .ssh/id_rsa.pub root@名字

查看cat /root/.ssh/authorized_keys

主机操作
[root@qqq ~]# cat /root/.ssh/authorized_keys
ssh-rsa AAAAB3NzaC1yc2EAAAADAQABAAABAQDcqcvxC7TYVQ7cq8iSF2GAEFerIYvGQopFV69qpkocUl6FtOTfgInI5PXEqeD3AUCpgSiYOmvB7qqHEKS8njVLqrTVSOB/U8DNG6myOHyQpjfV1CUETpWs7nakv7Hg7UrWIA1MR5lj8ZtHGXlYoK1lEAdHGRDgtFY8dj+kHpuT2Ul4LV4o1gK3sXthxo3yb1kfeQ8ZlSp3VbSNB3jU/RPeIOeVJpuKBn/71+NeWU1xEEZLWVgO6YAJRRiflPrEj8m0sCmWzAn4oi1yGDZKoYHhynkjCkxp2Q2HJ4RRzDcA6Auk/WHy8UtmVkeHM5ByDrkYSwhivx//Eyb+5SLs29e7 root@qqq
ssh-rsa AAAAB3NzaC1yc2EAAAADAQABAAABAQDci146ceYUzMlxV6eKmGUsjTXAYzRW4xWSX3e3arqDvuttTIOAB9/on9sgiFjTIQz1z8AXjFwldLzcpus0eRZT5hz/mNHKIgF2FEcpk8wZoNNX13pyaCn0LnuZkmDXXCPaV2n8uCyr3OA/q9sCoyRp8uWNshIZRFHjxmRHj47hV9fkusaZrr7l+yFM4l7LsrDGjwTj16mhehGDc52LgQML6Y86GcI+0gd0QW4aypiLOOEiKQYM1PbGd9M6fAjImQQCkWm5XDTmIOXwI09KuqVvICFJWxUEoW0EAHX4qMZauDjDoYDfE0SnggRvlzgmPhWh9WOZsYuwppdQCdTBaRir root@www
ssh-rsa AAAAB3NzaC1yc2EAAAADAQABAAABAQCqwuvAyEW/kF+defnFn22yYf0IAjaIxkMFPCd6V3fLVN4zwZAvj0bhgCSLyQkV9H23hwaqRrsI+maYVyuB0qWULJBpE+ZCIiBjj15dGjGYuB6NBvymtdbOVPExQXW9VGUQcRPlt8uhcVoJ4SB7wWXC2TJXFwKRu3WYdFAJZUW5cytMblLWBdBoZKRORwGF+c+4LpGuocx/+SH6u+OrR1lfoYNf05fbZXmgJsm0x+ExVWAos6ne7LD0p48zMKvoBAok7R0xKCXw6uOPzxB0FKvyy4Lz4feWhjwR4rxoMIOgFKp0BkMW+dN0xrR4SUNlCU37oe9IsgX9njZzGHbO/yJz root@eee
ssh-rsa AAAAB3NzaC1yc2EAAAADAQABAAABAQDD0e8ASnVTyhpCB71hZpYkP7izLgg4xCmX8dJVjvhWY/n89AzhzjIR45V+06td6RlO+U29ffurO5RFx8ryf8xOyeuTD3COCUBMaz+3+HUWNtei92fxLfjFYt1JWbBBt385eHM63HDdRQBHmvnBPuCUaOhm0urdYQye2HR0f35towDrT/kRAZYCFyw9yRzfZKjt7d4Iki2ooAyuPpvtPSucuQnKAQIUsguiQ8H7bnBCmkQIelJTwz+jwbKlCjdVNO6OjfrUB54P065lwV/zOj69elJ/yZ12APsAAxSQTLlxUsVi+m8cuIaJR5jlZDStOObwCHH66BpUuyHYOOsl2+Nh root@rrr

将authorized_keysau文件发送给其他人

scp .ssh/authorized_keys root@名字:.ssh/

主机
[root@qqq ~]# scp .ssh/authorized_keys root@www:.ssh/
The authenticity of host 'www (192.168.1.139)' can't be established.
ECDSA key fingerprint is SHA256:KUnDzA9EBdTNT0UE3pDB18XcdeWBK780vEkpfsZR7PI.
ECDSA key fingerprint is MD5:f6:96:4e:cc:7f:5d:73:60:d6:e3:22:7c:00:25:32:3a.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added 'www,192.168.1.139' (ECDSA) to the list of known hosts.
root@www's password: 
authorized_keys                                                     100% 1560     2.0MB/s   00:00    
[root@qqq ~]# scp .ssh/authorized_keys root@eee:.ssh/
The authenticity of host 'eee (192.168.1.129)' can't be established.
ECDSA key fingerprint is SHA256:cgSRKW28gqwbirXE1h/m+vDUazLg1njP+nkrYGzy+I4.
ECDSA key fingerprint is MD5:c2:e1:24:10:c9:ce:32:eb:61:4d:a1:0b:ef:11:d2:d8.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added 'eee,192.168.1.129' (ECDSA) to the list of known hosts.
root@eee's password: 
authorized_keys                                                     100% 1560     2.0MB/s   00:00    
[root@qqq ~]# scp .ssh/authorized_keys root@rrr:.ssh/
The authenticity of host 'rrr (192.168.1.127)' can't be established.
ECDSA key fingerprint is SHA256:qKSdToSlnsPSocexQbkMwS5tYDYo4jM6a+e7mIihZC0.
ECDSA key fingerprint is MD5:2b:72:4f:16:82:a1:72:ae:0f:3d:dc:c1:90:bd:ee:c8.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added 'rrr,192.168.1.127' (ECDSA) to the list of known hosts.
root@rrr's password: 
authorized_keys                                                     100% 1560     1.9MB/s   00:00    

ssh 登陆测试每台机相互可以免密登陆

2.  www 管理机

安装 yum -y install epel-release

        yum -y install mariadb mariadb-server

        rpm -ivh mha4mysql-node-0.56-0.el6.noarch.rpm             网上下载

mha4mysql是一个MySQL高可用性解决方案，它提供了自动故障切换和主从复制监控的功能。mha4mysql-node是用于MySQL节点的安装包，mha4mysql-manager是用于管理和监控MySQL集群的安装包。通过安装这两个包，你可以实现MySQL的自动故障切换和主从复制的监控。

修改配置文件

vim /etc/my.cnf
server-id = 1
log-bin = master-log
relay-log = relay-log
skip_name_resolve  

重启  mariadb

授权给从库使用  grant replication slave on *.* to ‘slave’@'%' identified by '123';】

授权用户给manager管理master使用 grant all on *.* to mha@'%' identified by '123';

查看二进制文件         show master status;

MySQL 进入
MariaDB [(none)]>  grant replication slave on *.* to 'slave'@'%' identified by '123'; grant all on *.* to 'mha'@'%' identified by '123'; 
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]>  show master status;
+-------------------+----------+--------------+------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------+----------+--------------+------------------+
| master-log.000003 |      911 |              |                  |
+-------------------+----------+--------------+------------------+

退出保存 exit

3.从  eee  配置

安装   yum -y install mariadb mariadb-server epel-release

           rpm -ivh mha4mysql-node-0.56-0.el6.noarch.rpm                  需要下载包

配置写入

vim /etc/my.cnf

server-id = 2                
relay-log = relay-log         
log-bin = master-log         
read_only = ON               
relay_log_purge = 0           
skip_name_resolve              
log_slave_updates = 1

mysql 进入

添加主库信息
MariaDB [(none)]>  change master to master_host='192.168.1.139',master_user='slave',master_password='123',master_log_file='master-log.000003',master_log_pos=911;
Query OK, 0 rows affected (0.00 sec)

开启从库复制进程
MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.00 sec)

查看主从复制状态      看到两个yes就是成功
MariaDB [(none)]> show slave status \G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.1.139
                  Master_User: slave
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: master-log.000003
          Read_Master_Log_Pos: 911
               Relay_Log_File: relay-log.000002
                Relay_Log_Pos: 530
        Relay_Master_Log_File: master-log.000003
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 

授权用户给manager管理slave使用
 grant all on *.* to 'mha'@'%' identified by '123';

exit保存退出

到这里 从rrr也和eee一样了

vim /etc/my.cnf
server-id = 3              只需要把这里改成三 若还有从递增就行了       
relay-log = relay-log         
log-bin = master-log         
read_only = ON               
relay_log_purge = 0           
skip_name_resolve              
log_slave_updates = 1

4.管理

安装  yum -y install mariadb mariadb-server epel-release

        yum -y install mha4mysql-node-0.56-0.el6.noarch.rpm mha4mysql-manager-0.56-0.el6.noarch.rpm

创建文件目录

[root@qqq /]# mkdir /etc/mha_master
[root@qqq /]#  vim /etc/mha_master/mha.cnf

写入

[root@qqq /]#  vim /etc/mha_master/mha.cnf

ssh_user=root
repl_user=slave
repl_password=123
ping_interval=1
[server1]
hostname=192.168.1.139
ssh_port=22
candidate_master=1
[server2]
hostname=192.168.1.129
ssh_port=22
candidate_master=1
[server3]
hostname=192.168.1.127
ssh_port=22

对各个节点进行检测masterha_check_ssh --conf=/etc/mha_master/mha.cnf      看到ok就okl

[root@qqq /]#  masterha_check_ssh --conf=/etc/mha_master/mha.cnf
Fri Jun 16 17:06:43 2023 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Fri Jun 16 17:06:43 2023 - [info] Reading application default configuration from /etc/mha_master/mha.cnf..
Fri Jun 16 17:06:43 2023 - [info] Reading server configuration from /etc/mha_master/mha.cnf..
Fri Jun 16 17:06:43 2023 - [info] Starting SSH connection tests..
Fri Jun 16 17:06:44 2023 - [debug] 
Fri Jun 16 17:06:43 2023 - [debug]  Connecting via SSH from root@192.168.1.139(192.168.1.139:22) to root@192.168.1.129(192.168.1.129:22)..
Fri Jun 16 17:06:43 2023 - [debug]   ok.
Fri Jun 16 17:06:43 2023 - [debug]  Connecting via SSH from root@192.168.1.139(192.168.1.139:22) to root@192.168.1.127(192.168.1.127:22)..
Fri Jun 16 17:06:44 2023 - [debug]   ok.
Fri Jun 16 17:06:45 2023 - [debug] 
Fri Jun 16 17:06:44 2023 - [debug]  Connecting via SSH from root@192.168.1.127(192.168.1.127:22) to root@192.168.1.139(192.168.1.139:22)..
Fri Jun 16 17:06:44 2023 - [debug]   ok.
Fri Jun 16 17:06:44 2023 - [debug]  Connecting via SSH from root@192.168.1.127(192.168.1.127:22) to root@192.168.1.129(192.168.1.129:22)..
Fri Jun 16 17:06:45 2023 - [debug]   ok.
Fri Jun 16 17:06:45 2023 - [debug] 
Fri Jun 16 17:06:43 2023 - [debug]  Connecting via SSH from root@192.168.1.129(192.168.1.129:22) to root@192.168.1.139(192.168.1.139:22)..
Fri Jun 16 17:06:44 2023 - [debug]   ok.
Fri Jun 16 17:06:44 2023 - [debug]  Connecting via SSH from root@192.168.1.129(192.168.1.129:22) to root@192.168.1.127(192.168.1.127:22)..
Fri Jun 16 17:06:44 2023 - [debug]   ok.
Fri Jun 16 17:06:45 2023 - [info] All SSH connection tests passed successfully.

检查管理的MySQL复制集群的连接配置参数是否OK

masterha_check_repl --conf=/etc/mha_master/mha.cnf

最后看到这个就是成功了       MySQL Replication Health is OK.

[root@qqq /]# masterha_check_repl --conf=/etc/mha_master/mha.cnf
Fri Jun 16 17:09:48 2023 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Fri Jun 16 17:09:48 2023 - [info] Reading application default configuration from /etc/mha_master/mha.cnf..
Fri Jun 16 17:09:48 2023 - [info] Reading server configuration from /etc/mha_master/mha.cnf..
Fri Jun 16 17:09:48 2023 - [info] MHA::MasterMonitor version 0.56.
Fri Jun 16 17:09:49 2023 - [info] GTID failover mode = 0
Fri Jun 16 17:09:49 2023 - [info] Dead Servers:
Fri Jun 16 17:09:49 2023 - [info] Alive Servers:
Fri Jun 16 17:09:49 2023 - [info]   192.168.1.139(192.168.1.139:3306)
Fri Jun 16 17:09:49 2023 - [info]   192.168.1.129(192.168.1.129:3306)
Fri Jun 16 17:09:49 2023 - [info]   192.168.1.127(192.168.1.127:3306)
Fri Jun 16 17:09:49 2023 - [info] Alive Slaves:
Fri Jun 16 17:09:49 2023 - [info]   192.168.1.129(192.168.1.129:3306)  Version=5.5.68-MariaDB (oldest major version between slaves) log-bin:enabled
Fri Jun 16 17:09:49 2023 - [info]     Replicating from 192.168.1.139(192.168.1.139:3306)
Fri Jun 16 17:09:49 2023 - [info]     Primary candidate for the new Master (candidate_master is set)
Fri Jun 16 17:09:49 2023 - [info]   192.168.1.127(192.168.1.127:3306)  Version=5.5.68-MariaDB (oldest major version between slaves) log-bin:enabled
Fri Jun 16 17:09:49 2023 - [info]     Replicating from 192.168.1.139(192.168.1.139:3306)
Fri Jun 16 17:09:49 2023 - [info]     Primary candidate for the new Master (candidate_master is set)
Fri Jun 16 17:09:49 2023 - [info] Current Alive Master: 192.168.1.139(192.168.1.139:3306)
Fri Jun 16 17:09:49 2023 - [info] Checking slave configurations..
Fri Jun 16 17:09:49 2023 - [warning]  relay_log_purge=0 is not set on slave 192.168.1.129(192.168.1.129:3306).
Fri Jun 16 17:09:49 2023 - [warning]  relay_log_purge=0 is not set on slave 192.168.1.127(192.168.1.127:3306).
Fri Jun 16 17:09:49 2023 - [info] Checking replication filtering settings..
Fri Jun 16 17:09:49 2023 - [info]  binlog_do_db= , binlog_ignore_db= 
Fri Jun 16 17:09:49 2023 - [info]  Replication filtering check ok.
Fri Jun 16 17:09:49 2023 - [info] GTID (with auto-pos) is not supported
Fri Jun 16 17:09:49 2023 - [info] Starting SSH connection tests..
Fri Jun 16 17:09:52 2023 - [info] All SSH connection tests passed successfully.
Fri Jun 16 17:09:52 2023 - [info] Checking MHA Node version..
Fri Jun 16 17:09:52 2023 - [info]  Version check ok.
Fri Jun 16 17:09:52 2023 - [info] Checking SSH publickey authentication settings on the current master..
Fri Jun 16 17:09:52 2023 - [info] HealthCheck: SSH to 192.168.1.139 is reachable.
Fri Jun 16 17:09:53 2023 - [info] Master MHA Node version is 0.56.
Fri Jun 16 17:09:53 2023 - [info] Checking recovery script configurations on 192.168.1.139(192.168.1.139:3306)..
Fri Jun 16 17:09:53 2023 - [info]   Executing command: save_binary_logs --command=test --start_pos=4 --binlog_dir=/var/lib/mysql,/var/log/mysql --output_file=/mydata/mha_master/app1/save_binary_logs_test --manager_version=0.56 --start_file=master-log.000003 
Fri Jun 16 17:09:53 2023 - [info]   Connecting to root@192.168.1.139(192.168.1.139:22).. 
  Creating /mydata/mha_master/app1 if not exists.. Creating directory /mydata/mha_master/app1.. done.
   ok.
  Checking output directory is accessible or not..
   ok.
  Binlog found at /var/lib/mysql, up to master-log.000003
Fri Jun 16 17:09:53 2023 - [info] Binlog setting check done.
Fri Jun 16 17:09:53 2023 - [info] Checking SSH publickey authentication and checking recovery script configurations on all alive slave servers..
Fri Jun 16 17:09:53 2023 - [info]   Executing command : apply_diff_relay_logs --command=test --slave_user='mha' --slave_host=192.168.1.129 --slave_ip=192.168.1.129 --slave_port=3306 --workdir=/mydata/mha_master/app1 --target_version=5.5.68-MariaDB --manager_version=0.56 --relay_log_info=/var/lib/mysql/relay-log.info  --relay_dir=/var/lib/mysql/  --slave_pass=xxx
Fri Jun 16 17:09:53 2023 - [info]   Connecting to root@192.168.1.129(192.168.1.129:22).. 
Creating directory /mydata/mha_master/app1.. done.
  Checking slave recovery environment settings..
    Opening /var/lib/mysql/relay-log.info ... ok.
    Relay log found at /var/lib/mysql, up to relay-log.000002
    Temporary relay log file is /var/lib/mysql/relay-log.000002
    Testing mysql connection and privileges.. done.
    Testing mysqlbinlog output.. done.
    Cleaning up test file(s).. done.
Fri Jun 16 17:09:53 2023 - [info]   Executing command : apply_diff_relay_logs --command=test --slave_user='mha' --slave_host=192.168.1.127 --slave_ip=192.168.1.127 --slave_port=3306 --workdir=/mydata/mha_master/app1 --target_version=5.5.68-MariaDB --manager_version=0.56 --relay_log_info=/var/lib/mysql/relay-log.info  --relay_dir=/var/lib/mysql/  --slave_pass=xxx
Fri Jun 16 17:09:53 2023 - [info]   Connecting to root@192.168.1.127(192.168.1.127:22).. 
Creating directory /mydata/mha_master/app1.. done.
  Checking slave recovery environment settings..
    Opening /var/lib/mysql/relay-log.info ... ok.
    Relay log found at /var/lib/mysql, up to relay-log.000002
    Temporary relay log file is /var/lib/mysql/relay-log.000002
    Testing mysql connection and privileges.. done.
    Testing mysqlbinlog output.. done.
    Cleaning up test file(s).. done.
Fri Jun 16 17:09:54 2023 - [info] Slaves settings check done.
Fri Jun 16 17:09:54 2023 - [info] 
192.168.1.139(192.168.1.139:3306) (current master)
 +--192.168.1.129(192.168.1.129:3306)
 +--192.168.1.127(192.168.1.127:3306)

Fri Jun 16 17:09:54 2023 - [info] Checking replication health on 192.168.1.129..
Fri Jun 16 17:09:54 2023 - [info]  ok.
Fri Jun 16 17:09:54 2023 - [info] Checking replication health on 192.168.1.127..
Fri Jun 16 17:09:54 2023 - [info]  ok.
Fri Jun 16 17:09:54 2023 - [warning] master_ip_failover_script is not defined.
Fri Jun 16 17:09:54 2023 - [warning] shutdown_script is not defined.
Fri Jun 16 17:09:54 2023 - [info] Got exit code 0 (Not master dead).

MySQL Replication Health is OK.