1、 MariaDB主从复制原理
答：
- 主MySQL服务器上的数据操作会被记录到二进制日志中
- 主MySQL服务器上的dump thread会将二进制日志中的数据发送给从MySQL服务器
- 从MySQL服务器通过IO thread接收主MySQL服务器的二进制日志中的数据，并将这些数据写入自己的中继日志中
- 从MySQL服务器最后使用sql thread将中继日志中的数据写入到服务器中，做到同步数据
2 、MariaDB一主一从架构构建
准备两台纯新的CentOS7.6服务器，其中主机称为node1的服务器地址为192.168.130.132，主机称为node2的服务器地址为192.168.130.133
答：
主mysql服务器：node1
- 在配置文件中添加如下几行
[root@node1 ~]#vim /etc/my.cnf [mysqld] server-id=132 log_bin=/data/mysql_log/binlog binlog_format=row
- 创建二进制日志所在目录，并启动服务
[root@node1 ~]#mkdir /data/mysql_log
[root@node1 ~]#chown mysql.mysql /data/mysql_log
[root@node1 ~]#systemctl restart mariadb
- 创建用于同步的用户，并查看二进制日志的位置
MariaDB [(none)]> GRANT replication slave ON *.* TO 'repluser'@'192.168.130.%' IDENTIFIED BY 'magedu';
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> show master logs;
+---------------+-----------+
| Log_name | File_size |
+---------------+-----------+
| binlog.000001 | 245 |
+---------------+-----------+
1 row in set (0.00 sec)
从MySQL服务器：node2
- 在配置文件中添加如下一行，并启动服务
[root@node2 ~]#vim /etc/my.cnf [mysqld] server-id=133
[root@node2 ~]#systemctl start mariadb
- 在mysql中执行如下语句
MariaDB [(none)]> CHANGE MASTER TO
-> MASTER_HOST='192.168.130.132',
-> MASTER_USER='repluser',
-> MASTER_PASSWORD='magedu',
-> MASTER_PORT=3306,
-> MASTER_LOG_FILE='binlog.000001',
-> MASTER_LOG_POS=245;
Query OK, 0 rows affected (0.00 sec)
- 开启从服务器上的2个线程
MariaDB [(none)]> start slave; Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> show slave statusG
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.0.0.7
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: binlog.000001
Read_Master_Log_Pos: 398
Relay_Log_File: mariadb-relay-bin.000002
Relay_Log_Pos: 679
Relay_Master_Log_File: binlog.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 398
Relay_Log_Space: 975
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 132
1 row in set (0.00 sec)
测试：
- 在主MySQL服务器上导入数据库
[root@node1 ~]#mysql < hellodb_innodb.sql
- 在从MySQL服务器上查看
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| hellodb |
| mysql |
| performance_schema |
| test |
+--------------------+
5 rows in set (0.00 sec)
3 、MariaDB级联复制
上一节中我们完成了一主一从的MariaDB的主从复制架构，但是有的时候，我们期望有一个后备的MariaDB的节点，只备份存储数据，不需要对外提供服务。为了实现该功能，我们可以在之前的一主一从架构上进行调整，即将从节点的二进制文件打开，然后给他配置一个远程同步数据用户，接着使用一台新的服务器做为从服务器的从属，同步从服务器数据，下面我们在原来的架构上添加一台新的CentOS7.6，作为节点3，IP地址为192.168.130.134
答：
中间MySQL服务器：node2
- 在中间MySQL服务器的配置文件中开启二进制日志，并重启服务
[root@node2 ~]#vim /etc/my.cnf
[mysqld]
server-id=133
log_bin
log_slave_updates
binlog_format=row
[root@node2 ~]#systemctl restart mariadb
- 对中间MySQL服务器做完全备份，并发送给node3
[root@node2 ~]#mysqldump -uroot -A -F --single-transaction --master-data=1 > /backup/all.sql
[root@node2 ~]#scp /backup/all.sql 192.168.130.134:/backup
节点3：node3
- 修改刚刚收到的all.sql文件
CHANGE MASTER TO
MASTER_HOST='10.0.0.17',
MASTER_USER='repluser',
MASTER_PASSWORD='magedu',
MASTER_PORT=3306,
MASTER_LOG_FILE='mariadb-bin.000002', MASTER_LOG_POS=245;
- 在node3配置文件中加入server-id并还原备份，由于之前已经在备份文件中指定了二进制文件的位置，所以还原后节点3自然而然就知道了以后的同步位置
[root@node3 ~]#vim /etc/my.cnf
[mysqld]
server-id=134
[root@node3 ~]#systemctl start mariadb
[root@node3 ~]#mysql < /backup/all.sql
- 开启2个线程
MariaDB [(none)]> start slave; Query OK, 0 rows affected (0.00 sec)
#可以看到之前node1上有的数据库和表已经还原到node3上
[root@node3 ~]#mysql
Welcome to the MariaDB monitor. Commands end with ; or g.
Your MariaDB connection id is 3
Server version: 5.5.65-MariaDB MariaDB Server
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.
MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.00 sec)
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]> show tables;
+-------------------+
| Tables_in_hellodb |
+-------------------+
| classes |
| coc |
| courses |
| scores |
| students |
| teachers |
| toc |
+-------------------+
7 rows in set (0.00 sec)
测试
- 在node1上调用过程生产testlog表
[root@node1 ~]#mysql hellodb < testlog.sql
[root@node1 ~]#mysql hellodb
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Welcome to the MariaDB monitor. Commands end with ; or g.
Your MariaDB connection id is 9
Server version: 5.5.65-MariaDB MariaDB Server
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.
MariaDB [hellodb]> call sp_testlog;
- 在node3上查看是否同步
MariaDB [hellodb]> select count(*) from testlog
-> ;
+----------+
| count(*) |
+----------+
| 100000 |
+----------+
1 row in set (0.02 sec)
4 、MariaDB半同步复制
答：
环境
- 3台centos8主机
- 主服务器：10.0.0.8
- 从服务器：10.0.0.18
- 从服务器：10.0.0.28
主MySQL服务器
- 修改配置文件，添加如下几行
[mysqld]
server-id=8
log-bin=/data/mysql-log/bin-log
plugin-load-add=semisync_master
rpl-semi-sync-master-enabled
rpl-semi-sync-master-timeout=3000
- 创建复制数据的用户，并查看二进制日志位置
MariaDB [(none)]> GRANT REPLICATION SLAVE ON *.* TO 'rpluser'@'10.0.0.%' IDENTIFIED BY 'magedu';
Query OK, 0 rows affected (3.001 sec)
MariaDB [(none)]> show master logs;
+----------------+-----------+
| Log_name | File_size |
+----------------+-----------+
| bin-log.000001 | 349 |
| bin-log.000002 | 524 |
+----------------+-----------+
2 rows in set (0.000 sec)
- 查看主节点的半同步状态
MariaDB [(none)]> SHOW VARIABLES LIKE '%semi%';
+---------------------------------------+--------------+
| Variable_name | Value |
+---------------------------------------+--------------+
| rpl_semi_sync_master_enabled | ON |
| rpl_semi_sync_master_timeout | 3000 |
| rpl_semi_sync_master_trace_level | 32 |
| rpl_semi_sync_master_wait_no_slave | ON |
| rpl_semi_sync_master_wait_point | AFTER_COMMIT |
| rpl_semi_sync_slave_delay_master | OFF |
| rpl_semi_sync_slave_enabled | OFF |
| rpl_semi_sync_slave_kill_conn_timeout | 5 |
| rpl_semi_sync_slave_trace_level | 32 |
+---------------------------------------+--------------+
9 rows in set (0.001 sec)
MariaDB [(none)]> SHOW STATUS LIKE '%semi%';
+--------------------------------------------+-------+
| Variable_name | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients | 1 |
| Rpl_semi_sync_master_get_ack | 1 |
| Rpl_semi_sync_master_net_avg_wait_time | 0 |
| Rpl_semi_sync_master_net_wait_time | 0 |
| Rpl_semi_sync_master_net_waits | 1 |
| Rpl_semi_sync_master_no_times | 1 |
| Rpl_semi_sync_master_no_tx | 1 |
| Rpl_semi_sync_master_request_ack | 1 |
| Rpl_semi_sync_master_status | ON |
| Rpl_semi_sync_master_timefunc_failures | 0 |
| Rpl_semi_sync_master_tx_avg_wait_time | 0 |
| Rpl_semi_sync_master_tx_wait_time | 0 |
| Rpl_semi_sync_master_tx_waits | 0 |
| Rpl_semi_sync_master_wait_pos_backtraverse | 0 |
| Rpl_semi_sync_master_wait_sessions | 0 |
| Rpl_semi_sync_master_yes_tx | 0 |
| Rpl_semi_sync_slave_send_ack | 0 |
| Rpl_semi_sync_slave_status | OFF |
+--------------------------------------------+-------+
18 rows in set (0.001 sec)
所有从MySQL服务器
- 修改配置文件，添加如下几行
[mysqld]
server-id=18
plugin-load-add=semisync_slave
rpl-semi-sync-slave-enabled
- 重启服务，并添加主节点，开启从节点线程
MariaDB [(none)]> CHANGE MASTER TO -> MASTER_HOST='10.0.0.8', -> MASTER_USER='rpluser', -> MASTER_PASSWORD='magedu', -> MASTER_PORT=3306, -> MASTER_LOG_FILE='bin-log.000001', -> MASTER_LOG_POS=349; Query OK, 0 rows affected (0.027 sec)
MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.002 sec)
- 查看从节点的半同步状态
MariaDB [(none)]> SHOW VARIABLES LIKE '%semi%';
+---------------------------------------+--------------+
| Variable_name | Value |
+---------------------------------------+--------------+
| rpl_semi_sync_master_enabled | OFF |
| rpl_semi_sync_master_timeout | 10000 |
| rpl_semi_sync_master_trace_level | 32 |
| rpl_semi_sync_master_wait_no_slave | ON |
| rpl_semi_sync_master_wait_point | AFTER_COMMIT |
| rpl_semi_sync_slave_delay_master | OFF |
| rpl_semi_sync_slave_enabled | ON |
| rpl_semi_sync_slave_kill_conn_timeout | 5 |
| rpl_semi_sync_slave_trace_level | 32 |
+---------------------------------------+--------------+
9 rows in set (0.001 sec)
MariaDB [(none)]> SHOW STATUS LIKE '%semi%';
+--------------------------------------------+-------+
| Variable_name | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients | 0 |
| Rpl_semi_sync_master_get_ack | 0 |
| Rpl_semi_sync_master_net_avg_wait_time | 0 |
| Rpl_semi_sync_master_net_wait_time | 0 |
| Rpl_semi_sync_master_net_waits | 0 |
| Rpl_semi_sync_master_no_times | 0 |
| Rpl_semi_sync_master_no_tx | 0 |
| Rpl_semi_sync_master_request_ack | 0 |
| Rpl_semi_sync_master_status | OFF |
| Rpl_semi_sync_master_timefunc_failures | 0 |
| Rpl_semi_sync_master_tx_avg_wait_time | 0 |
| Rpl_semi_sync_master_tx_wait_time | 0 |
| Rpl_semi_sync_master_tx_waits | 0 |
| Rpl_semi_sync_master_wait_pos_backtraverse | 0 |
| Rpl_semi_sync_master_wait_sessions | 0 |
| Rpl_semi_sync_master_yes_tx | 0 |
| Rpl_semi_sync_slave_send_ack | 1 |
| Rpl_semi_sync_slave_status | ON |
+--------------------------------------------+-------+
18 rows in set (0.001 sec)
测试
停止所有从节点，在主节点新增数据库，3秒后才创建成功
#未关闭从节点的情况下，在主节点上创建数据库，立即生效
MariaDB [(none)]> create database db1;
Query OK, 1 row affected (0.001 sec)
#所有从节点上实现
MariaDB [(none)]> stop slave;
Query OK, 0 rows affected (0.004 sec)
#主节点上实现，等待了3秒才显示成功
MariaDB [(none)]> create database db2;
Query OK, 1 row affected (3.001 sec)
5、 MariaDB高可用方案MHA
答：
环境：
- 10.0.0.7：centos7，MHA管理端
- 10.0.0.8：centos8，Master
- 10.0.0.18：centos18，Slave
- 10.0.0.28：centos28，Slave
配置MHA管理端
- 安装mha4mysql-manager和mha4mysql-node软件包，都为0.56版本
[root@mha ~]#ls mha4mysql-*
mha4mysql-manager-0.56-0.el6.noarch.rpm mha4mysql-node-0.56-0.el6.noarch.rpm
[root@mha ~]#yum install -y mha4mysql-*.rpm
- 在各个节点之间实现SSH KEY验证
[root@mha ~]#ssh-keygen
[root@mha ~]#ssh-copy-id 10.0.0.7
[root@mha ~]#rsync -av .ssh 10.0.0.8:/root
[root@mha ~]#rsync -av .ssh 10.0.0.18:/root
[root@mha ~]#rsync -av .ssh 10.0.0.28:/root
- 在MHA管理节点实现MHA配置文件
[root@mha ~]#mkdir /etc/mastermha
[root@mha ~]#cd /etc/mastermha
[root@mha mastermha]#vim app1.cnf
[server default]
user=mhauser
password=magedu
manager_workdir=/data/mastermha/app1/
manager_log=/data/mastermha/app1/manager.log
remote_workdir=/data/mastermha/app1/
ssh_user=root
repl_user=rpluser
repl_password=magedu
ping_interval=1
master_ip_failover_script=/usr/local/bin/master_ip_failover
report_script=/usr/local/bin/sendmail.sh
check_repl_delay=0
[server1]
hostname=10.0.0.8
[server2]
hostname=10.0.0.18
[server3]
hostname=10.0.0.28
candidate_master=1
- 配置”切换主节点脚本"和“节点切换后，发送邮件脚本”
#当主节点故障后 ，执行切换节点的脚本
[root@mha mastermha]#vim /usr/local/bin/master_ip_failover
#!/usr/bin/env perl
use strict;
use warnings FATAL => 'all';
use Getopt::Long;
my (
$command, $ssh_user, $orig_master_host, $orig_master_ip,
$orig_master_port, $new_master_host, $new_master_ip, $new_master_port
);
my $vip = '10.0.0.100';#设置Virtual IP
my $gateway = '10.0.0.254';#网关Gateway IP
my $interface = 'eth0';
my $key = "1";
my $ssh_start_vip = "/sbin/ifconfig $interface:$key $vip;/sbin/arping -I $interface -c 3 -s $vip $gateway >/dev/null 2>&1";
my $ssh_stop_vip = "/sbin/ifconfig $interface:$key down";
GetOptions(
'command=s' => $command,
'ssh_user=s' => $ssh_user,
'orig_master_host=s' => $orig_master_host,
'orig_master_ip=s' => $orig_master_ip,
'orig_master_port=i' => $orig_master_port,
'new_master_host=s' => $new_master_host,
'new_master_ip=s' => $new_master_ip,
'new_master_port=i' => $new_master_port,
);
exit &main();
sub main {
print "
IN SCRIPT TEST====$ssh_stop_vip==$ssh_start_vip===
";
if ( $command eq "stop" || $command eq "stopssh" ) {
# $orig_master_host, $orig_master_ip, $orig_master_port are passed.
# If you manage master ip address at global catalog database,
# invalidate orig_master_ip here.
my $exit_code = 1;
eval {
print "Disabling the VIP on old master: $orig_master_host
";
&stop_vip();
$exit_code = 0;
};
if ($@) {
warn "Got Error: $@
";
exit $exit_code;
}
exit $exit_code;
}
elsif ( $command eq "start" ) {
# all arguments are passed.
# If you manage master ip address at global catalog database,
# activate new_master_ip here.
# You can also grant write access (create user, set read_only=0, etc) here.
my $exit_code = 10;
eval {
print "Enabling the VIP - $vip on the new master - $new_master_host
";
&start_vip();
$exit_code = 0;
};
if ($@) {
warn $@;
exit $exit_code;
}
exit $exit_code;
}
elsif ( $command eq "status" ) {
print "Checking the Status of the script.. OK
";
`ssh $ssh_user@$orig_master_host " $ssh_start_vip "`;
exit 0;
}
else {
&usage();
exit 1;
}
}
# A simple system call that enable the VIP on the new master
sub start_vip() {
`ssh $ssh_user@$new_master_host " $ssh_start_vip "`;
}
# A simple system call that disable the VIP on the old_master
sub stop_vip() {
`ssh $ssh_user@$orig_master_host " $ssh_stop_vip "`;
}
sub usage {
print
"Usage: master_ip_failover --command=start|stop|stopssh|status --orig_master_host=host --orig_master_ip=ip --orig_master_port=port --new_master_host=host --new_master_ip=ip --new_master_port=port
";
}
[root@mha mastermha]#chmod +x /usr/local/bin/master_ip_failover
#设置切换节点后，发送邮件通知
[root@mha ~]#vim .mailrc
set from=465454572@qq.com
set smtp=smtp.qq.com
set smtp_auth_user=465454572@qq.com
set smtp_auth_password=QQ邮箱授权码
[root@mha ~]#vim /usr/local/bin/sendmail.sh
echo "MySQL is down" | mail -s "MHA Warning" 465454572@qq.com
[root@mha mastermha]#chmod +x /usr/local/bin/sendmail.sh
配置主从节点，并启动半同步复制
- 在所有主从节点上安装mha4mysql-node-0.56-0.el6.noarch.rpm
[root@master ~]#yum install -y mha4mysql-node-0.56-0.el6.noarch.rpm
[root@slave1 ~]#yum install -y mha4mysql-node-0.56-0.el6.noarch.rpm
[root@slave2 ~]#yum install -y mha4mysql-node-0.56-0.el6.noarch.rpm
- Master主节点配置文件中添加如下几行
[root@master ~]#vim /etc/my.cnf.d/mariadb-server.cnf
[mysqld]
server-id=8
log-bin
skip-name-resolve
general-log #通用日志可选择性开启
plugin-load-add=semisync_master #加载半同步复制的主节点插件
rpl-semi-sync-master-enabled #以主节点身份开启半同步复制
rpl-semi-sync-master-timeout=3000 #设置半同步复制的超时时长
- 启动Master，并且创建主从复制用户和MHA连接所有节点的用户
[root@master ~]#systemctl start mariadb [root@master ~]#mysql MariaDB [(none)]> GRANT replication slave ON *.* TO rpluser@'10.0.0.%' IDENTIFIED BY 'magedu'; Query OK, 0 rows affected (3.001 sec) MariaDB [(none)]> GRANT all ON *.* TO mhauser@'10.0.0.%' IDENTIFIED BY 'magedu'; Query OK, 0 rows affected (0.000 sec)
MariaDB [(none)]> SHOW MASTER LOGS;
+--------------------+-----------+
| Log_name | File_size |
+--------------------+-----------+
| mariadb-bin.000001 | 28198 |
| mariadb-bin.000002 | 722 |
+--------------------+-----------+
2 rows in set (0.000 sec)
- 在主节点上配置和MHA连接的虚拟ip地址（VIP）
#主从节点都必须安装ifconfig命令，因为之后切换主节点是，虚拟地址飘到新的主节点上时，脚本中使用的就是ifconfig命令，若无ifconfig命令，该虚拟地址就飘不到新的主节点上了
[root@master ~]#yum install -y net-tools
[root@master ~]#ifconfig eth0:1 10.0.0.100
[root@master ~]#ifconfig eth0:1
eth0:1: flags=4163<UP,BROADCAST,RUNNING,MULTICAST> mtu 1500
inet 10.0.0.100 netmask 255.0.0.0 broadcast 10.255.255.255
ether 00:0c:29:a8:d1:eb txqueuelen 1000 (Ethernet)
- 配置slave1和slave2，并启动同步
#修改配置文件，并设置同步 #slave1 [root@slave1 ~]#vim /etc/my.cnf.d/mariadb-server.cnf [mysqld] server-id=18 log-bin read-only skip-name-resolve relay-log-purge=0 plugin-load-add=semisync_slave rpl-semi-sync-slave-enabled [root@slave1 ~]#systemctl restart mariadb [root@slave1 ~]#mysql Welcome to the MariaDB monitor. Commands end with ; or g. Your MariaDB connection id is 9 Server version: 10.3.17-MariaDB-log MariaDB Server Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or 'h' for help. Type 'c' to clear the current input statement. MariaDB [(none)]> CHANGE MASTER TO -> MASTER_HOST='10.0.0.8', -> MASTER_USER='rpluser', -> MASTER_PASSWORD='magedu', -> MASTER_PORT=3306, -> MASTER_LOG_FILE='mariadb-bin.000001', -> MASTER_LOG_POS=28198; Query OK, 0 rows affected (0.025 sec) [root@slave2 ~]#mysql Welcome to the MariaDB monitor. Commands end with ; or g. Your MariaDB connection id is 9 Server version: 10.3.17-MariaDB-log MariaDB Server Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or 'h' for help. Type 'c' to clear the current input statement. MariaDB [(none)]> CHANGE MASTER TO -> MASTER_HOST='10.0.0.8', -> MASTER_USER='rpluser', -> MASTER_PASSWORD='magedu', -> MASTER_PORT=3306, -> MASTER_LOG_FILE='mariadb-bin.000001', -> MASTER_LOG_POS=28198; Query OK, 0 rows affected (0.004 sec) MariaDB [(none)]> START SLAVE; Query OK, 0 rows affected (0.002 sec) #slave2 [root@slave2 ~]#vim /etc/my.cnf.d/mariadb-server.cnf [mysqld] server-id=28 log-bin read-only skip-name-resolve relay-log-purge=0 plugin-load-add=semisync_slave rpl-semi-sync-slave-enabled [root@slave2 ~]#mysql Welcome to the MariaDB monitor. Commands end with ; or g. Your MariaDB connection id is 9 Server version: 10.3.17-MariaDB-log MariaDB Server Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or 'h' for help. Type 'c' to clear the current input statement. MariaDB [(none)]> CHANGE MASTER TO -> MASTER_HOST='10.0.0.8', -> MASTER_USER='rpluser', -> MASTER_PASSWORD='magedu', -> MASTER_PORT=3306, -> MASTER_LOG_FILE='mariadb-bin.000001', -> MASTER_LOG_POS=28198; Query OK, 0 rows affected (0.004 sec) MariaDB [(none)]> START SLAVE; Query OK, 0 rows affected (0.002 sec)
#安装ifconfig命令
[root@slave1 ~]#yum install -y net-tools
[root@slave2 ~]#yum install -y net-tools
检查MHA相关环境
- 检查各节点的ssh连接
[root@mha ~]#masterha_check_ssh --conf=/etc/mastermha/app1.cnf
Sun Sep 27 13:14:52 2020 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Sun Sep 27 13:14:52 2020 - [info] Reading application default configuration from /etc/mastermha/app1.cnf..
Sun Sep 27 13:14:52 2020 - [info] Reading server configuration from /etc/mastermha/app1.cnf..
Sun Sep 27 13:14:52 2020 - [info] Starting SSH connection tests..
Sun Sep 27 13:14:54 2020 - [debug]
Sun Sep 27 13:14:52 2020 - [debug] Connecting via SSH from root@10.0.0.18(10.0.0.18:22) to root@10.0.0.8(10.0.0.8:22)..
Sun Sep 27 13:14:53 2020 - [debug] ok.
Sun Sep 27 13:14:53 2020 - [debug] Connecting via SSH from root@10.0.0.18(10.0.0.18:22) to root@10.0.0.28(10.0.0.28:22)..
Warning: Permanently added '10.0.0.28' (ECDSA) to the list of known hosts.
Sun Sep 27 13:14:53 2020 - [debug] ok.
Sun Sep 27 13:14:54 2020 - [debug]
Sun Sep 27 13:14:53 2020 - [debug] Connecting via SSH from root@10.0.0.28(10.0.0.28:22) to root@10.0.0.8(10.0.0.8:22)..
Sun Sep 27 13:14:53 2020 - [debug] ok.
Sun Sep 27 13:14:53 2020 - [debug] Connecting via SSH from root@10.0.0.28(10.0.0.28:22) to root@10.0.0.18(10.0.0.18:22)..
Sun Sep 27 13:14:54 2020 - [debug] ok.
Sun Sep 27 13:14:54 2020 - [debug]
Sun Sep 27 13:14:52 2020 - [debug] Connecting via SSH from root@10.0.0.8(10.0.0.8:22) to root@10.0.0.18(10.0.0.18:22)..
Warning: Permanently added '10.0.0.18' (ECDSA) to the list of known hosts.
Sun Sep 27 13:14:53 2020 - [debug] ok.
Sun Sep 27 13:14:53 2020 - [debug] Connecting via SSH from root@10.0.0.8(10.0.0.8:22) to root@10.0.0.28(10.0.0.28:22)..
Warning: Permanently added '10.0.0.28' (ECDSA) to the list of known hosts.
Sun Sep 27 13:14:54 2020 - [debug] ok.
Sun Sep 27 13:14:54 2020 - [info] All SSH connection tests passed successfully.
- 检查主从复制
[root@mha ~]#masterha_check_repl --conf=/etc/mastermha/app1.cnf
MySQL Replication Health is OK.
开启MHA
[root@mha ~]#masterha_manager --conf=/etc/mastermha/app1.cnf Sun Sep 27 13:36:56 2020 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping. Sun Sep 27 13:36:56 2020 - [info] Reading application default configuration from /etc/mastermha/app1.cnf.. Sun Sep 27 13:36:56 2020 - [info] Reading server configuration from /etc/mastermha/app1.cnf..
[root@mha ~]#masterha_check_status --conf=/etc/mastermha/app1.cnf
app1 (pid:36419) is running(0:PING_OK), master:10.0.0.8
[root@mha ~]#
检测MHA是否有效
- 关闭Master主节点
[root@master ~]#killall -9 mysqld
- MHA完成切换后，自动退出
[root@mha ~]#masterha_manager --conf=/etc/mastermha/app1.cnf
Sun Sep 27 13:36:56 2020 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Sun Sep 27 13:36:56 2020 - [info] Reading application default configuration from /etc/mastermha/app1.cnf..
Sun Sep 27 13:36:56 2020 - [info] Reading server configuration from /etc/mastermha/app1.cnf..
Creating /data/mastermha/app1 if not exists.. ok.
Checking output directory is accessible or not..
ok.
Binlog found at /var/lib/mysql, up to mariadb-bin.000002
Sun Sep 27 13:41:00 2020 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Sun Sep 27 13:41:00 2020 - [info] Reading application default configuration from /etc/mastermha/app1.cnf..
Sun Sep 27 13:41:00 2020 - [info] Reading server configuration from /etc/mastermha/app1.cnf..
[root@mha ~]#
- 查看slave1，发现它的主节点（master）已经变为10.0.0.28，说明切换成功
MariaDB [(none)]> show slave statusG
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.0.0.28
Master_User: rpluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mariadb-bin.000002
Read_Master_Log_Pos: 344
Relay_Log_File: mariadb-relay-bin.000002
Relay_Log_Pos: 557
Relay_Master_Log_File: mariadb-bin.000002
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
。。。
- 并且虚拟ip（vip）也已经飘到了原来的slave2（10.0.0.28）上
[root@slave2 ~]#ifconfig
eth0: flags=4163<UP,BROADCAST,RUNNING,MULTICAST> mtu 1500
inet 10.0.0.28 netmask 255.255.255.0 broadcast 10.0.0.255
inet6 fe80::20c:29ff:fedb:fe0e prefixlen 64 scopeid 0x20<link>
ether 00:0c:29:db:fe:0e txqueuelen 1000 (Ethernet)
RX packets 49233 bytes 66673333 (63.5 MiB)
RX errors 0 dropped 0 overruns 0 frame 0
TX packets 7061 bytes 1118804 (1.0 MiB)
TX errors 0 dropped 0 overruns 0 carrier 0 collisions 0
eth0:1: flags=4163<UP,BROADCAST,RUNNING,MULTICAST> mtu 1500
inet 10.0.0.100 netmask 255.0.0.0 broadcast 10.255.255.255
ether 00:0c:29:db:fe:0e txqueuelen 1000 (Ethernet)