Mysql5.6简介
在MySQL 5.5发布两年后，Oracle宣布MySQL 5.6正式版发布，首个正式版版本号为MySQL 5.6.10。在MySQL 5.5中使用的是InnoDB作为默认的存储引擎，而MySQL 5.6则对InnoDB引擎进行了改造，提供全文索引能力，使InnoDB适合各种应用场景。
1、运行环境配置及安装
1.1、部署环境
系统：CentOS6.4 （64位）最小化安装
1.2、下载地址及版本
http://dev.mysql.com/downloads/mysql/
下载版本：mysql-5.6.19.tar.gz
1.3、安装
1.3.1编译环境准备
yum groupinstall -y"Desktop Platform Development" "Development tools"
yum install -y vim cmakebison-devel ncurses-devel perl-DBD-MySQL openssl openssl-devel freetype-demos libcurl-devellibjpeg-turbo-static libpng-static wget
关闭selinux
setenforce0
修改selinux配置文件
vim/etc/selinux/config
SELINUX=disabled
编辑主从hosts文件
shell>vim/etc/hosts
192.168.40.128master.testdb.com
192.168.40.129slave.testdb.com
1.3.2建立mysql所需账号、目录及权限
groupadd mysql
useradd -g mysql -s /bin/false -M mysql
mkdir -pv /data/mysqldata
mkdir -pv /data/mysqlbinlog
chown -R mysql.mysql /data/mysqldata
chown -R mysql.mysql /data/mysqlbinlog
1.3.3解压并安装mysql
注：在启动MySQL服务时，会按照一定次序搜索my.cnf，先在/etc目录下找，找不到则会搜索"$basedir/my.cnf"，在本例中就是 /usr/local/mysql/my.cnf，这是新版MySQL的配置文件的默认位置！注意：在CentOS 6.4版操作系统的最小安装完成后，在/etc目录下会存在一个my.cnf，需要将此文件更名为其他的名字，如：/usr/local/mysql/my.cnf.bak，否则，该文件会干扰源码安装的MySQL的正确配置，造成无法启动。
tar xf mysql-5.6.19.tar.gz
cd mysql-5.6.19
cmake -DCMAKE_INSTALL_PREFIX=/usr/local/mysql-DDEFAULT_CHARSET=utf8-DDEFAULT_COLLATION=utf8_general_ci-DEXTRA_CHARSETS=all-DWITH_MYISAM_STORAGE_ENGINE=1-DWITH_INNOBASE_STORAGE_ENGINE=1-DWITH_ARCHIVE_STORAGE_ENGINE=1-DWITH_BLACKHOLE_STORAGE_ENGINE=1-DWITH_MEMORY_STORAGE_ENGINE=1-DWITH_FEDERATED_STORAGE_ENGINE=1-DWITH_READLINE=1-DENABLED_LOCAL_INFILE=1-DMYSQL_DATADIR=/data/mysqldata-DMYSQL_USER=mysql-DMYSQL_TCP_PORT=3306-DSYSCONFDIR=/etc-DWITH_SSL=yes
编译注解：
-DCMAKE_INSTALL_PREFIX=/usr/local/mysql\ #安装目录
-DDEFAULT_CHARSET=utf8 \ #默认字符
-DDEFAULT_COLLATION=utf8_general_ci \ #校验字符
-DEXTRA_CHARSETS=all \ #安装所有扩展字符集
-DWITH_MYISAM_STORAGE_ENGINE=1 \ #安装myisam存储引擎
-DWITH_INNOBASE_STORAGE_ENGINE=1 \ #安装innodb存储引擎
-DWITH_ARCHIVE_STORAGE_ENGINE=1 \ #安装archive存储引擎
-DWITH_BLACKHOLE_STORAGE_ENGINE=1 \ #安装blackhole存储引擎
-DWITH_MEMORY_STORAGE_ENGINE=1 \ #安装memory存储引擎
-DWITH_FEDERATED_STORAGE_ENGINE=1 \ #安装frderated存储引擎
-DWITH_READLINE=1 \ #快捷键功能
-DENABLED_LOCAL_INFILE=1 \ #允许从本地导入数据
-DMYSQL_DATADIR=/data/mysqldata \ #数据库存放目录
-DMYSQL_USER=mysql \ #数据库属主
-DMYSQL_TCP_PORT=3306 \ #数据库端口
-DSYSCONFDIR=/etc \ #MySQL配辑文件
-DWITH_SSL=yes #数据库SSL
make && make install
将mysql的库文件路径加入系统的库文件搜索路径中
利用ldconfig导入系统库
echo"/usr/local/mysql/lib" >> /etc/ld.so.conf.d/mysql.conf
ldconfig
输出mysql的头文件到系统头文件
ln -s /usr/local/mysql/include/mysql/usr/include/mysql
进入安装路径,初始化配置脚本
cd /usr/local/mysql
scripts/mysql_install_db --basedir=/usr/local/mysql --datadir=/data/mysqldata--user=mysql
复制mysql启动脚本到系统服务目录
cp/usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld
系统启动项相关配置
chkconfig --add mysqld #添加开机启动服务
chkconfig mysqld on #设置mysql启动
配置mysql环境变量
vim /etc/profile
export PATH=$PATH:/usr/local/mysql/bin
加载环境变量
source /etc/profile
2、配置mysql主从同步
Master：192.168.40.128
Host： master.testdb.com
防火墙添加规则，允许主从两机互相访问：
iptables -I INPUT -s 192.168.40.129 -jACCEPT
Slave：192.168.40.129
Host： slave.testdb.com
防火墙添加规则，允许主从两机互相访问：
iptables -I INPUT -s 192.168.40.128 -jACCEPT
2.1修改mysql配置文件，配置GTID,创建同步账号
配置my.cnf
Mysql5.6默认my.cnf在安装目录下/usr/local/mysql/
根据服务器配置设置my.cnf内的参数,必须进行设置的为：
\\主节点：
[mysqld]
binlog_format=row
server-id = 1
datadir = /data/mysqldata
innodb_file_per_table = 1
log-bin = /data/mysqlbinlog/master_bin.log
log-slave-updates=true
# 忽略mysql系统库复制
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
binlog-ignore-db=performance_schema
binlog-ignore-db=test
replicate-ignore-db=test
replicate-ignore-db=mysql
replicate-ignore-db=information_schema
replicate-ignore-db=performance_schema
#创建function函数必须开启
log_bin_trust_function_creators=1
gtid-mode=on
enforce-gtid-consistency=true
master-info-repository=TABLE
relay-log-info-repository=TABLE
sync-master-info=1
slave-parallel-workers=4
binlog-checksum=CRC32
master-verify-checksum=1
slave-sql-verify-checksum=1
binlog-rows-query-log_events=1
report-port=3306
report-host=master.testdb.com
\\从节点：
[mysqld]
binlog_format=row
server-id = 2
datadir = /data/mysqldata
innodb_file_per_table = 1
log-bin = /data/mysqlbinlog/slave_bin.log
log-slave-updates=true
# 忽略mysql系统库复制
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
binlog-ignore-db=performance_schema
binlog-ignore-db=test
replicate-ignore-db=test
replicate-ignore-db=mysql
replicate-ignore-db=information_schema
replicate-ignore-db=performance_schema
#创建function函数必须开启
log_bin_trust_function_creators=1
gtid-mode=on
enforce-gtid-consistency=true
master-info-repository=TABLE
relay-log-info-repository=TABLE
sync-master-info=1
slave-parallel-workers=4
binlog-checksum=CRC32
master-verify-checksum=1
slave-sql-verify-checksum=1
binlog-rows-query-log_events=1
report-port=3306
report-host=slave.testdb.com
选项解析：
datadir：数据文件存放路径
innodb_file_per_table = 1：独立表空间
log-bin = /data/mysqlbinlog/slave_bin.log：二进制日志存放路径
binlog-format：二进制日志的格式，有row、statement和mixed三种类型；需要注意的是：当设置隔离级别为READ-COMMITED必须设置二进制日志格式为ROW，现在MySQL官方认为STATEMENT这个已经不再适合继续使用；但mixed类型在默认的事务隔离级别下，可能会导致主从数据不一致；
log-bin：启用二进制日志；
server-id：同一个复制拓扑中的所有服务器的id号必须惟一；
log-slave-updates：slave更新时是否记录到日志中；
gtid-mode：指定GTID的类型，否则就是普通的复制架构
enforce-gtid-consistency：是否强制GTID的一致性
report-port：产生复制报告时在哪个端口上提供相关功能
report-host：产生复制报告时在哪个主机上提供相关功能，一般为自己的主机名
master-info-repository和relay-log-info-repository：启用此两项，可用于实现在崩溃时保证二进制及从服务器安全的功能；
sync-master-info：启用之可确保服务器崩溃时无信息丢失；
slave-paralles-workers：设定从服务器启动几个SQL复制线程数；0表示关闭多线程复制功能；数字太大也无意义最好与要复制的数据库的数目相同
binlog-checksum：复制时是否校验二进制文件的完整性等相关功能；binlog的校验格式校验算法（CRC32：循环冗余校验码32位）
master-verify-checksum：检验主服务器二进制日志的相关功能
slave-sql-verify-checksum：校验从服务器中继日志的相关功能的
binlog-rows-query-log-events：启用之可用于在二进制日志详细记录事件相关的信息，可降低故障排除的复杂度；
启动mysql
service mysqld start
创建sock链接
ln -s /var/lib/mysql/mysql.sock/tmp/mysql.sock
设置初始账户,并登陆后台
/usr/local/mysql/bin/mysqladmin-u root password 111111 #设置密码
登陆mysql
Shell> mysql –uroot –p 111111
mysql> grant all privileges on *.* toroot@‘%‘ identified by ‘111111‘ with grant option; #给root用户非本地链接所有权限,并改密码和赋予其给其他人下发权限.
mysql> flush privileges; #刷新
登入mysql，创建同步账号
shell> mysql –uroot –p 111111
mysql> grant replicationclient,replication slave on *.* to ‘repl‘@‘192.168.40.%‘ identified by ‘mysql‘;
mysql> flush privileges;
mysql> show global variables like‘%UUID%‘;
+---------------+--------------------------------------+
| Variable_name | Value |
+---------------+--------------------------------------+
| server_uuid | cecbb9e0-07e1-11e4-bb96-000c2923ee83 |
+---------------+--------------------------------------+
1 row in set (0.01 sec)
2.2开启主从同步
登入从服务器mysql
shell> mysql –uroot –p 111111
mysql> grant replication slave on *.* to‘repl‘@‘192.168.40.%‘ identified by ‘mysql‘;
mysql> flush privileges;
同步
mysql > stop slave;
mysql > reset slave;
mysql>CHANGE MASTER TO MASTER_HOST=‘192.168.40.128‘, MASTER_USER=‘repl‘,MASTER_PASSWORD=‘mysql‘, MASTER_AUTO_POSITION=1;
mysql>show slave status \G
*************************** 1. row***************************
Slave_IO_State: Waitingfor master to send event
Master_Host: 192.168.40.128
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master_bin.000002
Read_Master_Log_Pos: 1274
Relay_Log_File:slave-relay-bin.000002
Relay_Log_Pos: 550
Relay_Master_Log_File: master_bin.000002
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB: test,mysql,information_schema,performance_schema
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: 1274
Relay_Log_Space: 754
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:cecbb9e0-07e1-11e4-bb96-000c2923ee83
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 theslave I/O thread to update it
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set: cecbb9e0-07e1-11e4-bb96-000c2923ee83:7
Executed_Gtid_Set: cecbb9e0-07e1-11e4-bb96-000c2923ee83:1-7,
f072628f-07e1-11e4-bb97-000c293e24ae:1-5
Auto_Position: 1
1 row in set (0.06 sec)
如果：
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
则说明同步成功。
此时在master库上解锁表：
mysql> unlock tables;
查看同步状态：
###########主节点：################
mysql> show processlist;
+----+------+----------------------+------+------------------+------+-----------------------------------------------------------------------+------------------+
| Id | User | Host | db | Command | Time | State |Info |
+----+------+----------------------+------+------------------+------+-----------------------------------------------------------------------+------------------+
| 3| root | localhost | NULL | Query | 0 | init | show processlist |
| 4| repl | slave.gtid.com:43028 | NULL | Binlog Dump GTID | 259 | Master has sent all binlog to slave;waiting for binlog to be updated | NULL |
+----+------+----------------------+------+------------------+------+-----------------------------------------------------------------------+------------------+
2 rows in set (0.00 sec)
###########从节点：################
mysql> show processlist;
+----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------------+------------------+
| Id | User | Host | db | Command | Time | State |Info |
+----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------------+------------------+
| 1| root | localhost | NULL |Query | 0 | init | show processlist |
| 8| system user | | NULL |Connect | 387 | Waiting for master tosend event | NULL |
| 9| system user | | NULL |Connect | 387 | Slave has read all relaylog; waiting for the slave I/O thread to update it | NULL |
| 10 | system user | | NULL | Connect | 387 | Waiting for an event fromCoordinator |NULL |
| 11 | system user | | NULL | Connect | 387 | Waiting for an event fromCoordinator | NULL |
| 12 | system user | | NULL | Connect | 387 | Waiting for an event fromCoordinator | NULL |
| 13 | system user | | NULL | Connect | 431 | Waiting for an event fromCoordinator | NULL |
+----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------------+------------------+
7 rows in set (0.05 sec)
测试：
可以在master数据库建立一个库和表，然后到slave上show databases进行验证。
2.3搭建mysql半复制(Semisynchronous Replication)
上面主从复制环境已经搭建成功，下面搭建mysql半复制(SemisynchronousReplication)
//master上安装半同步复制插件
mysql> install plugin rpl_semi_sync_master soname ‘semisync_master.so‘;
mysql> set global rpl_semi_sync_master_enabled=1;
mysql> set global rpl_semi_sync_master_timeout=1000;
mysql> show variables like ‘%semi%‘;+------------------------------------+-------+| Variable_name | Value |+------------------------------------+-------+| rpl_semi_sync_master_enabled | ON || rpl_semi_sync_master_timeout | 1000 || rpl_semi_sync_master_trace_level | 32 || rpl_semi_sync_master_wait_no_slave | ON |+------------------------------------+-------+4 rows in set (0.00 sec)
要想下次重启mysql自动生效,可以加入配置文件sehll> vim /etc/my.cnf[msyqld]rpl_semi_sync_master_enabled=1rpl_semi_sync_master_timeout=1000
//slave上安装插件
mysql> install pluginrpl_semi_sync_slave soname ‘semisync_slave.so‘;
mysql> set globalrpl_semi_sync_slave_enabled = 1;
mysql> stop slave;
mysql> start slave;
mysql> show variables like ‘%semi%‘;
+---------------------------------+-------+
| Variable_name | Value |
+---------------------------------+-------+
| rpl_semi_sync_slave_enabled | ON |
| rpl_semi_sync_slave_trace_level | 32 |
+---------------------------------+-------+
2 rows in set (0.00 sec)
同理，要想下次重启mysql自动生效,可以加入配置文件
sehll> vim /etc/my.cnf
[msyqld]
rpl_semi_sync_slave_enabled = 1
//在master服务器上测试半同步是否生效
mysql> show global status like ‘rpl_semi%‘;
+--------------------------------------------+-------+
| Variable_name | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients | 1 |
|Rpl_semi_sync_master_net_avg_wait_time | 0 |
| Rpl_semi_sync_master_net_wait_time | 0 |
| Rpl_semi_sync_master_net_waits | 0 |
| Rpl_semi_sync_master_no_times | 0 |
| Rpl_semi_sync_master_no_tx | 0 |
| Rpl_semi_sync_master_status | ON |
|Rpl_semi_sync_master_timefunc_failures | 0 |
|Rpl_semi_sync_master_tx_avg_wait_time | 0 |
| Rpl_semi_sync_master_tx_wait_time | 0 |
| Rpl_semi_sync_master_tx_waits | 0 |
|Rpl_semi_sync_master_wait_pos_backtraverse | 0 |
| Rpl_semi_sync_master_wait_sessions | 0 |
| Rpl_semi_sync_master_yes_tx | 0 |
+--------------------------------------------+-------+
14 rows in set (0.00 sec)
2.4基于ssl的主从同步
1.主服务器上生成私钥
shell> cd /etc/pki/CA/
shell> touch index.txt
shell> touch serial
shell> echo "00" > serial
shell> umask 077;openssl genrsa -out private/cakey.pem2048
Generating RSA private key, 2048 bit longmodulus
......................................+++
................................+++
e is 65537 (0x10001)
2.主服务器上生成自签证书
shell> openssl req -new -x509 -key private/cakey.pem-out cacert.pem -days36500
3.主服务器上申请证书
shell> cd /usr/local/mysql/
shell> mkdirssl
shell> cp /etc/pki/CA/cacert.pem /usr/local/mysql/ssl/
shell> umask 077; openssl genrsa -out/usr/local/mysql/ssl/master.key 2048
shell> openssl req -new -key/usr/local/mysql/ssl/master.key -out /usr/local/mysql/ssl/master.csr
shell> opensslca -in /usr/local/mysql/ssl/master.csr -out /usr/local/mysql/ssl/master.crt -days 36500
shell> chown -Rmysql.mysql /usr/local/mysql/ssl
4.配置文件中添加ssl支持
shell> vim /usr/local/mysql/my.cnf
[msyqld]
ssl
ssl-ca=/etc/pki/CA/cacert.pem
ssl-cert=/usr/local/mysql/ssl/master.crt
ssl-key=/usr/local/mysql/ssl/master.key
5.从服务器上
shell> cd /usr/local/mysql/
shell> mkdir ssl
shell> umask 077; openssl genrsa -out/usr/local/mysql/ssl/mysql.key 2048
shell> openssl req -new -key /usr/local/mysql/ssl/mysql.key -out /usr/local/mysql/ssl/mysql.csr
shell> chown -R mysql.mysql /usr/local/mysql/ssl/
6.把证书传给主服务器，在主服务器上签署证书后在传给从服务器
shell> cd /usr/local/mysql/ssl
shell> scp mysql.csr root@192.168.40.128:/etc/pki/CA
7.主服务器上签署证书
Shell> cd /etc/pki/CA/
shell> openssl ca -in mysql.csr -out mysql.crt -days 36500
8.传回给从服务器
shell> scp cacert.pem mysql.crt root@192.168.40.129:/usr/local/mysql/ssl
请确保主、从服务器中mysql路径下的ssl目录下的文件属主属组都是mysql
shell> chown -R mysql.mysql /usr/local/mysql/ssl/
9.配置从服务器my.cnf文件(证书路径建议不要在配置文件中指定，而是在change to命令中指定）
在my.cnf中添加ssl支持
[mysqld]
ssl #添加这一行
10.主从均重启mysql服务
shell> service mysqld restart
主服务器登录mysql：
mysql> show global variables like‘%ssl%‘;
+---------------+---------------------------------+
| Variable_name | Value |
+---------------+---------------------------------+
| have_openssl | DISABLED |
| have_ssl | DISABLED |
| ssl_ca | /etc/pki/CA/cacert.pem |
| ssl_capath | |
| ssl_cert | /usr/local/mysql/ssl/master.crt |
| ssl_cipher | |
| ssl_crl | |
| ssl_crlpath | |
| ssl_key | /usr/local/mysql/ssl/master.key |
+---------------+---------------------------------+
9 rows in set (0.07 sec)
11.主、从服务器上创建ssl链接用户
mysql> grant replication client,replication slave on*.* to "ssl"@"192.168.40.%" identified by ‘mysql‘;
mysql> flush privileges;
12.从服务器上配置
mysql > stop slave;
mysql > reset slave;
mysql > change master to master_host=‘192.168.40.128‘, master_user=‘ssl‘, master_password=‘mysql‘,MASTER_AUTO_POSITION=1,master_ssl=1,master_ssl_ca=‘/usr/local/mysql/ssl/cacert.pem‘,master_ssl_cert=‘/usr/local/mysql/ssl/mysql.crt‘,master_ssl_key=‘/usr/local/mysql/ssl/mysql.key‘;
mysql> start slave;
mysql> show slave status \G
*************************** 1. row***************************
Slave_IO_State: Waiting formaster to send event
Master_Host: 192.168.40.128
Master_User: ssl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File:master_bin.000003
Read_Master_Log_Pos: 734
Relay_Log_File:slave-relay-bin.000002
Relay_Log_Pos: 550
Relay_Master_Log_File: master_bin.000003
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB: test,mysql,information_schema,performance_schema
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: 734
Relay_Log_Space: 754
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: Yes
Master_SSL_CA_File: /usr/local/mysql/ssl/cacert.pem
Master_SSL_CA_Path:
Master_SSL_Cert:/usr/local/mysql/ssl/mysql.crt
Master_SSL_Cipher:
Master_SSL_Key:/usr/local/mysql/ssl/mysql.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:cecbb9e0-07e1-11e4-bb96-000c2923ee83
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 theslave I/O thread to update it
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set: cecbb9e0-07e1-11e4-bb96-000c2923ee83:16
Executed_Gtid_Set: cecbb9e0-07e1-11e4-bb96-000c2923ee83:1-16,
f072628f-07e1-11e4-bb97-000c293e24ae:1-7
Auto_Position: 1
1 row in set (0.00 sec)
3、搭建MHA环境
MHA介绍
MHA(MasterHigh Availability)是自动的master故障转移和Slave提升的软件包.它是基于标准的MySQL复制(异步/半同步).
MHA有两部分组成:MHA Manager(管理节点)和MHA Node(数据节点).
MHAManager可以单独部署在一*立机器上管理多个master-slave集群,也可以部署在一台slave上.MHA Manager探测集群的node节点,当发现master出现故障的时候,它可以自动将具有最新数据的slave提升为新的master,然后将所有其 它的slave导向新的master上.整个故障转移过程对应用程序是透明的。
MHA node运行在每台MySQL服务器上(master/slave/manager),它通过监控具备解析和清理logs功能的脚本来加快故障转移的。
MHA节点包含三个脚本，依赖perl模块。
save_binary_logs:保存和复制当掉的主服务器二进制日志。
apply_diff_relay_logs:识别差异的relay log事件，并应用于其他salve服务器。
purge_relay_logs:清除relay log文件。
需要在所有mysql服务器上安装MHA节点，MHA管理服务器也需要安装。MHA管理节点模块内部依赖MHA节点模块。MHA管理节点通过ssh连接管理mysql服务器和执行MHA节点脚本。MHA节点依赖perl的DBD::mysql模块。
MHA工作原理
-从宕机崩溃的master保存二进制日志事件(binlogevents)。
-识别含有最新更新的slave。
-应用差异的中继日志(relay log)到其它slave。
-应用从master保存的二进制日志事件(binlogevents)。
-提升一个slave为新master。
-使其它的slave连接新的master进行复制。
1、安装MHA
MHAmanager版本mha4mysql-manager-0.55-0.el6.noarch.rpm
MHA节点版本mha4mysql-node-0.54-0.el6.noarch.rpm
主服务器安装：
yum install -y perl-DBD-MySQL
从服务安装：
yum install -y perl-DBD-MySQLperl-Config-Tiny perl-Log-Dispatch perl-Parallel-ForkManager perl-MIME-Lite perl-Params-Validate
安装MHA manager （目前规定manager部署在slave服务器上192.168.40.129）
shell> yum install perl wget
shell> yum install cpan
shell> wgetftp://ftp.muug.mb.ca/mirror/centos/6.5/os/x86_64/Packages/compat-db43-4.3.29-15.el6.x86_64.rpm
shell> wgethttp://downloads.naulinux.ru/pub/NauLinux/6x/i386/sites/School/RPMS/perl-Log-Dispatch-2.27-1.el6.noarch.rpm
shell> wgethttp://dl.fedoraproject.org/pub/epel/6/i386/perl-Parallel-ForkManager-0.7.9-1.el6.noarch.rpm
shell> wgethttp://dl.fedoraproject.org/pub/epel/6/i386/perl-Mail-Sender-0.8.16-3.el6.noarch.rpm
shell> wgethttp://dl.fedoraproject.org/pub/epel/6/i386/perl-Mail-Sendmail-0.79-12.el6.noarch.rpm
shell> wget http://mirror.centos.org/centos/6/os/x86_64/Packages/perl-Time-HiRes-1.9721-136.el6.x86_64.rpm
shell> rpm -ivh perl-5.8.8-41.el5.x86_64.rpmcompat-db43-4.3.29-15.el6.x86_64.rpm perl-Log-Dispatch-2.27-1.el6.noarch.rpmperl-Parallel-ForkManager-0.7.9-1.el6.noarch.rpmperl-Mail-Sender-0.8.16-3.el6.noarch.rpmperl-Mail-Sendmail-0.79-12.el6.noarch.rpmperl-Time-HiRes-1.9721-136.el6.x86_64.rpm
shell> rpm -ivhmha4mysql-manager-0.55-0.el6.noarch.rpm
shell> rpm -ivhmha4mysql-manager-0.55-0.el6.noarch.rpm
2、建立ssh无密码登录环境
主mysql（192.168.40.128）
ssh-keygen -t rsa
ssh-copy-id -i .ssh/id_rsa.pubroot@192.168.40.129
从mysql（192.168.40.129）
ssh-keygen -t rsa
ssh-copy-id -i .ssh/id_rsa.pubroot@192.168.40.128
ssh-copy-id -i .ssh/id_rsa.pub root@192.168.40.129
因为mha运行时需要用到/usr/bin下的两个mysql命令（貌似路径写死了，更改/etc/profile对mha无效），所以建立两个软连接
ln -s /usr/local/mysql/bin/mysqlbinlog/usr/bin/mysqlbinlog
ln -s /usr/local/mysql/bin/mysql/usr/bin/mysql
从服务器的mysql要设置read_only=1
mysql> set global read_only=1;
3、配置MHA
1、在mha-manager上（本例是slave192.168.40.129上）
shell> mkdir –p /usr/local/mha-manager/app1
shell> mkdir –pv /etc/mha-manager
shell> vim /etc/mha-manager/app1.cnf
[server default]
manager_workdir=/usr/local/mha-manager/app1
manager_log=/usr/local/mha-manager/app1/manager.log
user=root
password=111111
ssh_user=root
repl_user=repl
repl_password=mysql
remote_workdir=/usr/local/mha-manager/app1
ping_interval=1
# master_ip_failover_script=/etc/mha/scripts/master_ip_failover
# shutdown_script=/etc/mha/scripts/power_manager
#report_script=/etc/mha/scripts/send_report
#master_ip_online_change_script=/etc/mha/scripts/master_ip_online_change
[server1]
hostname=192.168.40.128
master_binlog_dir=/data/mysqlbinlog
candidate_master=1
[server2]
hostname=192.168.40.129
master_binlog_dir=/data/mysqlbinlog
candidate_master=1
2、masterha_check_ssh工具验证ssh信任登录是否成功
shell>masterha_check_ssh--conf=/etc/mha-manager/app1.cnf
注意：用ssh-keygen实现三台主机之间相互免密钥登录决定这一步是否成功。
Tue Jun 10 09:26:23 2014 - [warning] Global configurationfile /etc/masterha_default.cnf not found. Skipping.
Tue Jun 10 09:26:23 2014 - [info] Reading applicationdefault configurations from /etc/mha-manager/app1.cnf..
Tue Jun 10 09:26:23 2014 - [info] Reading serverconfigurations from /etc/mha-manager/app1.cnf..
Tue Jun 10 09:26:23 2014 - [info] Starting SSH connectiontests..
Tue Jun 10 09:26:24 2014 - [debug]
Tue Jun 10 09:26:23 2014 - [debug] Connecting via SSH fromroot@192.168.40.128(192.168.40.128:22) toroot@192.168.40.129(192.168.40.129:22)..
Tue Jun 10 09:26:24 2014 - [debug] ok.
Tue Jun 10 09:26:24 2014 - [debug]
Tue Jun 10 09:26:23 2014 - [debug] Connecting via SSH fromroot@192.168.40.129(192.168.40.129:22) toroot@192.168.40.128(192.168.40.128:22)..
Tue Jun 10 09:26:24 2014 - [debug] ok.
Tue Jun 10 09:26:24 2014 - [info] All SSH connectiontests passed successfully.
成功！
3、masterha_check_repl工具验证mysql复制是否成功
shell> masterha_check_repl--conf=/etc/mha-manager/app1.cnf
Tue Jun 10 09:27:31 2014 - [info] Slaves settings checkdone.
Tue Jun 10 09:27:31 2014 - [info]
192.168.40.128 (current master)
+--192.168.40.129
Tue Jun 10 09:27:31 2014 - [info] Checking replicationhealth on 192.168.40.129..
Tue Jun 10 09:27:31 2014 - [info] ok.
Tue Jun 10 09:27:31 2014 - [warning]master_ip_failover_script is not defined.
Tue Jun 10 09:27:31 2014 - [warning] shutdown_script isnot defined.
Tue Jun 10 09:27:31 2014 - [info] Got exit code 0 (Notmaster dead).
MySQL Replication Health is OK.
4、启动MHA manager,并监控日志文件
shell> masterha_manager --conf=/etc/mha-manager/app1.cnf
后台运行可以使用
shell> nohup masterha_manager--conf=/etc/mha-manager/app1.cnf > /tmp/mha_manager.log 2>&1
shell> tail -f /usr/local/mha-manager/app1/manager.log //这里最好在新窗口执行
192.168.40.128 (current master)
+--192.168.40.129
Tue Jun 10 09:29:04 2014 - [warning]master_ip_failover_script is not defined.
Tue Jun 10 09:29:04 2014 - [warning] shutdown_script isnot defined.
Tue Jun 10 09:29:04 2014 - [info] Set master pinginterval 1 seconds.
Tue Jun 10 09:29:04 2014 - [warning]secondary_check_script is not defined. It is highly recommended setting it tocheck master reachability from two or more routes.
Tue Jun 10 09:29:04 2014 - [info] Starting ping healthcheck on 192.168.40.128(192.168.40.128:3306)..
Tue Jun 10 09:29:04 2014 - [info] Ping(SELECT) succeeded,waiting until MySQL doesn‘t respond..
成功。
5、测试
[master--128]
shell>service mysql stop
[manager--129]
shell>tail –f /usr/local/mha-manager/app1/manager.log
-----日志显示如下------
----- Failover Report -----
app1: MySQL Master failover 192.168.40.128 to192.168.40.129 succeeded
Master 192.168.40.128 is down!
Check MHA Manager logs atslave.testdb.com:/usr/local/mha-manager/app1/manager.log for details.
Started automated(non-interactive) failover.
The latest slave 192.168.40.129(192.168.40.129:3306) hasall relay logs for recovery.
Selected 192.168.40.129 as a new master.
192.168.40.129: OK: Applying all logs succeeded.
Generating relay diff files from the latest slavesucceeded.
192.168.40.129: Resetting slave info succeeded.
Master failover to 192.168.40.129(192.168.40.129:3306)completed successfully.
转移后mha-manager服务器上的mha-manager进程自动跳出。原来的slave（129）上的mysql自动变成了mater。
6、故障转移后，用命令恢复原来的master
1)在旧master上执行
shell>service mysql start //数据库启动
shell>mysql -uroot –p111111
mysql> reset master; //清除binlog，重新记录binlog
mysql> changemaster tomaster_host=‘192.168.40.129‘,master_user=‘ssl‘,master_password=‘mysql‘,master_log_file=‘slave_bin.000009‘,master_port=3306,master_log_pos=299, master_ssl=1,master_ssl_ca=‘/usr/local/mysql/ssl/cacert.pem‘,master_ssl_cert=‘/usr/local/mysql/ssl/mysql.crt‘,master_ssl_key=‘/usr/local/mysql/ssl/mysql.key‘;
mysql> start slave; #暂时先把旧master变为slave
2)然后在manager节点上：
shell> masterha_master_switch--master_state=alive --conf=/etc/mha-manager/app1.cnf
此时master会转到128上，但是129的slave会断开，129需要重新设置为slave。
重新设置主从后，删除/usr/local/mha-manager/app1/app1.failover.complete文件，否则再次运行mha-manager后，如果主再出问题，会导致主从切换不成功。
恢复后在从（如果就两台服务器的话，这里指的是备主那台服务器）mysql里执行以下命令重新同步
mysql> stop slave;
mysql> reset slave;
mysql> change master to master_host=‘192.168.40.128‘,master_user=‘ssl‘,master_password=‘mysql‘,master_log_file=‘master_bin.000001‘,master_port=3306,master_log_pos=120,master_ssl=1,master_ssl_ca=‘/usr/local/mysql/ssl/cacert.pem‘,master_ssl_cert=‘/usr/local/mysql/ssl/mysql.crt‘,master_ssl_key=‘/usr/local/mysql/ssl/mysql.key‘;
mysql>start slave;
mysql> show slave status;
同步设置好后,从服务器执行
mysql> set global read_only=1;
4、部署、安装keepalive
概述
keepalived介绍：Keepalived的作用是检测web服务器的状态，如果有一台web服务器死机，或工作出现故障，Keepalived将检测到，并将有故障的web 服务器从系统中剔除，当web服务器工作正常后Keepalived自动将web服务器加入到服务器群中，这些工作全部自动完成，不需要人工干涉，需要人工做的只是修复故障的web服务器。
1、keepalived安装
安装步骤两台机mysql-master、mysql-slave一样，如下：
shell>wgethttp://www.keepalived.org/software/keepalived-1.2.12.tar.gz
shell>tar xf keepalived-1.2.12.tar.gz
shell>cd keepalived-1.2.12
shell>./configure --prefix=/usr/local/keepalived--with-kernel-dir=/usr/src/kernels/2.6.32-431.17.1.el6.x86_64
shell>make && make install
设置keepalived开机启动脚本
shell>cp/usr/local/keepalived/etc/rc.d/init.d/keepalived /etc/rc.d/init.d/
shell>cp/usr/local/keepalived/etc/sysconfig/keepalived /etc/sysconfig/
shell>cp/usr/local/keepalived/sbin/keepalived /usr/sbin/
shell>chkconfig keepalived off \\不能让keepalived开机自动启动
2、keepalived配置
1）主服务上（128）：
shell>mkdir /etc/keepalived
shell> vim/etc/keepalived/keepalived.conf
global_defs {
router_id mysql-master #修改为自己的主机名
notification_email {
dinglei@boco.com.cn #接收邮件，可以有多个，一行一个
}
#当主、备份设备发生改变时，通过邮件通知
notification_email_from dinglei@boco.com.cn
#发送邮箱服务器
smtp_server 124.127.106.5
#发送邮箱超时时间
smtp_connect_timeout 30
}
##################第一部分###################
vrrp_instance VI_1 {
state BACKUP #都修改成BACKUP
interface eth1 #绑定的网卡
virtual_router_id 60 #默认51 主从都修改为60
priority 100 #优先级，在mysql-slave上LVS上修改成80
advert_int 1
nopreempt #不抢占资源，意思就是它活了之后也不会再把主抢回来
authentication {
# 认证方式，可以是PASS或AH两种认证方式
auth_typePASS
# 认证密码
auth_pass 1111
}
virtual_ipaddress {
192.168.40.130/24 #这可以增加多个VIP
}
}
##################第二部分###################
virtual_server 192.168.40.130 3306 {
delay_loop 6
lb_algo wrr
lb_kind DR
nat_mask 255.255.255.0
persistence_timeout 50
protocol TCP
real_server 192.168.40.128 3306 {
weight 1
notify_down /root/mysql_down.sh
TCP_CHECK {
connect_timeout 10
nb_get_retry 3
connect_port 3306
}
}
}
2）从服务器上（129）：
shell>mkdir /etc/keepalived
shell> vim/etc/keepalived/keepalived.conf
global_defs {
router_id mysql-master #修改为自己的主机名
notification_email {
dinglei@boco.com.cn #接收邮件，可以有多个，一行一个
}
#当主、备份设备发生改变时，通过邮件通知
notification_email_from dinglei@boco.com.cn
#发送邮箱服务器
smtp_server 124.127.106.5
#发送邮箱超时时间
smtp_connect_timeout 30
}
##################第一部分###################
vrrp_instance VI_1 {
state BACKUP #都修改成BACKUP
interface eth0 #绑定的网卡
virtual_router_id 60 #默认51 主从都修改为60
priority80 #优先级，在mysql-master上LVS上修改成100
advert_int 1
authentication {
# 认证方式，可以是PASS或AH两种认证方式
auth_type PASS
# 认证密码
auth_pass 1111
}
virtual_ipaddress {
192.168.40.130/24 #这可以增加多个VIP
}
}
##################第二部分###################
virtual_server 192.168.40.130 3306 {
delay_loop 6
lb_algo wrr
lb_kind DR
nat_mask 255.255.255.0
persistence_timeout 50
protocol TCP
real_server 192.168.40.129 3306 {
weight 1
notify_down /root/mysql_down.sh
TCP_CHECK {
connect_timeout 10
nb_get_retry 3
connect_port 3306
}
}
}
3）myslq_down.sh配置，两台机都要配置这一步！！！
这里需要注意的是，notify_down /root/mysql_down.sh这个选项，这个是是在keepalived检测不到mysql的时候要执行的脚本，从上面的配置文件来看real服务器只有本机。那么，keeaplived如果启动，客户端也只是访问本机的mysql。nopreempt这个选下也得注意，这个是不抢占资源在优先级高的机器上配置就可以。
看下这个脚本的内容：
shell> vim/root/mysql_down.sh
#!/bin/bash
pkillkeepalived
echo"VIP moved to 129 because of the 128-mysql is down !!!" |mail -s"VIP change warning from 128 !!!" dinglei@boco.com.cn
shell> chmod +x /root/mysql_down.sh #授权可执行权限
脚本内容就一条命令：pkill keepalived，主要作用是如果本机的mysql挂掉了，那么同时会杀死本机的keepalived，然后发送通知邮件（邮件smtp需要配置/etc/mail.rc），这样另外一台就会接替他工作，虚拟IP也会被另一台接管，如果不杀死keepalived虚拟IP不会被另一台接管，mysql访问也就不会切换过去。
注意：必须要先启动MYSQL,再启动keepalived，否则keepalived启动后会运行mysql_down.sh脚本，等于自杀。
4）主、从启动keepalived
shell>service keepalived start
查看ip地址：
shell> ip addr
或者简写
shell> ip a
3: eth1:<BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP qlen1000
link/ether 00:0c:29:4a:f6:d3 brd ff:ff:ff:ff:ff:ff
inet 192.168.40.128/24 brd 192.168.40.255 scope global eth1
inet 192.168.40.130/32 scope global eth1
inet6 fe80::20c:29ff:fe4a:f6d3/64 scope link
valid_lft forever preferred_lft forever
由于master的keepalived的配置priority为100，比slave的80高，所以master抢到了VIP地址。
用VIP连接mysql进行测试：
shell> mysql –uroot –h 192.168.40.130–p111111
5）keepalived切换测试
1、 关闭master的keepalived服务，在slave服务器上查看地址，看到VIP已经切换到slave服务器上。
2、 启动master的keepalived服务，在slave服务器上查看地址，看到VIP仍然在slave服务器上，因为我们在master的keepalived的配置文件里设置了nopreempt（即优先级高的服务器不抢占资源）。
关闭slave的keepalived服务，在master服务器上查看地址，看到VIP已经切换到master服务器上。
mysql5.6-gtid-半同步-ssl-mha-keepalived方案,布布扣,bubuko.com
mysql5.6-gtid-半同步-ssl-mha-keepalived方案