MySQL 高可用实践

[nepaul]

基于 MySQL5.7, Semisynchronous Replication(MySQL 半同步复制), Keepalived, MHA 的 MySQL 高可用方案

MySQL, 用得最多最广泛的关系型数据库。 在生产环境中，难免遇到诸如 需要重启服务器或者 MySQL 服务莫名宕了 等问题。如何保证 MySQL 能够持续提供读写等服务，即 高可用性？

本文主要讲述如何从无到有构建一套简单的 MySQL 高可用方案，少概念原理，多实操。

概念及架构简介

• semi-synchronous Replication: 传统的 asynchronous 模式在 master 宕机时仍然有数据丢失的风险。MySQL5.5 之后提供了 semi-synchronous 模式，它会在 master 处理完一个事务并且等待至少一个支持 semi-synchronous 的 slave 确认收到该事件并将其写 入relay-log 之后才会返回。相较于前者，数据安全性更高。性能也无需担心，因为 facebook 也用了。
• Keepalived: 基于 VRRP 实现的路由管理工具，在 负载均衡 和 高可用 场景中应用广泛。这里主要用来创建 VIP(虚拟路由)
• MHA: 即 mysql-master-ha, 最主要的一个功能是监控 MySQL 主从集群( 确切地说主要监控 Master), 在 Master 出现故障后自动选择一个 Slave 提升为 Master，并且可以保证新 Master 和 Slave 的一致性。

来看一下 架构图

应用通过 VIP(虚拟路由，由 Keepalived 创建)访问 MySQL 集群。一主(图 M )二从(图 S1 和 S2)，S2 作备用主库。一旦原 Master 节点出现故障，MHA 自动将主库切换到 S1，并且 S2 随之连接到新的 Master。MHA 同时通过 master_ip_failover_script 脚本停止原 Master 节点上的 Keepalived，路由自动切换到 新 Master。这时候外部应用实际访问的是 S1 了，不会出现 MySQL 突然无法使用的问题。

机器规划

Host	IP	VIP	Role	Software
M	10.20.78.241	10.20.78.11	Master	MySQL5.7 Keepalived MHA-Node
S1	10.20.78.243	备用	candidate_master (Slave)	MySQL5.7 Keepalived MHA-Node
S2	10.20.78.245	-	Slave	MySQL5.7 MHA-Node
MHA-Manager	10.20.78.227	-	MHA Manager	MHA-Node MHA-Manger

M 出现故障后：

Host	IP	VIP	Role
M	10.20.78.241	-	stoped
S1	10.20.78.243	10.20.78.11	master
S2	10.20.78.245	-	Slave
MHA-Manager	10.20.78.227	-	MHA Manager(stoped)

集群的搭建

整个集群搭建安装分为 MySQL 半同步复制、MHA 和 VIP(Keepalived) 三大部分。几乎都是以命令的方式来展示。

在安装具体的软件之前，对所有机器 yum -y upgrade (这是一个很好的习惯)。

所有服务器上 安装 EPEL( MHA 依赖包需要)：rpm -Uvh https://dl.fedoraproject.org/pub/epel/epel-release-latest-6.noarch.rpm

下面开始分别从 MySQL 的半同步复制、Keepalived(VIP) 和 MHA 三方面具体说明。

Semisynchronous Replication

MySQL 在 5.5 以上的版本支持 半同步 复制，并且需要已经配置好 主从复制。

安装 MySQL 5.7

// 安装
wget http://dev.mysql.com/get/mysql57-community-release-el6-7.noarch.rpm
yum localinstall mysql57-community-release-el6-7.noarch.rpm

// 检查是否安装成功
service mysqld start
service mysqld status
mysql --version

// 初始的安全配置
grep 'temporary password' /var/log/mysqld.log
mysql_secure_installation //根据提示操作, 包括设置 root 新密码等操作

// 开始使用
mysql -uroot -p

配置 MySQL 主从同步

Master 机器上的操作：

• 修改 my.cnf

  [mysqld]
  server-id=1     ## 在整个 MySQL 集群中必须唯一
  log-bin=mysql-bin
  binlog_format=ROW

• 重启服务：service mysqld restart
• 创建 slave 同步需要的用户: mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl_username'@'10.20.%.%' IDENTIFIED BY 'repl_password';
• 查看 master 状态：mysql> show master status\G

Slave 机器上的操作：

• 修改 my.cnf：

  [mysqld]
  server-id=2         
    log-bin=mysql-bin            // 只需要在 S1 节点上加入这个配置，因为该节点是作为 MySQL Master 的备机
  relay_log=relay-log
  skip-slave-start

• 重启 mysql: service mysqld restart
• S1(因为是作主备) 上创建 slave 同步需要的用户: mysql> GRANT REPLICATION SLAVE ON . TO 'repl_username'@'10.20.%.%' IDENTIFIED BY 'repl_password';
• S2 上创建一个用户方便后期 mha-manager 操作 GRANT ALL PRIVILEGES ON *.* TO 'repl_username'@'10.20.%.%' IDENTIFIED BY 'repl_password';
• 配置 Master 信息： mysql> CHANGE MASTER TO MASTER_HOST='10.20.78.241',MASTER_PORT=3306,MASTER_USER='mha',MASTER_PASSWORD='King@123',MASTER_LOG_FILE='mysql-bin.000009',MASTER_LOG_POS=496;

• 启动 slave 并 查看状态

  mysql> START SLAVE  
  mysql> SHOW SLAVE STATUS\G

主从同步配置完成，检查状态

• 回到 Master 机器，查看 slave 连接情况：mysql> show slave hosts;
• Master 上创建数据库 mysql> CREATE DATABASE sync_test;，在两个 Slave 上查看，发现同步成功。

  半同步

• Master

  // 动态加载插件
  mysql> INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so'; 
  mysql> SET GLOBAL rpl_semi_sync_master_enabled = {0|1}; // 1 开启
  mysql> SET GLOBAL rpl_semi_sync_master_timeout = N; // 单位是 ms

• Slave

  // 动态加载插件
  mysql> INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';   
  mysql> SET GLOBAL rpl_semi_sync_slave_enabled = {0|1}; / 1 开启
  mysql> STOP SLAVE IO_THREAD; START SLAVE IO_THREAD;

• 半同步状态监控

  可以分别在 Master 和 Slave 上查看
  查看配置：`SHOW VARIABLES LIKE 'rpl_semi_sync%';`
  查看状态：`SHOW STATUS LIKE 'Rpl_semi_sync%';`

  Keepalived

需要在 M 和 S1 两台服务器上安装配置

• 安装, 最后一行编辑的 keepalived.conf 在这里

  wget http://www.keepalived.org/software/keepalived-1.2.20.tar.gz
  tar -zxvf keepalived-1.2.20.tar.gz 
  cd keepalived-1.2.20
   ./configure --prefix=/usr/local/keepalived
  make && make install
  cp /usr/local/keepalived/etc/rc.d/init.d/keepalived /etc/init.d/keepalived
  cp /usr/local/keepalived/sbin/keepalived /usr/sbin/
  cp /usr/local/keepalived/etc/sysconfig/keepalived /etc/sysconfig/
  mkdir -p /etc/keepalived/
  cp /usr/local/keepalived/etc/keepalived/keepalived.conf /etc/keepalived/keepalived.conf
  vim /etc/keepalived/keepalived.conf

• 启动 keeplived： service keepalived start

• 查看虚拟 IP: ip a，此时可以看到在 M 上虚拟 IP 挂载成功，S1 上没有该 IP。一旦 M 上的 keeplived 服务挂掉，虚拟 IP 自动挂载到 S1 上。

  若没有挂载成功，可查看默认 日志文件：/var/log/message

• 给 MySQl 可操作 Keepalived 启停的权限

  通过 visudo 命令，在 /etc/sudoers 文件末尾添加如下几行：

  #mysql sudo keepalived
  Host_Alias HOSTKEEP =110.20.78.241 # 本机IP
  Cmnd_Alias COMKEEP = /etc/init.d/keepalived stop, /etc/init.d/keepalived start, /etc/init.d/keepalived restart, /etc/init.d/keepalived reload ## reload is used to check connection error
  User_Alias USERKEEP = mysql
  USERKEEP  HOSTKEEP=(ALL)  NOPASSWD:COMKEEP
  
  #Defaults   requiretty # 注释掉

  MHA

MHA 由 Node 和 Manager 两部分组成，Node 安装在每一台 MySQL 服务器上，而 MHA-Manager 需要同时安装 Node 和 Manager.

三台 MySQL 服务上创建 MHA-Manger 需要的用户

CREATE USER 'mha_manager'@'172.16.%.%' IDENTIFIED BY 'Crasheye+!@#123';
GRANT ALL ON *.* TO 'mha_manager'@'172.16.%.%';

各节点间的SSH 公钥免密码登陆

MHA Manager 使用 SSH 连接到各个 MySQL 服务器，最新 Slave 节点上的MHA Node 也要用 SSH (scp) 把 relay log 文件发给各个从库节点

• 创建 RSA Keys(详情)

  mkdir ~/.ssh
  chmod 700 ~/.ssh
  ssh-keygen -t rsa

• 在四台服务器上进行 ssh 配置

  ssh-copy-id -i /root/.ssh/id_rsa.pub root@10.20.78.241
  ssh-copy-id -i /root/.ssh/id_rsa.pub root@10.20.78.243
  ssh-copy-id -i /root/.ssh/id_rsa.pub root@10.20.78.245
  ssh-copy-id -i /root/.ssh/id_rsa.pub root@10.20.78.227

  MHA 安装

  三台 MySQL 服务器上安装 MHA-Node

yum install perl-DBD-MySQL -y
rpm -Uvh https://mysql-master-ha.googlecode.com/files/mha4mysql-node-0.52-0.noarch.rpm  #需翻墙

MHA-Manager 结点

• MHA-Manager 的安装

  yum install -y perl-DBD-MySQL perl-Config-Tiny perl-Log-Dispatch perl-Parallel-ForkManager
  rpm -Uvh https://mysql-master-ha.googlecode.com/files/mha4mysql-node-0.52-0.noarch.rpm
  rpm -Uvh https://mysql-master-ha.googlecode.com/files/mha4mysql-manager-0.52-0.noarch.rpm

• 配置 /etc/mha/mha.conf(具体查看这里)

• 编辑 master_ip_failover 脚本：/usr/local/mha/master_ip_failover

  主要作用是 kill 原 Master 服务器上的 Keeplived，核心代码如下(这里有完整可用的代码)

  my $vip = '10.20.78.11';
  my $ssh_start_vip = "/etc/init.d/keepalived start";
  my $ssh_stop_vip = "/etc/init.d/keepalived stop";

• 检查节点间的 ssh 连接: masterha_check_ssh --conf=/etc/mha/mha.conf

  成功会在终端输出 「All SSH connection tests passed successfully」

• 检查MySQL 主从同步： masterha_check_repl --conf=/etc/mha/mha.conf

  成功会在终端输出 MySQL Replication Health is OK.

• 启动 MHA Manager: masterha_manager --conf=/etc/mha/mha.conf

• 查看 MHA 状态 masterha_check_status --conf=/etc/mha/mha.conf

  正常显 「PING_OK」，否则显示 「NOT_RUNNING」

全部配置完毕，enjoy it!

Fail-Over 及 故障恢复

FAQ

1. 配置 ssh 免密码登陆

   Q: Permission denied (publickey,gssapi-keyex,gssapi-with-mic).

   A: 查看 ssh 配置文件(vim /etc/ssh/sshd_config)，确认一下参数为

   PasswordAuthentication yes
   RSAAuthentication yes
   PubkeyAuthentication yes

   1. 查看 mysql 所有 user: SELECT DISTINCT CONCAT('User: ''',user,'''@''',host,''';') AS query FROM mysql.user;
2. MHA check rel 报错： Q: error][/usr/share/perl5/vendor_perl/MHA/MasterMonitor.pm, ln424] Error happened on checking configurations. Argument "1 # M-hM-/M-% MySQL M-fM-0M-8M-hM-?M-^\M-dM-8M-\rM-eM-^A..." isn't numeric in numeric ge (>=) at /usr/share/perl5/vendor_perl/MHA/ServerManager.pm line 1157. A: 很有可能是 mha 的配置文件中有中文，包括注释，去掉再试试

3. 如何在 MySQL 间迁移数据 迁移指定某个数据库 数据源 MySQL 服务器：shell> mysqldump -uroot -p database_name > database_name.sql 目标源 MySQL 服务器：

    mysql> create database_name;
    mysql> use database_name;
    mysql> source database_name.sql;

   官方资料

   Links

   • mysql-master-ha-usercases
   • MySQL High Available with MHA (本文的架构图即出于此)
   • MySQL5.6一主多从的半同步复制实例 (开启了 GTID)
   • MySQL5.6 GTID新特性实践
   • How to Install Latest MySQL 5.7.9 on RHEL/CentOS 7/6/5 and Fedora 23/22/21