AWS EC2搭建MHA+VIP+MySQL5.7

发表于 2017-07-24 | 分类于集群高可用

字数统计: | 阅读时长 ≈

前言

随着云服务的迅速发展，越来越多企业选择将服务托管在云服务中，在数据库领域，AWS RDS、Aliyun RDS等都是不错的选择，默认已经做了高可用，基础运维，可以为企业节省不少的运维成本。由于RDS物理数据、Root权限等其他对象对用户不开放，难免在自动化运维中有些壁垒。本文将围绕在AWS EC2结合MHA做MySQL的高可用，EC2不支持VIP，可通过绑定私有IP的方式来实现。

MHA简介

MHA是由日本MySQL专家youshimaton(现就职于Facebook公司)用Perl写的一套MySQL故障切换方案，以保障数据库的高可用性。在MySQL故障切换过程中，MHA能做到在0~30s之内实现主MySQL故障转移。
该软件由两部分组成：MHA Manager（管理节点）和MHA Node（数据节点）。MHA Manager可以单独部署在一台独立的机器上管理多个master-slave集群，也可以部署在一台slave节点上。MHA Node运行在每台MySQL服务器上，MHA Manager会定时探测集群中的master节点，当master出现故障时，它可以自动将最新数据的slave提升为新的master，然后将所有其他的slave重新指向新的master。整个故障转移过程对应用程序完全透明。

准备

操作系统：Amazon Linux
master:172.31.13.126
slave:172.31.9.182
vip:172.31.0.200
其中，使用两台机器，分别部署主库和从库，MHA默认部署在从库上，下方中的VIP通指私有IP。

系统初始化修改

修改主机名

主库修改主机名

1
2
3

[root@master ~]# cat /etc/sysconfig/network
NETWORKING=yes
HOSTNAME=master

从库修改主机名

1
2
3

[root@slave ~]# cat /etc/sysconfig/network
NETWORKING=yes
HOSTNAME=slave

修改时区

Amazon Linux默认安装好后英国时间，需要在主库和从库修改时区

1 2	[root@master ~]# ln -sf /usr/share/zoneinfo/Asia/Shanghai /etc/localtime [root@master ~]# /usr/sbin/ntpdate 0.centos.pool.ntp.org && /sbin/hwclock -w &>/dev/null

设置防火墙

主库设置，允许从库访问

1
2
3

[root@master ~]# /etc/init.d/iptables status|grep 172.31.9.182
15   ACCEPT     all  --  172.31.9.182         0.0.0.0/0           
[root@master ~]#

其中，172.31.9.182是允许从库的访问规则

从库设置，允许主库访问

1
2
3

[root@slave ~]# /etc/init.d/iptables status|grep 172.31.13.126 
15   ACCEPT     all  --  172.31.13.126        0.0.0.0/0           
[root@slave ~]#

其中，172.31.13.126是允许主库的访问规则

关闭SELINUX

由于使用的操作系统为Amazon Linux，通过查看无selinux设置。

建立SSH无密码登录

主库设置

修改服务器/etc/ssh/ssh_config文件，把参数GSSAPIAuthentication修改为no。
修改服务器/etc/ssh/sshd_config文件，把参数PasswordAuthentication修改为yes,参数PermitRootLogin修改为yes,重启ssh服务
修改服务器/etc/hosts.allow文件，允许从库连接

[root@master ~]# sed '/^#/d;/^$/d' /etc/ssh/ssh_config |grep GSSAPIAuthentication
        GSSAPIAuthentication no
[root@master ~]# sed '/^#/d;/^$/d' /etc/ssh/sshd_config |grep -E 'PasswordAuthentication|PermitRootLogin'
PermitRootLogin yes
PermitRootLogin forced-commands-only
PasswordAuthentication yes
[root@master ~]# /etc/init.d/sshd restart
停止 sshd：                                                [确定]
正在启动 sshd：                                            [确定]

使用命令ssh-keygen生成公钥，发送到从库，同时尝试在主库无密码形式登录从库，而且也要保证本机无密码登录本机

[root@master ~]# ssh-keygen
Generating public/private rsa key pair.
Enter file in which to save the key (/root/.ssh/id_rsa): 
Enter passphrase (empty for no passphrase): 
Enter same passphrase again: 
Your identification has been saved in /root/.ssh/id_rsa.
Your public key has been saved in /root/.ssh/id_rsa.pub.
The key fingerprint is:
43:a3:80:f1:8c:d4:5e:8d:3c:99:e5:39:39:e3:89:9a root@master
The key's randomart image is:
+--[ RSA 2048]----+
|  o. . *.        |
| . *. B..o       |
|  o.+. .X        |
|    .. = *       |
|      o S        |
|     o   .       |
|    E            |
|                 |
|                 |
+-----------------+
[root@master ~]# ssh-copy-id -i ~/.ssh/id_rsa.pub root@172.31.9.182
The authenticity of host '172.31.9.182 (172.31.9.182)' can't be established.
ECDSA key fingerprint is 72:71:66:dc:6c:b0:31:e7:6c:77:4c:8d:32:69:e0:88.
Are you sure you want to continue connecting (yes/no)? yes
root@172.31.9.182's password: 

Number of key(s) added: 1

Now try logging into the machine, with:   "ssh 'root@172.31.9.182'"
and check to make sure that only the key(s) you wanted were added.
[root@master ~]# ssh-copy-id -i ~/.ssh/id_rsa.pub root@172.31.13.126
The authenticity of host '172.31.13.126 (172.31.13.126)' can't be established.
ECDSA key fingerprint is b6:ef:9f:f0:5e:fd:8f:49:ef:be:79:fb:44:ea:63:08.
Are you sure you want to continue connecting (yes/no)? yes
root@172.31.13.126's password: 

Number of key(s) added: 1

Now try logging into the machine, with:   "ssh 'root@172.31.13.126'"
and check to make sure that only the key(s) you wanted were added.

[root@master ~]# ssh 'root@172.31.9.182'
Last login: Thu Jul 20 04:51:07 2017

       __|  __|_  )
       _|  (     /   Amazon Linux AMI
      ___|\___|___|

https://aws.amazon.com/amazon-linux-ami/2017.03-release-notes/
[root@slave ~]# exit
logout
Connection to 172.31.9.182 closed.
[root@master ~]# ssh 'root@172.31.13.126'
Last login: Thu Jul 20 04:37:13 2017

       __|  __|_  )
       _|  (     /   Amazon Linux AMI
      ___|\___|___|

https://aws.amazon.com/amazon-linux-ami/2017.03-release-notes/
[root@master ~]# exit
logout
Connection to 172.31.13.126 closed.

从库设置

和主库的配置相同
修改服务器/etc/ssh/ssh_config文件，把参数GSSAPIAuthentication修改为no。
修改服务器/etc/ssh/sshd_config文件，把参数PasswordAuthentication修改为yes,参数PermitRootLogin修改为yes,重启ssh服务
修改服务器/etc/hosts.allow文件，允许从库连接

[root@slave ~]# sed '/^#/d;/^$/d' /etc/ssh/ssh_config |grep GSSAPIAuthentication
        GSSAPIAuthentication no
[root@slave ~]# sed '/^#/d;/^$/d' /etc/ssh/sshd_config |grep -E 'PasswordAuthentication|PermitRootLogin'
PermitRootLogin yes
PermitRootLogin forced-commands-only
PasswordAuthentication yes
[root@slave ~]# /etc/init.d/sshd restart
停止 sshd：                                                [确定]
正在启动 sshd：                                            [确定]

使用命令ssh-keygen生成公钥，发送到主库，同时尝试在从库无密码形式登录主库，而且也要保证本机无密码登录本机

[root@slave ~]# ssh-keygen
Generating public/private rsa key pair.
Enter file in which to save the key (/root/.ssh/id_rsa): 
Enter passphrase (empty for no passphrase): 
Enter same passphrase again: 
Your identification has been saved in /root/.ssh/id_rsa.
Your public key has been saved in /root/.ssh/id_rsa.pub.
The key fingerprint is:
7d:57:15:e0:20:6f:6f:45:00:76:c4:ba:66:32:fd:b3 root@slave
The key's randomart image is:
+--[ RSA 2048]----+
|         . ++=ooo|
|          + +.. .|
|           o.. ..|
|         .... .. |
|        S o oo.  |
|         o *..   |
|          = .    |
|             o   |
|             Eo  |
+-----------------+
[root@slave ~]# ssh-keygen
Generating public/private rsa key pair.
Enter file in which to save the key (/root/.ssh/id_rsa): 
Enter passphrase (empty for no passphrase): 
Enter same passphrase again: 
Your identification has been saved in /root/.ssh/id_rsa.
Your public key has been saved in /root/.ssh/id_rsa.pub.
The key fingerprint is:
da:ea:e3:1c:fb:4b:f5:84:da:a3:47:ea:2c:fa:3c:a6 root@slave
The key's randomart image is:
+--[ RSA 2048]----+
|                 |
|                 |
|                 |
|           .     |
|        S o .    |
|       o +.o     |
|      o +oo .    |
|     o=*....     |
|    EBO**o       |
+-----------------+
[root@slave ~]# ssh-copy-id -i ~/.ssh/id_rsa.pub root@172.31.13.126
The authenticity of host '172.31.13.126 (172.31.13.126)' can't be established.
ECDSA key fingerprint is b6:ef:9f:f0:5e:fd:8f:49:ef:be:79:fb:44:ea:63:08.
Are you sure you want to continue connecting (yes/no)? yes
root@172.31.13.126's password: 

Number of key(s) added: 1

Now try logging into the machine, with:   "ssh 'root@172.31.13.126'"
and check to make sure that only the key(s) you wanted were added.

[root@slave ~]# ssh-copy-id -i ~/.ssh/id_rsa.pub root@172.31.9.182
The authenticity of host '172.31.9.182 (172.31.9.182)' can't be established.
ECDSA key fingerprint is 72:71:66:dc:6c:b0:31:e7:6c:77:4c:8d:32:69:e0:88.
Are you sure you want to continue connecting (yes/no)? yes
root@172.31.9.182's password: 

Number of key(s) added: 1

Now try logging into the machine, with:   "ssh 'root@172.31.9.182'"
and check to make sure that only the key(s) you wanted were added.

[root@slave ~]# ssh 'root@172.31.13.126'
Last login: Thu Jul 20 05:36:57 2017 from 172.31.13.126

       __|  __|_  )
       _|  (     /   Amazon Linux AMI
      ___|\___|___|

https://aws.amazon.com/amazon-linux-ami/2017.03-release-notes/
[root@master ~]# exit
logout
Connection to 172.31.13.126 closed.
[root@slave ~]# ssh 'root@172.31.9.182' 
Last login: Thu Jul 20 05:36:46 2017 from 172.31.13.126

       __|  __|_  )
       _|  (     /   Amazon Linux AMI
      ___|\___|___|

https://aws.amazon.com/amazon-linux-ami/2017.03-release-notes/
[root@slave ~]# exit
logout
Connection to 172.31.9.182 closed.

MySQL安装

使用的是自己打包的RPM包，分别登录主库和从库，直接rpm -ivh percona-server-5.7.18-15.x86_64.rpm 即可，也可使用其他方式安装MySQL

[root@master ~]# mv /home/zhouting/percona-server-5.7.18-15.x86_64.rpm  /usr/src/
[root@master ~]# rpm -ivh /usr/src/percona-server-5.7.18-15.x86_64.rpm 
Preparing...                          ################################# [100%]
Updating / installing...
   1:percona-server-5.7.18-15         ################################# [100%]
error reading information on service /etc/rc.d/init.d/mysqld: No such file or directory
 ERROR! MySQL (Percona Server) PID file could not be found!
Starting MySQL (Percona Server).. SUCCESS! 
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
Warning: Since password will be sent to server in plain text, use ssl connection to ensure password safety.
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql: [Warning] Using a password on the command line interface can be insecure.

查看是否安装成功，分别登录主库和从库查看

[root@master ~]# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 5.7.18-15-log PLD/Linux Distribution Percona Server RPM

Copyright (c) 2009-2017 Percona LLC and/or its affiliates
Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MySQL [(none)] 16:57:43 > select version();
+---------------+
| version()     |
+---------------+
| 5.7.18-15-log |
+---------------+
1 row in set (0.00 sec)

MySQL [(none)] 16:57:47 >

MySQL互为主备

重要参数配置

主库必须包含以下参数

[root@master ~]# cat /etc/my.cnf 
[mysqld]
server-id = 1
autocommit = 1
auto_increment_increment = 1
auto_increment_offset = 2
log_bin = mysql-bin
gtid_mode = on
enforce_gtid_consistency = 1
log_slave_updates

从库必须包含以下参数，注意slave需要动态设置read_only=1，

[root@slave ~]# cat /etc/my.cnf 
[mysqld]
server-id = 2
autocommit = 1
auto_increment_increment = 2
auto_increment_offset = 2
log_bin = mysql-bin
gtid_mode = on
enforce_gtid_consistency = 1
log_slave_updates
relay_log_purge=0
[root@slave ~]# mysql -uroot -p -e "show variables like 'read_only'"
Enter password: 
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| read_only     | OFF   |
+---------------+-------+
[root@slave ~]# mysql -uroot -p -e "set global read_only=1"
Enter password: 
[root@slave ~]# mysql -uroot -p -e "show variables like 'read_only'"
Enter password: 
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| read_only     | ON    |
+---------------+-------+
[root@slave ~]#

复制帐号建立

由于MySQL版本使用的是5.7，创建用户的方法以之前有些不同

主库创建

[root@master ~]# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.7.18-15-log PLD/Linux Distribution Percona Server RPM

Copyright (c) 2009-2017 Percona LLC and/or its affiliates
Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MySQL [(none)] 17:16:52 > create user 'slave01'@'172.31.9.182' identified by 'slave123456';
Query OK, 0 rows affected (0.00 sec)

MySQL [(none)] 17:17:21 > grant replication slave,replication client on *.* to 'slave01'@'172.31.9.182';
Query OK, 0 rows affected (0.01 sec)

MySQL [(none)] 17:17:34 > flush privileges;
Query OK, 0 rows affected (0.00 sec)

MySQL [(none)] 17:17:38 > exit

从库创建

[root@slave ~]# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.7.18-15-log PLD/Linux Distribution Percona Server RPM

Copyright (c) 2009-2017 Percona LLC and/or its affiliates
Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MySQL [(none)] 17:19:24 > create user 'slave01'@'172.31.13.126' identified by 'slave123456';
Query OK, 0 rows affected (0.01 sec)

MySQL [(none)] 17:19:49 > grant replication slave,replication client on *.* to 'slave01'@'172.31.13.126';
Query OK, 0 rows affected (0.00 sec)

MySQL [(none)] 17:20:02 > flush privileges;
Query OK, 0 rows affected (0.00 sec)

MySQL [(none)] 17:20:05 > exit

复制帐号验证

主库登录从库

[root@master ~]# mysql -h172.31.9.182 -uslave01 -pslave123456
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.7.18-15-log PLD/Linux Distribution Percona Server RPM

Copyright (c) 2009-2017 Percona LLC and/or its affiliates
Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MySQL [(none)] 17:22:15 > exit

从库登录主库

[root@slave ~]# mysql -h172.31.13.126 -uslave01 -pslave123456             
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.7.18-15-log PLD/Linux Distribution Percona Server RPM

Copyright (c) 2009-2017 Percona LLC and/or its affiliates
Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MySQL [(none)] 17:22:57 > exit

复制关系配置

主库配置

登录主库，设置复制关系，来源于从库的复制

[root@master ~]# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 17
Server version: 5.7.18-15-log PLD/Linux Distribution Percona Server RPM

Copyright (c) 2009-2017 Percona LLC and/or its affiliates
Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MySQL [(none)] 17:32:19 > change master to master_host='172.31.9.182',master_user='slave01',master_password='slave123456',master_auto_position=1;
Query OK, 0 rows affected, 2 warnings (0.04 sec)

MySQL [(none)] 17:33:03 > start slave;
Query OK, 0 rows affected (0.03 sec)

MySQL [(none)] 17:33:47 > show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.31.9.182
                  Master_User: slave01
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000003
          Read_Master_Log_Pos: 810
               Relay_Log_File: master-relay-bin.000002
                Relay_Log_Pos: 1023
        Relay_Master_Log_File: mysql-bin.000003
             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: 810
              Relay_Log_Space: 1231
              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: 2
                  Master_UUID: a1c1d189-6b96-11e7-9825-02a13635a5ca
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: a1c1d189-6b96-11e7-9825-02a13635a5ca:1-3
            Executed_Gtid_Set: 9a7b78fc-6b96-11e7-9856-0232d9c5deea:1-17,
a1c1d189-6b96-11e7-9825-02a13635a5ca:1-3
                Auto_Position: 1
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
1 row in set (0.00 sec)

ERROR: 
No query specified

MySQL [(none)] 17:33:54 >

从库配置

登录从库，设置复制关系，来源于主库的复制

[root@slave ~]# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 5.7.18-15-log PLD/Linux Distribution Percona Server RPM

Copyright (c) 2009-2017 Percona LLC and/or its affiliates
Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MySQL [(none)] 17:34:41 > change master to master_host='172.31.13.126',master_user='slave01',master_password='slave123456',master_auto_position=1;
Query OK, 0 rows affected, 2 warnings (0.03 sec)

MySQL [(none)] 17:35:14 > start slave;
Query OK, 0 rows affected (0.03 sec)

MySQL [(none)] 17:35:15 > show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.31.0.139
                  Master_User: slave01
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000002
          Read_Master_Log_Pos: 4455
               Relay_Log_File: slave-relay-bin.000002
                Relay_Log_Pos: 4059
        Relay_Master_Log_File: mysql-bin.000002
             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: 4455
              Relay_Log_Space: 4266
              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: 1
                  Master_UUID: 9a7b78fc-6b96-11e7-9856-0232d9c5deea
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 9a7b78fc-6b96-11e7-9856-0232d9c5deea:1-17
            Executed_Gtid_Set: 9a7b78fc-6b96-11e7-9856-0232d9c5deea:1-17,
a1c1d189-6b96-11e7-9825-02a13635a5ca:1-3
                Auto_Position: 1
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
1 row in set (0.00 sec)

ERROR: 
No query specified

MySQL [(none)] 17:35:18 >

复制测试

主库登录测试

登录主库，插入测试数据

[root@master ~]# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 37
Server version: 5.7.18-15-log PLD/Linux Distribution Percona Server RPM

Copyright (c) 2009-2017 Percona LLC and/or its affiliates
Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MySQL [(none)] 17:35:48 > create database if not exists test11;use test11;                                                           
Query OK, 1 row affected (0.00 sec)

Database changed
MySQL [test11] 17:35:52 > create table if not exists t11(id int unsigned not null auto_increment,name varchar(20),primary key(`id`));
Query OK, 0 rows affected (0.02 sec)

MySQL [test11] 17:35:55 > insert into t11 values(null,'master11');
Query OK, 1 row affected (0.01 sec)

MySQL [test11] 17:35:58 > select * from t11;
+----+----------+
| id | name     |
+----+----------+
|  1 | master11 |
+----+----------+
1 row in set (0.00 sec)

MySQL [test11] 17:36:02 >

登录从库，查看测试数据，此时数据已经复制过来

[root@slave ~]# mysql -uroot -p 
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 29
Server version: 5.7.18-15-log PLD/Linux Distribution Percona Server RPM

Copyright (c) 2009-2017 Percona LLC and/or its affiliates
Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MySQL [(none)] 17:36:25 > select * from test11.t11;
+----+----------+
| id | name     |
+----+----------+
|  1 | master11 |
+----+----------+
1 row in set (0.00 sec)

MySQL [(none)] 17:36:30 >

从库登录测试

登录从库，插入测试数据

[root@slave ~]# mysql -uroot -p 
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 32
Server version: 5.7.18-15-log PLD/Linux Distribution Percona Server RPM

Copyright (c) 2009-2017 Percona LLC and/or its affiliates
Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MySQL [(none)] 17:37:59 > create database if not exists test11;use test11;
Query OK, 1 row affected, 1 warning (0.01 sec)

Database changed
MySQL [test11] 17:38:03 > create table if not exists t22(id int unsigned not null auto_increment,name varchar(20),primary key(`id`));     
Query OK, 0 rows affected (0.03 sec)

MySQL [test11] 17:38:18 > insert into t22 values(null,'slave11'); 
Query OK, 1 row affected (0.00 sec)

MySQL [test11] 17:38:30 > select * from t22;
+----+---------+
| id | name    |
+----+---------+
|  2 | slave11 |
+----+---------+
1 row in set (0.00 sec)

MySQL [test11] 17:38:35 >

登录主库，查看测试数据，此时数据已经复制过来

[root@master ~]# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 38
Server version: 5.7.18-15-log PLD/Linux Distribution Percona Server RPM

Copyright (c) 2009-2017 Percona LLC and/or its affiliates
Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MySQL [(none)] 17:39:09 > select * from test11.t22;
+----+---------+
| id | name    |
+----+---------+
|  2 | slave11 |
+----+---------+
1 row in set (0.00 sec)

MySQL [(none)] 17:39:15 >

MHA帐号建立

主库创建

登录主库，创建允许主库和从库连接的MHA用户信息，再分别尝试使用MHA用户登录(省略)

[root@master ~]# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 41
Server version: 5.7.18-15-log PLD/Linux Distribution Percona Server RPM

Copyright (c) 2009-2017 Percona LLC and/or its affiliates
Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MySQL [(none)] 09:36:55 > create user 'mha01'@'172.31.9.182' identified by 'mha123456';
Query OK, 0 rows affected (0.00 sec)

MySQL [(none)] 09:37:14 > grant all privileges on *.* to 'mha01'@'172.31.9.182';
Query OK, 0 rows affected (0.01 sec)

MySQL [(none)] 09:38:02 > create user 'mha01'@'172.31.13.126' identified by 'mha123456';
Query OK, 0 rows affected (0.00 sec)

MySQL [(none)] 09:38:14 > grant all privileges on *.* to 'mha01'@'172.31.13.126';
Query OK, 0 rows affected (0.01 sec)

MySQL [(none)] 09:38:18 > flush privileges;
Query OK, 0 rows affected (0.00 sec)

从库创建

登录从库，由于复制已经把在主库创建的MHA用户信息复制了过来，尝试登录即可(省略)

MHA安装

安装依赖

分别在主库和从库上安装，执行命令：
yum -y install gcc gcc-c++ make openssl-devel perl perl-DBD-MySQL perl-Config-Tiny perl-Log-Dispatch perl-Parallel-ForkManager perl-Config-IniFiles perl-Time-HiRes perl-Module-Install.noarch mailx jwhois

主库安装mha4mysql-node

MHA管理端安装在从库上，主库上只需要mha4mysql-node即可，由于MySQL使用5.7版本，MHA也使用最新的0.57版本，采用编译安装方式。下载地址：https://mega.nz/#F!G4oRjARB!SWzFS59bUv9VrKwdAeIGVw

[root@master ~]# cd /usr/src/
[root@master src]# tar -zxf mha4mysql-node-0.57.tar.gz
[root@master mha4mysql-node-0.57]# perl Makefile.PL             #编译过程省略
[root@master mha4mysql-node-0.57]# make
[root@master mha4mysql-node-0.57]# make install
[root@master mha4mysql-node-0.57]# ll /usr/local/bin/ -t        #查看安装，有以下文件则mha4mysql-node安装成功
total 1760
-r-xr-xr-x 1 root root  16381 Jul 21 09:41 apply_diff_relay_logs
-r-xr-xr-x 1 root root   8261 Jul 21 09:41 purge_relay_logs
-r-xr-xr-x 1 root root   7525 Jul 21 09:41 save_binary_logs
-r-xr-xr-x 1 root root   4807 Jul 21 09:41 filter_mysqlbinlog

从库安装mha4mysql-manager和mha4mysql-node

MHA管理端安装在从库，从库需要安装manager端和node端

[root@slave ~]# cd /usr/src/
[root@slave src]# tar -zxf mha4mysql-manager-0.57.tar.gz 
[root@slave src]# cd mha4mysql-manager-0.57
[root@slave mha4mysql-manager-0.57]# perl Makefile.PL           #编译过程省略
[root@slave mha4mysql-manager-0.57]# make
[root@slave mha4mysql-manager-0.57]# make install 
[root@slave mha4mysql-manager-0.57]# ll /usr/local/bin/ -t      #查看安装，有以下文件则mha4mysql-manager安装成功
total 1756
-r-xr-xr-x 1 root root   1779 Jul 21 09:48 masterha_check_ssh
-r-xr-xr-x 1 root root   2517 Jul 21 09:48 masterha_manager
-r-xr-xr-x 1 root root   2373 Jul 21 09:48 masterha_master_switch
-r-xr-xr-x 1 root root   5171 Jul 21 09:48 masterha_secondary_check
-r-xr-xr-x 1 root root   1995 Jul 21 09:48 masterha_check_repl
-r-xr-xr-x 1 root root   1865 Jul 21 09:48 masterha_check_status
-r-xr-xr-x 1 root root   3201 Jul 21 09:48 masterha_conf_host
-r-xr-xr-x 1 root root   2165 Jul 21 09:48 masterha_master_monitor
-r-xr-xr-x 1 root root   1739 Jul 21 09:48 masterha_stop
[root@slave mha4mysql-manager-0.57]# cd ..
[root@slave src]# tar -zxf mha4mysql-node-0.57.tar.gz 
[root@slave src]# cd mha4mysql-node-0.57
[root@slave mha4mysql-node-0.57]# perl Makefile.PL              #编译过程省略        
[root@slave mha4mysql-node-0.57]# make
[root@slave mha4mysql-node-0.57]# make install
[root@slave mha4mysql-node-0.57]# ll /usr/local/bin/ -t         #查看安装，有以下文件则mha4mysql-node安装成功
total 1800
-r-xr-xr-x 1 root root  16381 Jul 21 09:54 apply_diff_relay_logs
-r-xr-xr-x 1 root root   8261 Jul 21 09:54 purge_relay_logs
-r-xr-xr-x 1 root root   7525 Jul 21 09:54 save_binary_logs
-r-xr-xr-x 1 root root   4807 Jul 21 09:54 filter_mysqlbinlog

MHA目录结构说明

MHAManager

mhamanager工具包主要包括以下工具

[root@slave  ~]# ll /usr/local/bin/
总用量 84
-r-xr-xr-x  1 root root  1995 7月  19 11:49 masterha_check_repl         #检查MySQL复制情况
-r-xr-xr-x  1 root root  1779 7月  19 11:49 masterha_check_ssh          #检查MHA的SSH配置情况
-r-xr-xr-x  1 root root  1865 7月  19 11:49 masterha_check_status       #检测当前MHA运行状态
-r-xr-xr-x  1 root root  3201 7月  19 11:49 masterha_conf_host          #添加或删除配置的server信息
-r-xr-xr-x  1 root root  2517 7月  19 11:49 masterha_manager            #启动MHA
-r-xr-xr-x  1 root root  2165 7月  19 11:49 masterha_master_monitor     #检测Master是否宕机
-r-xr-xr-x  1 root root  2373 7月  19 11:49 masterha_master_switch      #控制故障转移，自动或者手动
-r-xr-xr-x  1 root root  5172 7月  19 11:49 masterha_secondary_check    #通过其他路由检测Master是否真的宕机
-r-xr-xr-x  1 root root  1739 7月  19 11:49 masterha_stop               #停止MHA

MHANode

mhanode工具包主要包括以下工具

[root@master  ~]# ll /usr/local/bin/
总用量 44
-r-xr-xr-x  1 root root 16371 7月  19 11:41 apply_diff_relay_logs       #识别差异日志的中继日志，并将其差异事件应用于其他Slave
-r-xr-xr-x  1 root root  4807 7月  19 11:41 filter_mysqlbinlog          #去除不必要的Rollback事件
-r-xr-xr-x  1 root root  8263 7月  19 11:41 purge_relay_logs            #删除无用的Relay log，避免延时
-r-xr-xr-x  1 root root  7525 7月  19 11:41 save_binary_logs            #保存和复制down掉的主服务器二进制日志

自定义扩展脚本说明

secondary_check_script #通过多条网络路由检测master的可用性
master_ip_failover_script #自动failover时候的切换脚本，可将vip信息写入此脚本中
shutdown_script #强制关闭master节点执行脚本
report_script #发送报告
init_conf_load_script #加载初始配置参数，如不想在配置中写明文密码
master_ip_online_change_script #手动failover时候的切换脚本

MHA扩展脚本

由于使用的AWS EC2，不支持VIP，可通过辅助IP的形式实现，这里使用脚本来辅助实现HA。

aws_vip_change.sh

[root@slave masterha]# cat /usr/local/bin/aws_vip_change.sh 
#!/bin/bash

if [ $# -lt 2 ]; then
        echo "Illegal param count.count = $#"
        exit 1
fi

# High Availability IP variables
# Other node's IP to ping and VIP to swap if other node goes down
VIP_ENI_ID=eni-7a5c7722

NEW_MASTER_IP=$1
OLD_MASTER_IP=$2

echo "NEW_MASTER_IP=${NEW_MASTER_IP},OLD_MASTER_IP=${OLD_MASTER_IP}"

# Specify the EC2 region that this will be running in   #以下为当前用户能够执行aws cli命令的key信息
REGION=cn-north-1
AWSAccessKeyId=AKIAPUXGZV6F6EKCOK7Q
AWSSecretKey=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       

# Run aws-apitools-common.sh to set up default environment variables and to
# leverage AWS security credentials provided by EC2 roles
. /etc/profile.d/aws-apitools-common.sh

ACCESS_USER_OPTION="--aws-access-key ${AWSAccessKeyId} --aws-secret-key ${AWSSecretKey}"

ssh -t -t root@${OLD_MASTER_IP} /sbin/ifconfig eth4 down    #其中eth4网卡名要与下文中，新申请ENI并绑定实例ID对应的网卡名相同

VIP_ATTACHMENT_ID=`ec2-describe-network-interface-attribute ${VIP_ENI_ID} ${ACCESS_USER_OPTION} --region ${REGION} -a | grep ATTACHMENT -m 1 | awk '{print $3;}'`
echo "VIP_ATTACHMENT_ID=${VIP_ATTACHMENT_ID}"

/opt/aws/bin/ec2-detach-network-interface ${VIP_ATTACHMENT_ID} ${ACCESS_USER_OPTION} --force --region ${REGION}
sleep 10

INSTANCE_ID=`ssh root@${NEW_MASTER_IP} /usr/bin/curl --silent http://169.254.169.254/latest/meta-data/instance-id`  #其中169.254.169.254从本地链路中获取实例ID
echo "INSTANCE_ID=${INSTANCE_ID}"

/opt/aws/bin/ec2-attach-network-interface ${VIP_ENI_ID} -i ${INSTANCE_ID} -d 1 ${ACCESS_USER_OPTION} --region ${REGION}
[root@slave masterha]#

master_ip_failover

[root@slave masterha]# cat /usr/local/bin/master_ip_failover 
#!/usr/bin/env perl

#  Copyright (C) 2011 DeNA Co.,Ltd.
#
#  This program is free software; you can redistribute it and/or modify
#  it under the terms of the GNU General Public License as published by
#  the Free Software Foundation; either version 2 of the License, or
#  (at your option) any later version.
#
#  This program is distributed in the hope that it will be useful,
#  but WITHOUT ANY WARRANTY; without even the implied warranty of
#  MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
#  GNU General Public License for more details.
#
#  You should have received a copy of the GNU General Public License
#   along with this program; if not, write to the Free Software
#  Foundation, Inc.,
#  51 Franklin Street, Fifth Floor, Boston, MA  02110-1301  USA

## Note: This is a sample script and is not complete. Modify the script based on your environment.

use strict;
use warnings FATAL => 'all';

use Getopt::Long;
use MHA::DBHelper;

my (
  $command,        $ssh_user,         $orig_master_host,
  $orig_master_ip, $orig_master_port, $new_master_host,
  $new_master_ip,  $new_master_port,  $new_master_user,
  $new_master_password
);
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,
  'new_master_user=s'     => \$new_master_user,
  'new_master_password=s' => \$new_master_password,
);

exit &main();

sub main {
  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 {

      # updating global catalog, etc
      $exit_code = 0;
    };
    if ($@) {
      warn "Got Error: $@\n";
      exit $exit_code;
    }
    exit $exit_code;
  }
  elsif ( $command eq "start" ) {
    my $exit_code = 10;
    my @vip_change_cmd = ("/usr/local/bin/aws_vip_change.sh",$new_master_host,$orig_master_host);
    system @vip_change_cmd;
    $exit_code = 0;
    if ($@) {
      warn $@;

      # If you want to continue failover, exit 10.
      exit $exit_code;
    }
    exit $exit_code;
  }
  elsif ( $command eq "status" ) {

    # do nothing
    exit 0;
  }
  else {
    &usage();
    exit 1;
  }
}

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\n";
}
[root@slave masterha]#

send_report

[root@slave masterha]# cat /usr/local/bin/send_report 
#!/usr/bin/perl
 
use strict;
use warnings FATAL => 'all';
use Mail::Sender;
use Getopt::Long;
 
#new_master_host and new_slave_hosts are set only when recovering master succeeded
my ( $dead_master_host, $new_master_host, $new_slave_hosts, $subject, $body );
my $smtp='smtp.126.com';
my $mail_from='xxx@126.com';
my $mail_user='xxx@126.com';
my $mail_pass='xxx';
#my $mail_to=['xxx','xxx'];
my $mail_to='xxx';
GetOptions(
  'orig_master_host=s' => \$dead_master_host,
  'new_master_host=s'  => \$new_master_host,
  'new_slave_hosts=s'  => \$new_slave_hosts,
  'subject=s'          => \$subject,
  'body=s'             => \$body,
);
 
mailToContacts($smtp,$mail_from,$mail_user,$mail_pass,$mail_to,$subject,$body);
 
sub mailToContacts {
    my ( $smtp, $mail_from, $user, $passwd, $mail_to, $subject, $msg ) = @_;
    open my $DEBUG, "> /var/log/masterha/app1/manager.log"
        or die "Can't open the debug      file:$!\n";
    my $sender = new Mail::Sender {
        ctype       => 'text/plain; charset=utf-8',
        encoding    => 'utf-8',
        smtp        => $smtp,
        from        => $mail_from,
        auth        => 'LOGIN',
        TLS_allowed => '0',
        authid      => $user,
        authpwd     => $passwd,
        to          => $mail_to,
        subject     => $subject,
        debug       => $DEBUG
    };
 
    $sender->MailMsg(
        {   msg   => $msg,
            debug => $DEBUG
        }
    ) or print $Mail::Sender::Error;
    return 1;
}
 
# Do whatever you want here
 
exit 0;

主库手动绑定ENI和实例关系

在主库上先申请新的ENI，注意申请时先绑定的group，以满足其他EC2能够连接些ENI。
申请成功后，根据新的ENI和当前主实例的实例ID绑定，最终确认新的VIP是否绑定上。


[root@master ~]# ifconfig
eth0      Link encap:Ethernet  HWaddr 02:E6:0B:68:5C:1C  
          inet addr:172.31.13.126  Bcast:172.31.15.255  Mask:255.255.240.0
          inet6 addr: fe80::e6:bff:fe68:5c1c/64 Scope:Link
          UP BROADCAST RUNNING MULTICAST  MTU:9001  Metric:1
          RX packets:4063 errors:0 dropped:0 overruns:0 frame:0
          TX packets:3292 errors:0 dropped:0 overruns:0 carrier:0
          collisions:0 txqueuelen:1000 
          RX bytes:691839 (675.6 KiB)  TX bytes:390499 (381.3 KiB)

lo        Link encap:Local Loopback  
          inet addr:127.0.0.1  Mask:255.0.0.0
          inet6 addr: ::1/128 Scope:Host
          UP LOOPBACK RUNNING  MTU:65536  Metric:1
          RX packets:79 errors:0 dropped:0 overruns:0 frame:0
          TX packets:79 errors:0 dropped:0 overruns:0 carrier:0
          collisions:0 txqueuelen:1 
          RX bytes:16736 (16.3 KiB)  TX bytes:16736 (16.3 KiB)

[root@master ~]# aws ec2 create-network-interface --subnet-id subnet-33859151 --private-ip-address 172.31.0.200 --groups sg-e50a1c87 sg-6b1f0809
{
    "NetworkInterface": {
        "Status": "pending", 
        "MacAddress": "02:12:25:83:a0:22", 
        "SourceDestCheck": true, 
        "VpcId": "vpc-b18195d3", 
        "Description": "", 
        "NetworkInterfaceId": "eni-7a5c7722",   #新申请的ENI名字
        "PrivateIpAddresses": [
            {
                "PrivateDnsName": "ip-172-31-0-200.cn-north-1.compute.internal", 
                "Primary": true, 
                "PrivateIpAddress": "172.31.0.200"  #新ENI绑定的私有IP
            }
        ], 
        "RequesterManaged": false, 
        "Groups": [
            {
                "GroupName": "For Mysql", 
                "GroupId": "sg-e50a1c87"
            }, 
            {
                "GroupName": "OnlySSH-Allow", 
                "GroupId": "sg-6b1f0809"
            }
        ], 
        "PrivateDnsName": "ip-172-31-0-200.cn-north-1.compute.internal", 
        "AvailabilityZone": "cn-north-1a", 
        "RequesterId": "AIDAPVDKK6NLF6SZ553KS", 
        "SubnetId": "subnet-33859151", 
        "OwnerId": "981100955930", 
        "TagSet": [], 
        "PrivateIpAddress": "172.31.0.200"
    }
}
[root@master ~]# aws ec2 attach-network-interface --network-interface-id eni-7a5c7722 --instance-id i-0a259e4950b0b3cf9 --device-index 1    #将ENI和实例绑定
{
    "AttachmentId": "eni-attach-2b2c9345"
}
}
[root@master ~]# ifconfig   #查看ip：172.31.0.200已绑定成功，同时要在其他EC2确定此安全组是否能够满足需求，否则还需要重新调整。
eth0      Link encap:Ethernet  HWaddr 02:E6:0B:68:5C:1C  
          inet addr:172.31.13.126  Bcast:172.31.15.255  Mask:255.255.240.0
          inet6 addr: fe80::e6:bff:fe68:5c1c/64 Scope:Link
          UP BROADCAST RUNNING MULTICAST  MTU:9001  Metric:1
          RX packets:5435 errors:0 dropped:0 overruns:0 frame:0
          TX packets:4190 errors:0 dropped:0 overruns:0 carrier:0
          collisions:0 txqueuelen:1000 
          RX bytes:810999 (791.9 KiB)  TX bytes:501986 (490.2 KiB)

eth4      Link encap:Ethernet  HWaddr 02:12:25:83:A0:22  
          inet addr:172.31.0.200  Bcast:172.31.15.255  Mask:255.255.240.0
          inet6 addr: fe80::12:25ff:fe83:a022/64 Scope:Link
          UP BROADCAST RUNNING MULTICAST  MTU:9001  Metric:1
          RX packets:11 errors:0 dropped:0 overruns:0 frame:0
          TX packets:26 errors:0 dropped:0 overruns:0 carrier:0
          collisions:0 txqueuelen:1000 
          RX bytes:1218 (1.1 KiB)  TX bytes:2664 (2.6 KiB)

lo        Link encap:Local Loopback  
          inet addr:127.0.0.1  Mask:255.0.0.0
          inet6 addr: ::1/128 Scope:Host
          UP LOOPBACK RUNNING  MTU:65536  Metric:1
          RX packets:79 errors:0 dropped:0 overruns:0 frame:0
          TX packets:79 errors:0 dropped:0 overruns:0 carrier:0
          collisions:0 txqueuelen:1 
          RX bytes:16736 (16.3 KiB)  TX bytes:16736 (16.3 KiB)

[root@master ~]#

从库配置MHAManager

创建配置目录/etc/masterha/，同时创建一个项目上的配置文件，仅配置自动FailOver部分，只需要master_ip_failover和report_script脚本，其他脚本暂时不定义。

[root@slave ~]# mkdir -p /etc/masterha/
[root@slave ~]# cd /etc/masterha/
[root@slave masterha]# 
[root@slave masterha]# cat app1.cnf 
[server default]
manager_workdir=/var/log/masterha/app1                              #manager工作目录
manager_log=/var/log/masterha/app1/manager.log                      #manager日志
master_binlog_dir=/hwdata/data/percona                              #mysql数据目录
password=mha123456                                                  #mha连接密码
user=mha01                                                          #mha连接用户
ping_interval=1                                                     #监控主库，发送ping包的时间间隔，默认是3秒，尝试三次没有回应的时候自动进行failover
remote_workdir=/tmp                                                 #远端mysql在发生切换时binlog的保存位置
repl_password=slave123456                                           #复制密码
repl_user=slave01                                                   #复制用户
ssh_user=root                                                       #ssh用户
master_ip_failover_script=/usr/local/bin/master_ip_failover         #自动failover切换脚本
#master_ip_online_change_script= /usr/local/bin/master_ip_online_change      #手动failover切换脚本
report_script=/usr/local/bin/send_report                            #报告发送脚本(不是必须)
#shutdown_script=                                                   #故障发生后关闭主机的脚本(不是必须)
secondary_check_script = masterha_secondary_check -s 172.31.13.126 -s 172.31.9.182 --user=root hostname=172.31.13.126  --master_ip=172.31.13.126 --master_port=3306 #通过第三方机器确认目标主库是否存活，这里的ip可换成其他机器，用于检测

[server1]
hostname=172.31.13.126                                              #主库地址
port=3306

[server2]
hostname=172.31.9.182                                               #从库地址
port=3306
candidate_master=1                                                  #候选master

测试MHAManager SSH

[root@slave masterha]# masterha_check_ssh --conf=/etc/masterha/app1.cnf 
Mon Jul 24 13:58:04 2017 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Mon Jul 24 13:58:04 2017 - [info] Reading application default configuration from /etc/masterha/app1.cnf..
Mon Jul 24 13:58:04 2017 - [info] Reading server configuration from /etc/masterha/app1.cnf..
Mon Jul 24 13:58:04 2017 - [info] Starting SSH connection tests..
Mon Jul 24 13:58:04 2017 - [debug] 
Mon Jul 24 13:58:04 2017 - [debug]  Connecting via SSH from root@172.31.13.126(172.31.13.126:22) to root@172.31.9.182(172.31.9.182:22)..
Mon Jul 24 13:58:04 2017 - [debug]   ok.
Mon Jul 24 13:58:05 2017 - [debug] 
Mon Jul 24 13:58:04 2017 - [debug]  Connecting via SSH from root@172.31.9.182(172.31.9.182:22) to root@172.31.13.126(172.31.13.126:22)..
Mon Jul 24 13:58:04 2017 - [debug]   ok.
Mon Jul 24 13:58:05 2017 - [info] All SSH connection tests passed successfully.
[root@slave masterha]#

测试MHAManager 复制

[root@slave masterha]# masterha_check_repl --conf=/etc/masterha/app1.cnf 
Mon Jul 24 13:58:17 2017 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Mon Jul 24 13:58:17 2017 - [info] Reading application default configuration from /etc/masterha/app1.cnf..
Mon Jul 24 13:58:17 2017 - [info] Reading server configuration from /etc/masterha/app1.cnf..
Mon Jul 24 13:58:17 2017 - [info] MHA::MasterMonitor version 0.57.
Mon Jul 24 13:58:18 2017 - [info] Multi-master configuration is detected. Current primary(writable) master is 172.31.13.126(172.31.13.126:3306)
Mon Jul 24 13:58:18 2017 - [info] Master configurations are as below: 
Master 172.31.13.126(172.31.13.126:3306), replicating from 172.31.9.182(172.31.9.182:3306)
Master 172.31.9.182(172.31.9.182:3306), replicating from 172.31.13.126(172.31.13.126:3306), read-only

Mon Jul 24 13:58:18 2017 - [info] GTID failover mode = 1
Mon Jul 24 13:58:18 2017 - [info] Dead Servers:
Mon Jul 24 13:58:18 2017 - [info] Alive Servers:
Mon Jul 24 13:58:18 2017 - [info]   172.31.13.126(172.31.13.126:3306)
Mon Jul 24 13:58:18 2017 - [info]   172.31.9.182(172.31.9.182:3306)
Mon Jul 24 13:58:18 2017 - [info] Alive Slaves:
Mon Jul 24 13:58:18 2017 - [info]   172.31.9.182(172.31.9.182:3306)  Version=5.7.18-15-log (oldest major version between slaves) log-bin:enabled
Mon Jul 24 13:58:18 2017 - [info]     GTID ON
Mon Jul 24 13:58:18 2017 - [info]     Replicating from 172.31.13.126(172.31.13.126:3306)
Mon Jul 24 13:58:18 2017 - [info]     Primary candidate for the new Master (candidate_master is set)
Mon Jul 24 13:58:18 2017 - [info] Current Alive Master: 172.31.13.126(172.31.13.126:3306)
Mon Jul 24 13:58:18 2017 - [info] Checking slave configurations..
Mon Jul 24 13:58:18 2017 - [info] Checking replication filtering settings..
Mon Jul 24 13:58:18 2017 - [info]  binlog_do_db= , binlog_ignore_db= 
Mon Jul 24 13:58:18 2017 - [info]  Replication filtering check ok.
Mon Jul 24 13:58:18 2017 - [info] GTID (with auto-pos) is supported. Skipping all SSH and Node package checking.
Mon Jul 24 13:58:18 2017 - [info] Checking SSH publickey authentication settings on the current master..
Mon Jul 24 13:58:18 2017 - [info] HealthCheck: SSH to 172.31.13.126 is reachable.
Mon Jul 24 13:58:18 2017 - [info] 
172.31.13.126(172.31.13.126:3306) (current master)
 +--172.31.9.182(172.31.9.182:3306)

Mon Jul 24 13:58:18 2017 - [info] Checking replication health on 172.31.9.182..
Mon Jul 24 13:58:18 2017 - [info]  ok.
Mon Jul 24 13:58:18 2017 - [info] Checking master_ip_failover_script status:
Mon Jul 24 13:58:18 2017 - [info]   /usr/local/bin/master_ip_failover --command=status --ssh_user=root --orig_master_host=172.31.13.126 --orig_master_ip=172.31.13.126 --orig_master_port=3306 
Mon Jul 24 13:58:18 2017 - [info]  OK.
Mon Jul 24 13:58:18 2017 - [warning] shutdown_script is not defined.
Mon Jul 24 13:58:18 2017 - [info] Got exit code 0 (Not master dead).

MySQL Replication Health is OK.
[root@slave masterha]#

启动MHAManager

启动进程

[root@slave masterha]# nohup masterha_manager --conf=/etc/masterha/app1.cnf &
[1] 4439
[root@slave masterha]# nohup: ignoring input and appending output to ‘nohup.out’

[root@slave masterha]#

查看启动日志

[root@slave ~]# cat /var/log/masterha/app1/manager.log 
Mon Jul 24 14:04:10 2017 - [info] MHA::MasterMonitor version 0.57.  #检查版本
Mon Jul 24 14:04:11 2017 - [info] Multi-master configuration is detected. Current primary(writable) master is 172.31.13.126(172.31.13.126:3306) #获取当前主节点ip
Mon Jul 24 14:04:11 2017 - [info] Master configurations are as below:   #获取复制结构
Master 172.31.13.126(172.31.13.126:3306), replicating from 172.31.9.182(172.31.9.182:3306)
Master 172.31.9.182(172.31.9.182:3306), replicating from 172.31.13.126(172.31.13.126:3306), read-only

Mon Jul 24 14:04:11 2017 - [info] GTID failover mode = 1    #GTID模式
Mon Jul 24 14:04:11 2017 - [info] Dead Servers:
Mon Jul 24 14:04:11 2017 - [info] Alive Servers:            #当前在线实例列表
Mon Jul 24 14:04:11 2017 - [info]   172.31.13.126(172.31.13.126:3306)
Mon Jul 24 14:04:11 2017 - [info]   172.31.9.182(172.31.9.182:3306)
Mon Jul 24 14:04:11 2017 - [info] Alive Slaves:             #当前在线slave列表及版本信息
Mon Jul 24 14:04:11 2017 - [info]   172.31.9.182(172.31.9.182:3306)  Version=5.7.18-15-log (oldest major version between slaves) log-bin:enabled
Mon Jul 24 14:04:11 2017 - [info]     GTID ON
Mon Jul 24 14:04:11 2017 - [info]     Replicating from 172.31.13.126(172.31.13.126:3306)
Mon Jul 24 14:04:11 2017 - [info]     Primary candidate for the new Master (candidate_master is set)
Mon Jul 24 14:04:11 2017 - [info] Current Alive Master: 172.31.13.126(172.31.13.126:3306)   #当前主实例信息
Mon Jul 24 14:04:11 2017 - [info] Checking slave configurations..
Mon Jul 24 14:04:11 2017 - [info] Checking replication filtering settings..
Mon Jul 24 14:04:11 2017 - [info]  binlog_do_db= , binlog_ignore_db=                        #复制过滤
Mon Jul 24 14:04:11 2017 - [info]  Replication filtering check ok.
Mon Jul 24 14:04:11 2017 - [info] GTID (with auto-pos) is supported. Skipping all SSH and Node package checking.
Mon Jul 24 14:04:11 2017 - [info] Checking SSH publickey authentication settings on the current master..
Mon Jul 24 14:04:11 2017 - [info] HealthCheck: SSH to 172.31.13.126 is reachable.           #ssh可用
Mon Jul 24 14:04:11 2017 - [info] 
172.31.13.126(172.31.13.126:3306) (current master)                                          #再次确认当前复制架构
 +--172.31.9.182(172.31.9.182:3306)

Mon Jul 24 14:04:11 2017 - [info] Checking master_ip_failover_script status:
Mon Jul 24 14:04:11 2017 - [info]   /usr/local/bin/master_ip_failover --command=status --ssh_user=root --orig_master_host=172.31.13.126 --orig_master_ip=172.31.13.126 --orig_master_port=3306 
Mon Jul 24 14:04:11 2017 - [info]  OK.
Mon Jul 24 14:04:11 2017 - [warning] shutdown_script is not defined.                        #shutdown_script脚本未定义    
Mon Jul 24 14:04:11 2017 - [info] Set master ping interval 1 seconds.
Mon Jul 24 14:04:11 2017 - [info] Set secondary check script: masterha_secondary_check -s 172.31.13.126 -s 172.31.9.182 --user=root hostname=172.31.13.126  --master_ip=172.31.13.126 --master_port=3306                                                #第三方检查登录
Mon Jul 24 14:04:11 2017 - [info] Starting ping health check on 172.31.13.126(172.31.13.126:3306)..
Mon Jul 24 14:04:11 2017 - [info] Ping(SELECT) succeeded, waiting until MySQL doesn't respond.. #启动成功，等待failover
[root@slave masterha]# ps aux|grep master
root      4439  0.0  0.2 287004 23636 pts/0    S    14:04   0:00 perl /usr/local/bin/masterha_manager --conf=/etc/masterha/app1.cnf #进程存在
root      5008  0.0  0.0 110456  2092 pts/0    S+   14:13   0:00 grep --color=auto master
[root@slave masterha]#

模拟主库故障

确认当前VIP状态

在主库上执行停止服务，观察MHAManager日志，并确认VIP是否切换到从库上

[root@master ~]# ifconfig       #确认当前VIP：172.31.0.200在主库上
eth0      Link encap:Ethernet  HWaddr 02:E6:0B:68:5C:1C  
          inet addr:172.31.13.126  Bcast:172.31.15.255  Mask:255.255.240.0
          inet6 addr: fe80::e6:bff:fe68:5c1c/64 Scope:Link
          UP BROADCAST RUNNING MULTICAST  MTU:9001  Metric:1
          RX packets:9629 errors:0 dropped:0 overruns:0 frame:0
          TX packets:8119 errors:0 dropped:0 overruns:0 carrier:0
          collisions:0 txqueuelen:1000 
          RX bytes:1202679 (1.1 MiB)  TX bytes:1113439 (1.0 MiB)

eth4      Link encap:Ethernet  HWaddr 02:12:25:83:A0:22  
          inet addr:172.31.0.200  Bcast:172.31.15.255  Mask:255.255.240.0
          inet6 addr: fe80::12:25ff:fe83:a022/64 Scope:Link
          UP BROADCAST RUNNING MULTICAST  MTU:9001  Metric:1
          RX packets:4 errors:0 dropped:0 overruns:0 frame:0
          TX packets:4 errors:0 dropped:0 overruns:0 carrier:0
          collisions:0 txqueuelen:1000 
          RX bytes:562 (562.0 b)  TX bytes:600 (600.0 b)

lo        Link encap:Local Loopback  
          inet addr:127.0.0.1  Mask:255.0.0.0
          inet6 addr: ::1/128 Scope:Host
          UP LOOPBACK RUNNING  MTU:65536  Metric:1
          RX packets:90 errors:0 dropped:0 overruns:0 frame:0
          TX packets:90 errors:0 dropped:0 overruns:0 carrier:0
          collisions:0 txqueuelen:1 
          RX bytes:17324 (16.9 KiB)  TX bytes:17324 (16.9 KiB)

[root@master ~]#

确认MHAManger进程状态

[root@slave masterha]# ps aux|grep master
root     11333  0.4  0.2 287004 23604 pts/0    S    15:36   0:00 perl /usr/local/bin/masterha_manager --conf=/etc/masterha/app1.cnf
root     11377  0.0  0.0 110456  2252 pts/0    S+   15:37   0:00 grep --color=auto master
[root@slave masterha]# cd /var/log/masterha/app1/
[root@slave app1]# ll
total 8
-rw-r--r-- 1 root root   36 Jul 24 15:37 app1.master_status.health
-rw-r--r-- 1 root root 2833 Jul 24 15:36 manager.log
[root@slave app1]# cat manager.log |tail -2
Mon Jul 24 15:36:54 2017 - [info] Starting ping health check on 172.31.13.126(172.31.13.126:3306)..
Mon Jul 24 15:36:54 2017 - [info] Ping(SELECT) succeeded, waiting until MySQL doesn't respond..     #正在等待主库FailOver
[root@slave app1]# ifconfig     #当前从库没有存在VIP：172.31.0.200
eth0      Link encap:Ethernet  HWaddr 02:0E:FB:F1:E9:4E  
          inet addr:172.31.9.182  Bcast:172.31.15.255  Mask:255.255.240.0
          inet6 addr: fe80::e:fbff:fef1:e94e/64 Scope:Link
          UP BROADCAST RUNNING MULTICAST  MTU:9001  Metric:1
          RX packets:16565 errors:0 dropped:0 overruns:0 frame:0
          TX packets:17269 errors:0 dropped:0 overruns:0 carrier:0
          collisions:0 txqueuelen:1000 
          RX bytes:2084631 (1.9 MiB)  TX bytes:2304176 (2.1 MiB)

lo        Link encap:Local Loopback  
          inet addr:127.0.0.1  Mask:255.0.0.0
          inet6 addr: ::1/128 Scope:Host
          UP LOOPBACK RUNNING  MTU:65536  Metric:1
          RX packets:2556 errors:0 dropped:0 overruns:0 frame:0
          TX packets:2556 errors:0 dropped:0 overruns:0 carrier:0
          collisions:0 txqueuelen:1 
          RX bytes:605471 (591.2 KiB)  TX bytes:605471 (591.2 KiB)

[root@slave app1]#

停止主库服务，触发主库FailOver

[root@master ~]# /etc/init.d/mysqld stop                    #服务停止
Shutting down MySQL (Percona Server)............ SUCCESS! 
[root@master ~]# ifconfig                                   #VIP已经不存在
eth0      Link encap:Ethernet  HWaddr 02:E6:0B:68:5C:1C  
          inet addr:172.31.13.126  Bcast:172.31.15.255  Mask:255.255.240.0
          inet6 addr: fe80::e6:bff:fe68:5c1c/64 Scope:Link
          UP BROADCAST RUNNING MULTICAST  MTU:9001  Metric:1
          RX packets:10242 errors:0 dropped:0 overruns:0 frame:0
          TX packets:8535 errors:0 dropped:0 overruns:0 carrier:0
          collisions:0 txqueuelen:1000 
          RX bytes:1256340 (1.1 MiB)  TX bytes:1175340 (1.1 MiB)

lo        Link encap:Local Loopback  
          inet addr:127.0.0.1  Mask:255.0.0.0
          inet6 addr: ::1/128 Scope:Host
          UP LOOPBACK RUNNING  MTU:65536  Metric:1
          RX packets:92 errors:0 dropped:0 overruns:0 frame:0
          TX packets:92 errors:0 dropped:0 overruns:0 carrier:0
          collisions:0 txqueuelen:1 
          RX bytes:17424 (17.0 KiB)  TX bytes:17424 (17.0 KiB)

[root@master ~]#

观察FailOver日志

登录从库，查看MHAManager日志

[root@slave app1]# cat manager.log 
Mon Jul 24 15:36:52 2017 - [info] MHA::MasterMonitor version 0.57.
Mon Jul 24 15:36:54 2017 - [info] Multi-master configuration is detected. Current primary(writable) master is 172.31.13.126(172.31.13.126:3306)
Mon Jul 24 15:36:54 2017 - [info] Master configurations are as below: 
Master 172.31.13.126(172.31.13.126:3306), replicating from 172.31.9.182(172.31.9.182:3306)
Master 172.31.9.182(172.31.9.182:3306), replicating from 172.31.13.126(172.31.13.126:3306), read-only

Mon Jul 24 15:36:54 2017 - [info] GTID failover mode = 1
Mon Jul 24 15:36:54 2017 - [info] Dead Servers:
Mon Jul 24 15:36:54 2017 - [info] Alive Servers:
Mon Jul 24 15:36:54 2017 - [info]   172.31.13.126(172.31.13.126:3306)
Mon Jul 24 15:36:54 2017 - [info]   172.31.9.182(172.31.9.182:3306)
Mon Jul 24 15:36:54 2017 - [info] Alive Slaves:
Mon Jul 24 15:36:54 2017 - [info]   172.31.9.182(172.31.9.182:3306)  Version=5.7.18-15-log (oldest major version between slaves) log-bin:enabled
Mon Jul 24 15:36:54 2017 - [info]     GTID ON
Mon Jul 24 15:36:54 2017 - [info]     Replicating from 172.31.13.126(172.31.13.126:3306)
Mon Jul 24 15:36:54 2017 - [info]     Primary candidate for the new Master (candidate_master is set)
Mon Jul 24 15:36:54 2017 - [info] Current Alive Master: 172.31.13.126(172.31.13.126:3306)
Mon Jul 24 15:36:54 2017 - [info] Checking slave configurations..
Mon Jul 24 15:36:54 2017 - [info] Checking replication filtering settings..
Mon Jul 24 15:36:54 2017 - [info]  binlog_do_db= , binlog_ignore_db= 
Mon Jul 24 15:36:54 2017 - [info]  Replication filtering check ok.
Mon Jul 24 15:36:54 2017 - [info] GTID (with auto-pos) is supported. Skipping all SSH and Node package checking.
Mon Jul 24 15:36:54 2017 - [info] Checking SSH publickey authentication settings on the current master..
Mon Jul 24 15:36:54 2017 - [info] HealthCheck: SSH to 172.31.13.126 is reachable.
Mon Jul 24 15:36:54 2017 - [info] 
172.31.13.126(172.31.13.126:3306) (current master)
 +--172.31.9.182(172.31.9.182:3306)

Mon Jul 24 15:36:54 2017 - [info] Checking master_ip_failover_script status:
Mon Jul 24 15:36:54 2017 - [info]   /usr/local/bin/master_ip_failover --command=status --ssh_user=root --orig_master_host=172.31.13.126 --orig_master_ip=172.31.13.126 --orig_master_port=3306 
Mon Jul 24 15:36:54 2017 - [info]  OK.
Mon Jul 24 15:36:54 2017 - [warning] shutdown_script is not defined.
Mon Jul 24 15:36:54 2017 - [info] Set master ping interval 1 seconds.
Mon Jul 24 15:36:54 2017 - [info] Set secondary check script: masterha_secondary_check -s 172.31.13.126 -s 172.31.9.182 --user=root hostname=172.31.13.126  --master_ip=172.31.13.126 --master_port=3306
Mon Jul 24 15:36:54 2017 - [info] Starting ping health check on 172.31.13.126(172.31.13.126:3306)..
Mon Jul 24 15:36:54 2017 - [info] Ping(SELECT) succeeded, waiting until MySQL doesn't respond..         #等待主库FailOver
Mon Jul 24 15:40:01 2017 - [warning] Got error on MySQL select ping: 2006 (MySQL server has gone away)
Mon Jul 24 15:40:01 2017 - [info] Executing secondary network check script: masterha_secondary_check -s 172.31.13.126 -s 172.31.9.182 --user=root hostname=172.31.13.126  --master_ip=172.31.13.126 --master_port=3306  --user=root  --master_host=172.31.13.126  --master_ip=172.31.13.126  --master_port=3306 --master_user=mha01 --master_password=mha123456 --ping_type=SELECT
Mon Jul 24 15:40:01 2017 - [info] Executing SSH check script: exit 0
Mon Jul 24 15:40:01 2017 - [info] HealthCheck: SSH to 172.31.13.126 is reachable.
Monitoring server 172.31.13.126 is reachable, Master is not reachable from 172.31.13.126. OK.
Monitoring server 172.31.9.182 is reachable, Master is not reachable from 172.31.9.182. OK.
Mon Jul 24 15:40:01 2017 - [info] Master is not reachable from all other monitoring servers. Failover should start.
Mon Jul 24 15:40:02 2017 - [warning] Got error on MySQL connect: 2003 (Can't connect to MySQL server on '172.31.13.126' (111))
Mon Jul 24 15:40:02 2017 - [warning] Connection failed 2 time(s)..
Mon Jul 24 15:40:03 2017 - [warning] Got error on MySQL connect: 2003 (Can't connect to MySQL server on '172.31.13.126' (111))
Mon Jul 24 15:40:03 2017 - [warning] Connection failed 3 time(s)..
Mon Jul 24 15:40:04 2017 - [warning] Got error on MySQL connect: 2003 (Can't connect to MySQL server on '172.31.13.126' (111))
Mon Jul 24 15:40:04 2017 - [warning] Connection failed 4 time(s)..
Mon Jul 24 15:40:04 2017 - [warning] Master is not reachable from health checker!
Mon Jul 24 15:40:04 2017 - [warning] Master 172.31.13.126(172.31.13.126:3306) is not reachable!
Mon Jul 24 15:40:04 2017 - [warning] SSH is reachable.
Mon Jul 24 15:40:04 2017 - [info] Connecting to a master server failed. Reading configuration file /etc/masterha_default.cnf and /etc/masterha/app1.cnf again, and trying to connect to all servers to check server status..
Mon Jul 24 15:40:04 2017 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Mon Jul 24 15:40:04 2017 - [info] Reading application default configuration from /etc/masterha/app1.cnf..
Mon Jul 24 15:40:04 2017 - [info] Reading server configuration from /etc/masterha/app1.cnf..
Mon Jul 24 15:40:05 2017 - [info] GTID failover mode = 1
Mon Jul 24 15:40:05 2017 - [info] Dead Servers:
Mon Jul 24 15:40:05 2017 - [info]   172.31.13.126(172.31.13.126:3306)
Mon Jul 24 15:40:05 2017 - [info] Alive Servers:
Mon Jul 24 15:40:05 2017 - [info]   172.31.9.182(172.31.9.182:3306)
Mon Jul 24 15:40:05 2017 - [info] Alive Slaves:
Mon Jul 24 15:40:05 2017 - [info]   172.31.9.182(172.31.9.182:3306)  Version=5.7.18-15-log (oldest major version between slaves) log-bin:enabled
Mon Jul 24 15:40:05 2017 - [info]     GTID ON
Mon Jul 24 15:40:05 2017 - [info]     Replicating from 172.31.13.126(172.31.13.126:3306)
Mon Jul 24 15:40:05 2017 - [info]     Primary candidate for the new Master (candidate_master is set)
Mon Jul 24 15:40:05 2017 - [info] Checking slave configurations..
Mon Jul 24 15:40:05 2017 - [info] Checking replication filtering settings..
Mon Jul 24 15:40:05 2017 - [info]  Replication filtering check ok.
Mon Jul 24 15:40:05 2017 - [info] Master is down!
Mon Jul 24 15:40:05 2017 - [info] Terminating monitoring script.
Mon Jul 24 15:40:05 2017 - [info] Got exit code 20 (Master dead).
Mon Jul 24 15:40:05 2017 - [info] MHA::MasterFailover version 0.57.
Mon Jul 24 15:40:05 2017 - [info] Starting master failover.
Mon Jul 24 15:40:05 2017 - [info] 
Mon Jul 24 15:40:05 2017 - [info] * Phase 1: Configuration Check Phase..
Mon Jul 24 15:40:05 2017 - [info] 
Mon Jul 24 15:40:07 2017 - [info] GTID failover mode = 1
Mon Jul 24 15:40:07 2017 - [info] Dead Servers:
Mon Jul 24 15:40:07 2017 - [info]   172.31.13.126(172.31.13.126:3306)
Mon Jul 24 15:40:07 2017 - [info] Checking master reachability via MySQL(double check)...
Mon Jul 24 15:40:07 2017 - [info]  ok.
Mon Jul 24 15:40:07 2017 - [info] Alive Servers:
Mon Jul 24 15:40:07 2017 - [info]   172.31.9.182(172.31.9.182:3306)
Mon Jul 24 15:40:07 2017 - [info] Alive Slaves:
Mon Jul 24 15:40:07 2017 - [info]   172.31.9.182(172.31.9.182:3306)  Version=5.7.18-15-log (oldest major version between slaves) log-bin:enabled
Mon Jul 24 15:40:07 2017 - [info]     GTID ON
Mon Jul 24 15:40:07 2017 - [info]     Replicating from 172.31.13.126(172.31.13.126:3306)
Mon Jul 24 15:40:07 2017 - [info]     Primary candidate for the new Master (candidate_master is set)
Mon Jul 24 15:40:07 2017 - [info] Starting GTID based failover.
Mon Jul 24 15:40:07 2017 - [info] 
Mon Jul 24 15:40:07 2017 - [info] ** Phase 1: Configuration Check Phase completed.
Mon Jul 24 15:40:07 2017 - [info] 
Mon Jul 24 15:40:07 2017 - [info] * Phase 2: Dead Master Shutdown Phase..
Mon Jul 24 15:40:07 2017 - [info] 
Mon Jul 24 15:40:07 2017 - [info] Forcing shutdown so that applications never connect to the current master..
Mon Jul 24 15:40:07 2017 - [info] Executing master IP deactivation script:
Mon Jul 24 15:40:07 2017 - [info]   /usr/local/bin/master_ip_failover --orig_master_host=172.31.13.126 --orig_master_ip=172.31.13.126 --orig_master_port=3306 --command=stopssh --ssh_user=root  
Mon Jul 24 15:40:07 2017 - [info]  done.
Mon Jul 24 15:40:07 2017 - [warning] shutdown_script is not set. Skipping explicit shutting down of the dead master.
Mon Jul 24 15:40:07 2017 - [info] * Phase 2: Dead Master Shutdown Phase completed.
Mon Jul 24 15:40:07 2017 - [info] 
Mon Jul 24 15:40:07 2017 - [info] * Phase 3: Master Recovery Phase..
Mon Jul 24 15:40:07 2017 - [info] 
Mon Jul 24 15:40:07 2017 - [info] * Phase 3.1: Getting Latest Slaves Phase..
Mon Jul 24 15:40:07 2017 - [info] 
Mon Jul 24 15:40:07 2017 - [info] The latest binary log file/position on all slaves is mysql-bin.000011:234
Mon Jul 24 15:40:07 2017 - [info] Latest slaves (Slaves that received relay log files to the latest):
Mon Jul 24 15:40:07 2017 - [info]   172.31.9.182(172.31.9.182:3306)  Version=5.7.18-15-log (oldest major version between slaves) log-bin:enabled
Mon Jul 24 15:40:07 2017 - [info]     GTID ON
Mon Jul 24 15:40:07 2017 - [info]     Replicating from 172.31.13.126(172.31.13.126:3306)
Mon Jul 24 15:40:07 2017 - [info]     Primary candidate for the new Master (candidate_master is set)
Mon Jul 24 15:40:07 2017 - [info] The oldest binary log file/position on all slaves is mysql-bin.000011:234
Mon Jul 24 15:40:07 2017 - [info] Oldest slaves:
Mon Jul 24 15:40:07 2017 - [info]   172.31.9.182(172.31.9.182:3306)  Version=5.7.18-15-log (oldest major version between slaves) log-bin:enabled
Mon Jul 24 15:40:07 2017 - [info]     GTID ON
Mon Jul 24 15:40:07 2017 - [info]     Replicating from 172.31.13.126(172.31.13.126:3306)
Mon Jul 24 15:40:07 2017 - [info]     Primary candidate for the new Master (candidate_master is set)
Mon Jul 24 15:40:07 2017 - [info] 
Mon Jul 24 15:40:07 2017 - [info] * Phase 3.3: Determining New Master Phase..
Mon Jul 24 15:40:07 2017 - [info] 
Mon Jul 24 15:40:07 2017 - [info] Searching new master from slaves..
Mon Jul 24 15:40:07 2017 - [info]  Candidate masters from the configuration file:
Mon Jul 24 15:40:07 2017 - [info]   172.31.9.182(172.31.9.182:3306)  Version=5.7.18-15-log (oldest major version between slaves) log-bin:enabled
Mon Jul 24 15:40:07 2017 - [info]     GTID ON
Mon Jul 24 15:40:07 2017 - [info]     Replicating from 172.31.13.126(172.31.13.126:3306)
Mon Jul 24 15:40:07 2017 - [info]     Primary candidate for the new Master (candidate_master is set)
Mon Jul 24 15:40:07 2017 - [info]  Non-candidate masters:
Mon Jul 24 15:40:07 2017 - [info]  Searching from candidate_master slaves which have received the latest relay log events..
Mon Jul 24 15:40:07 2017 - [info] New master is 172.31.9.182(172.31.9.182:3306)
Mon Jul 24 15:40:07 2017 - [info] Starting master failover..
Mon Jul 24 15:40:07 2017 - [info] 
From:
172.31.13.126(172.31.13.126:3306) (current master)
 +--172.31.9.182(172.31.9.182:3306)

To:
172.31.9.182(172.31.9.182:3306) (new master)
Mon Jul 24 15:40:07 2017 - [info] 
Mon Jul 24 15:40:07 2017 - [info] * Phase 3.3: New Master Recovery Phase..
Mon Jul 24 15:40:07 2017 - [info] 
Mon Jul 24 15:40:07 2017 - [info]  Waiting all logs to be applied.. 
Mon Jul 24 15:40:07 2017 - [info]   done.
Mon Jul 24 15:40:07 2017 - [info] Getting new master's binlog name and position..
Mon Jul 24 15:40:07 2017 - [info]  mysql-bin.000008:234
Mon Jul 24 15:40:07 2017 - [info]  All other slaves should start replication from here. Statement should be: CHANGE MASTER TO MASTER_HOST='172.31.9.182', MASTER_PORT=3306, MASTER_AUTO_POSITION=1, MASTER_USER='slave01', MASTER_PASSWORD='xxx';
Mon Jul 24 15:40:07 2017 - [info] Master Recovery succeeded. File:Pos:Exec_Gtid_Set: mysql-bin.000008, 234, 28a037e5-6d0f-11e7-b43a-02e60b685c1c:1-15,
39292a50-6d0f-11e7-999e-020efbf1e94e:1-4
Mon Jul 24 15:40:07 2017 - [info] Executing master IP activate script:
Mon Jul 24 15:40:07 2017 - [info]   /usr/local/bin/master_ip_failover --command=start --ssh_user=root --orig_master_host=172.31.13.126 --orig_master_ip=172.31.13.126 --orig_master_port=3306 --new_master_host=172.31.9.182 --new_master_ip=172.31.9.182 --new_master_port=3306 --new_master_user='mha01'   --new_master_password=xxx
NEW_MASTER_IP=172.31.9.182,OLD_MASTER_IP=172.31.13.126
Connection to 172.31.13.126 closed.
VIP_ATTACHMENT_ID=eni-attach-5b219e35
ATTACHMENT              eni-attach-5b219e35     detaching
INSTANCE_ID=i-03135a59c242e9bad
eni-attach-0f209f61
Mon Jul 24 15:40:22 2017 - [info]  OK.
Mon Jul 24 15:40:22 2017 - [info] Setting read_only=0 on 172.31.9.182(172.31.9.182:3306)..
Mon Jul 24 15:40:22 2017 - [info]  ok.
Mon Jul 24 15:40:22 2017 - [info] ** Finished master recovery successfully.
Mon Jul 24 15:40:22 2017 - [info] * Phase 3: Master Recovery Phase completed.
Mon Jul 24 15:40:22 2017 - [info] 
Mon Jul 24 15:40:22 2017 - [info] * Phase 4: Slaves Recovery Phase..
Mon Jul 24 15:40:22 2017 - [info] 
Mon Jul 24 15:40:22 2017 - [info] 
Mon Jul 24 15:40:22 2017 - [info] * Phase 4.1: Starting Slaves in parallel..
Mon Jul 24 15:40:22 2017 - [info] 
Mon Jul 24 15:40:22 2017 - [info] All new slave servers recovered successfully.
Mon Jul 24 15:40:22 2017 - [info] 
Mon Jul 24 15:40:22 2017 - [info] * Phase 5: New master cleanup phase..
Mon Jul 24 15:40:22 2017 - [info] 
Mon Jul 24 15:40:22 2017 - [info] Resetting slave info on the new master..
Mon Jul 24 15:40:22 2017 - [info]  172.31.9.182: Resetting slave info succeeded.
Mon Jul 24 15:40:22 2017 - [info] Master failover to 172.31.9.182(172.31.9.182:3306) completed successfully.
Mon Jul 24 15:40:22 2017 - [info] 

----- Failover Report -----

app1: MySQL Master failover 172.31.13.126(172.31.13.126:3306) to 172.31.9.182(172.31.9.182:3306) succeeded

Master 172.31.13.126(172.31.13.126:3306) is down!

Check MHA Manager logs at slave:/var/log/masterha/app1/manager.log for details.

Started automated(non-interactive) failover.
Invalidated master IP address on 172.31.13.126(172.31.13.126:3306)
Selected 172.31.9.182(172.31.9.182:3306) as a new master.
172.31.9.182(172.31.9.182:3306): OK: Applying all logs succeeded.
172.31.9.182(172.31.9.182:3306): OK: Activated master IP address.
172.31.9.182(172.31.9.182:3306): Resetting slave info succeeded.
Master failover to 172.31.9.182(172.31.9.182:3306) completed successfully.
Mon Jul 24 16:27:13 2017 - [info] Sending mail..
defined(@array) is deprecated at /usr/share/perl5/vendor_perl/Mail/Sender.pm line 318.
        (Maybe you should just omit the defined()?)
defined(@array) is deprecated at /usr/share/perl5/vendor_perl/Mail/Sender.pm line 2693.
        (Maybe you should just omit the defined()?)
Option new_slave_hosts requires an argument
Unknown option: conf
tail: manager.log: file truncated
>> 220 126.com Anti-spam GT for Coremail System (126com[20140526])
<< EHLO slave
>> 250-mail
>> 250-PIPELINING
>> 250-AUTH LOGIN PLAIN
>> 250-AUTH=LOGIN PLAIN
>> 250-coremail 1Uxr2xKj7kG0xkI17xGrU7I0s8FY2U3Uj8Cz28x1UUUUU7Ic2I0Y2Ur0Vl_3UCa0xDrUUUUj
>> 250-STARTTLS
>> 250 8BITMIME
<< AUTH LOGIN
>> 334 dXNlcm5hbWU6
<< ZHdqNTY4NTg4QDEyNi5jb20=
>> 334 UGFzc3dvcmQ6
<< RHdqMTIzNDU=
>> 235 Authentication successful
<< MAIL FROM:<dwj568588@126.com>
>> 250 Mail OK
<< RCPT TO:<duanwenjie@huan.tv>
>> 250 Mail OK
<< DATA
>> 354 End data with <CR><LF>.<CR><LF>
<< To: duanwenjie@huan.tv
<< From: dwj568588@126.com
<< Subject: app1: MySQL Master failover 172.31.13.126(172.31.13.126:3306) to 172.31.9.182(172.31.9.182:3306) succeeded
<< Date: Mon, 24 Jul 2017 16:27:15 +0800
<< X-Mailer: Perl script "send_report"
<<      using Mail::Sender 0.8.16 by Jenda Krynicky, Czechlands
<<      running on slave ()
<<      under account "zhouting"
<< Message-ID: <20170724_082715_056339.dwj568588@126.com>
<< MIME-Version: 1.0
<< Content-type: text/plain; charset=utf-8
<< 
<< Master 172.31.13.126(172.31.13.126:3306) is down!
<< 
<< Check MHA Manager logs at slave:/var/log/masterha/app1/manager.log for details.
<< 
<< Started automated(non-interactive) failover.
<< Invalidated master IP address on 172.31.13.126(172.31.13.126:3306)
<< Selected 172.31.9.182(172.31.9.182:3306) as a new master.
<< 172.31.9.182(172.31.9.182:3306): OK: Applying all logs succeeded.
<< 172.31.9.182(172.31.9.182:3306): OK: Activated master IP address.
<< 172.31.9.182(172.31.9.182:3306): Resetting slave info succeeded.
<< Master failover to 172.31.9.182(172.31.9.182:3306) completed successfully.
<< 
<< .
>> 250 Mail OK queued as smtp1,C8mowAAH7yZhr3VZFR+JCA--.54250S2 1500884834
<< QUIT
>> 221 Bye
[root@slave app1]#

日志过程解析

启动前的准备工作
检查数据库服务器状态,获取相关参数设置
检查GTID、candidate_master、过滤DB是否设置
测试ssh连接是否成功
测试MHA node是否可用
创建MHA日志目录
开始检查slave的差异日志应用权限
确定当前的复制架构
调试master_ip_failover_script
调试shutdown_script
设置二次检查的主机masterha_secondary_check
MHA启动完毕,进入监测状态
监测master服务器挂了
通过定义的二次监测,确认master是否挂了
确认master挂了,开始进入failover流程
再试尝试连接master和master的ssh
通过MHA配置文件,监测其他slave的状态
再次监测slave的配置是否有变化,是否符合failover条件
正式开始failover
再次对slave配置做检查
对原Master做master_ip_failover_script和shutdown_script的操作
开始差异日志的恢复，获取slave最后得到的binlog位置
获取原master的binlog日志
确定新的master
在new master上应用差异的binlog日志
获取new master的binlog位置。
执行master_ip_failover_script,调用aws_vip_change.sh，执行VIP漂移
开始恢复其他slave的差异日志
差异日志应用完成以后,切换所有slave到new master。
failover操作完成,生成failover报告
最后发送邮件通知

确认FailOver状态

[root@slave app1]# ifconfig             #vip已经添加到从库上
eth0      Link encap:Ethernet  HWaddr 02:0E:FB:F1:E9:4E  
          inet addr:172.31.9.182  Bcast:172.31.15.255  Mask:255.255.240.0
          inet6 addr: fe80::e:fbff:fef1:e94e/64 Scope:Link
          UP BROADCAST RUNNING MULTICAST  MTU:9001  Metric:1
          RX packets:17060 errors:0 dropped:0 overruns:0 frame:0
          TX packets:17883 errors:0 dropped:0 overruns:0 carrier:0
          collisions:0 txqueuelen:1000 
          RX bytes:2142676 (2.0 MiB)  TX bytes:2390595 (2.2 MiB)

eth1      Link encap:Ethernet  HWaddr 02:12:25:83:A0:22  
          inet addr:172.31.0.200  Bcast:172.31.15.255  Mask:255.255.240.0
          inet6 addr: fe80::12:25ff:fe83:a022/64 Scope:Link
          UP BROADCAST RUNNING MULTICAST  MTU:9001  Metric:1
          RX packets:8 errors:0 dropped:0 overruns:0 frame:0
          TX packets:18 errors:0 dropped:0 overruns:0 carrier:0
          collisions:0 txqueuelen:1000 
          RX bytes:798 (798.0 b)  TX bytes:1828 (1.7 KiB)

lo        Link encap:Local Loopback  
          inet addr:127.0.0.1  Mask:255.0.0.0
          inet6 addr: ::1/128 Scope:Host
          UP LOOPBACK RUNNING  MTU:65536  Metric:1
          RX packets:2789 errors:0 dropped:0 overruns:0 frame:0
          TX packets:2789 errors:0 dropped:0 overruns:0 carrier:0
          collisions:0 txqueuelen:1 
          RX bytes:669913 (654.2 KiB)  TX bytes:669913 (654.2 KiB)

[root@slave app1]#

查看邮件发送状态

登录邮件，查看是否收到报警邮件。

手动FailOver

MHA的手动FailOver不在本文范围，详情理论可参考官方文档。

参数列表

参数名	是否必须	参数使用域	默认值	例子
hostname	Yes	Local Only	-	hostname=master,hostname=slave
ip	No	Local Only	gethostbyname($hostname)	ip=192.168.1.100
port	No	Local/App/Global	3306	port=3306
ssh_host	No	Local Only	same as hostname	ssh_host=master,ssh_host=slave
ssh_ip	No	Local Only	gethostbyname($ssh_host)	ssh_ip=192.168.1.101
ssh_port	No	Local/App/Global	22	ssh_port=22
ssh_connection_timeout	No	Local/App/Global	5	ssh_connection_timeout=20
ssh_options	No	Local/App/Global	“”(empty string)	ssh_options=”-i /root/.ssh/id_dsa2”
candidate_master	No	Local Only	0	candidate_master=1
no_master	No	Local Only	0	no_master=1
ignore_fail	No	Local Only	0	ignore_fail=1
skip_init_ssh_check	No	Local Only	0	skip_init_ssh_check=1
skip_reset_slave	No	Local/App/Global	0	skip_reset_slave=1
user	No	Local/App/Global	root	user=root
password	No	Local/App/Global	“”(empty string)	password=rootpass
repl_user	No	Local/App/Global	Master_User value from SHOW SLAVE STATUS	repl_user=repl
repl_password	No	Local/App/Global	(current replication password)	repl_user=replpass
disable_log_bin	No	Local/App/Global	0	disable_log_bin=1
master_pid_file	No	Local/App/Global	“”(empty string)	master_pid_file=/var/lib/mysql/master1.pid
ssh_user	No	Local/App/Global	current OS user	ssh_user=root
remote_workdir	No	Local/App/Global	/var/tmp	remote_workdir=/var/log/masterha/app1
master_binlog_dir	No	Local/App/Global	/var/lib/mysql	master_binlog_dir=/data/mysql1
log_level	No	App/Global	info	log_level=debug
manager_workdir	No	App	/var/tmp	manager_workdir=/var/log/masterha
client_bindir	No	App	-	client_bindir=/usr/mysql/bin
client_libdir	No	App	-	client_libdir=/usr/lib/mysql
manager_log	No	App	STDERR	manager_log=/var/log/masterha/app1.log
check_repl_delay	No	App/Global	1	check_repl_delay=0
check_repl_filter	No	App/Global	1	check_repl_filter=0
latest_priority	No	App/Global	1	latest_priority=0
multi_tier_slave	No	App/Global	0	multi_tier_slave=1
ping_interval	No	App/Global	3	ping_interval=5
ping_type	No	App/Global	SELECT	ping_type=CONNECT
secondary_check_script	No	App/Global	null	secondary_check_script= masterha_secondary_check -s remote_dc1 -s remote_dc2
master_ip_failover_script	No	App/Global	null	master_ip_failover_script=/usr/local/bin/master_ip_failover
master_ip_online_change_script	No	App/Global	null	master_ip_online_change_script= /usr/local/bin/master_ip_online_change
shutdown_script	No	App/Global	null	shutdown_script= /usr/local/bin/master_shutdown
report_script	No	App/Global	null	report_script= /usr/local/bin/report

详情解释

Local Scope:        针对每个server级别有效的选项.local scope级别的参数需要在配置文件的 [server_xxx]段落配置  
App Scope:          这个参数可以理解为针对一组master-slave集群. 这些参数需要在 [server_default]段落配置  
Global Scope:       这个参数针对所有的MHA管理的实例. global scope级别的配置只有你在使用一个manager server管理多组master-slave时使用 
hostname：          目标Mysql服务器的主机名或者IP地址,这个参数是强制的,并且必须配置在[server_xxx]段落
ip：                目标Mysql服务器的IP地址,默认是通过$hostname变量获取.MHA manager和MHA Node
                    内部使用这个IP地址链接到Mysql和SSH.通常你不需要配置这个参数,因为它可以通过$hostname解析获得
port：              mysql服务器使用的端口号,默认是3306,MHA链接到mysql通过IP地址和端口
ssh_host：          从0.53版本开始支持此参数,此参数的主要目的在于你mysql复制使用的IP地址由于安全策略,不能直接连接,
                    所以需要通过其他IP和端口连接到mysql server或者ssh,默认这个参数和hostname的值相同
ssh_ip：            从0.53版本开始支持此参数,连接到目标mysql server的ssh ip地址,默认从$ssh_host获取
ssh_port：          从0.53版本开始支持此参数,连接到目标mysql server的ssh端口,默认是22
ssh_connection_timeout：从0.54开始支持此参数,默认是5秒,添加此参数的原因是以前我们在程序里面写死了
ssh_options：       从0.53开始支持此参数,可以添加SSH命令行的参数
candidate_master：  
你的slave的硬件配置或者用途可能不太一样,你想要提升更加可靠的slave作为新的master
如果设置candidate_master的值为1,那么这个server会优先成为master,只要它满足成为master的条件(binlog开启的,没有严重的复制延时等)
所以意味着配置了candiate_master=1的服务器并不是肯定可以成为new master,但是这个参数能够帮助提高优先级
如果你在多个服务器上设置了candidate_master=1,那么在配置文件中[server_xxx]的配置顺序,会成为第二排序规则,排在上面的越优先
no_master：         no_master=1是默认值,意思是这个server从来不会成为新的master,这个参数用来标记某些从来不用成为new master的服务器
ignore_fail：       
默认是0,当某个slave的ssh或者mysql当掉或者复制失败的时候,MHA manager不启动failover。
但是有些环境下,你想要在某个特定的slave失败的时候继续执行failover,把么就设置这个ignore_fail=1,即时这个slave失败的时候,failover依然继续执行
skip_init_ssh_check：启动的时候跳过ssh连接测试
skip_reset_slave：  从0.56版本开始支持此参数,在master执行failover以后跳过执行reset slave all(译者感觉应该是方便MM复制的后期恢复)
user：              mysql管理员命令,建议赋予all privileges权限
password：          上面mysql user的密码
repl_user：         mysql复制使用的用户名,需要执行change master to命令和show slave status命令等.一般赋予replication slave,replication client权限
repl_password：     上面用户对应的密码.默认情况下,使用的是当前复制的密码
disable_log_bin：   当这个参数设置以后,当应用差异日志到slave的时候,slave不会生成binlog. Internally MHA passes --disable-log-bin to mysqlbinlog command
master_pid_file：   设置master的pid文件位置,当你的服务器上运行了多个mysql的时候使用
ssh_user：          
MHA manager和MHA node通过这个用户连接到mysql server的OS上,在对应的OS上执行相关命令和拷贝差异日志等等
这个用户必须有读取mysql binary/relay 相关日志文件的权限,还有日志目录的写入权限.(remote_workdir目录)
这个用户必须可以直接连接到服务器上,不用任何交互的操作.通过使用ssh public key作为认证方式.默认使用的用户是manager当前的用户
remote_workdir：    
每个MHA node上,MHA工作使用的目录的绝对路径.如果目录不存在,MHA会自动创建,如果权限不够,那么MHA node会意外终止,
注意MHA manager和MHA node都不会检查这个目录的磁盘可用空间,你需要自己保证有足够的可用空间.默认的remote_workdir是'/var/tmp'
master_binlog_dir： 
master mysql保存binlog的目录的绝对路径.如果参数的主要目的是在master mysql宕机以后,为了通过ssh拷贝需要的binlog event
这个参数是需要的,因为当mysql宕机以后,没法自动获取binary log的目录
默认情况下,master_binlog_dir的值是"/var/lib/mysql/,/var/log/mysql/",/var/lib/mysql/是大部分mysql发布版本的默认mysql输出目录,
你可以设置多个目录,使用逗号分隔.比如(/data1,/data2,/data3)
log_level：         MHA manager 的日志级别,默认是info级别,在大多数环境下没有问题.可用的级别有.debug/info/warning/error
manager_workdir：   MHA manager使用的工作目录,如果没有设置,默认使用/var/tmp
client_bindir：     如果mysql命令行工具没有安装在默认目录,可以使用这个选项配置
client_libdir：     如果mysql lib没有安装在默认目录,可以使用这个选项配置
manager_log：       MHA manager日志文件的绝对目录和文件名,如果没有设置,那么将直接打印到标准输出和标准错误输出
当执行交互式的failover时,MHA manager将会忽略manager_log设置,直接答应到标准输出和标准错误输出
check_repl_delay：  
默认情况下如果一个slave比master延时了100M的relay logs.MHA不会选择这个slave作为新的master.因为他需要更多的时间来recovery
设置check_repl_delay=0,MHA在选择new master时将会忽略复制的延时.这个参数通常和candidate_master=1同时使用
check_repl_filter： 
默认情况下如果master和slave有不同的binary log/replication 过滤规则的话,MHA打印错误,不进行start monitoring或者failover
这么做的目的是为了避免recover的时候出现意外的错误,例如"Table not exists",如果你100%确定你不同的过滤规则不会导致recover时候报错,
那么你可以设置check_repl_fiter=0,这样的话,MHA在应用差异日志的时候将不会在检查过滤规则.使用这个参数的时候需要非常小心
latest_priority：   默认情况下,接收到了最新的binlog的slave优先被选为new master,如果你想要控制优先级的顺序,比如(host2>host3>host4),那么设置latest_priority=0会有所帮助
multi_tier_slave：  
MHA manager从0.5.2开始支持多主复制.默认情况下,它不支持三层以上的复制架构.例如,host2是host1的slave,host3是host2的slave,
默认不允许把这个三个主机写到同一个配置文件中因为这是一个三层的复制架构,并且MHA会因此报错,并停止工作.如果设置了multi_tier_slave,
MHA manager不会因为多层架构而终止,它会忽略三层及以上的主机,例如master(host1)挂掉以后,host2会被选为新的master,host3将继续从host2复制
ping_interval：     
这个参数声明MHA manager pings(通过执行sql来ping) master的间隔.当连续三次ping失败,MHA manager认为这个Mysql master宕机,从宕机到检测到宕机,
最大的消耗时间是这个参数的四倍,这个参数默认值是3(3秒),如果MHA manager因为权限问题多次连接失败,这不认为master dead
ping_type：
从MHA manager 0.53开始支持这个参数, MHA建立一个长连接到master,然后通过执行"SELECT 1" (ping_type=SELECT)来判断master是否可用
但是在一些环境下,最好使用短连接的方式,因为这样的方式更严格,也能检测TCP连接级别的失败.设置ping_type=CONNECT可以实现.从0.56版本开始,支持ping_type=INSERT
secondary_check_script：
通常情况,强力推荐使用两个或者多个路由检测Mysql master默认MHA通过一个路由检测:从manager到master.这是不推荐的
MHA manager可以通过secondary_check_script参数调用一个内部脚本来实现两个或者多个路由的检测.下面是一个配置实例
secondary_check_script = masterha_secondary_check -s remote_host1 -s remote_host2
MHA manager包含masterha_secondary_check 脚本.内置的masterha_secondary_check脚本可以满足大多是环境,当然你也可以调用你自定义的脚本.
在上面的实例中,MHA manager通过这两个路由检测master主机
Manager-(A)->remote_host1-(B)->master_host
Manager-(A)->remote_host2-(B)->master_host
如果两条路径中,A都是成功的,B都是不成功的,那么masterha_secondary_check将会退出,返回状态0.MHA manager将会认为Mysql master宕机,开始failover
如果A失败,那么masterha_secondary_check将退出,返回状态2,MHA manager网络出现问题,不会开始failover
如果B检测成功,masterha_secondary_check退出并返回状态3,MHA manager明确的知道mysql master是活跃的,不开始failover
通常来讲,remote_host1和remote_host2必须和 MHA manager及Mysql server不在同一个网段,这样才更有意义
MHA调用secondary_check_script参数对应的脚本会自动传递以下参数(所以你不需要在配置文件中设置),
masterha_secondary_check适用于大多数环境,如果你需要其他的功能可以自己写一个网络检查的脚本.
--user=(SSH username of the remote hosts. ssh_user parameter value will be passed)
--master_host=(master's hostname)
--master_ip=(master's ip address)
--master_port=(master's port number)
注意:
masterha_secondary_check脚本依赖于IO::Socket::INET perl包,在perl v5.6.0中默认包含,masterha_secondary_check脚本会通过
ssh连接到所有的remote servers.所以SSH public key 认证需要设置.另外masterha_secondary_check脚本尝试使用TCP连接从remote server到mysql master.这意味着服务器设置的max_connections参数对此连接无效,并且如果TCP连接成功,Aborted_connects状态将会加1
master_ip_failover_script：
常见的HA环境下,大多是情况会给master分配一个虚拟IP,如果master宕机,HA软件像一个Pacemaker将虚拟IP转移到备用的master上
另外一种常见的方法就是创建一个全局目录数据库,包含所有应用和writer/reader ip地址.例如{app_master1,192.168.0.1},{app_master2,192.168.0.2}...,代替使用虚拟IP,
这种情况,你需要在master宕机的时候更新目录数据库
两种方法都有好的或者不好的地方,MHA不强制要求使用哪一种,但是提供了master_ip_failover_script参数来完成此目的
换句话说,你需要写一个脚本来调整应用服务连接到新的master,然后定义master_ip_failover_script的参数,下面是一个实例
master_ip_failover_script= /usr/local/bin/master_ip_failover
你可以从(MHA Manager package)/samples/scripts/master_ip_failover找到一个简单的脚本.这个脚本在manager的tarball和GitHUb branch中才包含
MHA manager会调用master_ip_failover_script三次,
第一次,在开始master monitor之前调用(目的是检查脚本是否可用),
第二次是在调用shutdown_script脚本前调用,
第三次是在new master应用完所有的差异日志以后,MHA manager会传递给脚本如下参数.(你不用在配置文件中指明这些参数)
Checking phase
--command=status
--ssh_user=(current master's ssh username)
--orig_master_host=(current master's hostname)
--orig_master_ip=(current master's ip address)
--orig_master_port=(current master's port number)
Current master shutdown phase
--command=stop or stopssh
--ssh_user=(dead master's ssh username, if reachable via ssh)
--orig_master_host=(current(dead) master's hostname)
--orig_master_ip=(current(dead) master's ip address)
--orig_master_port=(current(dead) master's port number)
New master activation phase
--command=start
--ssh_user=(new master's ssh username)
--orig_master_host=(dead master's hostname)
--orig_master_ip=(dead master's ip address)
--orig_master_port=(dead master's port number)
--new_master_host=(new master's hostname)
--new_master_ip=(new master's ip address)
--new_master_port(new master's port number)
--new_master_user=(new master's user)
--new_master_password(new master's password)
如果你使用共享虚拟IP的方法,你不需要在master shutdown阶段做任何事情,只要通过shutdown_script脚本关掉原来master的电源,在激活new master阶段,
你需要分配虚拟IP给new master.如果你使用目录数据库的方法,你需要在宕机的master shutdown阶段删除或者更新失效master的记录
在new master生效阶段需要插入或者更新new master的记录.另外你可以做任何事情来保证应用程序可以成功的写入new master
比如SET GLOBAL read_only=0，创建有写入权限的数据库用户等等
MHA manager检查脚本的退出状态,如果脚本退出状态是0或者10,MHA manager继续操作,如果脚本退出状态不是0或者10,MHA manager将会意外终止,并不继续continue failover.默认这个参数是空的
master_ip_online_change_script：
这是几个简单版本的master_ip_failover_script参数,但是master failover命令并不调用它.master online change命令会调用它.(masterha_master_switch --master_state=alive),传递以下参数
Current master write freezing phase
--command=stop or stopssh
--orig_master_host=(current master's hostname)
--orig_master_ip=(current master's ip address)
--orig_master_port=(current master's port number)
--orig_master_user=(current master's user)
--orig_master_password=(current master's password)
--orig_master_ssh_user=(from 0.56, current master's ssh user)
--orig_master_is_new_slave=(from 0.56, notifying whether the orig master will be new slave or not)
New master granting write phase
--command=start
--orig_master_host=(orig master's hostname)
--orig_master_ip=(orig master's ip address)
--orig_master_port=(orig master's port number)
--new_master_host=(new master's hostname)
--new_master_ip=(new master's ip address)
--new_master_port(new master's port number)
--new_master_user=(new master's user)
--new_master_password=(new master's password)
--new_master_ssh_user=(from 0.56, new master's ssh user)
MHA在当前的master write freezing阶段后执行FLUASH TABLES WITH READ LOCK, 在new mastergranting write阶段你可以执行一些类似master_ip_failover_script的操作
比如创建一个有写入权限的用户,执行SET GLOBAL read_only=0,更新目录数据库等.如果你的脚本退出返回状态不是1或者10,那么MHA manager将会意外终止,
停止master switch这个参数默认为空,所以MHA manager不做任何调用你可以从(MHA Manager package)/samples/scripts/master_ip_online_change找到一个简单的脚本
这个脚本在manager的tarball和GitHUb branch中才包含
shutdown_script：
你可能需要强制关闭master服务器,避免他再次提供服务,这对于避免脑裂很重要.下面是一个实例
shutdown_script= /usr/local/bin/power_manager
你可以从(MHA Manager package)/samples/scripts/power_manager找到一个简单的脚本.这个脚本在manager的tarball和GitHUb branch中才包含
在调用shutdown_script脚本之前,MHA manager内部会通过ssh尝试连接到mysql master,如果ssh可以连接(意思就是OS是存活的,但是Mysqld没有运行),MHAmanager就会传递下面的参数
--command=stopssh (这个意思就是指停止服务,不会关机)
--ssh_user=(ssh username so that you can connect to the master)
--host=(master's hostname)
--ip=(master's ip address)
--port=(master's port number)
--pid_file=(master's pid file)
如果master主机的ssh不能连接,那么MHA会使用如下参数
--command=stop (这个会通过fence设备关掉电源)
--host=(master's hostname)
--ip=(master's ip address)
这个脚本的大概功能如下,如果--command=stopssh被调用,脚本会使用killall -9 杀掉目标服务器上所有的mysqld_safe服务
如果--pid_file被设置,脚本尝试kill指定的进程.如果脚本执行成功,那么脚本会退出返回状态10.如果退出状态为10,
MHA manager后面会通过ssh连接到master,获取需要的binary log.如果脚本通过ssh连接到服务器失败,那么就会传递--command=stop参数,这个参数尝试关闭机器的电源,
关闭电源依赖于H/W.HP(ILO),DELL(DRAC).如果power off成功,脚本会然会状态0,其他情况会返回状态1.当返回状态是0的时候MHA manager 
开始failover.如果返回状态不是0或者10,那么MHA manager会意外终止.这个参数默认是空,所以MHA manager不会调用任何脚本
另外,MHA manager在启动monitoring之前调用shutdown_script.这时候会传递下面的参数.目的是检测脚本是否可用,如果发现错误,你可以提前知道
--command=status
--host=(master's hostname)
--ip=(master's ip address)
report_script：
你希望当failover发生以后可以发送一个报告(例如email),report_script可以达到这个目的,MHA manager传递下面的参数
--orig_master_host=(dead master's hostname)
--new_master_host=(new master's hostname)
--new_slave_hosts=(new slaves' hostnames, delimited by commas)
--subject=(mail subject)
--body=(body)
默认这个参数是空的,所以MHA manager不调用任何脚本，你可以从(MHA Manager package)/samples/scripts/send_report找到一个简单的脚本
这个脚本在manager的tarball和GitHUb branch中才包含
init_conf_load_script：
这个脚本可以在你不想在配置文件中写明文密码的时候使用.你可以覆盖全局配置参数.实例脚本如下
#!/usr/bin/perl
print "password=$ROOT_PASS\n";
print "repl_password=$REPL_PASS\n";
这个参数默认为空,所以MHA manager默认不调用任何脚本

参考

MySQL Master High Available 理论篇：
https://yq.aliyun.com/articles/58004?spm=5176.100239.blogcont57855.9.jUuCt0

结束语

云服务器上MySQL高可用，也可通过云负载均衡产品+MySQL复制来实现，但在数据安全性上，没有MHA+VIP+MySQL相对安全。

MongoDB从MMAPv1在线迁移至WiredTiger引擎

发表于 2017-07-21 | 分类于 mongodb

字数统计: | 阅读时长 ≈

前言

MongoDB是一个开源的文档型数据库，介于关系数据库和非关系数据库之间，是非关系数据库中功能最丰富，最像关系数据库的。它支持的数据结构非常松散，是类似json的bson格式，因此可以存储比较复杂的数据类型。Mongo最大的特点是它支持的查询语言非常强大，其语法有点类似于面向对象的查询语言，几乎可以实现类似关系数据库单表查询的绝大部分功能，而且还支持对数据建立索引。

存储引擎

存储引擎是MongoDB的核心组件，负责管理数据如何存储在硬盘和内存上。从MongoDB3.0版本开始，MongoDB支持多数据存储引擎，如WiredTiger，MMAPv1，In-Memory等，每个存储引擎都有自己的优势，percona版本还支持RocksDB引擎。从3.2版本开始，WiredTiger成为了默认的存储引擎，在之前版本MMAPv1为默认存储引擎。这两种引擎在目前的使用量占比最大。

WiredTiger、MMAPv1对比

性能、性能

MMAPv1引擎使用的是表级锁，当单个集合上有并发操作时，吞吐会受限制。
Wiredtiger引擎使用文档级锁，通过多版本并发控制，带来并发和吞吐的提高。

压缩、加密

MMAPv1引擎要求数据在内存和在磁盘的形式一致(map磁盘内存映射)，因此不支持压缩和加密。
Wiredtiger引擎支持对所有集合和索引进行Block压缩和前缀压缩，可以大大节省存储空间。但是消耗额外的CPU执行数据压缩和解压缩的操作。

存储方式

MMAPv1引擎在数据库级别配置文件，数据库中的所有集合和索引都混合存储在数据库文件中，即使删掉了某个集合或者索引，占用的磁盘空间也很难回收。
WiredTiger引擎在集合和索引级别分配文件，数据库中的所有集合和索引都存储在单独的文件中，集合或者索引删除后，磁盘空间方便回收。

内存使用

MMAPv1引擎消耗内存大，无有效控制手段。
WiredTiger引擎支持内存使用空间配置，可通过storage.wiredTiger.engineConfig.cacheSizeGB参控制MongoDB所能使用的最大内存。

准备

操作系统：centos 6.5 x86_64

迁移

变更存储引擎的常见方式有2种，分别是停机和不停机。本文只介绍在不停机的情况下从MMAPv1引擎副本集迁移至WiredTiger引擎副本集。

安装

注意：

迁移的成功取决于oplog的大小是否能够满足从库完全初始化数据。

首先安装好mongodb，并依次启动，实例以3.4.4版本为例，端口分别是27017/28017/29017，部署在一台服务器，具体安装步骤省略。

[root@iZuf6c08fdv8duubho2b0rZ ~]# /usr/local/mongodb1/bin/mongod -f /hwdata/data/mongodb1/conf/mongodb.conf 
note: noprealloc may hurt performance in many applications
about to fork child process, waiting until server is ready for connections.
forked process: 14873
child process started successfully, parent exiting
[root@iZuf6c08fdv8duubho2b0rZ ~]# /usr/local/mongodb2/bin/mongod -f /hwdata/data/mongodb2/conf/mongodb.conf   
note: noprealloc may hurt performance in many applications
about to fork child process, waiting until server is ready for connections.
forked process: 14894
child process started successfully, parent exiting
[root@iZuf6c08fdv8duubho2b0rZ ~]# /usr/local/mongodb3/bin/mongod -f /hwdata/data/mongodb3/conf/mongodb.conf   
note: noprealloc may hurt performance in many applications
about to fork child process, waiting until server is ready for connections.
forked process: 14919
child process started successfully, parent exiting
[root@iZuf6c08fdv8duubho2b0rZ ~]# netstat -tunlp|grep mongo
tcp        0      0 0.0.0.0:27017               0.0.0.0:*                   LISTEN      14873/mongod        
tcp        0      0 0.0.0.0:28017               0.0.0.0:*                   LISTEN      14894/mongod        
tcp        0      0 0.0.0.0:29017               0.0.0.0:*                   LISTEN      14919/mongod

配置文件

详情的配置文件如下：

[root@iZuf6c08fdv8duubho2b0rZ ~]# cat /hwdata/data/mongodb1/conf/mongodb.conf 
dbpath=/hwdata/data/mongodb1/data
logpath=/hwdata/data/mongodb1/logs/mongodb.log
pidfilepath=/hwdata/data/mongodb1/logs/mongodb.pid
directoryperdb=true
logappend=true
port=27017
fork=true
noprealloc=true
maxConns=10000
profile=1
slowms=100
journal=true
replSet=jiessie
storageEngine=mmapv1
[root@iZuf6c08fdv8duubho2b0rZ ~]# cat /hwdata/data/mongodb2/conf/mongodb.conf  
dbpath=/hwdata/data/mongodb2/data
logpath=/hwdata/data/mongodb2/logs/mongodb.log
pidfilepath=/hwdata/data/mongodb2/logs/mongodb.pid
directoryperdb=true
logappend=true
port=28017
fork=true
noprealloc=true
maxConns=10000
profile=1
slowms=100
journal=true
replSet=jiessie
storageEngine=mmapv1
[root@iZuf6c08fdv8duubho2b0rZ ~]# cat /hwdata/data/mongodb3/conf/mongodb.conf  
dbpath=/hwdata/data/mongodb3/data
logpath=/hwdata/data/mongodb3/logs/mongodb.log
pidfilepath=/hwdata/data/mongodb3/logs/mongodb.pid
directoryperdb=true
logappend=true
port=29017
fork=true
noprealloc=true
maxConns=10000
profile=1
slowms=100
journal=true
replSet=jiessie
storageEngine=mmapv1

搭建副本集

登录其中一个实例，这里以27017为例子，搭建副本

[root@iZuf6c08fdv8duubho2b0rZ ~]# /usr/local/mongodb1/bin/mongo
MongoDB shell version v3.4.4
connecting to: mongodb://127.0.0.1:27017
MongoDB server version: 3.4.4
> cfg = {_id: "jiessie", members: [{_id: 0, host: "192.168.7.50:27017",priority:3},{_id: 1, host: "192.168.7.50:28017",priority:2},{_id: 2, host: "192.168.7.50:29017",priority:3}]};
{
        "_id" : "jiessie",
        "members" : [
                {
                        "_id" : 0,
                        "host" : "192.168.7.50:27017",
                        "priority" : 3
                },
                {
                        "_id" : 1,
                        "host" : "192.168.7.50:28017",
                        "priority" : 2
                },
                {
                        "_id" : 2,
                        "host" : "192.168.7.50:29017",
                        "priority" : 3
                }
        ]
}
> rs.initiate(cfg);
{ "ok" : 1 }
jiessie:SECONDARY> 
jiessie:PRIMARY> rs.status();
{
        "set" : "jiessie",
        "date" : ISODate("2017-07-21T09:47:24.215Z"),
        "myState" : 1,
        "term" : NumberLong(1),
        "heartbeatIntervalMillis" : NumberLong(2000),
        "optimes" : {
                "lastCommittedOpTime" : {
                        "ts" : Timestamp(1500630436, 1),
                        "t" : NumberLong(1)
                },
                "appliedOpTime" : {
                        "ts" : Timestamp(1500630436, 1),
                        "t" : NumberLong(1)
                },
                "durableOpTime" : {
                        "ts" : Timestamp(1500630436, 1),
                        "t" : NumberLong(1)
                }
        },
        "members" : [
                {
                        "_id" : 0,
                        "name" : "192.168.7.50:27017",
                        "health" : 1,
                        "state" : 1,
                        "stateStr" : "PRIMARY",
                        "uptime" : 1056,
                        "optime" : {
                                "ts" : Timestamp(1500630436, 1),
                                "t" : NumberLong(1)
                        },
                        "optimeDate" : ISODate("2017-07-21T09:47:16Z"),
                        "infoMessage" : "could not find member to sync from",
                        "electionTime" : Timestamp(1500630354, 1),
                        "electionDate" : ISODate("2017-07-21T09:45:54Z"),
                        "configVersion" : 1,
                        "self" : true
                },
                {
                        "_id" : 1,
                        "name" : "192.168.7.50:28017",
                        "health" : 1,
                        "state" : 2,
                        "stateStr" : "SECONDARY",
                        "uptime" : 99,
                        "optime" : {
                                "ts" : Timestamp(1500630436, 1),
                                "t" : NumberLong(1)
                        },
                        "optimeDurable" : {
                                "ts" : Timestamp(1500630436, 1),
                                "t" : NumberLong(1)
                        },
                        "optimeDate" : ISODate("2017-07-21T09:47:16Z"),
                        "optimeDurableDate" : ISODate("2017-07-21T09:47:16Z"),
                        "lastHeartbeat" : ISODate("2017-07-21T09:47:22.597Z"),
                        "lastHeartbeatRecv" : ISODate("2017-07-21T09:47:23.554Z"),
                        "pingMs" : NumberLong(0),
                        "syncingTo" : "192.168.7.50:29017",
                        "configVersion" : 1
                },
                {
                        "_id" : 2,
                        "name" : "192.168.7.50:29017",
                        "health" : 1,
                        "state" : 2,
                        "stateStr" : "SECONDARY",
                        "uptime" : 99,
                        "optime" : {
                                "ts" : Timestamp(1500630436, 1),
                                "t" : NumberLong(1)
                        },
                        "optimeDurable" : {
                                "ts" : Timestamp(1500630436, 1),
                                "t" : NumberLong(1)
                        },
                        "optimeDate" : ISODate("2017-07-21T09:47:16Z"),
                        "optimeDurableDate" : ISODate("2017-07-21T09:47:16Z"),
                        "lastHeartbeat" : ISODate("2017-07-21T09:47:22.597Z"),
                        "lastHeartbeatRecv" : ISODate("2017-07-21T09:47:23.527Z"),
                        "pingMs" : NumberLong(0),
                        "syncingTo" : "192.168.7.50:27017",
                        "configVersion" : 1
                }
        ],
        "ok" : 1
}
jiessie:PRIMARY>

查看存储引擎

登录其中一个实例，这里以27017为例子，当出现mmapv1时，代表当前使用存储引擎为mmapv1

1
2
3

[root@iZuf6c08fdv8duubho2b0rZ ~]# /usr/local/mongodb1/bin/mongo --eval "db.serverStatus()"|grep name
                "name" : "mmapv1",
[root@iZuf6c08fdv8duubho2b0rZ ~]#

测试副本集

登录27017的主库，插入数据，在其他节点上查看

[root@iZuf6c08fdv8duubho2b0rZ ~]# /usr/local/mongodb1/bin/mongo 
MongoDB shell version v3.4.4
connecting to: mongodb://127.0.0.1:27017
MongoDB server version: 3.4.4
jiessie:PRIMARY> show dbs;
admin   0.078GB
local  12.072GB
jiessie:PRIMARY> use test11;
switched to db test11
jiessie:PRIMARY> db.tmp11.insert({"id":"11"});
WriteResult({ "nInserted" : 1 })
jiessie:PRIMARY> db.tmp11.find();
{ "_id" : ObjectId("5971ced335d36a3364107ac7"), "id" : "11" }
jiessie:PRIMARY> exit
bye
[root@iZuf6c08fdv8duubho2b0rZ ~]# /usr/local/mongodb1/bin/mongo localhost:28017
MongoDB shell version v3.4.4
connecting to: localhost:28017
MongoDB server version: 3.4.4
jiessie:SECONDARY> rs.slaveOk();
jiessie:SECONDARY> use test11;
switched to db test11
jiessie:SECONDARY> db.tmp11.find();
{ "_id" : ObjectId("5971ced335d36a3364107ac7"), "id" : "11" }
jiessie:SECONDARY> exit
bye
[root@iZuf6c08fdv8duubho2b0rZ ~]# /usr/local/mongodb1/bin/mongo localhost:29017
MongoDB shell version v3.4.4
connecting to: localhost:29017
MongoDB server version: 3.4.4
jiessie:SECONDARY> use test11;
switched to db test11
jiessie:SECONDARY> rs.slaveOk();
jiessie:SECONDARY> db.tmp11.find();
{ "_id" : ObjectId("5971ced335d36a3364107ac7"), "id" : "11" }
jiessie:SECONDARY> exit

迁移步骤

将29017的从节点设置为隐藏节点，不再成为主，对应用不可见
把29017的节点存储引擎从MMAPv1设置为WiredTiger，删除原MMAPv1数据，重新加入副本集中
当29017的WiredTiger节点从主节点初始化数据成功后，依次将其他从节点的引擎修改为的WiredTiger
当所有从节点的引擎修改为WiredTiger后，在主节点执行rs.stepdown()将主降级，触发在其他从节点选举新主
再将原主库按照以上的方式将引擎从MMAPv1设置为WiredTiger

详情请参考：http://docs.mongoing.com/manual-zh/tutorial/configure-a-hidden-replica-set-member.html

开始迁移

副本集的架构下，所有节点之间都是同步的，即使存储引擎不同也不受影响。
实验中的副本集端口分别为27017/28017/29017，其中主节点为27017，28017和29017为从节点。
27017权重为3，28017权重为2，29017权重为1。

步骤最后一项，将原主库引擎从MMAPv1设置为WiredTiger，可在原主库上执行rs.stepDown()触发选举，也可提高其他从节点的权重，需要高于主节点。

29017实例

设置节点隐藏

[root@iZuf6c08fdv8duubho2b0rZ ~]# /usr/local/mongodb1/bin/mongo
MongoDB shell version v3.4.4
connecting to: mongodb://127.0.0.1:27017
MongoDB server version: 3.4.4
jiessie:PRIMARY> cfg = rs.config();
{
        "_id" : "jiessie",
        "version" : 1,
        "protocolVersion" : NumberLong(1),
        "members" : [
                {
                        "_id" : 0,
                        "host" : "192.168.7.50:27017",
                        "arbiterOnly" : false,
                        "buildIndexes" : true,
                        "hidden" : false,
                        "priority" : 3,
                        "tags" : {

                        },
                        "slaveDelay" : NumberLong(0),
                        "votes" : 1
                },
                {
                        "_id" : 1,
                        "host" : "192.168.7.50:28017",
                        "arbiterOnly" : false,
                        "buildIndexes" : true,
                        "hidden" : false,
                        "priority" : 2,
                        "tags" : {

                        },
                        "slaveDelay" : NumberLong(0),
                        "votes" : 1
                },
                {
                        "_id" : 2,
                        "host" : "192.168.7.50:29017",
                        "arbiterOnly" : false,
                        "buildIndexes" : true,
                        "hidden" : false,
                        "priority" : 3,
                        "tags" : {

                        },
                        "slaveDelay" : NumberLong(0),
                        "votes" : 1
                }
        ],
        "settings" : {
                "chainingAllowed" : true,
                "heartbeatIntervalMillis" : 2000,
                "heartbeatTimeoutSecs" : 10,
                "electionTimeoutMillis" : 10000,
                "catchUpTimeoutMillis" : 2000,
                "getLastErrorModes" : {

                },
                "getLastErrorDefaults" : {
                        "w" : 1,
                        "wtimeout" : 0
                },
                "replicaSetId" : ObjectId("5971cd4835e57f0331e944c8")
        }
}
jiessie:PRIMARY> cfg.members[2].host;
192.168.7.50:29017
jiessie:PRIMARY> cfg.members[2].priority = 0;       #优化级降为0，不再参数选主
0
jiessie:PRIMARY> cfg.members[2].hidden = true;      #对应用隐藏
true
jiessie:PRIMARY> rs.reconfig(cfg);
{ "ok" : 1 }
jiessie:PRIMARY>

修改引擎，删除原数据

[root@iZuf6c08fdv8duubho2b0rZ ~]# /usr/local/mongodb1/bin/mongo localhost:29017
MongoDB shell version v3.4.4
connecting to: localhost:29017
MongoDB server version: 3.4.4
jiessie:SECONDARY> rs.slaveOk();
jiessie:SECONDARY> use admin;
switched to db admin
jiessie:SECONDARY> db.shutdownServer();                                                      #关闭服务
server should be down...
[root@iZuf6c08fdv8duubho2b0rZ ~]# rm -rf /hwdata/data/mongodb3/data/*                        #删除原引擎数据
[root@iZuf6c08fdv8duubho2b0rZ ~]# cat /hwdata/data/mongodb3/conf/mongodb.conf |grep sto      #查看原配置
storageEngine=mmapv1
[root@iZuf6c08fdv8duubho2b0rZ ~]# sed -i 's@storageEngine=mmapv1@storageEngine=wiredTiger@g' /hwdata/data/mongodb3/conf/mongodb.conf    #修改配置文件的引擎部分
[root@iZuf6c08fdv8duubho2b0rZ ~]# cat /hwdata/data/mongodb3/conf/mongodb.conf |grep sto      #查看新配置
storageEngine=wiredTiger
[root@iZuf6c08fdv8duubho2b0rZ ~]# /usr/local/mongodb3/bin/mongod -f /hwdata/data/mongodb3/conf/mongodb.conf     #启动服务
note: noprealloc may hurt performance in many applications
about to fork child process, waiting until server is ready for connections.
forked process: 613
child process started successfully, parent exiting
[root@iZuf6c08fdv8duubho2b0rZ ~]# /usr/local/mongodb3/bin/mongo localhost:29017 --eval "db.serverStatus()"|grep '"name"'    #验证是否修改成功
                "name" : "wiredTiger",
[root@iZuf6c08fdv8duubho2b0rZ ~]# /usr/local/mongodb3/bin/mongo localhost:29017
MongoDB shell version v3.4.4
connecting to: localhost:29017
MongoDB server version: 3.4.4
jiessie:SECONDARY> exit                     #验证初始化同步是否成功，SECONDARY状态表示已经是从节点

加入副本集

[root@iZuf6c08fdv8duubho2b0rZ ~]# /usr/local/mongodb1/bin/mongo
MongoDB shell version v3.4.4
connecting to: mongodb://127.0.0.1:27017
MongoDB server version: 3.4.4
jiessie:PRIMARY> cfg = rs.config();
{
        "_id" : "jiessie",
        "version" : 2,
        "protocolVersion" : NumberLong(1),
        "members" : [
                {
                        "_id" : 0,
                        "host" : "192.168.7.50:27017",
                        "arbiterOnly" : false,
                        "buildIndexes" : true,
                        "hidden" : false,
                        "priority" : 3,
                        "tags" : {

                        },
                        "slaveDelay" : NumberLong(0),
                        "votes" : 1
                },
                {
                        "_id" : 1,
                        "host" : "192.168.7.50:28017",
                        "arbiterOnly" : false,
                        "buildIndexes" : true,
                        "hidden" : false,
                        "priority" : 2,
                        "tags" : {

                        },
                        "slaveDelay" : NumberLong(0),
                        "votes" : 1
                },
                {
                        "_id" : 2,
                        "host" : "192.168.7.50:29017",
                        "arbiterOnly" : false,
                        "buildIndexes" : true,
                        "hidden" : true,
                        "priority" : 0,
                        "tags" : {

                        },
                        "slaveDelay" : NumberLong(0),
                        "votes" : 1
                }
        ],
        "settings" : {
                "chainingAllowed" : true,
                "heartbeatIntervalMillis" : 2000,
                "heartbeatTimeoutSecs" : 10,
                "electionTimeoutMillis" : 10000,
                "catchUpTimeoutMillis" : 2000,
                "getLastErrorModes" : {

                },
                "getLastErrorDefaults" : {
                        "w" : 1,
                        "wtimeout" : 0
                },
                "replicaSetId" : ObjectId("5971cd4835e57f0331e944c8")
        }
}
jiessie:PRIMARY> cfg.members[2].hidden=false;       #加入副本集，对应用可见，其中[2]表示29017端口
false
jiessie:PRIMARY> cfg.members[2].priority = 1;       #修改为之前的优先级
1
jiessie:PRIMARY> rs.reconfig(cfg);
{ "ok" : 1 }
jiessie:PRIMARY>

28017实例

设置节点隐藏

[root@iZuf6c08fdv8duubho2b0rZ ~]# /usr/local/mongodb1/bin/mongo
MongoDB shell version v3.4.4
connecting to: mongodb://127.0.0.1:27017
MongoDB server version: 3.4.4
jiessie:PRIMARY> cfg = rs.config();
{
        "_id" : "jiessie",
        "version" : 4,
        "protocolVersion" : NumberLong(1),
        "members" : [
                {
                        "_id" : 0,
                        "host" : "192.168.7.50:27017",
                        "arbiterOnly" : false,
                        "buildIndexes" : true,
                        "hidden" : false,
                        "priority" : 3,
                        "tags" : {

                        },
                        "slaveDelay" : NumberLong(0),
                        "votes" : 1
                },
                {
                        "_id" : 1,
                        "host" : "192.168.7.50:28017",
                        "arbiterOnly" : false,
                        "buildIndexes" : true,
                        "hidden" : false,
                        "priority" : 2,
                        "tags" : {

                        },
                        "slaveDelay" : NumberLong(0),
                        "votes" : 1
                },
                {
                        "_id" : 2,
                        "host" : "192.168.7.50:29017",
                        "arbiterOnly" : false,
                        "buildIndexes" : true,
                        "hidden" : false,
                        "priority" : 1,
                        "tags" : {

                        },
                        "slaveDelay" : NumberLong(0),
                        "votes" : 1
                }
        ],
        "settings" : {
                "chainingAllowed" : true,
                "heartbeatIntervalMillis" : 2000,
                "heartbeatTimeoutSecs" : 10,
                "electionTimeoutMillis" : 10000,
                "catchUpTimeoutMillis" : 2000,
                "getLastErrorModes" : {

                },
                "getLastErrorDefaults" : {
                        "w" : 1,
                        "wtimeout" : 0
                },
                "replicaSetId" : ObjectId("5971cd4835e57f0331e944c8")
        }
}
jiessie:PRIMARY> cfg.members[1].host
192.168.7.50:28017
jiessie:PRIMARY> cfg.members[1].priority = 0;
0
jiessie:PRIMARY> cfg.members[1].hidden = true;
true
jiessie:PRIMARY> rs.reconfig(cfg);
{ "ok" : 1 }
jiessie:PRIMARY>

修改引擎，删除原数据

[root@iZuf6c08fdv8duubho2b0rZ ~]# /usr/local/mongodb1/bin/mongo localhost:28017
MongoDB shell version v3.4.4
connecting to: localhost:28017
MongoDB server version: 3.4.4
jiessie:SECONDARY> rs.slaveOk();
jiessie:SECONDARY> use admin;
switched to db admin
jiessie:SECONDARY> db.shutdownServer();                                                      #关闭服务
server should be down...
[root@iZuf6c08fdv8duubho2b0rZ ~]# rm -rf /hwdata/data/mongodb2/data/*                        #删除原引擎数据
[root@iZuf6c08fdv8duubho2b0rZ ~]# cat /hwdata/data/mongodb2/conf/mongodb.conf |grep sto      #查看原配置
storageEngine=mmapv1
[root@iZuf6c08fdv8duubho2b0rZ ~]# sed -i 's@storageEngine=mmapv1@storageEngine=wiredTiger@g' /hwdata/data/mongodb2/conf/mongodb.conf    #修改配置文件的引擎部分
[root@iZuf6c08fdv8duubho2b0rZ ~]# cat /hwdata/data/mongodb2/conf/mongodb.conf |grep sto      #查看新配置
storageEngine=wiredTiger
[root@iZuf6c08fdv8duubho2b0rZ ~]# /usr/local/mongodb2/bin/mongod -f /hwdata/data/mongodb2/conf/mongodb.conf     #启动服务
note: noprealloc may hurt performance in many applications
about to fork child process, waiting until server is ready for connections.
forked process: 1275
child process started successfully, parent exiting
[root@iZuf6c08fdv8duubho2b0rZ ~]# /usr/local/mongodb2/bin/mongo localhost:28017 --eval "db.serverStatus()"|grep '"name"'    #验证是否修改成功
                "name" : "wiredTiger",
[root@iZuf6c08fdv8duubho2b0rZ ~]# /usr/local/mongodb2/bin/mongo localhost:28017
MongoDB shell version v3.4.4
connecting to: localhost:28017
MongoDB server version: 3.4.4
jiessie:SECONDARY> exit                     #验证初始化同步是否成功，SECONDARY状态表示已经是从节点

加入副本集

[root@iZuf6c08fdv8duubho2b0rZ ~]# /usr/local/mongodb1/bin/mongo 
MongoDB shell version v3.4.4
connecting to: mongodb://127.0.0.1:27017
MongoDB server version: 3.4.4
jiessie:PRIMARY> cfg = rs.config();
{
        "_id" : "jiessie",
        "version" : 5,
        "protocolVersion" : NumberLong(1),
        "members" : [
                {
                        "_id" : 0,
                        "host" : "192.168.7.50:27017",
                        "arbiterOnly" : false,
                        "buildIndexes" : true,
                        "hidden" : false,
                        "priority" : 3,
                        "tags" : {

                        },
                        "slaveDelay" : NumberLong(0),
                        "votes" : 1
                },
                {
                        "_id" : 1,
                        "host" : "192.168.7.50:28017",
                        "arbiterOnly" : false,
                        "buildIndexes" : true,
                        "hidden" : true,
                        "priority" : 0,
                        "tags" : {

                        },
                        "slaveDelay" : NumberLong(0),
                        "votes" : 1
                },
                {
                        "_id" : 2,
                        "host" : "192.168.7.50:29017",
                        "arbiterOnly" : false,
                        "buildIndexes" : true,
                        "hidden" : false,
                        "priority" : 1,
                        "tags" : {

                        },
                        "slaveDelay" : NumberLong(0),
                        "votes" : 1
                }
        ],
        "settings" : {
                "chainingAllowed" : true,
                "heartbeatIntervalMillis" : 2000,
                "heartbeatTimeoutSecs" : 10,
                "electionTimeoutMillis" : 10000,
                "catchUpTimeoutMillis" : 2000,
                "getLastErrorModes" : {

                },
                "getLastErrorDefaults" : {
                        "w" : 1,
                        "wtimeout" : 0
                },
                "replicaSetId" : ObjectId("5971cd4835e57f0331e944c8")
        }
}
jiessie:PRIMARY> cfg.members[1].hidden = false;     #加入副本集，对应用可见，其中[1]表示28017端口
false
jiessie:PRIMARY> cfg.members[1].priority = 2;       #修改为之前的优先级
2
jiessie:PRIMARY> rs.reconfig(cfg);
{ "ok" : 1 }
jiessie:PRIMARY>

27017实例

设置节点隐藏

[root@iZuf6c08fdv8duubho2b0rZ ~]# /usr/local/mongodb1/bin/mongo
MongoDB shell version v3.4.4
connecting to: mongodb://127.0.0.1:27017
MongoDB server version: 3.4.4
jiessie:PRIMARY> 
jiessie:PRIMARY> rs.stepDown();
2017-07-22T21:51:26.392+0800 E QUERY    [thread1] Error: error doing query: failed: network error while attempting to run command 'replSetStepDown' on host '127.0.0.1:27017'  :
DB.prototype.runCommand@src/mongo/shell/db.js:132:1
DB.prototype.adminCommand@src/mongo/shell/db.js:150:16
rs.stepDown@src/mongo/shell/utils.js:1261:12
@(shell):1:1
2017-07-22T21:51:26.394+0800 I NETWORK  [thread1] trying reconnect to 127.0.0.1:27017 (127.0.0.1) failed
2017-07-22T21:51:26.394+0800 I NETWORK  [thread1] reconnect 127.0.0.1:27017 (127.0.0.1) ok
jiessie:SECONDARY> rs.slaveOk();
jiessie:SECONDARY> rs.status();                     #等待几秒，查看状态，此时28017实例是已经提升为新主库
{
        "set" : "jiessie",
        "date" : ISODate("2017-07-22T13:51:34.336Z"),
        "myState" : 2,
        "term" : NumberLong(12),
        "heartbeatIntervalMillis" : NumberLong(2000),
        "optimes" : {
                "lastCommittedOpTime" : {
                        "ts" : Timestamp(1500731480, 1),
                        "t" : NumberLong(12)
                },
                "appliedOpTime" : {
                        "ts" : Timestamp(1500731480, 1),
                        "t" : NumberLong(12)
                },
                "durableOpTime" : {
                        "ts" : Timestamp(1500731480, 1),
                        "t" : NumberLong(12)
                }
        },
        "members" : [
                {
                        "_id" : 0,
                        "name" : "192.168.7.50:27017",
                        "health" : 1,
                        "state" : 2,
                        "stateStr" : "SECONDARY",
                        "uptime" : 102106,
                        "optime" : {
                                "ts" : Timestamp(1500731480, 1),
                                "t" : NumberLong(12)
                        },
                        "optimeDate" : ISODate("2017-07-22T13:51:20Z"),
                        "infoMessage" : "could not find member to sync from",
                        "configVersion" : 6,
                        "self" : true
                },
                {
                        "_id" : 1,
                        "name" : "192.168.7.50:28017",
                        "health" : 1,
                        "state" : 2,
                        "stateStr" : "SECONDARY",
                        "uptime" : 2403,
                        "optime" : {
                                "ts" : Timestamp(1500731480, 1),
                                "t" : NumberLong(12)
                        },
                        "optimeDurable" : {
                                "ts" : Timestamp(1500731480, 1),
                                "t" : NumberLong(12)
                        },
                        "optimeDate" : ISODate("2017-07-22T13:51:20Z"),
                        "optimeDurableDate" : ISODate("2017-07-22T13:51:20Z"),
                        "lastHeartbeat" : ISODate("2017-07-22T13:51:31.557Z"),
                        "lastHeartbeatRecv" : ISODate("2017-07-22T13:51:33.176Z"),
                        "pingMs" : NumberLong(0),
                        "syncingTo" : "192.168.7.50:29017",
                        "configVersion" : 6
                },
                {
                        "_id" : 2,
                        "name" : "192.168.7.50:29017",
                        "health" : 1,
                        "state" : 2,
                        "stateStr" : "SECONDARY",
                        "uptime" : 4846,
                        "optime" : {
                                "ts" : Timestamp(1500731480, 1),
                                "t" : NumberLong(12)
                        },
                        "optimeDurable" : {
                                "ts" : Timestamp(1500731480, 1),
                                "t" : NumberLong(12)
                        },
                        "optimeDate" : ISODate("2017-07-22T13:51:20Z"),
                        "optimeDurableDate" : ISODate("2017-07-22T13:51:20Z"),
                        "lastHeartbeat" : ISODate("2017-07-22T13:51:31.557Z"),
                        "lastHeartbeatRecv" : ISODate("2017-07-22T13:51:30.019Z"),
                        "pingMs" : NumberLong(0),
                        "configVersion" : 6
                }
        ],
        "ok" : 1
}
jiessie:SECONDARY> exit
bye
[root@iZuf6c08fdv8duubho2b0rZ ~]# /usr/local/mongodb1/bin/mongo localhost:28017     #登录新主库，原27017实例修改实例引擎
MongoDB shell version v3.4.4
connecting to: localhost:28017
MongoDB server version: 3.4.4
jiessie:PRIMARY> cfg = rs.config();
{
        "_id" : "jiessie",
        "version" : 6,
        "protocolVersion" : NumberLong(1),
        "members" : [
                {
                        "_id" : 0,
                        "host" : "192.168.7.50:27017",
                        "arbiterOnly" : false,
                        "buildIndexes" : true,
                        "hidden" : false,
                        "priority" : 3,
                        "tags" : {

                        },
                        "slaveDelay" : NumberLong(0),
                        "votes" : 1
                },
                {
                        "_id" : 1,
                        "host" : "192.168.7.50:28017",
                        "arbiterOnly" : false,
                        "buildIndexes" : true,
                        "hidden" : false,
                        "priority" : 2,
                        "tags" : {

                        },
                        "slaveDelay" : NumberLong(0),
                        "votes" : 1
                },
                {
                        "_id" : 2,
                        "host" : "192.168.7.50:29017",
                        "arbiterOnly" : false,
                        "buildIndexes" : true,
                        "hidden" : false,
                        "priority" : 1,
                        "tags" : {

                        },
                        "slaveDelay" : NumberLong(0),
                        "votes" : 1
                }
        ],
        "settings" : {
                "chainingAllowed" : true,
                "heartbeatIntervalMillis" : 2000,
                "heartbeatTimeoutSecs" : 10,
                "electionTimeoutMillis" : 10000,
                "catchUpTimeoutMillis" : 2000,
                "getLastErrorModes" : {

                },
                "getLastErrorDefaults" : {
                        "w" : 1,
                        "wtimeout" : 0
                },
                "replicaSetId" : ObjectId("5971cd4835e57f0331e944c8")
    essie:PRIMARY> cfg.members[0].priority = 0;
0
jiessie:PRIMARY> cfg.members[0].hidden = true;
true
jiessie:PRIMARY> rs.reconfig(cfg);
{ "ok" : 1 }
jiessie:PRIMARY>

修改引擎，删除原数据

[root@iZuf6c08fdv8duubho2b0rZ ~]# /usr/local/mongodb1/bin/mongo localhost:27017
MongoDB shell version v3.4.4
connecting to: localhost:27017
MongoDB server version: 3.4.4
jiessie:SECONDARY> rs.slaveOk();
jiessie:SECONDARY> use admin;
switched to db admin
jiessie:SECONDARY> db.shutdownServer();                                                      #关闭服务
server should be down...
[root@iZuf6c08fdv8duubho2b0rZ ~]# rm -rf /hwdata/data/mongodb1/data/*                        #删除原引擎数据
[root@iZuf6c08fdv8duubho2b0rZ ~]# cat /hwdata/data/mongodb1/conf/mongodb.conf |grep sto      #查看原配置
storageEngine=mmapv1
[root@iZuf6c08fdv8duubho2b0rZ ~]# sed -i 's@storageEngine=mmapv1@storageEngine=wiredTiger@g' /hwdata/data/mongodb1/conf/mongodb.conf    #修改配置文件的引擎部分
[root@iZuf6c08fdv8duubho2b0rZ ~]# cat /hwdata/data/mongodb2/conf/mongodb.conf |grep sto      #查看新配置
storageEngine=wiredTiger
[root@iZuf6c08fdv8duubho2b0rZ ~]# /usr/local/mongodb1/bin/mongod -f /hwdata/data/mongodb1/conf/mongodb.conf     #启动服务
note: noprealloc may hurt performance in many applications
about to fork child process, waiting until server is ready for connections.
forked process: 2157
child process started successfully, parent exiting
[root@iZuf6c08fdv8duubho2b0rZ ~]# /usr/local/mongodb1/bin/mongo localhost:27017 --eval "db.serverStatus()"|grep '"name"'    #验证是否修改成功
                "name" : "wiredTiger",
[root@iZuf6c08fdv8duubho2b0rZ ~]# /usr/local/mongodb1/bin/mongo localhost:27017
MongoDB shell version v3.4.4
connecting to: localhost:27017
MongoDB server version: 3.4.4
jiessie:SECONDARY> exit                     #验证初始化同步是否成功，SECONDARY状态表示已经是从节点

加入副本集

[root@iZuf6c08fdv8duubho2b0rZ ~]# /usr/local/mongodb1/bin/mongo localhost:28017
MongoDB shell version v3.4.4
connecting to: mongodb://127.0.0.1:27017
MongoDB server version: 3.4.4
jiessie:PRIMARY> cfg = rs.config();
{
        "_id" : "jiessie",
        "version" : 7,
        "protocolVersion" : NumberLong(1),
        "members" : [
                {
                        "_id" : 0,
                        "host" : "192.168.7.50:27017",
                        "arbiterOnly" : false,
                        "buildIndexes" : true,
                        "hidden" : true,
                        "priority" : 0,
                        "tags" : {

                        },
                        "slaveDelay" : NumberLong(0),
                        "votes" : 1
                },
                {
                        "_id" : 1,
                        "host" : "192.168.7.50:28017",
                        "arbiterOnly" : false,
                        "buildIndexes" : true,
                        "hidden" : false,
                        "priority" : 2,
                        "tags" : {

                        },
                        "slaveDelay" : NumberLong(0),
                        "votes" : 1
                },
                {
                        "_id" : 2,
                        "host" : "192.168.7.50:29017",
                        "arbiterOnly" : false,
                        "buildIndexes" : true,
                        "hidden" : false,
                        "priority" : 1,
                        "tags" : {

                        },
                        "slaveDelay" : NumberLong(0),
                        "votes" : 1
                }
        ],
        "settings" : {
                "chainingAllowed" : true,
                "heartbeatIntervalMillis" : 2000,
                "heartbeatTimeoutSecs" : 10,
                "electionTimeoutMillis" : 10000,
                "catchUpTimeoutMillis" : 2000,
                "getLastErrorModes" : {

                },
                "getLastErrorDefaults" : {
                        "w" : 1,
                        "wtimeout" : 0
                },
                "replicaSetId" : ObjectId("5971cd4835e57f0331e944c8")
        }
}
jiessie:PRIMARY> cfg.members[0].hidden = false;     #加入副本集，对应用可见，其中[0]表示27017端口
false
jiessie:PRIMARY> cfg.members[0].priority = 3;       #修改为之前的优先级
3
jiessie:PRIMARY> rs.reconfig(cfg);
{ "ok" : 1 }
jiessie:SECONDARY>                                  #等待几秒后，PRIMARY会变成SECONDARY

结束语

mongodb作为发展迅速的开源数据库一员，版本迭代升级很快，目前使用旧引擎的公司不在小数，新引擎的优点足以促使其升级引擎。

使用Percona Data Recovery Tool for InnoDB恢复数据

发表于 2017-07-14 | 分类于备份恢复

字数统计: | 阅读时长 ≈

前言

数据是一个企业最重要的资产，备份的重要性不言而喻，有效的备份能够减少意外情况下的公司损失。当使用MySQL数据库时，mysqldump、mysqlpump、mydumper、xtrabackup等都是不错的备份工具，极端情况下，当开启了binlog，也可以成为恢复的救命稻草。但是，还有存在无备份，没有开启binlog的情况，如果是InnoDB表，这时percona推出的Percona Data Recovery Tool工具可以派上用场。

注意事项

此工具仅支持InnoDB/XtraDB表，不支持MyISAM表
此工具可恢复数据文件的副本，并不要求是正在运行的MySQL服务器
此工具并不保证数据一定可恢复，如数据被覆盖
支持的误操作类型：DELETE、TRUNCATE，表格式为Compact
在文件系统级别删除的文件
如数据文件损失，此工具也无法恢复

原理

通过尝试在页面中找到有效的行来工作，InnoDB的数据都是索引的方式组织的，所有的数据都是存储在16KB的数据块中，每个页面都有属于特定表的特定索引。恢复时分解所有的数据文件为单个16kb大小的页面，根据每个页面的标记的数据起点开始尝试匹配，如果与给定表定义的属于匹配，则输出匹配记录。

表空间

当服务器设置了innodb_file_per_table=1时，工具不需要再额外的从ibdata1中拆分数据，所有的数据都保存在ibd文件中。
当服务器设置了innodb_file_per_table=0时，工具需要先使用page_parser，把数据文件从ibdata1中拆分出来，再按照规则匹配记录。

准备

操作系统：centos 6.5 x86_64

安装依赖

1	yum -y install gcc gcc-c++ glibc glibc-static perl-DBI perl-DBD-MySQL

安装Percona Data Recovery Tool

[root@iZuf6c08fdv8duubho2b0rZ ~]# cd /usr/src/
[root@iZuf6c08fdv8duubho2b0rZ src]# wget https://launchpad.net/percona-data-recovery-tool-for-innodb/trunk/release-0.5/+download/percona-data-recovery-tool-for-innodb-0.5.tar.gz --no-check-certificate 
[root@iZuf6c08fdv8duubho2b0rZ src]# tar -zxf percona-data-recovery-tool-for-innodb-0.5.tar.gz 
[root@iZuf6c08fdv8duubho2b0rZ src]# cd percona-data-recovery-tool-for-innodb-0.5/mysql-source/
[root@iZuf6c08fdv8duubho2b0rZ mysql-source]# ./configure 
[root@iZuf6c08fdv8duubho2b0rZ mysql-source]# cd ..
[root@iZuf6c08fdv8duubho2b0rZ percona-data-recovery-tool-for-innodb-0.5]# make
[root@iZuf6c08fdv8duubho2b0rZ percona-data-recovery-tool-for-innodb-0.5]# ll
总用量 3168
-rw-r--r--  1  510 wheel    6269 8月  28 2011 check_data.c
-rwxr-xr-x  1 root root   748761 7月  17 09:06 constraints_parser
-rw-r--r--  1  510 wheel   22172 8月  28 2011 constraints_parser.c
-rwxr-xr-x  1  510 wheel   12051 8月  28 2011 create_defs.pl
drwxr-xr-x  2  510 wheel    4096 8月  28 2011 docs
-rwxr-xr-x  1  510 wheel    1978 8月  28 2011 fetch_data.sh
-rwxr-xr-x  1 root root   979051 7月  17 09:06 ibdconnect
-rw-r--r--  1  510 wheel   12200 8月  28 2011 ibdconnect.c
drwxr-xr-x  2  510 wheel    4096 8月  28 2011 include
-rw-r--r--  1  510 wheel    8262 8月  28 2011 incrementalupdate.c
-rwxr-xr-x  1 root root    14873 7月  17 09:06 innochecksum
-rw-r--r--  1  510 wheel    9117 8月  28 2011 innochecksum.c
-rw-r--r--  1  510 wheel      74 8月  28 2011 INSTALL
drwxr-xr-x  2 root root     4096 7月  17 09:06 lib
-rw-r--r--  1  510 wheel    2676 8月  28 2011 Makefile
drwxr-xr-x 40  510 wheel    4096 7月  17 09:06 mysql-source
-rwxr-xr-x  1 root root  1346155 7月  17 09:06 page_parser
-rw-r--r--  1  510 wheel   15239 8月  28 2011 page_parser.c
-rw-r--r--  1  510 wheel   10608 8月  28 2011 print_data.c
-rwxr-xr-x  1  510 wheel     302 8月  28 2011 split_dump.pl
-rw-r--r--  1  510 wheel    2046 8月  28 2011 tables_dict.c

恢复实战(delete误操作)

innodb_file_per_table=1

模拟数据被误删除

[root@iZuf6c08fdv8duubho2b0rZ percona-data-recovery-tool-for-innodb-0.5]# mysql -uroot -p123456
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 35514
Server version: 5.6.36-82.0-log Source distribution

Copyright (c) 2009-2017 Percona LLC and/or its affiliates
Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MySQL [(none)] 09:25:34 > create database test111;
Query OK, 1 row affected (0.00 sec)

MySQL [(none)] 09:25:40 > use test111;
Database changed
MySQL [test111] 09:25:43 > create table t222(id int unsigned not null auto_increment,name varchar(20),age int,primary key(`id`),key idx_age(`age`));   
Query OK, 0 rows affected (0.02 sec)

MySQL [test111] 09:25:51 > insert into t222 values(null,'jiessie',18);           
Query OK, 1 row affected (0.00 sec)

MySQL [test111] 09:26:00 > insert into t222 (name,age) select name,age from t222;     
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

MySQL [test111] 09:26:10 > insert into t222 (name,age) select name,age from t222;
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

MySQL [test111] 09:26:11 > insert into t222 (name,age) select name,age from t222;
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

MySQL [test111] 09:26:11 > insert into t222 (name,age) select name,age from t222;
Query OK, 8 rows affected (0.00 sec)
Records: 8  Duplicates: 0  Warnings: 0

MySQL [test111] 09:26:12 > insert into t222 (name,age) select name,age from t222;
Query OK, 16 rows affected (0.00 sec)
Records: 16  Duplicates: 0  Warnings: 0

MySQL [test111] 09:26:12 > insert into t222 (name,age) select name,age from t222;
Query OK, 32 rows affected (0.00 sec)
Records: 32  Duplicates: 0  Warnings: 0

MySQL [test111] 09:26:12 > insert into t222 (name,age) select name,age from t222;
Query OK, 64 rows affected (0.00 sec)
Records: 64  Duplicates: 0  Warnings: 0

MySQL [test111] 09:26:13 > insert into t222 (name,age) select name,age from t222;
Query OK, 128 rows affected (0.01 sec)
Records: 128  Duplicates: 0  Warnings: 0

MySQL [test111] 09:26:13 > insert into t222 (name,age) select name,age from t222;
Query OK, 256 rows affected (0.00 sec)
Records: 256  Duplicates: 0  Warnings: 0

MySQL [test111] 09:26:14 > insert into t222 (name,age) select name,age from t222;
Query OK, 512 rows affected (0.01 sec)
Records: 512  Duplicates: 0  Warnings: 0

MySQL [test111] 09:26:14 > select count(*) from t222;
+----------+
| count(*) |
+----------+
|     1024 |
+----------+
1 row in set (0.00 sec)

MySQL [test111] 09:26:21 > exit
Bye
[root@iZuf6c08fdv8duubho2b0rZ percona-data-recovery-tool-for-innodb-0.5]# mysql -uroot -p123456 test111 -e "delete from t222;select count(*) from t222;" && cp /hwdata/data/percona/test111/t222.* /tmp/
Warning: Using a password on the command line interface can be insecure.
+----------+
| count(*) |
+----------+
|        0 |
+----------+

解析ibd文件

实战mysql安装在/usr/local/percona，数据目录在/hwdata/data/percona，注意保存数据文件。
此过程会将表的idb文件解析为很多的page，innodb的page分为两大部分，一部分一级索引部分（primary key），另一部分为二级索引部分（secondary key），所以解析出来的ibd包括了主键数据和索引数据两大部分（如果该表有多个二级索引，则会生成多个文件

[root@iZuf6c08fdv8duubho2b0rZ percona-data-recovery-tool-for-innodb-0.5]# ll -t /tmp/t222*
-rw-r----- 1 root root 163840 7月  17 09:26 /tmp/t222.ibd
-rw-r----- 1 root root   8614 7月  17 09:26 /tmp/t222.frm
[root@iZuf6c08fdv8duubho2b0rZ percona-data-recovery-tool-for-innodb-0.5]# ./page_parser -5 -f /tmp/t222.ibd 
Opening file: /tmp/t222.ibd:
64513           ID of device containing file
1597422         inode number
33184           protection
1               number of hard links
0               user ID of owner
0               group ID of owner
0               device ID (if special file)
163840          total size, in bytes
4096            blocksize for filesystem I/O
320             number of blocks allocated
1500254806      time of last access
1500254806      time of last modification
1500254806      time of last status change
163840  Size to process in bytes
104857600       Disk cache size in bytes
19.97% done. 2017-07-17 09:27:37 ETA(in 00:00 hours). Processing speed: 32723 B/sec
[root@iZuf6c08fdv8duubho2b0rZ percona-data-recovery-tool-for-innodb-0.5]# ll -t
总用量 3172
drwxr-xr-x  3 root root     4096 7月  17 09:27 pages-1500254852
-rwxr-xr-x  1 root root  1346155 7月  17 09:21 page_parser
-rwxr-xr-x  1 root root   748729 7月  17 09:21 constraints_parser
drwxr-xr-x  2 root root     4096 7月  17 09:21 lib
-rwxr-xr-x  1 root root   979051 7月  17 09:06 ibdconnect
-rwxr-xr-x  1 root root    14873 7月  17 09:06 innochecksum
drwxr-xr-x 40  510 wheel    4096 7月  17 09:06 mysql-source
drwxr-xr-x  2  510 wheel    4096 8月  28 2011 docs
drwxr-xr-x  2  510 wheel    4096 8月  28 2011 include
-rw-r--r--  1  510 wheel    6269 8月  28 2011 check_data.c
-rw-r--r--  1  510 wheel   22172 8月  28 2011 constraints_parser.c
-rwxr-xr-x  1  510 wheel   12051 8月  28 2011 create_defs.pl
-rwxr-xr-x  1  510 wheel    1978 8月  28 2011 fetch_data.sh
-rw-r--r--  1  510 wheel   12200 8月  28 2011 ibdconnect.c
-rw-r--r--  1  510 wheel    8262 8月  28 2011 incrementalupdate.c
-rw-r--r--  1  510 wheel    9117 8月  28 2011 innochecksum.c
-rw-r--r--  1  510 wheel      74 8月  28 2011 INSTALL
-rw-r--r--  1  510 wheel    2676 8月  28 2011 Makefile
-rw-r--r--  1  510 wheel   15239 8月  28 2011 page_parser.c
-rw-r--r--  1  510 wheel   10608 8月  28 2011 print_data.c
-rwxr-xr-x  1  510 wheel     302 8月  28 2011 split_dump.pl
-rw-r--r--  1  510 wheel    2046 8月  28 2011 tables_dict.c

参数解释：
-5：代表 row format为Compact mysql5.0后的版本，-4 代表mysql5.0前版本
-f：代表要解析的文件

结果分析

从下面的解析后的结果看到，当前目录多了pages-1500254852，其中39为主键索引的index_id,40为二级索引的index_id,该id可以通过开启innodb_table_monitor查看

[root@iZuf6c08fdv8duubho2b0rZ percona-data-recovery-tool-for-innodb-0.5]# ll pages-1500254852/FIL_PAGE_INDEX/0-*
pages-1500254852/FIL_PAGE_INDEX/0-39:
总用量 64
-rw-r--r-- 1 root root 16384 7月  17 09:27 0-00000003.page
-rw-r--r-- 1 root root 16384 7月  17 09:27 2-00000005.page
-rw-r--r-- 1 root root 16384 7月  17 09:27 3-00000006.page
-rw-r--r-- 1 root root 16384 7月  17 09:27 4-00000007.page

pages-1500254852/FIL_PAGE_INDEX/0-40:
总用量 16
-rw-r--r-- 1 root root 16384 7月  17 09:27 1-00000004.page

生成表定义

由于该工具在解析数据pages的时候，需要获得该table的表结构定义，所以需要执行如下命令。

[root@iZuf6c08fdv8duubho2b0rZ percona-data-recovery-tool-for-innodb-0.5]# ./create_defs.pl --host localhost --user root --password 123456 --db test111 --table t222 > include/table_defs.h
[root@iZuf6c08fdv8duubho2b0rZ percona-data-recovery-tool-for-innodb-0.5]# cat include/table_defs.h
#ifndef table_defs_h
#define table_defs_h

// Table definitions
table_def_t table_definitions[] = {
        {
                name: "t222",
                {
                        { /* int(10) unsigned */
                                name: "id",
                                type: FT_UINT,
                                fixed_length: 4,

                                has_limits: FALSE,
                                limits: {
                                        can_be_null: FALSE,
                                        uint_min_val: 0,
                                        uint_max_val: 4294967295ULL
                                },

                                can_be_null: FALSE
                        },
                        { /*  */
                                name: "DB_TRX_ID",
                                type: FT_INTERNAL,
                                fixed_length: 6,

                                can_be_null: FALSE
                        },
                        { /*  */
                                name: "DB_ROLL_PTR",
                                type: FT_INTERNAL,
                                fixed_length: 7,

                                can_be_null: FALSE
                        },
                        { /* varchar(20) */
                                name: "name",
                                type: FT_CHAR,
                                min_length: 0,
                                max_length: 60,

                                has_limits: FALSE,
                                limits: {
                                        can_be_null: TRUE,
                                        char_min_len: 0,
                                        char_max_len: 60,
                                        char_ascii_only: TRUE
                                },

                                can_be_null: TRUE
                        },
                        { /* int(11) */
                                name: "age",
                                type: FT_INT,
                                fixed_length: 4,

                                has_limits: FALSE,
                                limits: {
                                        can_be_null: TRUE,
                                        int_min_val: -2147483648LL,
                                        int_max_val: 2147483647LL
                                },

                                can_be_null: TRUE
                        },
                        { type: FT_NONE }
                }
        },
};

#endif
[root@iZuf6c08fdv8duubho2b0rZ percona-data-recovery-tool-for-innodb-0.5]# 
[root@iZuf6c08fdv8duubho2b0rZ percona-data-recovery-tool-for-innodb-0.5]# make
gcc -DHAVE_OFFSET64_T -D_FILE_OFFSET_BITS=64 -D_LARGEFILE64_SOURCE=1 -D_LARGEFILE_SOURCE=1 -Wall -O3 -g -I include -I mysql-source/include -I mysql-source/innobase/include -c tables_dict.c -o lib/tables_dict.o
gcc -DHAVE_OFFSET64_T -D_FILE_OFFSET_BITS=64 -D_LARGEFILE64_SOURCE=1 -D_LARGEFILE_SOURCE=1 -Wall -O3 -g -I include -I mysql-source/include -I mysql-source/innobase/include -c check_data.c -o lib/check_data.o
gcc -DHAVE_OFFSET64_T -D_FILE_OFFSET_BITS=64 -D_LARGEFILE64_SOURCE=1 -D_LARGEFILE_SOURCE=1 -Wall -O3 -g -I include -I mysql-source/include -I mysql-source/innobase/include -o constraints_parser constraints_parser.c lib/tables_dict.o lib/print_data.o lib/check_data.o lib/libut.a lib/libmystrings.a
gcc -DHAVE_OFFSET64_T -D_FILE_OFFSET_BITS=64 -D_LARGEFILE64_SOURCE=1 -D_LARGEFILE_SOURCE=1 -Wall -O3 -g -I include -I mysql-source/include -I mysql-source/innobase/include -static -lrt -o page_parser page_parser.c lib/tables_dict.o lib/libut.a 
[root@iZuf6c08fdv8duubho2b0rZ percona-data-recovery-tool-for-innodb-0.5]# make
gcc -DHAVE_OFFSET64_T -D_FILE_OFFSET_BITS=64 -D_LARGEFILE64_SOURCE=1 -D_LARGEFILE_SOURCE=1 -Wall -O3 -g -I include -I mysql-source/include -I mysql-source/innobase/include -c tables_dict.c -o lib/tables_dict.o
gcc -DHAVE_OFFSET64_T -D_FILE_OFFSET_BITS=64 -D_LARGEFILE64_SOURCE=1 -D_LARGEFILE_SOURCE=1 -Wall -O3 -g -I include -I mysql-source/include -I mysql-source/innobase/include -c print_data.c -o lib/print_data.o 
gcc -DHAVE_OFFSET64_T -D_FILE_OFFSET_BITS=64 -D_LARGEFILE64_SOURCE=1 -D_LARGEFILE_SOURCE=1 -Wall -O3 -g -I include -I mysql-source/include -I mysql-source/innobase/include -o constraints_parser constraints_parser.c lib/tables_dict.o lib/print_data.o lib/check_data.o lib/libut.a lib/libmystrings.a
gcc -DHAVE_OFFSET64_T -D_FILE_OFFSET_BITS=64 -D_LARGEFILE64_SOURCE=1 -D_LARGEFILE_SOURCE=1 -Wall -O3 -g -I include -I mysql-source/include -I mysql-source/innobase/include -static -lrt -o page_parser page_parser.c lib/tables_dict.o lib/libut.a

上面的命令会将t222表的表定义传入到table_defs.h中，在生成了表结构定义后，重新make该恢复工具

注意：需要make 2次

开始提取page中删除的数据

[root@iZuf6c08fdv8duubho2b0rZ percona-data-recovery-tool-for-innodb-0.5]# ./constraints_parser -5 -f pages-1500254852/FIL_PAGE_INDEX/0-39/ > /tmp/t222.sql
LOAD DATA INFILE '/usr/src/percona-data-recovery-tool-for-innodb-0.5/dumps/default/t222' REPLACE INTO TABLE `t222` FIELDS TERMINATED BY '\t' OPTIONALLY ENCLOSED BY '"' LINES STARTING BY 't222\t' (id, name, age);
[root@iZuf6c08fdv8duubho2b0rZ percona-data-recovery-tool-for-innodb-0.5]# cat /tmp/t222.sql |wc -l
1024
[root@iZuf6c08fdv8duubho2b0rZ percona-data-recovery-tool-for-innodb-0.5]# head -10 /tmp/t222.sql 
t222    1       "jiessie"       18
t222    2       "jiessie"       18
t222    3       "jiessie"       18
t222    4       "jiessie"       18
t222    6       "jiessie"       18
t222    7       "jiessie"       18
t222    8       "jiessie"       18
t222    9       "jiessie"       18
t222    13      "jiessie"       18
t222    14      "jiessie"       18

参数解释：
-5 -f的参数和page_parser相同，代表 row format为Compact ；
-D:该参数的含义为代表恢复删除的数据页；

恢复数据

使用上面constraints_parser执行后的load data导入数据，需要注意目录

[root@iZuf6c08fdv8duubho2b0rZ percona-data-recovery-tool-for-innodb-0.5]# mysql -uroot -p123456 
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 35598
Server version: 5.6.36-82.0-log Source distribution

Copyright (c) 2009-2017 Percona LLC and/or its affiliates
Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MySQL [(none)] 09:32:33 > use test111;
Database changed
MySQL [test111] 09:32:35 > LOAD DATA INFILE '/tmp/t222.sql' REPLACE INTO TABLE `t222` FIELDS TERMINATED BY '\t' OPTIONALLY ENCLOSED BY '"' LINES STARTING BY 't222\t' (id, name, age);
Query OK, 1024 rows affected (0.00 sec)
Records: 1024  Deleted: 0  Skipped: 0  Warnings: 0

MySQL [test111] 09:32:53 > select count(*) from t222;
+----------+
| count(*) |
+----------+
|     1024 |
+----------+
1 row in set (0.00 sec)

MySQL [test111] 09:32:59 > select * from t222 limit 10;
+----+---------+------+
| id | name    | age  |
+----+---------+------+
|  1 | jiessie |   18 |
|  2 | jiessie |   18 |
|  3 | jiessie |   18 |
|  4 | jiessie |   18 |
|  6 | jiessie |   18 |
|  7 | jiessie |   18 |
|  8 | jiessie |   18 |
|  9 | jiessie |   18 |
| 13 | jiessie |   18 |
| 14 | jiessie |   18 |
+----+---------+------+
10 rows in set (0.00 sec)

查看数据，数据恢复成功。从实验来看，当delete误操作，第一时间要保护好ibd数据，拷贝到其他目录。

恢复脚本

当误操作的数据量大时，constraints_parser恢复是单线程，速度较慢，此处引用了2个恢复脚本
详情请参考：http://www.orczhou.com/index.php/2013/07/how-to-recover-data-from-mysql-innodb-data-file-ibd-file/

[root@iZuf6c08fdv8duubho2b0rZ percona-data-recovery-tool-for-innodb-0.5]# pwd
/usr/src/percona-data-recovery-tool-for-innodb-0.5
[root@iZuf6c08fdv8duubho2b0rZ percona-data-recovery-tool-for-innodb-0.5]# cat parallel_exec.sh 
#!/bin/bash
ws=/usr/src/percona-data-recovery-tool-for-innodb-0.5/
pagedir=/usr/src/percona-data-recovery-tool-for-innodb-0.5/pages-1500255306/FIL_PAGE_INDEX
logdir=/usr/src/percona-data-recovery-tool-for-innodb-0.5/log
rectool=/usr/src/percona-data-recovery-tool-for-innodb-0.5/constraints_parser
cd `dirname $rectool`
count=0
page_count=353894
page_done=0
startdate=`date +%s`
for d1 in `ls $pagedir`
do
  count=$(($count+1))
  echo "in page $d2 at dir $d1" > $logdir/$count.log
  thedate=`date +%s`
  echo "$page_done / $page_count at $thedate from $startdate"
  total=`ls -l $pagedir/$d1/|wc -l`
  page_done=$(($page_done+$total))
  threads=`ps axu|grep parser_jobs|grep -v grep|wc -l`
  echo $threads
  while [ $threads -gt 48 ];
  do
    sleep 1
    threads=`ps axu|grep parser_jobs|grep -v grep|wc -l`
  done
  $ws/parser_jobs.sh $pagedir/$d1 > $ws/job.log 2>&1 &
done
[root@iZuf6c08fdv8duubho2b0rZ percona-data-recovery-tool-for-innodb-0.5]# cat parser_jobs.sh 
#!/bin/bash
pagedir=/usr/src/percona-data-recovery-tool-for-innodb-0.5/pages-1500255306/FIL_PAGE_INDEX
logdir=/usr/src/percona-data-recovery-tool-for-innodb-0.5/log
rectool=/usr/src/percona-data-recovery-tool-for-innodb-0.5/constraints_parser
logfile="$logdir/`basename $1`.log"
echo "$1" > $logfile
if [ -d $1 ];then
  for d2 in `ls $1`
  do
    $rectool -5 -f $1/$d2 >> $logfile 2>/dev/null
  done
fi
[root@iZuf6c08fdv8duubho2b0rZ percona-data-recovery-tool-for-innodb-0.5]# ./parallel_exec.sh 
0 / 353894 at 1500255771 from 1500255771
0
[root@iZuf6c08fdv8duubho2b0rZ percona-data-recovery-tool-for-innodb-0.5]# ll log/
总用量 296
-rw-r--r-- 1 root root 296770 7月  17 09:42 0-41.log
-rw-r--r-- 1 root root     21 7月  17 09:42 1.log
[root@iZuf6c08fdv8duubho2b0rZ percona-data-recovery-tool-for-innodb-0.5]# cat log/0-41.log |wc -l
10001
[root@iZuf6c08fdv8duubho2b0rZ percona-data-recovery-tool-for-innodb-0.5]# head -10 log/0-41.log 
/usr/src/percona-data-recovery-tool-for-innodb-0.5/pages-1500255306/FIL_PAGE_INDEX/0-41
employee        3670    3670    "test3670"
employee        3671    3671    "test3671"
employee        3672    3672    "test3672"
employee        3673    3673    "test3673"
employee        3674    3674    "test3674"
employee        3675    3675    "test3675"
employee        3676    3676    "test3676"
employee        3677    3677    "test3677"
employee        3678    3678    "test3678"

用法：
1.修改脚本路
2.修改page_parser后的pages目录
3.创建log目录
4.执行parallel_exec脚本
5.注意，上面演示的示例，0-41.log中在第一行多了一句待恢复innodb页的路径，实际恢复中应删除

innodb_file_per_table=0

模拟数据被误删除

[root@iZuf6c08fdv8duubho2b0rZ percona-data-recovery-tool-for-innodb-0.5]# mysql -uroot -p123456 -e "show variables like 'innodb_file_per_table'"
Warning: Using a password on the command line interface can be insecure.
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_file_per_table | OFF   |
+-----------------------+-------+
[root@iZuf6c08fdv8duubho2b0rZ percona-data-recovery-tool-for-innodb-0.5]# mysql -uroot -p123456
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 5.6.36-82.0-log Source distribution

Copyright (c) 2009-2017 Percona LLC and/or its affiliates
Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MySQL [(none)] 09:58:22 > create database test111;
Query OK, 1 row affected (0.00 sec)

MySQL [(none)] 09:58:31 > use test111;
Database changed
MySQL [test111] 09:58:34 > create table t222(id int unsigned not null auto_increment,name varchar(20),age int,primary key(`id`),key idx_age(`age`)); 
Query OK, 0 rows affected (0.01 sec)

MySQL [test111] 09:58:52 > insert into t222 values(null,'jiessie',18);   
Query OK, 1 row affected (0.00 sec)

MySQL [test111] 09:58:57 > insert into t222 (name,age) select name,age from t222;   
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

MySQL [test111] 09:59:02 > insert into t222 (name,age) select name,age from t222;   
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

MySQL [test111] 09:59:03 > insert into t222 (name,age) select name,age from t222;   
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

MySQL [test111] 09:59:04 > insert into t222 (name,age) select name,age from t222;   
Query OK, 8 rows affected (0.00 sec)
Records: 8  Duplicates: 0  Warnings: 0

MySQL [test111] 09:59:04 > insert into t222 (name,age) select name,age from t222;                                                                                       
Query OK, 16 rows affected (0.00 sec)
Records: 16  Duplicates: 0  Warnings: 0

MySQL [test111] 09:59:06 > insert into t222 (name,age) select name,age from t222;   
Query OK, 32 rows affected (0.00 sec)
Records: 32  Duplicates: 0  Warnings: 0

MySQL [test111] 09:59:07 > insert into t222 (name,age) select name,age from t222;   
Query OK, 64 rows affected (0.00 sec)
Records: 64  Duplicates: 0  Warnings: 0

MySQL [test111] 09:59:07 > insert into t222 (name,age) select name,age from t222;   
Query OK, 128 rows affected (0.00 sec)
Records: 128  Duplicates: 0  Warnings: 0

MySQL [test111] 09:59:08 > insert into t222 (name,age) select name,age from t222;   
Query OK, 256 rows affected (0.00 sec)
Records: 256  Duplicates: 0  Warnings: 0

MySQL [test111] 09:59:08 > insert into t222 (name,age) select name,age from t222;   
Query OK, 512 rows affected (0.00 sec)
Records: 512  Duplicates: 0  Warnings: 0

MySQL [test111] 09:59:09 > select count(*) from t222;
+----------+
| count(*) |
+----------+
|     1024 |
+----------+
1 row in set (0.00 sec)

解析ibd文件

实战mysql安装在/usr/local/percona，数据目录在/hwdata/data/percona，注意保存数据文件。
当设置了使用共享表空间(innodb_file_per_table=0)时，所有数据文件存在在ibdata1文件里面，不再是存在在单独表中，此时需要从ibdata1提取出需要被恢复的表

[root@iZuf6c08fdv8duubho2b0rZ percona-data-recovery-tool-for-innodb-0.5]# ./page_parser -5 -f /tmp/ibdata1 
Opening file: /tmp/ibdata1:
64513           ID of device containing file
1597426         inode number
33184           protection
1               number of hard links
0               user ID of owner
0               group ID of owner
0               device ID (if special file)
1073741824              total size, in bytes
4096            blocksize for filesystem I/O
2097160         number of blocks allocated
1500256783      time of last access
1500256790      time of last modification
1500256790      time of last status change
1073741824      Size to process in bytes
104857600       Disk cache size in bytes
1.65% done. 2017-07-17 10:03:48 ETA(in 00:00 hours). Processing speed: 17666552 B/sec
3.41% done. 2017-07-17 10:03:44 ETA(in 00:00 hours). Processing speed: 19000304 B/sec
5.18% done. 2017-07-17 10:03:44 ETA(in 00:00 hours). Processing speed: 19005354 B/sec
6.96% done. 2017-07-17 10:03:44 ETA(in 00:00 hours). Processing speed: 19008049 B/sec
8.73% done. 2017-07-17 10:03:44 ETA(in 00:00 hours). Processing speed: 19015298 B/sec
10.47% done. 2017-07-17 10:03:45 ETA(in 00:00 hours). Processing speed: 18719062 B/sec
12.24% done. 2017-07-17 10:03:44 ETA(in 00:00 hours). Processing speed: 19008805 B/sec
14.01% done. 2017-07-17 10:03:44 ETA(in 00:00 hours). Processing speed: 19009523 B/sec
15.78% done. 2017-07-17 10:03:44 ETA(in 00:00 hours). Processing speed: 19007004 B/sec
17.55% done. 2017-07-17 10:03:44 ETA(in 00:00 hours). Processing speed: 19008533 B/sec
19.32% done. 2017-07-17 10:03:44 ETA(in 00:00 hours). Processing speed: 19008676 B/sec
21.06% done. 2017-07-17 10:03:45 ETA(in 00:00 hours). Processing speed: 18715203 B/sec
22.83% done. 2017-07-17 10:03:44 ETA(in 00:00 hours). Processing speed: 19007750 B/sec
24.60% done. 2017-07-17 10:03:44 ETA(in 00:00 hours). Processing speed: 19002802 B/sec
26.37% done. 2017-07-17 10:03:44 ETA(in 00:00 hours). Processing speed: 18998409 B/sec
28.14% done. 2017-07-17 10:03:44 ETA(in 00:00 hours). Processing speed: 18991699 B/sec
29.88% done. 2017-07-17 10:03:45 ETA(in 00:00 hours). Processing speed: 18708371 B/sec
31.65% done. 2017-07-17 10:03:44 ETA(in 00:00 hours). Processing speed: 18999365 B/sec
33.42% done. 2017-07-17 10:03:44 ETA(in 00:00 hours). Processing speed: 18997104 B/sec
35.19% done. 2017-07-17 10:03:44 ETA(in 00:00 hours). Processing speed: 19001358 B/sec
36.96% done. 2017-07-17 10:03:44 ETA(in 00:00 hours). Processing speed: 18999823 B/sec
38.73% done. 2017-07-17 10:03:44 ETA(in 00:00 hours). Processing speed: 18997814 B/sec
40.47% done. 2017-07-17 10:03:45 ETA(in 00:00 hours). Processing speed: 18706585 B/sec
42.24% done. 2017-07-17 10:03:44 ETA(in 00:00 hours). Processing speed: 18996668 B/sec
44.01% done. 2017-07-17 10:03:44 ETA(in 00:00 hours). Processing speed: 18998094 B/sec
45.78% done. 2017-07-17 10:03:44 ETA(in 00:00 hours). Processing speed: 18998427 B/sec
47.55% done. 2017-07-17 10:03:44 ETA(in 00:00 hours). Processing speed: 19002403 B/sec
49.30% done. 2017-07-17 10:03:45 ETA(in 00:00 hours). Processing speed: 18724158 B/sec
51.06% done. 2017-07-17 10:03:44 ETA(in 00:00 hours). Processing speed: 18997507 B/sec
52.83% done. 2017-07-17 10:03:44 ETA(in 00:00 hours). Processing speed: 18999524 B/sec
54.60% done. 2017-07-17 10:03:44 ETA(in 00:00 hours). Processing speed: 18998796 B/sec
56.37% done. 2017-07-17 10:03:44 ETA(in 00:00 hours). Processing speed: 19000200 B/sec
58.14% done. 2017-07-17 10:03:44 ETA(in 00:00 hours). Processing speed: 18998670 B/sec
59.88% done. 2017-07-17 10:03:45 ETA(in 00:00 hours). Processing speed: 18699601 B/sec
61.65% done. 2017-07-17 10:03:44 ETA(in 00:00 hours). Processing speed: 19000413 B/sec
63.42% done. 2017-07-17 10:03:44 ETA(in 00:00 hours). Processing speed: 18997737 B/sec
65.19% done. 2017-07-17 10:03:44 ETA(in 00:00 hours). Processing speed: 18996538 B/sec
66.96% done. 2017-07-17 10:03:44 ETA(in 00:00 hours). Processing speed: 18999244 B/sec
68.70% done. 2017-07-17 10:03:44 ETA(in 00:00 hours). Processing speed: 18704824 B/sec
70.47% done. 2017-07-17 10:03:44 ETA(in 00:00 hours). Processing speed: 19002702 B/sec
72.24% done. 2017-07-17 10:03:44 ETA(in 00:00 hours). Processing speed: 19002732 B/sec
74.01% done. 2017-07-17 10:03:44 ETA(in 00:00 hours). Processing speed: 18999341 B/sec
75.78% done. 2017-07-17 10:03:44 ETA(in 00:00 hours). Processing speed: 19001510 B/sec
77.55% done. 2017-07-17 10:03:44 ETA(in 00:00 hours). Processing speed: 18998098 B/sec
79.29% done. 2017-07-17 10:03:44 ETA(in 00:00 hours). Processing speed: 18706109 B/sec
81.06% done. 2017-07-17 10:03:44 ETA(in 00:00 hours). Processing speed: 19007616 B/sec
82.83% done. 2017-07-17 10:03:44 ETA(in 00:00 hours). Processing speed: 19007630 B/sec
84.60% done. 2017-07-17 10:03:44 ETA(in 00:00 hours). Processing speed: 19004630 B/sec
86.37% done. 2017-07-17 10:03:44 ETA(in 00:00 hours). Processing speed: 19004716 B/sec
88.12% done. 2017-07-17 10:03:44 ETA(in 00:00 hours). Processing speed: 18717386 B/sec
89.89% done. 2017-07-17 10:03:44 ETA(in 00:00 hours). Processing speed: 19005803 B/sec
91.66% done. 2017-07-17 10:03:44 ETA(in 00:00 hours). Processing speed: 19007785 B/sec
93.43% done. 2017-07-17 10:03:44 ETA(in 00:00 hours). Processing speed: 19006419 B/sec
95.20% done. 2017-07-17 10:03:44 ETA(in 00:00 hours). Processing speed: 19004360 B/sec
96.97% done. 2017-07-17 10:03:44 ETA(in 00:00 hours). Processing speed: 19008201 B/sec
98.73% done. 2017-07-17 10:03:44 ETA(in 00:00 hours). Processing speed: 18940047 B/sec
[root@iZuf6c08fdv8duubho2b0rZ percona-data-recovery-tool-for-innodb-0.5]# ll -t
总用量 3172
drwxr-xr-x  4 root root     4096 7月  17 10:18 pages-1500257904
-rwxr-xr-x  1 root root   979051 7月  17 10:18 ibdconnect
-rwxr-xr-x  1 root root    14873 7月  17 10:18 innochecksum
-rwxr-xr-x  1 root root  1346155 7月  17 10:18 page_parser
-rwxr-xr-x  1 root root   748761 7月  17 10:18 constraints_parser
drwxr-xr-x  2 root root     4096 7月  17 10:18 lib
drwxr-xr-x 40  510 wheel    4096 7月  17 10:18 mysql-source
drwxr-xr-x  2  510 wheel    4096 8月  28 2011 docs
drwxr-xr-x  2  510 wheel    4096 8月  28 2011 include
-rw-r--r--  1  510 wheel    6269 8月  28 2011 check_data.c
-rw-r--r--  1  510 wheel   22172 8月  28 2011 constraints_parser.c
-rwxr-xr-x  1  510 wheel   12051 8月  28 2011 create_defs.pl
-rwxr-xr-x  1  510 wheel    1978 8月  28 2011 fetch_data.sh
-rw-r--r--  1  510 wheel   12200 8月  28 2011 ibdconnect.c
-rw-r--r--  1  510 wheel    8262 8月  28 2011 incrementalupdate.c
-rw-r--r--  1  510 wheel    9117 8月  28 2011 innochecksum.c
-rw-r--r--  1  510 wheel      74 8月  28 2011 INSTALL
-rw-r--r--  1  510 wheel    2676 8月  28 2011 Makefile
-rw-r--r--  1  510 wheel   15239 8月  28 2011 page_parser.c
-rw-r--r--  1  510 wheel   10608 8月  28 2011 print_data.c
-rwxr-xr-x  1  510 wheel     302 8月  28 2011 split_dump.pl
-rw-r--r--  1  510 wheel    2046 8月  28 2011 tables_dict.c

从ibdata1提取待恢复数据

由于ibdata1恢复的是所有数据，需要从这些数据找到误操作的表，通过查询元数据，得到表空间的id和name

[root@iZuf6c08fdv8duubho2b0rZ percona-data-recovery-tool-for-innodb-0.5]# mysql -uroot -p123456 
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 39
Server version: 5.6.36-82.0-log Source distribution

Copyright (c) 2009-2017 Percona LLC and/or its affiliates
Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MySQL [(none)] 10:06:55 > use information_schema;
Database changed
MySQL [information_schema] 10:06:58 > select i.INDEX_ID, i.NAME FROM INNODB_SYS_INDEXES as i INNER JOIN INNODB_SYS_TABLES as t USING(TABLE_ID) WHERE t.NAME='test111/t222';
+----------+---------+
| INDEX_ID | NAME    |
+----------+---------+
|       20 | PRIMARY |
|       21 | idx_age |
+----------+---------+
2 rows in set (0.00 sec)

MySQL [information_schema] 10:06:59 > exit
Bye
[root@iZuf6c08fdv8duubho2b0rZ percona-data-recovery-tool-for-innodb-0.5]# ll pages-1500256968/FIL_PAGE_INDEX/
总用量 68
drwxr-xr-x 2 root root 4096 7月  17 10:02 0-1
drwxr-xr-x 2 root root 4096 7月  17 10:02 0-11
drwxr-xr-x 2 root root 4096 7月  17 10:02 0-12
drwxr-xr-x 2 root root 4096 7月  17 10:02 0-13
drwxr-xr-x 2 root root 4096 7月  17 10:02 0-14
drwxr-xr-x 2 root root 4096 7月  17 10:02 0-15
drwxr-xr-x 2 root root 4096 7月  17 10:02 0-16
drwxr-xr-x 2 root root 4096 7月  17 10:02 0-17
drwxr-xr-x 2 root root 4096 7月  17 10:02 0-18
drwxr-xr-x 2 root root 4096 7月  17 10:02 0-19
drwxr-xr-x 2 root root 4096 7月  17 10:02 0-2
drwxr-xr-x 2 root root 4096 7月  17 10:02 0-20
drwxr-xr-x 2 root root 4096 7月  17 10:02 0-21
drwxr-xr-x 2 root root 4096 7月  17 10:02 0-3
drwxr-xr-x 2 root root 4096 7月  17 10:02 0-4
drwxr-xr-x 2 root root 4096 7月  17 10:02 0-5
drwxr-xr-x 2 root root 4096 7月  17 10:02 4294967295-0
[root@iZuf6c08fdv8duubho2b0rZ percona-data-recovery-tool-for-innodb-0.5]#

结果分析

从下面的解析后的结果看到，当前目录pages-1500257904，其中20为主键索引的index_id,21为二级索引的index_id,该id可以通过开启innodb_table_monitor查看

[root@iZuf6c08fdv8duubho2b0rZ percona-data-recovery-tool-for-innodb-0.5]# ll pages-1500256968/FIL_PAGE_INDEX/0-20/
总用量 128
-rw-r--r-- 1 root root 16384 7月  17 10:02 15-00000054.page
-rw-r--r-- 1 root root 16384 7月  17 10:02 17-00000056.page
-rw-r--r-- 1 root root 16384 7月  17 10:02 18-00000057.page
-rw-r--r-- 1 root root 16384 7月  17 10:02 19-00000058.page
-rw-r--r-- 1 root root 16384 7月  17 10:02 20-00000054.page
-rw-r--r-- 1 root root 16384 7月  17 10:02 22-00000057.page
-rw-r--r-- 1 root root 16384 7月  17 10:02 23-00000056.page
-rw-r--r-- 1 root root 16384 7月  17 10:02 24-00000058.page
[root@iZuf6c08fdv8duubho2b0rZ percona-data-recovery-tool-for-innodb-0.5]# ll pages-1500256968/FIL_PAGE_INDEX/0-21
总用量 32
-rw-r--r-- 1 root root 16384 7月  17 10:02 16-00000055.page
-rw-r--r-- 1 root root 16384 7月  17 10:02 21-00000055.page

生成表定义

由于该工具在解析数据pages的时候，需要获得该table的表结构定义，所以需要执行如下命令。

[root@iZuf6c08fdv8duubho2b0rZ percona-data-recovery-tool-for-innodb-0.5]# ./create_defs.pl --host localhost --user root --password 123456 --db test111 --table t222 > include/table_defs.h
[root@iZuf6c08fdv8duubho2b0rZ percona-data-recovery-tool-for-innodb-0.5]# cat include/table_defs.h
#ifndef table_defs_h
#define table_defs_h

// Table definitions
table_def_t table_definitions[] = {
        {
                name: "t222",
                {
                        { /* int(10) unsigned */
                                name: "id",
                                type: FT_UINT,
                                fixed_length: 4,

                                has_limits: FALSE,
                                limits: {
                                        can_be_null: FALSE,
                                        uint_min_val: 0,
                                        uint_max_val: 4294967295ULL
                                },

                                can_be_null: FALSE
                        },
                        { /*  */
                                name: "DB_TRX_ID",
                                type: FT_INTERNAL,
                                fixed_length: 6,

                                can_be_null: FALSE
                        },
                        { /*  */
                                name: "DB_ROLL_PTR",
                                type: FT_INTERNAL,
                                fixed_length: 7,

                                can_be_null: FALSE
                        },
                        { /* varchar(20) */
                                name: "name",
                                type: FT_CHAR,
                                min_length: 0,
                                max_length: 60,

                                has_limits: FALSE,
                                limits: {
                                        can_be_null: TRUE,
                                        char_min_len: 0,
                                        char_max_len: 60,
                                        char_ascii_only: TRUE
                                },

                                can_be_null: TRUE
                        },
                        { /* int(11) */
                                name: "age",
                                type: FT_INT,
                                fixed_length: 4,

                                has_limits: FALSE,
                                limits: {
                                        can_be_null: TRUE,
                                        int_min_val: -2147483648LL,
                                        int_max_val: 2147483647LL
                                },

                                can_be_null: TRUE
                        },
                        { type: FT_NONE }
                }
        },
};

#endif
[root@iZuf6c08fdv8duubho2b0rZ percona-data-recovery-tool-for-innodb-0.5]# 
[root@iZuf6c08fdv8duubho2b0rZ percona-data-recovery-tool-for-innodb-0.5]# make
gcc -DHAVE_OFFSET64_T -D_FILE_OFFSET_BITS=64 -D_LARGEFILE64_SOURCE=1 -D_LARGEFILE_SOURCE=1 -Wall -O3 -g -I include -I mysql-source/include -I mysql-source/innobase/include -c tables_dict.c -o lib/tables_dict.o
gcc -DHAVE_OFFSET64_T -D_FILE_OFFSET_BITS=64 -D_LARGEFILE64_SOURCE=1 -D_LARGEFILE_SOURCE=1 -Wall -O3 -g -I include -I mysql-source/include -I mysql-source/innobase/include -c check_data.c -o lib/check_data.o
gcc -DHAVE_OFFSET64_T -D_FILE_OFFSET_BITS=64 -D_LARGEFILE64_SOURCE=1 -D_LARGEFILE_SOURCE=1 -Wall -O3 -g -I include -I mysql-source/include -I mysql-source/innobase/include -o constraints_parser constraints_parser.c lib/tables_dict.o lib/print_data.o lib/check_data.o lib/libut.a lib/libmystrings.a
gcc -DHAVE_OFFSET64_T -D_FILE_OFFSET_BITS=64 -D_LARGEFILE64_SOURCE=1 -D_LARGEFILE_SOURCE=1 -Wall -O3 -g -I include -I mysql-source/include -I mysql-source/innobase/include -static -lrt -o page_parser page_parser.c lib/tables_dict.o lib/libut.a 
[root@iZuf6c08fdv8duubho2b0rZ percona-data-recovery-tool-for-innodb-0.5]# make
gcc -DHAVE_OFFSET64_T -D_FILE_OFFSET_BITS=64 -D_LARGEFILE64_SOURCE=1 -D_LARGEFILE_SOURCE=1 -Wall -O3 -g -I include -I mysql-source/include -I mysql-source/innobase/include -c tables_dict.c -o lib/tables_dict.o
gcc -DHAVE_OFFSET64_T -D_FILE_OFFSET_BITS=64 -D_LARGEFILE64_SOURCE=1 -D_LARGEFILE_SOURCE=1 -Wall -O3 -g -I include -I mysql-source/include -I mysql-source/innobase/include -c print_data.c -o lib/print_data.o 
gcc -DHAVE_OFFSET64_T -D_FILE_OFFSET_BITS=64 -D_LARGEFILE64_SOURCE=1 -D_LARGEFILE_SOURCE=1 -Wall -O3 -g -I include -I mysql-source/include -I mysql-source/innobase/include -o constraints_parser constraints_parser.c lib/tables_dict.o lib/print_data.o lib/check_data.o lib/libut.a lib/libmystrings.a
gcc -DHAVE_OFFSET64_T -D_FILE_OFFSET_BITS=64 -D_LARGEFILE64_SOURCE=1 -D_LARGEFILE_SOURCE=1 -Wall -O3 -g -I include -I mysql-source/include -I mysql-source/innobase/include -static -lrt -o page_parser page_parser.c lib/tables_dict.o lib/libut.a

上面的命令会将t222表的表定义传入到table_defs.h中，在生成了表结构定义后，重新make该恢复工具

注意：需要make 2次

开始提取page中删除的数据

[root@iZuf6c08fdv8duubho2b0rZ percona-data-recovery-tool-for-innodb-0.5]# ./constraints_parser -5 -f pages-1500258331/FIL_PAGE_INDEX/0-20/ > /tmp/0_t222.sql            
LOAD DATA INFILE '/usr/src/percona-data-recovery-tool-for-innodb-0.5/dumps/default/t222' REPLACE INTO TABLE `t222` FIELDS TERMINATED BY '\t' OPTIONALLY ENCLOSED BY '"' LINES STARTING BY 't222\t' (id, name, age);
[root@iZuf6c08fdv8duubho2b0rZ percona-data-recovery-tool-for-innodb-0.5]# cat /tmp/0_t222.sql |wc -l
2048
[root@iZuf6c08fdv8duubho2b0rZ percona-data-recovery-tool-for-innodb-0.5]# head -10 /tmp/0_t222.sql 
t222    1145    "jiessie"       18
t222    1146    "jiessie"       18
t222    1147    "jiessie"       18
t222    1148    "jiessie"       18
t222    1149    "jiessie"       18
t222    1150    "jiessie"       18
t222    1151    "jiessie"       18
t222    1152    "jiessie"       18
t222    1153    "jiessie"       18
t222    1154    "jiessie"       18

从结果上来看，数据重复了一份，通过load data导入只导入一份正常的数据
参数解释：
-5 -f的参数和page_parser相同，代表 row format为Compact ；
-D:该参数的含义为代表恢复删除的数据页；

恢复数据

使用上面constraints_parser执行后的load data导入数据，需要注意目录

[root@iZuf6c08fdv8duubho2b0rZ percona-data-recovery-tool-for-innodb-0.5]# mysql -uroot -p123456
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 108
Server version: 5.6.36-82.0-log Source distribution

Copyright (c) 2009-2017 Percona LLC and/or its affiliates
Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MySQL [(none)] 10:28:16 > use test111;
Database changed
MySQL [test111] 10:28:20 > LOAD DATA INFILE '/tmp/0_t222.sql' REPLACE INTO TABLE `t222` FIELDS TERMINATED BY '\t' OPTIONALLY ENCLOSED BY '"' LINES STARTING BY 't222\t' (id, name, age);
Query OK, 2048 rows affected (0.01 sec)
Records: 2048  Deleted: 0  Skipped: 0  Warnings: 0

MySQL [test111] 10:28:39 > select count(*) from t222;
+----------+
| count(*) |
+----------+
|     1024 |
+----------+
1 row in set (0.00 sec)

MySQL [test111] 10:28:44 > select * from t222 limit 10;
+----+---------+------+
| id | name    | age  |
+----+---------+------+
|  1 | jiessie |   18 |
|  2 | jiessie |   18 |
|  3 | jiessie |   18 |
|  4 | jiessie |   18 |
|  6 | jiessie |   18 |
|  7 | jiessie |   18 |
|  8 | jiessie |   18 |
|  9 | jiessie |   18 |
| 13 | jiessie |   18 |
| 14 | jiessie |   18 |
+----+---------+------+
10 rows in set (0.00 sec)

从结果看，数据恢复完成

结束语

Percona Data Recovery Tool for InnoDB工具并不保证100%能够恢复数据，而且会存在丢失数据的现象，特别是当发生误删除后，数据文件并没有第一时间保存起来，而导致了被重写。truncate操作在此不做演示，建议还是做好备份恢复工作，并做日常的演练。

percona server 5.6 自制rpm包

发表于 2017-07-13 | 分类于安装

字数统计: | 阅读时长 ≈

前言

RPM是Red-hat系统的软件管理工具，目前，RPM已发展成为业界认可的Linux系统软件工具。RPM制作灵活方便，在安装、升级、卸载等方面有着显著的优点。mysql通过自制RPM包方式，可以缩短编译安装时间，简化安装过程，自定义数据库配置，非常灵活方便，可以极大的提升效率。

优点：
RPM内含已编译过的程序与设置文件等数据，可以让用户免除重新编译的困扰。
RPM在被安装之前，会先检查系统的硬盘容量、操作系统版本等，可避免文件被错误安装。
RPM文件本身提供软件版本信息、依赖属性软件名称、软件用途说明、软件所含文件信息，便于了解软件。
RPM管理的方式使用数据库记录RPM文件的相关参数，便于升级、删除、查询与验证。
详情请参考：http://rpm.org/documentation.html

准备

操作系统：centos 6.5 x86_64
构建工具：rpmbuild

RPM常用命令

列出已安装的RPM包

列出系统中已安装的全部RPM包

[root@iZuf6c08fdv8duubho2b0rZ ~]# rpm -qa|head -10  #由于包太多，用head过滤了前10条
glib2-2.28.8-5.el6.x86_64
gnome-icon-theme-2.28.0-8.el6.noarch
bind-libs-9.8.2-0.23.rc1.el6_5.1.x86_64
mhash-0.9.9.9-3.el6.x86_64
libreport-2.0.9-19.el6.centos.x86_64
libcroco-0.6.2-5.el6.x86_64
libcap-2.16-5.5.el6.x86_64
libxml2-python-2.7.6-21.el6_8.1.x86_64
abrt-addon-kerneloops-2.0.8-21.el6.centos.x86_64
libasyncns-0.8-1.1.el6.x86_64

列出系统中以mysql开头的RPM包

[root@iZuf6c08fdv8duubho2b0rZ ~]# rpm -qa "mysql*"
mysql-community-libs-5.7.16-1.el6.x86_64
mysql-libs-5.1.73-3.el6_5.x86_64
mysql-devel-5.1.73-8.el6_8.x86_64
mysql-5.7.17-1.el6.x86_64

列出系统中包括mysql关键字的RPM包

[root@iZuf6c08fdv8duubho2b0rZ ~]# rpm -qa|grep "mysql"  
php56w-mysql-5.6.30-1.w6.x86_64
mysql-community-libs-5.7.16-1.el6.x86_64
zabbix-server-mysql-3.0.9-1.el6.x86_64
mysql-libs-5.1.73-3.el6_5.x86_64
mysql-devel-5.1.73-8.el6_8.x86_64
mysql-5.7.17-1.el6.x86_64
zabbix-web-mysql-3.0.9-1.el6.noarch

列出系统中近期安装的RPM包

[root@iZuf6c08fdv8duubho2b0rZ ~]# rpm -qa --last | head 
tree-1.5.3-3.el6.x86_64                       2017年07月13日 星期四 09时35分13秒
git-1.7.1-8.el6.x86_64                        2017年07月12日 星期三 10时29分20秒
perl-Git-1.7.1-8.el6.noarch                   2017年07月12日 星期三 10时29分19秒
elfutils-0.164-2.el6.x86_64                   2017年07月11日 星期二 10时30分26秒
perf-2.6.32-696.3.2.el6.x86_64                2017年07月11日 星期二 10时30分25秒
elfutils-libs-0.164-2.el6.x86_64              2017年07月11日 星期二 10时30分25秒
elfutils-libelf-0.164-2.el6.x86_64            2017年07月11日 星期二 10时30分25秒
pmm-client-1.1.6-1.x86_64                     2017年07月10日 星期一 16时58分04秒
gcc-gfortran-4.4.7-18.el6.x86_64              2017年07月06日 星期四 15时40分43秒
gcc-c++-4.4.7-18.el6.x86_64                   2017年07月06日 星期四 15时40分43秒

查找特定RPM包

查找某RPM包是否安装

[root@iZuf6c08fdv8duubho2b0rZ ~]# rpm -q mysql-server
package mysql-server is not installed
[root@iZuf6c08fdv8duubho2b0rZ ~]# 
[root@iZuf6c08fdv8duubho2b0rZ ~]# rpm -q zabbix
package zabbix is not installed
[root@iZuf6c08fdv8duubho2b0rZ ~]# 
[root@iZuf6c08fdv8duubho2b0rZ ~]# rpm -q gcc
gcc-4.4.7-18.el6.x86_64

查找某RPM包的基本信息

[root@iZuf6c08fdv8duubho2b0rZ ~]# rpm -q percona-server
percona-server-5.6.36-82.0.x86_64
[root@iZuf6c08fdv8duubho2b0rZ ~]# 
[root@iZuf6c08fdv8duubho2b0rZ ~]# rpm -qi percona-server
Name        : percona-server               Relocations: /usr/local/percona 
Version     : 5.6.36                            Vendor: Jiessie
Release     : 82.0                          Build Date: 2017年06月20日 星期二 13时08分20秒
Install Date: 2017年06月20日 星期二 13时42分36秒      Build Host: iZuf6c08fdv8duubho2b0rZ
Group       : applications/database         Source RPM: percona-server-5.6.36-82.0.src.rpm
Size        : 460543421                        License: GPL
Signature   : (none)
Packager    : dwj999@163.com
URL         : https://www.percona.com/downloads/Percona-Server-5.6/Percona-Server-5.6.36-82.0/source/tarball/percona-server-5.6.36-82.0.tar.gz
Summary     : percona-server-5.6.36-82.0
Description :
Percona Server is an enhanced, drop-in MySQL® replacement which has been downloaded more than 1,000,000 times.
A free open source solution, Percona Server is a MySQL alternative which offers breakthrough performance, scalability, features, and instrumentation. Self-tuning algorithms and support for extremely high-performance hardware make it the clear choice for organizations that demand excellent performance and reliability from their MySQL database server.
[root@iZuf6c08fdv8duubho2b0rZ ~]#

查找某RPM包的配置信息

[root@iZuf6c08fdv8duubho2b0rZ ~]# rpm -qc httpd
/etc/httpd/conf.d/welcome.conf
/etc/httpd/conf/httpd.conf
/etc/httpd/conf/magic
/etc/logrotate.d/httpd
/etc/sysconfig/htcacheclean
/etc/sysconfig/httpd
/var/www/error/HTTP_BAD_GATEWAY.html.var
/var/www/error/HTTP_BAD_REQUEST.html.var
/var/www/error/HTTP_FORBIDDEN.html.var
/var/www/error/HTTP_GONE.html.var
/var/www/error/HTTP_INTERNAL_SERVER_ERROR.html.var
/var/www/error/HTTP_LENGTH_REQUIRED.html.var
/var/www/error/HTTP_METHOD_NOT_ALLOWED.html.var
/var/www/error/HTTP_NOT_FOUND.html.var
/var/www/error/HTTP_NOT_IMPLEMENTED.html.var
/var/www/error/HTTP_PRECONDITION_FAILED.html.var
/var/www/error/HTTP_REQUEST_ENTITY_TOO_LARGE.html.var
/var/www/error/HTTP_REQUEST_TIME_OUT.html.var
/var/www/error/HTTP_REQUEST_URI_TOO_LARGE.html.var
/var/www/error/HTTP_SERVICE_UNAVAILABLE.html.var
/var/www/error/HTTP_UNAUTHORIZED.html.var
/var/www/error/HTTP_UNSUPPORTED_MEDIA_TYPE.html.var
/var/www/error/HTTP_VARIANT_ALSO_VARIES.html.var
/var/www/error/contact.html.var
/var/www/error/include/bottom.html
/var/www/error/include/spacer.html
/var/www/error/include/top.html

查找某RPM包的文档信息

[root@iZuf6c08fdv8duubho2b0rZ ~]# rpm -qd httpd
/usr/share/doc/httpd-2.2.15/ABOUT_APACHE
/usr/share/doc/httpd-2.2.15/CHANGES
/usr/share/doc/httpd-2.2.15/LICENSE
/usr/share/doc/httpd-2.2.15/NOTICE
/usr/share/doc/httpd-2.2.15/README
/usr/share/doc/httpd-2.2.15/VERSIONING
/usr/share/man/man8/apachectl.8.gz
/usr/share/man/man8/htcacheclean.8.gz
/usr/share/man/man8/httpd.8.gz
/usr/share/man/man8/rotatelogs.8.gz
/usr/share/man/man8/suexec.8.gz

查找某RPM包的状态

[root@iZuf6c08fdv8duubho2b0rZ ~]# rpm -qs rsync
normal        /etc/xinetd.d/rsync
normal        /usr/bin/rsync
normal        /usr/share/doc/rsync-3.0.6
normal        /usr/share/doc/rsync-3.0.6/COPYING
normal        /usr/share/doc/rsync-3.0.6/NEWS
normal        /usr/share/doc/rsync-3.0.6/OLDNEWS
normal        /usr/share/doc/rsync-3.0.6/README
normal        /usr/share/doc/rsync-3.0.6/support
normal        /usr/share/doc/rsync-3.0.6/support/Makefile
normal        /usr/share/doc/rsync-3.0.6/support/atomic-rsync
normal        /usr/share/doc/rsync-3.0.6/support/cvs2includes
normal        /usr/share/doc/rsync-3.0.6/support/deny-rsync
normal        /usr/share/doc/rsync-3.0.6/support/file-attr-restore
normal        /usr/share/doc/rsync-3.0.6/support/files-to-excludes
normal        /usr/share/doc/rsync-3.0.6/support/git-set-file-times
normal        /usr/share/doc/rsync-3.0.6/support/logfilter
normal        /usr/share/doc/rsync-3.0.6/support/lsh
normal        /usr/share/doc/rsync-3.0.6/support/mnt-excl
normal        /usr/share/doc/rsync-3.0.6/support/munge-symlinks
normal        /usr/share/doc/rsync-3.0.6/support/rrsync
normal        /usr/share/doc/rsync-3.0.6/support/rsyncstats
normal        /usr/share/doc/rsync-3.0.6/support/savetransfer.c
normal        /usr/share/doc/rsync-3.0.6/tech_report.tex
normal        /usr/share/man/man1/rsync.1.gz
normal        /usr/share/man/man5/rsyncd.conf.5.gz

RPM包的状态

状态	说明
Normal	正常，表明文件未被其他软件包修改过
not installed	未安装，表明文件未安装
Replaced	已替换，表明文件已被其他软件包修改替换过，不再是原先的文件
net shared	网络共享，表明文件处于网络共享状态

查找某RPM包的所有文件

[root@iZuf6c08fdv8duubho2b0rZ ~]# rpm -ql rsync
/etc/xinetd.d/rsync
/usr/bin/rsync
/usr/share/doc/rsync-3.0.6
/usr/share/doc/rsync-3.0.6/COPYING
/usr/share/doc/rsync-3.0.6/NEWS
/usr/share/doc/rsync-3.0.6/OLDNEWS
/usr/share/doc/rsync-3.0.6/README
/usr/share/doc/rsync-3.0.6/support
/usr/share/doc/rsync-3.0.6/support/Makefile
/usr/share/doc/rsync-3.0.6/support/atomic-rsync
/usr/share/doc/rsync-3.0.6/support/cvs2includes
/usr/share/doc/rsync-3.0.6/support/deny-rsync
/usr/share/doc/rsync-3.0.6/support/file-attr-restore
/usr/share/doc/rsync-3.0.6/support/files-to-excludes
/usr/share/doc/rsync-3.0.6/support/git-set-file-times
/usr/share/doc/rsync-3.0.6/support/logfilter
/usr/share/doc/rsync-3.0.6/support/lsh
/usr/share/doc/rsync-3.0.6/support/mnt-excl
/usr/share/doc/rsync-3.0.6/support/munge-symlinks
/usr/share/doc/rsync-3.0.6/support/rrsync
/usr/share/doc/rsync-3.0.6/support/rsyncstats
/usr/share/doc/rsync-3.0.6/support/savetransfer.c
/usr/share/doc/rsync-3.0.6/tech_report.tex
/usr/share/man/man1/rsync.1.gz
/usr/share/man/man5/rsyncd.conf.5.gz

查找某RPM包的安装、卸载前后的脚本

[root@iZuf6c08fdv8duubho2b0rZ ~]# rpm -q --scripts httpd         
preinstall scriptlet (using /bin/sh):
# Add the "apache" user
getent group apache >/dev/null || groupadd -g 48 -r apache
getent passwd apache >/dev/null || \
  useradd -r -u 48 -g apache -s /sbin/nologin \
    -d /var/www -c "Apache" apache
exit 0
postinstall scriptlet (using /bin/sh):
# Register the httpd service
/sbin/chkconfig --add httpd
/sbin/chkconfig --add htcacheclean
preuninstall scriptlet (using /bin/sh):
if [ $1 = 0 ]; then
        /sbin/service httpd stop > /dev/null 2>&1
        /sbin/chkconfig --del httpd
        /sbin/service htcacheclean stop > /dev/null 2>&1
        /sbin/chkconfig --del htcacheclean
fi
posttrans scriptlet (using /bin/sh):
test -f /etc/sysconfig/httpd-disable-posttrans || \
 /sbin/service httpd condrestart >/dev/null 2>&1 || :

查找某RPM包的修改历史

[root@iZuf6c08fdv8duubho2b0rZ ~]# rpm -q --changelog python |head -10
* 二 8月 09 2016 Charalampos Stratakis <cstratak@redhat.com> - 2.6.6-66
- Fix for CVE-2016-1000110 HTTPoxy attack
Resolves: rhbz#1359161

* 二 6月 21 2016 Tomas Orsava <torsava@redhat.com> - 2.6.6-65
- Fix for CVE-2016-0772 python: smtplib StartTLS stripping attack (rhbz#1303647)
  Raise an error when STARTTLS fails (upstream patch)
- Fix for CVE-2016-5699 python: http protocol steam injection attack (rhbz#1303699)
  Disabled HTTP header injections in httplib (upstream patch)
Resolves: rhbz#1346354

查找某个组群里的RPM包

[root@iZuf6c08fdv8duubho2b0rZ ~]# rpm -qg Applications/Databases 
db4-utils-4.7.25-18.el6_4.x86_64
mysql-libs-5.1.73-3.el6_5.x86_64
percona-xtrabackup-2.3.6-1.el6.x86_64
Percona-Server-client-51-5.1.73-rel14.12.624.rhel6.x86_64
rh-postgresql95-postgresql-libs-9.5.4-1.el6.x86_64
postgresql95-libs-9.5.5-1PGDG.rhel6.x86_64
postgresql95-9.5.5-1PGDG.rhel6.x86_64
postgresql95-contrib-9.5.5-1PGDG.rhel6.x86_64
postgresql95-server-9.5.5-1PGDG.rhel6.x86_64
sqlite-3.6.20-1.el6_7.2.x86_64
mongodb-2.4.14-4.el6.x86_64
mongodb-server-2.4.14-4.el6.x86_64
mysql-devel-5.1.73-8.el6_8.x86_64
innotop-1.11.4-1.el6.noarch
Percona-Server-shared-56-5.6.35-rel80.0.el6.x86_64
mysql-community-libs-5.7.16-1.el6.x86_64
redis-2.4.10-1.el6.x86_64
percona-toolkit-3.0.3-1.el6.x86_64
percona-zabbix-templates-1.1.7-2.noarch
pmm-client-1.1.6-1.x86_64

通常，RPM 可以分为这几大类：娱乐（Amusement）、开发（Development）、文档（Document）、硬件（Hardware）、综合包（Metapackages）、多媒体（Multimedia）、生产力（Productivity）和系统（System）。

查询特定RPM包依赖

查询RPM包依赖

[root@iZuf6c08fdv8duubho2b0rZ ~]# rpm -q --requires percona-server
coreutils  
shadow-utils  
/bin/sh  
/bin/sh  
/bin/sh  
/bin/sh  
rpmlib(FileDigests) <= 4.6.0-1
rpmlib(PayloadFilesHavePrefix) <= 4.0-1
rpmlib(CompressedFileNames) <= 3.0.4-1
rpmlib(PayloadIsXz) <= 5.2-1

查询RPM包提供

[root@iZuf6c08fdv8duubho2b0rZ ~]# rpm -q --provides percona-server
adt_null.so()(64bit)  
audit_log.so()(64bit)  
auth.so()(64bit)  
auth_socket.so()(64bit)  
auth_test_plugin.so()(64bit)  
connection_control.so()(64bit)  
ha_tokudb.so()(64bit)  
handlersocket.so()(64bit)  
innodb_engine.so()(64bit)  
libHotBackup.so()(64bit)  
libdaemon_example.so()(64bit)  
libfnv1a_udf.so()(64bit)  
libfnv_udf.so()(64bit)  
libmemcached.so()(64bit)  
libmurmur_udf.so()(64bit)  
libperconaserverclient.so.18()(64bit)  
libperconaserverclient.so.18(libperconaserverclient_18)(64bit)  
mypluglib.so()(64bit)  
mysql_no_login.so()(64bit)  
perl(My::Config)  
perl(My::Config::Group)  
perl(My::Config::Option)  
perl(My::ConfigFactory)  
perl(My::CoreDump)  
perl(My::Exec)  
perl(My::File::Path)  
perl(My::Find)  
perl(My::Handles)  
perl(My::Memcache)  
perl(My::Memcache::Binary)  
perl(My::Options)  
perl(My::Platform)  
perl(My::SafeProcess)  
perl(My::SafeProcess::Base)  
perl(My::Suite::Query_response_time)  
perl(My::SysInfo)  
perl(My::Test)  
perl(Subunit) = 0.0.2
perl(mtr_cases)  
perl(mtr_match)  
perl(mtr_report)  
perl(mtr_results)  
perl(mtr_unique)  
qa_auth_client.so()(64bit)  
qa_auth_interface.so()(64bit)  
qa_auth_server.so()(64bit)  
query_response_time.so()(64bit)  
semisync_master.so()(64bit)  
semisync_slave.so()(64bit)  
test_udf_services.so()(64bit)  
tokudb_backup.so()(64bit)  
validate_password.so()(64bit)  
percona-server = 5.6.36-82.0
percona-server(x86-64) = 5.6.36-82.0

查询某依赖的RPM包

1
2
3

[root@iZuf6c08fdv8duubho2b0rZ ~]# rpm -q --whatrequires openssl 
postfix-2.6.6-6.el6_5.x86_64
openssl-devel-1.0.1e-48.el6_8.4.x86_64

RPM包的验证

验证某个RPM包状态

[root@iZuf6c08fdv8duubho2b0rZ ~]# rpm -V percona-server
missing     /usr/local/percona/data
missing     /usr/local/percona/data/test
missing     /usr/local/percona/data/test/db.opt
missing     /usr/local/percona/my.cnf
此处由于自定义了RPM包，配置文件和数据目录转移了，显示missing

RPM包的备份与恢复

RPM的Database文件存放在/var/lib/rpm目录。除了 __db.00* 是数据文件外，其他文件都属于 Berkeley DB 格式,所以要注意备份此目录。

RPM延伸

其他高级用法，请参考：https://docs.fedoraproject.org/en-US/Fedora_Draft_Documentation/0.1/html/RPM_Guide/index.html

安装依赖

1	yum -y install rpm-build readline-devel redhat-rpm-config gcc gcc-c++ cmake make zlib-devel openssl-devel perl libtool automake autoconf time ccache bison libaio-devel gperf

创建用户及配置

创建用户

1 2	useradd jiessie su - jiessie

创建目录

1 2	mkdir -p ~/rpmbuild/{BUILD,RPMS,SOURCES,SPECS,SRPMS} echo '%_topdir /home/jiessie/rpmbuild' > ~/.rpmmacros

RPMBUILD

目录结构

[root@iZuf6c08fdv8duubho2b0rZ jiessie]# tree rpmbuild/
rpmbuild/
├── BUILD   #编译生成的临时文件
├── RPMS    #RPM包存放目录
├── SOURCES #源码存放目录
├── SPECS   #SPEC文件目录
└── SRPMS   #SRC RPM包存放目录

5 directories, 0 files

编写SPEC文件

详情，请参考：https://fedoraproject.org/wiki/How_to_create_an_RPM_package/zh-cn#.25prep_.E9.83.A8.E5.88.86

-bash-4.1$ cat percona.5.6.36.spec 
Name: percona-server    #软件包名
Version:5.6.36          #版本号
Release: 82.0           #发布序列号
License: GPL            #软件授权方式      
Vendor: Jiessie         #作者信息 

Group: applications/database        #软件包所属类别
URL: https://www.percona.com/downloads/Percona-Server-5.6/Percona-Server-5.6.36-82.0/source/tarball/percona-server-5.6.36-82.0.tar.gz    #软件的项目主页
BuildRoot: %{_tmppath}/%{name}-%{version}-%{release}-root   #安装或编译时使用的虚拟目录，在生成rpm的过程中，执行make install时就会把软件安装到该路径中，默认构建根目录为%{_topdir}/BUILDROOT，可以用$RPM_BUILD_ROOT方式引用
BuildRequires: cmake                        #编译过程中需要的包列表
Requires: coreutils,shadow-utils            #程序安装时需要的包列表
Packager: dwj999@163.com                    #打包者信息
Autoreq: no
Source: percona-server-5.6.36-82.0.tar.gz   #源代码包
prefix: /usr/local/percona                  #rpm包安装的路径
Summary: percona-server-5.6.36-82.0         #一行简短的软件包介绍

%description                                #软件包详细说明，可写在多个行上
Percona Server is an enhanced, drop-in MySQL® replacement which has been downloaded more than 1,000,000 times.
A free open source solution, Percona Server is a MySQL alternative which offers breakthrough performance, scalability, features, and instrumentation. Self-tuning algorithms and support for extremely high-performance hardware make it the clear choice for organizations that demand excellent performance and reliability from their MySQL database server.

%define MYSQL_USER mysql                    #定义的MYSQL_USER变量
%define MYSQL_GROUP mysql                   #定义的MYSQL_GROUP变量
%define __os_install_post %{nil}

%prep                                       #这个段是预处理段，通常用来执行一些解开源程序包的命令，为下一步的编译安装作准备。读取位于%_sourcedir(~/rpmbuild/SOURCES)目录的源代码和patch，随后会解压源代码至%_builddir(~/rpmbuild/BUILD)下的子目录并应用所有patch，描述解压源代码的方法，包含%setup和%patch命令
%setup -q -n %{name}-%{version}-%{release}  #-q不输出信息，-n解压到指定目录中

%build  #包含构建阶段执行的命令，构建完成后便开始后续安装
CFLAGS="-O3 -g -static-libgcc -fno-omit-frame-pointer -fno-strict-aliasing"
CXX=g++
CXXFLAGS="-O3 -g -fno-rtti -static-libgcc -fno-omit-frame-pointer -fno-strict-aliasing"
export CFLAGS CXX CXXFLAGS

cmake .                                                  \
  -DSYSCONFDIR:PATH=%{prefix}                            \
  -DCMAKE_INSTALL_PREFIX:PATH=%{prefix}                  \
  -DCMAKE_BUILD_TYPE:STRING=Release                      \
  -DENABLE_PROFILING:BOOL=ON                             \
  -DWITH_DEBUG:BOOL=OFF                                  \
  -DWITH_VALGRIND:BOOL=OFF                               \
  -DENABLE_DEBUG_SYNC:BOOL=OFF                           \
  -DWITH_EXTRA_CHARSETS:STRING=all                       \
  -DWITH_SSL:STRING=bundled                              \
  -DWITH_UNIT_TESTS:BOOL=OFF                             \
  -DWITH_ZLIB:STRING=bundled                             \
  -DWITH_PARTITION_STORAGE_ENGINE:BOOL=ON                \
  -DWITH_INNOBASE_STORAGE_ENGINE:BOOL=ON                 \
  -DWITH_TOKUDB_STORAGE_ENGINE:BOOL=ON                   \
  -DWITH_ARCHIVE_STORAGE_ENGINE:BOOL=ON                  \
  -DWITH_BLACKHOLE_STORAGE_ENGINE:BOOL=ON                \
  -DWITH_PERFSCHEMA_STORAGE_ENGINE:BOOL=ON               \
  -DDEFAULT_CHARSET=utf8mb4                              \
  -DDEFAULT_COLLATION=utf8mb4_general_ci                 \
  -DENABLED_LOCAL_INFILE:BOOL=ON                         \
  -DWITH_EMBEDDED_SERVER=0                               \
  -DINSTALL_LAYOUT:STRING=STANDALONE                     \
  -DCOMMUNITY_BUILD:BOOL=ON                              \
  -DWITHOUT_EXAMPLE_STORAGE_ENGINE=1                     \
  -DWITHOUT_NDBCLUSTER_STORAGE_ENGINE=1                  \
  -DENABLED_PROFILING=1                                  \
  -DINNODB_PAGE_ATOMIC_REF_COUNT=1                       \
  -DWITH_INNODB_MEMCACHED=1;

make -j `cat /proc/cpuinfo | grep processor| wc -l`

%install    #包含安装阶段执行的命令。命令将文件从 %{_builddir} 目录安装至 %{buildroot} 目录
make DESTDIR=$RPM_BUILD_ROOT install
cp %{_sourcedir}/my.cnf $RPM_BUILD_ROOT%{prefix}/   #拷贝模板配置文件,下文会介绍配置文件

%clean      #清理安装目录的命令
rm -rf $RPM_BUILD_ROOT
rm -rf $RPM_BUILD_DIR/*

%files      #需要被打包/安装的文件列表
%defattr(-, %{MYSQL_USER}, %{MYSQL_GROUP})              #设置默认文件权限格式%defattr(<file permissions>, <user>, <group>, <directory permissions>)，-使用默认的权限，文本文件是0644，可执行文件是0755
%attr(755, %{MYSQL_USER}, %{MYSQL_GROUP}) %{prefix}/*   

%pre        #软件安装之前执行的脚本
if ! id %{MYSQL_USER} > /dev/null 2>&1;then
useradd -M -s /sbin/nologin %{MYSQL_USER}               #创建MySQL用户
fi

%post       #软件安装之后执行的脚本
if [ -f %{prefix}/support-files/mysql.server > /dev/null 2>&1 ]  && [ ! -f %{_initddir}/mysql > /dev/null 2>&1 ];then
cp %{prefix}/support-files/mysql.server %{_initddir}/mysqld #拷贝启动脚本
chmod +x %{_initddir}/mysqld                                #添加启动脚本执行权限
chkconfig --level 2345 %{_initddir}/mysqld on               #添加到系统启动服务中
fi

if [ ! -f %{_sysconfdir}/my.cnf ];then
cp %{prefix}/my.cnf %{_sysconfdir}/my.cnf                   #拷贝配置文件
else
cp %{prefix}/my.cnf %{_sysconfdir}/my.cnf.rpmnew            #如存在文件，则重命名
fi
mkdir -p /hwdata/data/percona                               #创建数据目录
chown -R mysql:mysql /hwdata/data/percona                   #授权数据目录
rm -rf %{prefix}/my.cnf                                     #删除prefix目录的my.cnf
rm -rf %{prefix}/data                                       #删除prefix目录的data目录
%{prefix}/scripts/mysql_install_db --rpm --user=mysql --basedir=%{prefix}   #初始化安装 --datadir=/hwdata/data/percona
/etc/init.d/mysqld start                                    #开启MySQL服务
%{prefix}/bin/mysqladmin -uroot password '123456'
%{prefix}/bin/mysql -uroot -p123456 -e "grant all privileges on *.* to root@'127.0.0.1' identified by '123456' with grant option;"
%{prefix}/bin/mysql -uroot -p123456 -e "grant all privileges on *.* to root@'localhost' identified by '123456' with grant option;"
%{prefix}/bin/mysql -uroot -p123456 -e "delete from mysql.user where Password='';"
%{prefix}/bin/mysql -uroot -p123456 -e "delete from mysql.db where User='';"
%{prefix}/bin/mysql -uroot -p123456 -e "delete from mysql.proxies_priv where Host!='localhost';"
%{prefix}/bin/mysql -uroot -p123456 -e "drop database test;"
%{prefix}/bin/mysql -uroot -p123456 -e "reset master;"

echo "PATH=/usr/local/percona/bin/:$PATH:$HOME/bin" >> /etc/profile #添加环境变量
echo "export PATH" >> /etc/profile  
source /etc/profile     #环境变量生效

%preun      #rpm卸载前执行的脚本，在升级的时候会执行
if [ -f %{_sysconfdir}/my.cnf ];then
mv %{_sysconfdir}/my.cnf %{_sysconfdir}/my.cnf.rpmold
fi

if [ -f %{_initddir}/mysql ];then
mv %{_initddir}/mysql %{_initddir}/mysql.rpmold
fi

%postun     #rpm卸载后执行的脚本，在升级rpm包的时候不会执行
rm -rf %{prefix}
rm -rf /hwdata/data/percona 
userdel -r %{MYSQL_USER} >/dev/null 2>&1

%changelog  #RPM 包变更日志

MySQL模板文件(my.cnf)

以下为my.cnf配置模板文件，不做过多解释，请参考：https://dev.mysql.com/doc/refman/5.6/en/option-files.html

-bash-4.1$ cat my.cnf 
[client]
port = 3306
socket = /tmp/mysql.sock
default-character-set = utf8

[mysql]
#prompt="MySQL [\d]> "
prompt="MySQL [\d] \R:\m:\\s > "
no-auto-rehash

[mysqld]
# basic setting #
port = 3306
socket = /tmp/mysql.sock
basedir = /usr/local/percona
datadir = /hwdata/data/percona
pid-file = /hwdata/data/percona/mysql.pid
user = mysql
bind-address = 0.0.0.0
server-id = 1
autocommit = 1
auto_increment_increment = 1
auto_increment_offset = 1
event_scheduler = 1     #slave need off
#read_only=ON           #slave need on
skip-name-resolve
skip-external-locking
transaction_isolation = READ-COMMITTED
init-connect = 'SET NAMES utf8'
character-set-server = utf8
query_cache_size = 0
query_cache_type = 0

max_connections = 2000
max_connect_errors = 100000
max_length_for_sort_data = 8192
max_allowed_packet = 32M
max_heap_table_size = 128M
tmp_table_size = 128M
open_files_limit = 65535
table_open_cache = 1024
table_definition_cache = 2048
table_open_cache = 1024

read_buffer_size = 4M
read_rnd_buffer_size = 8M
sort_buffer_size = 4M
join_buffer_size = 16M
key_buffer_size = 256M

back_log = 300
expire_logs_days = 7
log_error = /hwdata/data/percona/mysql-error.log
log_bin = mysql-bin
log_warnings = 2
log_queries_not_using_indexes = 1
binlog_format = ROW
slow_query_log = 1
slow_query_log_file = /hwdata/data/percona/mysql-slow.log
long_query_time = 1
log_slow_rate_limit=100
log_slow_rate_type=query
log_slow_verbosity=full
slow_query_log_always_write_time=1
slow_query_log_use_global_control=all
log_slow_admin_statements = 1
log_slow_slave_statements = 1
log_throttle_queries_not_using_indexes = 10
min_examined_row_limit = 100
binlog-rows-query-log-events = 1
log-bin-trust-function-creators = 1
binlog_cache_size = 1M
userstat=1
performance_schema = 0
#lower_case_table_names = 1
default_storage_engine = InnoDB
innodb_page_size = 16384
innodb_data_file_path = ibdata1:1024M:autoextend
innodb_file_per_table = 1
innodb_open_files = 1024
innodb_buffer_pool_size = 4096M
innodb_buffer_pool_instances = 8
innodb_buffer_pool_load_at_startup = 1
innodb_buffer_pool_dump_at_shutdown = 1
innodb_write_io_threads = 8
innodb_read_io_threads = 8
innodb_io_capacity = 4000
innodb_io_capacity_max = 8000
innodb_lru_scan_depth = 4000
innodb_thread_concurrency = 0
innodb_purge_threads = 1
innodb_flush_log_at_trx_commit = 2
innodb_flush_method=O_DIRECT
innodb_file_format = Barracuda
innodb_file_format_max = Barracuda
innodb_undo_logs = 128
innodb_undo_tablespaces = 3
innodb_log_buffer_size = 16M
innodb_log_file_size = 1536M
innodb_log_files_in_group = 3
innodb_max_dirty_pages_pct = 75
innodb_lock_wait_timeout = 120
innodb_large_prefix = 1
innodb_print_all_deadlocks = 1
innodb_autoinc_lock_mode = 2
innodb_online_alter_log_max_size=1G
innodb_sync_spin_loops = 100
innodb_spin_wait_delay = 30
metadata_locks_hash_instances = 8

gtid_mode = on
enforce_gtid_consistency = 1
log_slave_updates

slave_skip_errors = ddl_exist_errors
slave-rows-search-algorithms = 'INDEX_SCAN,HASH_SCAN'
slave_net_timeout = 60
master_info_repository = TABLE
relay_log_info_repository = TABLE

thread_cache_size = 64
thread_handling = pool-of-threads
thread_pool_oversubscribe = 10

bulk_insert_buffer_size = 8M
myisam_sort_buffer_size = 64M
myisam_max_sort_file_size = 10G
myisam_repair_threads = 1

interactive_timeout = 1800
wait_timeout = 1800
lock_wait_timeout = 1800

[mysqldump]
quick
max_allowed_packet = 16M

[myisamchk]
key_buffer_size = 256M
sort_buffer_size = 8M
read_buffer = 4M
write_buffer = 4M
-bash-4.1$

上传源码文件及模板文件

-bash-4.1$ ls -l rpmbuild/SOURCES/
总用量 54808
-rw-r--r-- 1 jiessie jiessie     3629 7月  14 10:22 my.cnf
-rw-r--r-- 1 jiessie jiessie 56116691 7月  14 10:22 percona-server-5.6.36-82.0.tar.gz
-bash-4.1$

执行编译过程

rpmbuild -bb percona.5.6.36.spec

-bash-4.1$ pwd
/home/jiessie/rpmbuild/SPECS
-bash-4.1$ rpmbuild -bb percona.5.6.36.spec 
Executing(%prep): /bin/sh -e /var/tmp/rpm-tmp.YmJTDn
+ umask 022
+ cd /home/jiessie/rpmbuild/BUILD
+ LANG=C
+ export LANG
+ unset DISPLAY
+ cd /home/jiessie/rpmbuild/BUILD
+ rm -rf percona-server-5.6.36-82.0
+ /usr/bin/gzip -dc /home/jiessie/rpmbuild/SOURCES/percona-server-5.6.36-82.0.tar.gz
+ /bin/tar -xf -
+ STATUS=0
+ '[' 0 -ne 0 ']'
+ cd percona-server-5.6.36-82.0
+ /bin/chmod -Rf a+rX,u+w,g-w,o-w .
+ exit 0
Executing(%build): /bin/sh -e /var/tmp/rpm-tmp.vvnRcl
+ umask 022
+ cd /home/jiessie/rpmbuild/BUILD
+ cd percona-server-5.6.36-82.0
+ LANG=C
+ export LANG
+ unset DISPLAY
+ CFLAGS='-O3 -g -static-libgcc -fno-omit-frame-pointer -fno-strict-aliasing'
+ CXX=g++
+ CXXFLAGS='-O3 -g -fno-rtti -static-libgcc -fno-omit-frame-pointer -fno-strict-aliasing'
+ export CFLAGS CXX CXXFLAGS
+ cmake . -DSYSCONFDIR:PATH=/usr/local/percona -DCMAKE_INSTALL_PREFIX:PATH=/usr/local/percona -DCMAKE_BUILD_TYPE:STRING=Release -DENABLE_PROFILING:BOOL=ON -DWITH_DEBUG:BOOL=OFF -DWITH_VALGRIND:BOOL=OFF -DENABLE_DEBUG_SYNC:BOOL=OFF -DWITH_EXTRA_CHARSETS:STRING=all -DWITH_SSL:STRING=bundled -DWITH_UNIT_TESTS:BOOL=OFF -DWITH_ZLIB:STRING=bundled -DWITH_PARTITION_STORAGE_ENGINE:BOOL=ON -DWITH_INNOBASE_STORAGE_ENGINE:BOOL=ON -DWITH_TOKUDB_STORAGE_ENGINE:BOOL=ON -DWITH_ARCHIVE_STORAGE_ENGINE:BOOL=ON -DWITH_BLACKHOLE_STORAGE_ENGINE:BOOL=ON -DWITH_PERFSCHEMA_STORAGE_ENGINE:BOOL=ON -DDEFAULT_CHARSET=utf8mb4 -DDEFAULT_COLLATION=utf8mb4_general_ci -DENABLED_LOCAL_INFILE:BOOL=ON -DWITH_EMBEDDED_SERVER=0 -DINSTALL_LAYOUT:STRING=STANDALONE -DCOMMUNITY_BUILD:BOOL=ON -DWITHOUT_EXAMPLE_STORAGE_ENGINE=1 -DWITHOUT_NDBCLUSTER_STORAGE_ENGINE=1 -DENABLED_PROFILING=1 -DINNODB_PAGE_ATOMIC_REF_COUNT=1 -DWITH_INNODB_MEMCACHED=1
-- Running cmake version 2.8.12.2
-- Found Git: /usr/bin/git (found version "1.7.1") 
-- The C compiler identification is GNU 4.4.7
-- The CXX compiler identification is GNU 4.4.7
-- Check for working C compiler: /usr/bin/cc
-- Check for working C compiler: /usr/bin/cc -- works
-- Detecting C compiler ABI info
-- Detecting C compiler ABI info - done
-- Check for working CXX compiler: /usr/bin/g++
-- Check for working CXX compiler: /usr/bin/g++ -- works
-- Detecting CXX compiler ABI info
-- Detecting CXX compiler ABI info - done
-- ...省略部分...
-- Not building tokudb-backup-plugin
-- Library perconaserverclient depends on OSLIBS -lpthread;m;rt;dl
-- Skipping deb packaging on unsupported platform Final.
-- CMAKE_BUILD_TYPE: Release
-- COMPILE_DEFINITIONS: HAVE_CONFIG_H
-- CMAKE_C_FLAGS: -O3 -g -static-libgcc -fno-omit-frame-pointer -fno-strict-aliasing  -Wall -Wextra -Wformat-security -Wvla -Wwrite-strings -Wdeclaration-after-statement
-- CMAKE_CXX_FLAGS: -O3 -g -fno-rtti -static-libgcc -fno-omit-frame-pointer -fno-strict-aliasing  -Wall -Wextra -Wformat-security -Wvla -Woverloaded-virtual -Wno-unused-parameter
-- CMAKE_C_FLAGS_RELEASE: -O3 -DNDEBUG -DDBUG_OFF
-- CMAKE_CXX_FLAGS_RELEASE: -O3 -DNDEBUG -DDBUG_OFF
-- Configuring done
-- Generating done
CMake Warning:
  Manually-specified variables were not used by the project:

    COMMUNITY_BUILD
    ENABLE_DEBUG_SYNC
    ENABLE_PROFILING
    WITH_TOKUDB_STORAGE_ENGINE


-- Build files have been written to: /home/jiessie/rpmbuild/BUILD/percona-server-5.6.36-82.0
++ cat /proc/cpuinfo
++ grep processor
++ wc -l
+ make -j 4
Scanning dependencies of target abi_check
Scanning dependencies of target INFO_BIN
Scanning dependencies of target INFO_SRC
Scanning dependencies of target zlib
[  0%] [  0%] Building C object zlib/CMakeFiles/zlib.dir/adler32.c.o
[100%] ...省略部分...
[100%] Built target udf_example
+ exit 0
Executing(%install): /bin/sh -e /var/tmp/rpm-tmp.MNmU8M
+ umask 022
+ cd /home/jiessie/rpmbuild/BUILD
+ '[' /home/jiessie/rpmbuild/BUILDROOT/percona-server-5.6.36-82.0.x86_64 '!=' / ']'
+ rm -rf /home/jiessie/rpmbuild/BUILDROOT/percona-server-5.6.36-82.0.x86_64
++ dirname /home/jiessie/rpmbuild/BUILDROOT/percona-server-5.6.36-82.0.x86_64
+ mkdir -p /home/jiessie/rpmbuild/BUILDROOT
+ mkdir /home/jiessie/rpmbuild/BUILDROOT/percona-server-5.6.36-82.0.x86_64
+ cd percona-server-5.6.36-82.0
+ LANG=C
+ export LANG
+ unset DISPLAY
+ make DESTDIR=/home/jiessie/rpmbuild/BUILDROOT/percona-server-5.6.36-82.0.x86_64 install
[  0%] Built target INFO_BIN
[100%] ...省略部分...
[100%] Built target my_safe_process
Install the project...
-- Install configuration: "Release"
-- Installing: /home/jiessie/rpmbuild/BUILDROOT/percona-server-5.6.36-82.0.x86_64/usr/local/percona/docs/mysql.info
-- ...省略部分...
cpio: percona-server-5.6.36-82.0/storage/innobase/pars0lex.l: Cannot stat: No such file or directory
97189 blocks
+ /usr/lib/rpm/check-buildroot
Processing files: percona-server-5.6.36-82.0.x86_64
Provides: adt_null.so()(64bit) audit_log.so()(64bit) auth.so()(64bit) auth_socket.so()(64bit) auth_test_plugin.so()(64bit) connection_control.so()(64bit) handlersocket.so()(64bit) innodb_engine.so()(64bit) libdaemon_example.so()(64bit) libfnv1a_udf.so()(64bit) libfnv_udf.so()(64bit) libmemcached.so()(64bit) libmurmur_udf.so()(64bit) libperconaserverclient.so.18()(64bit) libperconaserverclient.so.18(libperconaserverclient_18)(64bit) mypluglib.so()(64bit) mysql_no_login.so()(64bit) perl(My::Config) perl(My::Config::Group) perl(My::Config::Option) perl(My::ConfigFactory) perl(My::CoreDump) perl(My::Exec) perl(My::File::Path) perl(My::Find) perl(My::Handles) perl(My::Memcache) perl(My::Memcache::Binary) perl(My::Options) perl(My::Platform) perl(My::SafeProcess) perl(My::SafeProcess::Base) perl(My::Suite::Query_response_time) perl(My::SysInfo) perl(My::Test) perl(Subunit) = 0.0.2 perl(mtr_cases) perl(mtr_match) perl(mtr_report) perl(mtr_results) perl(mtr_unique) qa_auth_client.so()(64bit) qa_auth_interface.so()(64bit) qa_auth_server.so()(64bit) query_response_time.so()(64bit) semisync_master.so()(64bit) semisync_slave.so()(64bit) test_udf_services.so()(64bit) validate_password.so()(64bit)
Requires(interp): /bin/sh /bin/sh /bin/sh /bin/sh
Requires(rpmlib): rpmlib(FileDigests) <= 4.6.0-1 rpmlib(PayloadFilesHavePrefix) <= 4.0-1 rpmlib(CompressedFileNames) <= 3.0.4-1
Requires(pre): /bin/sh
Requires(post): /bin/sh
Requires(preun): /bin/sh
Requires(postun): /bin/sh
Processing files: percona-server-debuginfo-5.6.36-82.0.x86_64
Checking for unpackaged file(s): /usr/lib/rpm/check-files /home/jiessie/rpmbuild/BUILDROOT/percona-server-5.6.36-82.0.x86_64
Wrote: /home/jiessie/rpmbuild/RPMS/x86_64/percona-server-5.6.36-82.0.x86_64.rpm
Wrote: /home/jiessie/rpmbuild/RPMS/x86_64/percona-server-debuginfo-5.6.36-82.0.x86_64.rpm
Executing(%clean): /bin/sh -e /var/tmp/rpm-tmp.FOVDND
+ umask 022
+ cd /home/jiessie/rpmbuild/BUILD
+ cd percona-server-5.6.36-82.0
+ rm -rf /home/jiessie/rpmbuild/BUILDROOT/percona-server-5.6.36-82.0.x86_64
+ rm -rf /home/jiessie/rpmbuild/BUILD/percona-server-5.6.36-82.0
+ exit 0
-bash-4.1$

查看生成的RPM文件

-bash-4.1$ ls -l rpmbuild/RPMS/x86_64/
总用量 99528
-rw-rw-r-- 1 jiessie jiessie 53752860 7月  14 10:34 percona-server-5.6.36-82.0.x86_64.rpm
-rw-rw-r-- 1 jiessie jiessie 48158544 7月  14 10:35 percona-server-debuginfo-5.6.36-82.0.x86_64.rpm
-bash-4.1$

percona-server-5.6.36-82.0.x86_64.rpm 此文件即是编译后生成的RPM安装文件

结束语

随着云计算时代的到来，docker被越来越多的应用的实际生产中，但对于公司规模较小，自动化平台还不够完善的企业，自制RPM包安装MySQL也是一种不错的选择。

hexo next 在github上搭建个人博客

发表于 2013-07-12 | 分类于工具

字数统计: | 阅读时长 ≈

前言

目前网上的很多平台都提供了博客功能，如csdn，51cto，cnblog，iteye等等，只要你想写博客，这些平台都可以选择。只需注册个帐号，就可以写博客，实现比较简单，但是局限性也很大，风格千篇一律，没有个性化，扩展也稍麻烦。并且，广告很多，这点很受不了。在此选择了hexo+next+github建立个人的博客平台。

准备

github：https://github.com/dwj999
发布平台：centos6.5 x86_64
发布工具：git
编写平台：windows7 x86_64
编写工具：有道云笔记
框架：hexo
主题：next

安装Node.js

操作系统为centos6.5，首先删除旧版本

1	yum -y remove nodejs

node使用最新版本，下载源码包

1
2
3

wget https://nodejs.org/dist/v8.1.4/node-v8.1.4-linux-x64.tar.xz  
tar -xvJf node-v8.1.4-linux-x64.tar.xz  
mv node-v8.1.4-linux-x64 /usr/local/node

配置系统环境变量

vim /etc/profile  
在底部添加 PATH 变量  
export PATH=$PATH:/usr/local/node/bin  
最后保存并使其生效即可  
source /etc/profile  
查看版本：node -v

安装Git(已安装可跳过)

1	yum install git-core

安装Hexo

这里以我的github为例，地址为：https://github.com/dwj999

创建目录  
mkdir /hwdata/dwj999  
切换目录  
cd /hwdata/dwj999  
安装 Hexo  
npm config set user 0  
npm config set unsafe-perm true  
npm install -g hexo-cli  
初始化 Hexo(如果报Error: ENOENT: no such file or directory, uv_cwd,可能是删除的旧版本nodejs导致,重启服务器再试) 
hexo init  
生成静态网页
hexo generate

安装插件

npm install hexo-generator-index --save  
npm install hexo-generator-archive --save  
npm install hexo-generator-category --save  
npm install hexo-generator-tag --save  
npm install hexo-server --save  
npm install hexo-deployer-git --save  
npm install hexo-deployer-heroku --save  
npm install hexo-deployer-rsync --save  
npm install hexo-deployer-openshift --save  
npm install hexo-renderer-marked --save  
npm install hexo-renderer-stylus --save  
npm install hexo-generator-feed --save  
npm install hexo-generator-sitemap --save

目录结构

|-- _config.yml  
|-- db.json  
|-- node_modules  
|-- package.json  
|-- package-lock.json  
|-- public  
|-- scaffolds  
|-- source  
    |-- _posts  
|-- themes

目录结构说明

_config.yml #全局配置文件，网站的很多信息都在这里配置，诸如网站名称，副标题，描述，作者，语言，主题，部署等等参数。请参考：https://hexo.io/zh-cn/docs/configuration.html
db.json #缓存文件
node_modules #hexo插件
package.json #hexo框架的参数
package-lock.json #根据package.json自动创建锁定依赖版本，删掉会重新创建
public  #生成的静态网页文件
scaffolds   #模板文件夹，请参考：https://hexo.io/zh-cn/docs/writing.html
source  #资源文件夹，用于存放用户资源。旧版本有两个文件夹，_drafts和_posts。新版本只有_posts，用于存放博客的正文。
themes  #网站主题目录，请参考：https://hexo.io/zh-cn/docs/themes.html
详情请参考：请参考：https://hexo.io/zh-cn/docs/setup.html

部署到GitHub

站点配置

修改Hexo站点配置文件/hwdata/dwj999/_config.yml,以下为例

# Site  
title: Jiessie's' Blog #标题  
subtitle: 没伞的孩子必须努力奔跑  #副标题
description: 最怕一生碌碌无为,还说平凡难能可贵！  
author: Jiessie  
language: zh-Hans  

url: http://dwj999.github.io  

deploy:  
  type: git  
  repo: git@github.com:dwj999/dwj999.github.io.git  
  branch: master  
  message: '站点更新:{{now("YYYY-MM-DD HH/mm/ss")}}'

github创建项目

仓库的名字必须和你的帐号对应，比如我的github地址：https://github.com/dwj999 新建仓库名为dwj999.github.io,因我的项目已创建，此为演示，故有提醒，可忽略

git配置

1
2
3

git config --global user.email "dwj999@163.com" 
git config --global user.name "dwj999"
ssh-keygen -t rsa -C "dwj999@163.com"   #一路回车，如已生成过，可覆盖成功后会~/.ssh目录生成key，复制id_rsa.pub内容

添加key

打开github地址：https://github.com/settings/ssh，打开ssh keys，添加key

代码上传

# linux编译
hexo generate
# 在主机的hexo目录下 执行以下命令将自动更新到Github
hexo d

站点访问，左面菜单和主题已经配置过

主题和配置文件

切换到站点目录，下载主题代码

1 2	cd /hwdata/dwj999 git clone https://github.com/iissnan/hexo-theme-next themes/next #主题的配置文件，位于 hexo/theme/next/_config.yml

配置主题

vim _config.yml
# 找到theme:修改后面的参数，默认是landscape
theme: next
hexo d -g #重新编译，上传

主题和第三方服务说明
官方文档很详情
请参考：
http://theme-next.iissnan.com/getting-started.html
http://theme-next.iissnan.com/theme-settings.html
http://theme-next.iissnan.com/third-party-services.html

发布文章

由于hexo支持markdown写文章，并且我使用的是windows机器，代码发布在linux机器上，所以选择了有道云笔记的markdown笔记来写文章，虽然语法支持有缺陷，但是以前习惯了使用有道云，没有使用其他工具，像Cmd Markdown的在线编辑器也不错。

1	hexo new "hexo next 在github上搭建个人博客" #创建了一个博客文章

再使用有道云的markdown进行写文章，写好后，导出，上传到github。