@written by Jane Hoo
---------------------------------------------------------------------------------
1.架构环境
1.1 系统
系统版本:centos 6.3 系统位数:x86_64 内存:2G cpu: 1核 防火墙:关闭
1.2 数据库
数据库类型:mysql 版本:5.6.16
安装方式:源码安装
1.3 MHA
manager版本:0.55 node版本:0.54
安装方式:rpm包安装,软件包下载地址:http://code.google.com/p/mysql-master-ha/
2.搭建过程
2.1 主机部署
manager机:192.168.231.47
master机:192.168.231.46
slave1机:192.168.231.48 (主备)
slave2机:192.168.231.47 (与manager节点在同一台机器上)
三台机器安装的是centos6.3系统
2.2 三台机器免密钥登录
用ssh-keygen实现四台主机间相互免密钥登录,具体实现以192.168.231.47为例:
[root@test06]# ssh-keygen -t rsa
Generating public/private rsa key pair.
Enter file in which to save the key (/root/.ssh/id_rsa): /root/.ssh/id_rsa
Created directory '/root/.ssh'.
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:
75:e3:0e:6e:10:d2:b7:fb:0d:4f:95:03:e1:ab:91:06 root@test06
The key's randomart image is:
+--[ RSA 2048]----+
| . |
| . . . |
| . oEo oo |
| . +.+..o .|
| S o+.. o.|
| o.+o ..|
| +.o . |
| . . = |
| . o |
+-----------------+
[root@test06]# cd /root
[root@test06]# ssh-copy-id -i .ssh/id_rsa.pub root@192.168.231.47
[root@test06]# ssh-copy-id -i .ssh/id_rsa.pub root@192.168.231.46
[root@test06]# ssh-copy-id -i .ssh/id_rsa.pub root@192.168.231.48
在其它两台机器上,同样执行相同的操作,使用ssh登录验证这三台机器的任意两台机器间是否可以免密码登录。如:
[root@test06 app1]# ssh 192.168.231.48
Last login: Wed Apr 2 09:41:55 2014 from test05
[root@test07 ~]#
2.3 安装MHA软件包
MHA的软件包分为node和manager连部分:mha4mysql-node,mha4mysql-manager。
mha4mysql-node依赖软件包:perl-DBD-MySQL,ncftp
mha4mysql-manager依赖软件包:perl-Config-Tiny, perl-Params-Validate, perl-Log-Dispatch, perl-Parallel-ForkManager, perl-Config-IniFiles
三台机器上均需要安装mha4mysql-node节点,同时mha4mysql-manager也是依赖mha4mysql-node节点的。在管理节点上安装mha4mysql-manager包。
安装mha4mysql-node,以192.168.231.47为例:
[root@test06 .ssh]# yum install perl-DBD-MySQL
[root@test06 .ssh]# yum install ncftp
[root@test06 .ssh]#
rpm -ivh http://dl.fedoraproject.org/pub/epel/6/i386/epel-release-6-8.noarch.rpm
[root@test06 .ssh]#
rpm -ivh http://dl.fedoraproject.org/pub/epel/6/x86_64/epel-release-6-8.noarch.rpm
[root@test06 ~]#
wget http://mysql-master-ha.googlecode.com/files/mha4mysql-node-0.54-0.el6.noarch.rpm
[root@test06 ~]# rpm -ivh mha4mysql-node-0.54-0.el6.noarch.rpm
安装mha4mysql-manager,manager安装在192.168.231.47上:
[root@test06 ~]# yum install perl-Config-Tiny
[root@test06 ~]# yum install perl-Params-Validate
[root@test06 ~]#yum install perl-Log-Dispatch
[root@test06 ~]# yum install perl-Parallel-ForkManager
[root@test06 ~]# yum install perl-Config-IniFiles
wget http://mysql-master-ha.googlecode.com/files/mha4mysql-manager-0.55-0.el6.noarch.rpm
[root@test06 ~]# rpm -ivh mha4mysql-manager-0.55-0.el6.noarch.rpm
2.4 建立mysql主从复制关系
建立192.168.231.46(master), 192.168.231.48(slave1), 192.168.231.47(slave2)之间的主从复制关系,首先保证这三台机器mysql的server_id是互异且唯一的。同时关闭中继日志自动清理的功能。 分别在这三个实例上建立对应的主从复制用户repl及mha管理用户manager。
在192.168.231.46上设置如下:
mysql>grant replication slave on *.* to 'repl'@'192.168.231.%' identified by 'qwert';
mysql>grant all privileges on *.* to 'manager'@'192.168.231.%' identified by 'manager';
在192.168.231.47上设置如下:
mysql>grant replication slave on *.* to 'repl'@'192.168.231.%' identified by 'qwert';
mysql>grant all privileges on *.* to 'manager'@'192.168.231.%' identified by 'manager';
mysql>set global read_only=1;
mysql>set global relay_log_purge=0;
mysql>change master to master_host='192.168.231.46',
master_user='repl',master_password='qwert',
master_log_file='mysql-bin.xxxxx',master_log_pos=xxx;
在192.168.231.48上设置如下:
mysql>grant replication slave on *.* to 'repl'@'192.168.231.%' identified by 'qwert';
mysql>grant all privileges on *.* to 'manager'@'192.168.231.%' identified by 'manager';
mysql>set global read_only=1;
mysql>set global relay_log_purge=0;
mysql>change master to master_host='192.168.231.46',
master_user='repl',master_password='qwert',
master_log_file='mysql-bin.xxxxx',master_log_pos=xxx;
在192.168.231.47,192.168.231.48上show slave status\G;查看主从同步是否正常进行。
2.5 配置MHA
在192.168.21.47【manager】上创建,修改manager的相关配置。
[root@test06 ~]# mkdir -p /masterha/app1
manager工作目录
[root@test06 ~]# mkdir /etc/masterha
manager配置文件目录
创建manager的配置文件:
[root@test06 ~]# vi /etc/masterha/app1.cnf
/etc/masterha/app1.cnf 配置文件内容如下:
[server default]
user=manager #mysql中建立的manager管理用户
password=manager #mysql中manager的密码
manager_workdir=/masterha/app1 #manager的工作目录
manager_log=/masterha/app1/manager.log #manager日志文件位置
remote_workdir=/masterha/app1
ssh_user=root #免密钥登录用户
repl_user=repl #mysql的主从复制账号
repl_password=qwert #repl的密码
ping_interval=1
#ping间隔,用来检测master是否正常
report_script= /scripts/send_report
master_ip_online_change_script= /scripts/master_ip_online_change
[server1]
hostname=192.168.231.46
master_binlog_dir=/data/mysql/binlog #46的二进制日志目录
candidate_master=1 #master宕机后,优先启用该台机器作为新的master
check_repl_filter = 0 #应用relay log,不会检查过滤规则
[server2]
hostname=192.168.231.48
master_binlog_dir=/data/mysql/binlog
candidate_master=1
check_repl_filter = 0
[server3]
hostname=192.168.231.47
master_binlog_dir=/data/mysql/binlog
no_master=1 #设置no_master=1使主机不能成为新master
ignore_fail=1 #服务器失败,继续故障转移
check_repl_filter = 0
2.6 校验
[root@test06 ~]# masterha_check_ssh --conf=/etc/masterha/app1.cnf
.....
Wed Apr 2 16:39:41 2014 - [debug] ok.
Wed Apr 2 16:39:41 2014 - [debug] Connecting via SSH from root@192.168.231.47(192.168.231.47:22) to root@192.168.231.48(192.168.231.48:22)..
Wed Apr 2 16:39:41 2014 - [debug] ok.
Wed Apr 2 16:39:41 2014 - [info] All SSH connection tests passed successfully.
[root@test06 ~]#masterha_check_repl --conf=/etc/masterha/app1.cnf
....
Wed Apr 2 16:45:41 2014 - [info]
192.168.231.46 (current master)
+--192.168.231.48
+--192.168.231.47
Wed Apr 2 16:45:41 2014 - [info] Checking replication health on 192.168.231.48..
Wed Apr 2 16:45:41 2014 - [info] ok.
Wed Apr 2 16:45:41 2014 - [info] Checking replication health on 192.168.231.47..
Wed Apr 2 16:45:41 2014 - [info] ok.
Wed Apr 2 16:45:41 2014 - [warning] master_ip_failover_script is not defined.
Wed Apr 2 16:45:41 2014 - [warning] shutdown_script is not defined.
Wed Apr 2 16:45:41 2014 - [info] Got exit code 0 (Not master dead).
MySQL Replication Health is OK.
查看下manager的版本
[root@test06 ~]# masterha_manager --version
masterha_manager version 0.55.
2.7 启动MHA
[root@test06 ~]#
nohup masterha_manager --conf=/etc/masterha/app1.cnf >/tmp/mha_manager.log 2>&1
查看manager的监控日志,查看是否已经成功启动
Wed Apr 2 14:37:48 2014 - [info] Slaves settings check done.
Wed Apr 2 14:37:48 2014 - [info]
192.168.231.46 (current master)
+--192.168.231.48
+--192.168.231.47
Wed Apr 2 14:37:48 2014 - [warning] master_ip_failover_script is not defined.
2.7 测试MHA
测试过程 :
关闭192.168.231.46上的mysql,启动46上的mysql,将46作为48的从,关闭48数据库。
查看192.168.231.47上mysql在46机器宕机前后slave的状态,以及48宕机前后slave的状态,状态切换如下:
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.231.46
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000009
Read_Master_Log_Pos: 120
Relay_Log_File: test06-relay-bin.000006
Relay_Log_Pos: 283
Relay_Master_Log_File: mysql-bin.000009
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: 120
Relay_Log_Space: 1805
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: c915a10a-b586-11e3-a290-000c29e6d7ec
Master_Info_File: /data/mysql/data/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave 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:
Executed_Gtid_Set:
Auto_Position: 0
1 row in set (0.00 sec)
ERROR:
No query specified
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.231.48
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000009
Read_Master_Log_Pos: 120
Relay_Log_File: test06-relay-bin.000002
Relay_Log_Pos: 283
Relay_Master_Log_File: mysql-bin.000009
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: 120
Relay_Log_Space: 457
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: 3
Master_UUID: 61d771c7-b658-11e3-a7e6-080027beb8b6
Master_Info_File: /data/mysql/data/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave 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:
Executed_Gtid_Set:
Auto_Position: 0
1 row in set (0.00 sec)
ERROR:
No query specified
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.231.46
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000010
Read_Master_Log_Pos: 120
Relay_Log_File: test06-relay-bin.000002
Relay_Log_Pos: 283
Relay_Master_Log_File: mysql-bin.000010
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: 120
Relay_Log_Space: 457
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: c915a10a-b586-11e3-a290-000c29e6d7ec
Master_Info_File: /data/mysql/data/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave 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:
Executed_Gtid_Set:
Auto_Position: 0
1 row in set (0.00 sec)
ERROR:
No query specified
3.注意事项
在架构的搭建过程中,必须注意以下几点:
1.注意系统版本,需要根据系统版本,选择相应的epel-release-6-8.noarch.rpm及mha4mysql-node,mha4mysql-manager版本。选择合适的版本。
2.在做ssh免密钥登录的时候,需要将自己的密钥也加入到authorized_keys中,否则,在主机间ssh无需密码,但是使用masterha_check_ssh会失败
3.建立相应的软连接,对于源码安装的mysql需要对mysql的工具建立对应的软连接,否则会报mysqlbinlog version not found! 等错误,在给node上:ln -s /usr/local/mysql/bin/mysqlbinlog /usr/bin/mysqlbinlog ln -s /usr/local/mysql/bin/mysql /usr/bin/mysql
4.对于64位的系统还需要为mha的包文件建立软连接ln -s /usr/lib/perl5/vendor_perl/MHA /usr/lib64/perl5/vendor_perl/