mysql日常维护工作内容 mysql数据库运维的五大指标

Linux运维之mysql数据库的高可用（MHA）

• 什么是mysql的数据库的高可用（MHA）

MHA是一套相对成熟的MySQL高可用方案，能做到在0~30s内自动完成数据库的故障切换操作，在master服务器不宕机的情况下，基本能保证数据的一致性

• MHA的配置

实验环境准备：
在做MHA之前 不要和之前的实验混淆 做好gtid主从复制就好

#修改server3的配置：

vim /etc/my.cnf

server_id=3
gtid_mode=ON
enforce_gtid_consistency=ON
log_slave_updates=ON
log_bin=binlog

不需要做半同步复制

#在server1和server2上都安装半同步插件
mysql> INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';

mysql> INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';	##每个节点都安装master和slave插件是因为高可用需要每个节点都可以做master或者slave

#重启io线程使server1和server2同步

#server1和server2都激活半同步插件

mysql> SET GLOBAL rpl_semi_sync_master_enabled =1;

#server2上先不用设置这个

mysql> SET GLOBAL rpl_semi_sync_master_timeout =10000000000000000000;	##半同步超时时间，生产环境应该设置为无穷大，这里这个值是我试出来的最大值，再大就会报错

mysql> show variables like '%rpl%';	##查看

##配置server3
#先删除数据目录中的内容

rm -fr /var/lib/mysql/*

#启动mysqld

systemctl start mysqld

#初始化

mysql_secure_installation

#server3上配置mysql的GTID主从复制

mysql> change master to master_host='172.25.136.1', master_user='repl', master_password='Wsp+123ld', master_auto_position=1;
Query OK, 0 rows affected, 2 warnings (0.04 sec)

mysql> start slave;

#####这里如果出现server3无法同步的情况，怎么解决(1236错误)
先查看gtid执行了多少

mysql> show global variables like '%gtid%';
+----------------------------------+------------------------------------------+
| Variable_name                    | Value                                    |
+----------------------------------+------------------------------------------+
| binlog_gtid_simple_recovery      | ON                                       |
| enforce_gtid_consistency         | ON                                       |
| gtid_executed                    | 1a49d884-4b83-11e9-913f-5254008ec469:1-4 |
| gtid_executed_compression_period | 1000                                     |
| gtid_mode                        | ON                                       |
| gtid_owned                       |                                          |
| gtid_purged                      | 1a49d884-4b83-11e9-913f-5254008ec469:1-4 |
| session_track_gtids              | OFF                                      |
+----------------------------------+------------------------------------------+

gtid_executed表示已经执行了多少，gtid_purged表示执行完清除了二进制日志清除了多少
因为日志已经清除，所以slave节点无法同步数据

先在server1上备份所有数据

mysqldump --all-databases --single-transaction --triggers --routines --events --host=127.0.0.1 --port=3306 --user=root --password=Wsp+123ld > wsp.sql

然后在server3上进行导入

[root@server3 ~]# mysql -p < wsp.sql

会有报错

[root@server3 ~]# mysql -p < wsp.sql 
Enter password: 
ERROR 1840 (HY000) at line 24: @@GLOBAL.GTID_PURGED can only be set when @@GLOBAL.GTID_EXECUTED is empty.

登录数据库，清除原来数据

mysql> stop slave;

mysql> reset master;

再导入，正常

进入数据库，重新配置复制

mysql> CHANGE MASTER TO MASTER_HOST = '172.25.0.1', MASTER_USER = 'repl', MASTER_PASSWORD = 'Wsp+123ld', MASTER_AUTO_POSITION = 1;

mysql> start slave;

主从复制正常

#安装并启用半同步插件 ##其实不需要这步

mysql> INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';

mysql> INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';

mysql> SET GLOBAL rpl_semi_sync_slave_enabled =1;

#为了不每次启动数据库都激活插件，写入配置文件（server1 2 3都写,，写在配置文件最后）

vim /etc/my.cnf

rpl_semi_sync_master_enabled=1
rpl_semi_sync_master_timeout=10000000000000000000

rpl_semi_sync_slave_enabled=1

#配置刚可用(再开一台server4来做master，因为高可用一般都是奇数个节点，因为如果master挂了，其他从节点需要投票选举一个新的master，偶数节点可能出现投票数相同)

在server4上安装MHA-7：

yum install -y mha4mysql-manager-0.58-0.el7.centos.noarch.rpm perl-* mha4mysql-node-0.58-0.el7.centos.noarch.rpm

#配置免密访问（server4需要连接server1 2 3都免密码）
在server4上：

[root@server4 ~]# ssh-keygen	##先生成密钥

[root@server4 ~]# ssh-copy-id server1

[root@server4 ~]# ssh-copy-id server2	#发送密钥

[root@server4 ~]# ssh-copy-id server3

#其他3个节点都安装mha4mysql-node-0.58-0.el7.centos.noarch.rpm即可

yum install -y mha4mysql-node-0.58-0.el7.centos.noarch.rpm

#在server4上配置mha工作目录及配置文件

mkdir /etc/masterha

vim /etc/masterha/app1.cnf

[server default]
manager_workdir=/etc/masterha
manager_log=/var/log/masterha.log  # manager 日志文件
master_binlog_dir=/etc/masterha
#master_ip_failover_script= /usr/local/bin/master_ip_failover
#master_ip_online_change_script= /usr/local/bin/master_ip_online_change
password=Wsp+123ld  #MySQL管理帐号和密码
user=root
ping_interval=1
remote_workdir=/tmp
repl_password=Wsp+123ld
repl_user=repl  # 复制帐号和密码
#report_script=/usr/local/send_report
#secondary_check_script= /usr/local/bin/masterha_secondary_check -s server03 -s server02
#shutdown_script=""
ssh_user=root # 系统ssh用户

[server1]
hostname=172.25.136.1
port=3306

[server2]
hostname=172.25.136.2
port=3306
candidate_master=1
check_repl_delay=0

[server3]
hostname=172.25.136.3
port=3306
no_master=1	##no_master表示这个节点不能作为master

##检测ssh连接

[root@server4 masterha]# masterha_check_ssh --conf=/etc/masterha/app1.cnf

发现报错，server1 2 3互相之间不免密
拷贝server4上的密钥给server1 2 3

[root@server4 ~]# scp -r .ssh server1:
[root@server4 ~]# scp -r .ssh server2:
[root@server4 ~]# scp -r .ssh server3:

再检测ssh，成功

接下来检测复制功能

[root@server4 ~]# masterha_check_repl --conf=/etc/masterha/app1.cnf

发现有报错

Fri Mar 22 10:03:45 2019 - [error][/usr/share/perl5/vendor_perl/MHA/Server.pm, ln180] Got MySQL error when connecting 172.25.0.2(172.25.0.2:3306) :1130:Host 'server4' is not allowed to connect to this MySQL server, but this is not a MySQL crash. Check MySQL server settings.

这是因为server4默认是用root远程连接数据库，但是在配置数据库是已经禁用了root的远程连接

在主库上授权用户

mysql> grant all on *.* to root@'%' identified by 'Wsp+123ld';

再检测发现可能卡住了，因为之前半同步的配置写在了配置文件里，数据不同步就会卡住，在所有节点配置文件里删除半同步配置，只保留之前的

server_id=1
gtid_mode=ON
enforce_gtid_consistency=ON
log_slave_updates=ON
log_bin=binlog

##所有节点的配置文件必须一样！！！！（前面的内容其实都不需要做 只要保持三个server的my.cnf的内容与这里的一致就可以 三台做好gtid的复制就ok ）
##而且三个节点必须有数据（数据必须同步）

再检测，成功

再在两个slave节点设置只读（不写在配置文件里，因为slave随时可能切换为master）

#测试manager能否开启

nohup masterha_manager --conf=/etc/masterha/app1.cnf --remove_dead_master_conf --ignore_last_failover < /dev/null > /var/log/masterha.log 2>&1 &

#测试手动failover切换
先关闭manager，不关的话切不了，manager就是自动切换的工具

[root@server4 masterha]# masterha_stop --conf=/etc/masterha/app1.cnf

server1:systemctl stop mysqld
#手动切换

masterha_master_switch --master_state=dead --conf=/etc/masterha/app1.cnf --dead_master_host=172.25.0.1 --dead_master_ip=172.25.0.1 --dead_master_port=3306 --new_master_host=172.25.0.2 --new_master_port=3306

‘手动切换之前，需要保证主从同步正常，repl复制用户能够远程连接’

中间都选yes

切换成功后，可以在server3上看到它的master已经变成了server2
而server2已经时master了

再手动开启server1，作为slave加入集群

[root@server1 ~]# systemctl start mysqld

mysql> CHANGE MASTER TO MASTER_HOST = '172.25.0.2', MASTER_USER = 'repl', MASTER_PASSWORD = 'Wsp+123ld', MASTER_AUTO_POSITION = 1;

mysql> start slave;

mysql> show slave status\G

至次完成了一次手动切换

这时会在/etc/masterha目录下生成一个app1.failover.complete文件，是来记录failover情况的，再进行failover时必须先把这个文件删除，不然不会failover

#手动在线切换，刚才是master挂掉后切换

masterha_master_switch --conf=/etc/masterha/app1.cnf --master_state=alive --new_master_host=172.25.0.1 --new_master_port=3306 --orig_master_is_new_slave --running_updates_limit=10000

又切换回172.25.0.1为master

#测试半自动failover
首先清理app1.failover.complete

开启mha manager

[root@server4 masterha]# nohup masterha_manager --conf=/etc/masterha/app1.cnf &>/dev/null &

mha manager自带守护进程

在server1上查看

[root@server1 ~]# ps ax | grep mysql
 2320 ?        Sl     0:00 /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid
 2366 pts/0    S+     0:00 grep --color=auto mysql

看到mysql的进程

kill -9 2320

再查看，发现mysql进程又开启了

[root@server1 ~]# ps ax | grep mysql
 2400 ?        Sl     0:00 /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid
 2431 pts/0    S+     0:00 grep --color=auto mysql

在server1上手动关闭mysqld

[root@server1 ~]# systemctl stop mysqld

在server4上查看

[root@server4 masterha]# cat /var/log/masterha.log

发现已经切换，同时manager进程退出，所以全自动需要脚本

先把server1加回集群

mysql> CHANGE MASTER TO MASTER_HOST = '172.25.0.2', MASTER_USER = 'repl', MASTER_PASSWORD = 'Wsp+123ld', MASTER_AUTO_POSITION = 1;

mysql> start slave;

#配置脚本和vip漂移
因为用户访问入口只能有一个，所以需要配置vip

编辑master_ip_failover 和 master_ip_online_change 两个脚本
修改内容：

my $ssh_start_vip = "/sbin/ip addr add $vip dev eth0";
my $ssh_stop_vip = "/sbin/ip addr del $vip dev eth0";

配置自动添加和删除vip

cp master_ip_failover master_ip_online_change /usr/local/bin
cd /usr/local/
cd bin/
chmod +x master_ip_failover master_ip_online_change

###注意在server4的配置文件种 这两行的注释要打开

master_ip_failover_script= /usr/local/bin/master_ip_failover
master_ip_online_change_script= /usr/local/bin/master_ip_online_change

#目前server2是master，所以先给server2添加vip

[root@server2 ~]# ip addr add 172.25.0.100/24 dev eth0

测试vip漂移

[root@server4 bin]# masterha_master_switch --conf=/etc/masterha/app1.cnf --master_state=alive --new_master_host=172.25.0.1 --new_master_port=3306 --orig_master_is_new_slave --running_updates_limit=10000

可以看到切换vip

---

Disabling the VIP - 172.25.0.100/24 on old master: 172.25.0.2

---

---

Enabling the VIP - 172.25.0.100/24 on new master: 172.25.0.1

---

#测试全自动切换
在server4上先删除app1.failover.complete

开启manager

nohup masterha_manager --conf=/etc/masterha/app1.cnf &>/dev/null &

然后在master（server1）上关闭mysql，模拟故障

再在server4上查看日志

cat /var/log/masterha.log

可以看到切换成功，vip也成功漂移

• END