MySQL-20190225-Ubuntu MySQL 主从 HA with KeepAlived

Intro

这不是第一次接触数据库的 HA，不过完全地自己搭建是第一次。询问并参考了官网等多方推荐做法，考虑到适用场景，最终选择主从 HA 模式。

MySQL双主架构的适用场景举个例子，比如一个应用前台和后台用的不同的服务器，数据库大多数是主从结构，但是日志数据库，需要共用一个，这个时候可以把两边的日志数据库设为双主。
以上参考：MySQL主从和双主架构的适用场景

Problem & Mark

HA 的多种模式

有个老外的 PPT，对 MySQL HA 的各种描述进行了详尽的描述，大概一百来页，深入且面面俱到，有时间可以仔细查阅。

下图参考：Best Practice for MySQL High Availability

Mysql 解决主从不一致

在主从同步的过程中，很容易遇到主从不一致的情况，以下链接方法写得很详尽。
MySQL主从不一致情形与解决方法

Mysql 双主

配置MySQL主从/双主引发的反思

Mysql 主从 UUIDs 冲突

报错如下：

show slave status\G;
...
Last_IO_Error: Fatal error: The slave I/O thread stops because master and slave have equal MySQL server UUIDs; these UUIDs must be different for replication to work.
...

关闭从节点的 Mysql 进程，然后删除从节点的 Mysql UUID 自动配置文件 /var/lib/mysql/auto.cnf，重新启动从节点，让他重新生成一个 UUID 即可。

参考如下链接：
MHA集群报错：The slave I/O thread stops because master and slave have equal MySQL server UUIDs

另，Mark：

 # 可以查看当前生效的 server_id
 show variables like 'server_id';

Mysql 关闭免密登录及解决本地 root 改完密码不生效

参考：

1. 为什么mysql设置了密码之后,不需要输入密码就可以登录数据库了？
2. MySQL 修改用户密码及重置root密码

# 安装 mysql 的过程中没有设置密码，可以查看 /etc/mysql/debian.cnf，并用 debian-sys-maint 用户登录 MySQL：
	root@hostname:~# cat /etc/mysql/debian.cnf 
	# Automatically generated for Debian scripts. DO NOT TOUCH!
	[client]
	host     = localhost
	user     = debian-sys-maint
	password = auto-generated-password
	socket   = /var/run/mysqld/mysqld.sock
	[mysql_upgrade]
	host     = localhost
	user     = debian-sys-maint
	password = auto-generated-password
	socket   = /var/run/mysqld/mysqld.sock
# 用这个密码进入 mysql cli，然后更改 root 密码，以及新加其他用户用以登录。
	grant all privileges on *.* to 'root'@'%' identified by 'password' with grant option;
	flush privileges;
# 修改完退出以后发现并不生效，其实是因为在 mysql.root 数据表中关于 root 的条目有好几项，所以这些项都要对应作修改:
	mysql> use mysql
	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
	mysql> select user,host from user;
	+------------------+-----------+
	| user             | host      |
	+------------------+-----------+
	| admin            | %         |
	| root             | %         |
	| root             | 127.0.0.1 |
	| root             | ::1       |
# 另一种改密方式，因为当前用的是 5.7 版本的 MySQL，所以意外发现 mysql.user 表中没有 password 字段，可以改成更新 authentication_string 字段
	root@hostname:/opt/jumpserver# mysql --version
	mysql  xxx Distrib 5.7.25, xxx
	
	update MySQL.user set authentication_string=password('root') where user='root' ;

除了set password 和 update user 表在这两种方法之外，还可以参考链接：mysql修改root密码和设置权限

# 另外，这个地方有点奇怪，有个别记录没有 user 这一字段，所以要删除才能阻止本地免密登录。
	mysql> select * from mysql.user where user='';
	+-----------+------+----------+-------------+-------------+-------------+-------------+----------------+-----------+------------+-----------------+------------+------------+--------------+----------------+-----------------+------------------+------------------+----------------+--------------------+--------------+----------+------------+-------------+--------------+---------------+-----
	| Host      | User | Password | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_pr_priv | File_priv | Grant_priv | References_priv | Index_priv | Alter_priv | Show_db_priv | Supercute_priv | Repl_slave_priv | Repl_client_priv | Create_view_priv | Show_view_priv | Create_routit_priv | Trigger_priv | ssl_type | ssl_cipher | x509_issuer | x509_subject | max_questions | max_
	+-----------+------+----------+-------------+-------------+-------------+-------------+----------------+-----------+------------+-----------------+------------+------------+--------------+----------------+-----------------+------------------+------------------+----------------+--------------------+--------------+----------+------------+-------------+--------------+---------------+-----
	| localhost |      |          | N           | N           | N           | N           | N              | N         | N          | N               | N          | N          | N            | N              | N               | N                | N                | N              | N                  | N            |          |            |             |              |             0 |     
	mysql> use mysql;
	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
	mysql> delete from user where user = '';
	Query OK, 1 row affected (0.00 sec)

KeepAlived

原理配置

KeepAlived 的工作原理/实现方式/实际应用/健康监控等入门详解：
keepalived实现服务高可用

KeepAlived 的配置详解：
keepalived 只有一个配置文件 keepalived.conf，里面主要包括以下几个配置区域，分别是 global_defs、static_ipaddress、static_routes、vrrp_script、vrrp_instance 和 virtual_server。
高可用解决方案—keepalived
keepalived工作原理和配置说明

防火墙问题

KeepAlived 需要关闭 selinux，在测试的过程中出现 redis-cli ping 总是报错 no permission 的 issue。也就是说，在配置 KeepAlived 的时候，需要预先屏蔽防火墙的问题：

# ubuntu
iptables -F;
setenforce 0;
ufw disable; 

# centos
iptables -F;
setenforce 0;
firewall-cmd add-port xxx/tcp; # 或者直接 systemctl disable firewalld; systemctl stop firewalld;

以上参考： keepalived 配置需要注意的问题

VRRP id 冲突

默认 id 51 很容易被同网段其他的小伙伴用掉，所以，换一个 id，问题就解决了。
keepalived 报：bogus VRRP packet received on eth0 !!!

Solution

# 安装 mysql
	apt-get -y install mysql-server libmysqlclient-dev
# 新建数据库并设置授权用户及其密码
	DB_PASSWD="password"
	mysql -uroot -p -e "create database dbname default charset 'utf8'; grant all on dbname.* to 'username'@'%' identified by '$DB_PASSWORD'; flush privileges;"
# 一种设置密码的方式
	grant all privileges on *.* to 'root'@'%' identified by 'password' with grant option;
	grant all privileges on *.* to 'username'@'%' identified by 'password' with grant option;
	flush privileges;
# 另一种设置密码的方式
	set password for 'root'@'%'=password('password');
	set password for 'root'@'127.0.0.1'=password('password');
	set password for 'root'@'::1'=password('password');
	flush privileges;
# 还有更多设置密码的方式可以查看本文的前半段

# 设置 master 节点的 mysql 配置文件 /etc/mysql/my.cnf
	[mysqld]
	server-id=1 # 配置本台机器 mysql 的 id
	
	log-bin = mysql-bin # 开启二进制日志
	relay-log = mysql-relay-bin
	# 或者
	log_bin			= /var/log/mysql/mysql-bin.log
	relay-log		= /var/log/mysql/mysql-relay-bin.log
	
	skip-name-resolve
	binlog-do-db = jumpserver # 配置需要同步的数据库
	binlog_ignore_db	= mysql
	binlog_ignore_db	= information_schema

# 设置 slave 节点的 mysql 配置文件 /etc/mysql/my.cnf
	[mysqld]
	server-id=2 # 配置本台机器 mysql 的 id，要比 master 大
	
	log-bin = mysql-bin # 开启二进制日志
	relay-log = mysql-relay-bin
	# 或者
	log_bin			= /var/log/mysql/mysql-bin.log
	relay-log		= /var/log/mysql/mysql-relay-bin.log
	
	skip-name-resolve
	binlog-do-db = jumpserver # 配置需要同步的数据库
	binlog_ignore_db	= mysql
	binlog_ignore_db	= information_schema

# 设置授权从节点复制主节点数据
	grant replication slave on *.* to 'root'@'slave_hostname' identified by 'password'; flush privileges;

# 设置 slave 节点的 master 节点
	stop slave;
	change master to
	  master_host='hostname',
	  master_user='root',
	  master_password='password',
	  master_port=3306;
	start slave;

# 查看 slave 状态
	mysql> show slave status\G
	*************************** 1. row ***************************
	               Slave_IO_State: Waiting for master to send event
	                  Master_Host: master_hostname
	                  Master_User: root
	                  Master_Port: 3306
	                Connect_Retry: 60
	              Master_Log_File: mysql-bin.000007
	          Read_Master_Log_Pos: 70733
	               Relay_Log_File: mysql-relay-bin.000014
	                Relay_Log_Pos: 70946
	        Relay_Master_Log_File: mysql-bin.000007
	             Slave_IO_Running: Yes
	            Slave_SQL_Running: Yes
	              ...
	      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
	              ...
	1 row in set (0.00 sec)

# 看到Slave_IO_Running: Yes 和 Slave_SQL_Running: Yes，就说明配置成功。
# 如果出现两个属性不总是 Yes，那请参考本文上半部分主从不一致的部分。

接下来配置 KeepAlived 的部分参考如下链接：Keepalived+MySQL实现高可用

# 安装 KeepAlived
	apt-get install keepalived
# master 节点上的 keepalived 配置
	global_defs {
	    router_id master
	} 
	
	vrrp_script check_mysql_run {
	    script "/opt/mysql_ha/mysql_check.sh"
	    interval 30
	}
	
	vrrp_instance mysql {
	    state BACKUP
	    interface ens33  
	    virtual_router_id 51
	    priority 100  
	    advert_int 1
	    nopreempt
	    authentication {
	        auth_type PASS
	        auth_pass 1111
	    }
	    track_script {
	        check_mysql_run
	    }
	 
	    notify_master /opt/mysql_ha/master.sh
	    notify_backup /opt/mysql_ha/backup.sh
	    notify_stop /opt/mysql_ha/stop.sh
	
	    virtual_ipaddress {
	        ip_x.x.x.x
	    }
	}
	
# slave 节点上的 keepalived 配置
	global_defs {
	    router_id slave
	}
	
	vrrp_script check_mysql_run {
	    script "/opt/mysql_ha/mysql_check.sh"
	    interval 30
	}
	
	vrrp_instance mysql {
	    state BACKUP
	    interface eth0
	    virtual_router_id 51
	    priority 90
	    advert_int 1
	    authentication {
	        auth_type PASS
	        auth_pass 1111
	    }
	    track_script {
	        check_mysql_run
	    }
	 
	    notify_master /opt/mysql_ha/master.sh
	    notify_backup /opt/mysql_ha/backup.sh
	    notify_stop /opt/mysql_ha/stop.sh
	
	    virtual_ipaddress {
	        ip_x.x.x.x
	    }
	}

# mysql_check.sh
	#!/bin/bash
	. /root/.bash_profile
	
	count=1
	PASSWORD="xxx"
	
	while true
	do
	
	mysql -uroot --password=$PASSWORD -e "show status;" > /dev/null 2>&1
	i=$?
	ps aux | grep mysqld | grep -v grep > /dev/null 2>&1
	j=$?
	if [ $i = 0 ] && [ $j = 0 ]
	then
	   exit 0
	else
	   if [ $i = 1 ] && [ $j = 0 ]
	   then
	       exit 0
	   else
	        if [ $count -gt 5 ]
	        then
	              break
	        fi
	   let count++
	   continue
	   fi
	fi
	
	done
	
	systemctl stop keepalived

# master.sh
	#!/bin/bash
	. /root/.bash_profile
	
	PASSWORD="xxx"
	
	Master_Log_File=$(mysql -uroot --password="$PASSWORD" -e "show slave status\G" | grep -w Master_Log_File | awk -F": " '{print $2}')
	Relay_Master_Log_File=$(mysql -uroot --password="$PASSWORD" -e "show slave status\G" | grep -w Relay_Master_Log_File | awk -F": " '{print $2}')
	Read_Master_Log_Pos=$(mysql -uroot --password="$PASSWORD" -e "show slave status\G" | grep -w Read_Master_Log_Pos | awk -F": " '{print $2}')
	Exec_Master_Log_Pos=$(mysql -uroot --password="$PASSWORD" -e "show slave status\G" | grep -w Exec_Master_Log_Pos | awk -F": " '{print $2}')
	
	i=1
	
	while true
	do
	
	if [ $Master_Log_File = $Relay_Master_Log_File ] && [ $Read_Master_Log_Pos -eq $Exec_Master_Log_Pos ]
	then
	   echo "ok"
	   break
	else
	   sleep 1
	
	   if [ $i -gt 60 ]
	   then
	      break
	   fi
	   continue
	   let i++
	fi
	done
	
	mysql -uroot --password="$PASSWORD" -e "stop slave;"
	mysql -uroot --password="$PASSWORD" -e "set global innodb_support_xa=0;"
	mysql -uroot --password="$PASSWORD" -e "set global sync_binlog=0;"
	mysql -uroot --password="$PASSWORD" -e "set global innodb_flush_log_at_trx_commit=0;"
	mysql -uroot --password="$PASSWORD" -e "flush logs;GRANT ALL PRIVILEGES ON *.* TO 'admin'@'%' IDENTIFIED BY '$PASSWORD';flush privileges;"
	mysql -uroot --password="$PASSWORD" -e "show master status;" > /tmp/master_status_$(date "+%y%m%d-%H%M").txt
	
# backup.sh
	#!/bin/bash
	. /root/.bash_profile
	
	PASSWORD="xxx"
	
	mysql -uroot --password="Thu$5fri" -e "GRANT ALL PRIVILEGES ON *.* TO 'admin'@'%' IDENTIFIED BY '$PASSWORD';flush privileges;"
	mysql -uroot --password="Thu$5fri" -e "set global event_scheduler=0;"
	mysql -uroot --password="Thu$5fri" -e "set global innodb_support_xa=0;"
	mysql -uroot --password="Thu$5fri" -e "set global sync_binlog=0;"
	mysql -uroot --password="Thu$5fri" -e "set global innodb_flush_log_at_trx_commit=0;"
	
# stop.sh
	#!/bin/bash
	. /root/.bash_profile
	
	PASSWORD="xxx"
	
	mysql -uroot --password="$PASSWORD" -e "GRANT ALL PRIVILEGES ON *.* TO 'admin'@'%' IDENTIFIED BY '$PASSWORD';flush privileges;"
	mysql -uroot --password="$PASSWORD" -e "set global innodb_support_xa=1;"
	mysql -uroot --password="$PASSWORD" -e "set global sync_binlog=1;"
	mysql -uroot --password="$PASSWORD" -e "set global innodb_flush_log_at_trx_commit=1;"
	
	M_File1=$(mysql -uroot --password="$PASSWORD" -e "show master status\G" | awk -F': ' '/File/{print $2}')
	M_Position1=$(mysql -uroot --password="$PASSWORD" -e "show master status\G" | awk -F': ' '/Position/{print $2}')
	sleep 1
	M_File2=$(mysql -uroot --password="$PASSWORD" -e "show master status\G" | awk -F': ' '/File/{print $2}')
	M_Position2=$(mysql -uroot --password="$PASSWORD" -e "show master status\G" | awk -F': ' '/Position/{print $2}')
	
	i=1
	
	while true
	do
	
	if [ $M_File1 = $M_File1 ] && [ $M_Position1 -eq $M_Position2 ]
	then
	   echo "ok"
	   break
	else
	   sleep 1
	
	   if [ $i -gt 60 ]
	   then
	      break
	   fi
	   continue
	   let i++
	fi
	done

# 重启 KeepAlived 即可，进一步查看日志，/var/log/messages 可看出绑定情况；或者 systemctl status keepalived -l
	systemctl restart keepalived
	
	2月 23 14:47:46 hostname Keepalived_vrrp[11203]: VRRP_Instance(mysql) Entering BACKUP STATE
	2月 23 14:47:46 hostname Keepalived_vrrp[11203]: Opening script file /opt/mysql_ha/backup.sh
	2月 23 14:47:46 hostname Keepalived_vrrp[11203]: VRRP_Instance(redis) Entering BACKUP STATE
	2月 23 14:47:46 hostname Keepalived_vrrp[11203]: Opening script file /opt/redis_ha/backup.sh
	2月 23 14:47:46 hostname Keepalived_healthcheckers[11202]: Registering Kernel netlink reflector
	2月 23 14:47:46 hostname Keepalived_healthcheckers[11202]: Registering Kernel netlink command channel
	2月 23 14:47:46 hostname Keepalived_healthcheckers[11202]: Opening file '/etc/keepalived/keepalived.conf'.
	2月 23 14:47:46 hostname Keepalived_healthcheckers[11202]: Using LinkWatch kernel netlink reflector...
	2月 23 14:47:46 hostname Keepalived_vrrp[11203]: VRRP_Script(check_redis_run) succeeded
	2月 23 14:47:46 hostname Keepalived_vrrp[11203]: VRRP_Script(check_mysql_run) succeeded