MySql半同步复制+MHA数据库高可用安装配置

一、总概:

本文介绍MySQL高可用性的实现方案MHA,MHA由Node和Manager组成,Node运行在每一台MySQL服务器上,不管是MySQL主 服务器,还是MySQL从服务器,都要安装Node。

image

二、环境

1、操作系统:centos 6.8 64位

2、数据库:MySQL-5.6.17-1.el6.x86_64.rpm-bundle.tar

3、MHA版本:
mha4mysql-node-0.54-0.el6.noarch.rpm,
mha4mysql-manager-0.54-0.el6.noarch.rpm

4、主机部署
manager机:10.101.26.100 master机:10.101.26.101 slave1机:10.101.26.103(备用master) slave2机:10.101.26.105

5、大致步骤:
(1)、安装MYSQL 5.6.
(2)、MYSQL配置主重复制.
(3)、首先用ssh-keygen实现四台主机之间相互免密钥登录.
(4)、安装MHAmha4mysql-node,mha4mysql-manager 软件包
(5)、在MHA配置master,slave的相关文件。
(6)、管理机manager上配置MHA文件
(7)、masterha_check_ssh工具验证ssh信任登录是否成功
(8)、masterha_check_repl工具验证mysql复制是否成功
(9)、启动MHA manager,并监控日志文件.
(10)、测试master宕机后,是否会自动切换。

三、mysql安装

1、下载MYSQL 5.6

wget http://cdn.mysql.com/Downloads/MySQL-5.6/MySQL-5.6.17-1.el6.x86_64.rpm-bundle.tar
tar -cf MySQL-5.6.17-1.el6.x86_64.rpm-bundle.tar

2、解压后文件有:
MySQL-client-5.6.17-1.el6.x86_64.rpm
MySQL-devel-5.6.17-1.el6.x86_64.rpm
MySQL-embedded-5.6.17-1.el6.x86_64.rpm
MySQL-server-5.6.17-1.el6.x86_64.rpm
MySQL-shared-5.6.17-1.el6.x86_64.rpm
MySQL-shared-compat-5.6.17-1.el6.x86_64.rpm
MySQL-test-5.6.17-1.el6.x86_64.rpm

**注意:其实只要安装,服务器端MySQL-server-5.6.17-1.el6.x86_64.rpm 客户端MySQL-client-5.6.17-1.el6.x86_64.rpm 及MySQL-shared-compat-5.6.17-1.el6.x86_64.rpm(可以解决一些兼容性)

3、安装(4台机器都要安装)
卸载系统原有的MYSQL-LIB*相关组件,并安装MYSQL5.6。

yum -y remove mysql-libs-5.1.71*
rpm -ivh MySQL-client-5.6.17-1.el6.x86_64.rpm
rpm -ivh MySQL-server-5.6.17-1.el6.x86_64.rpm
rpm -ivh MySQL-shared-compat-5.6.17-1.el6.x86_64.rpm

**注意:安装完MYSQL会在/root/.mysql_secret生成root的默认登录密码。(cat /root/.mysql_secret)

4、由于是RPM安装的所以在/etc/下没有MYSQL的my.cnf文件。解决方法:

cp /usr/share/mysql/my-medium.cnf  /etc/my.cnf

5、mysql启动、停止、重启。

service mysql start 
service mysql stop
service mysql restart

四、MYSQL主从复制配置(manager、slave1、slave2)

**【本文采用半同步,半同步可防止事务丢失导致从机和主机数据不一致。】

1、主从服务器分别作以下操作:
1.1、版本一致
1.2、初始化表,并在后台启动mysql
1.3、修改root的密码

2、修改主服务器master:

   #vi /etc/my.cnf
       [mysqld]
       log-bin=mysql-bin        //[必须]启用二进制日志
       server-id=222            //[必须]服务器唯一ID,默认是1,一般取IP最后一段
       binlog-do-db=db_nameA    //指定对db_nameA记录二进制日志  
       binlog-ignore-db=db_namB //指定不对db_namB记录二进制日志  

3、修改从服务器slave:

   #vi /etc/my.cnf
       [mysqld]
       log-bin=mysql-bin            //[不是必须]启用二进制日志
       server-id=226                //[必须]服务器唯一ID,默认是1,一般取IP最后一段
       relay_log = mysql-relay-bin

4、重启两台服务器的mysql

   /etc/init.d/mysql restart

5、在主服务器上建立帐户并授权slave:

   #/usr/local/mysql/bin/mysql -uroot -pmttang   
   mysql>GRANT REPLICATION SLAVE ON *.* to 'mysync'@'%' identified by 'q123456'; //一般不用root帐号,'%'表示所有客户端都可能连,只要帐号,密码正确,此处可用具体客户端IP代替,如10.101.26.101,加强安全。

6、登录主服务器的mysql,查询master的状态

   mysql>show master status;
   +------------------+----------+--------------+------------------+
   | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
   +------------------+----------+--------------+------------------+
   | mysql-bin.000004 |      308 |              |                  |
   +------------------+----------+--------------+------------------+
   1 row in set (0.00 sec)
   注:执行完此步骤后不要再操作主服务器MYSQL,防止主服务器状态值变化

7、配置从服务器Slave:(slave1 salve2)

   mysql>change master to master_host='10.101.26.100',master_user='mysync',master_password='q123456',
         master_log_file='mysql-bin.000004',master_log_pos=308;   //注意不要断开,308数字前后无单引号。
   mysql>start slave;    //启动从服务器复制功能

8、检查从服务器复制功能状态:

   mysql> show slave status\G

   *************************** 1. row ***************************

              Slave_IO_State: Waiting for master to send event
              Master_Host: 10.101.26.100  //主服务器地址
              Master_User: mysync   //授权帐户名,尽量避免使用root
              Master_Port: 3306    //数据库端口,部分版本没有此行
              Connect_Retry: 60
              Master_Log_File: mysql-bin.000004
              Read_Master_Log_Pos: 600     //#同步读取二进制日志的位置,大于等于Exec_Master_Log_Pos
              Relay_Log_File: ddte-relay-bin.000003
              Relay_Log_Pos: 251
              Relay_Master_Log_File: mysql-bin.000004
              Slave_IO_Running: Yes    //此状态必须YES
              Slave_SQL_Running: Yes     //此状态必须YES
                    ......

注:Slave_IO及Slave_SQL进程必须正常运行,即YES状态,否则都是错误的状态(如:其中一个NO均属错误)。

9、添加半同步插件(master slave1 salve2)

master:
mysql>install plugin rpl_semi_sync_master soname 'semisync_master.so';

slave1:
mysql>install plugin rpl_semi_sync_slave soname 'semisync_slave.so';

slave2:
mysql>install plugin rpl_semi_sync_slave soname 'semisync_slave.so';

master:
mysql>set global rpl_semi_sync_master_enabled = 1;
mysql>set global rpl_semi_sync_master_timeout = 100; 

slave1:
mysql>set global rpl_semi_sync_slave_enabled = ON;

slave2:
mysql>set global rpl_semi_sync_slave_enabled = ON;

在从库上重启IO线程(slave1 slave2)
stop slave IO_thread;
start slave IO_thread;

以上操作过程,主从服务器配置完成。

五、MHA配置。

//环境介绍

1、主机部署

manager机:10.101.26.100
master机:10.101.26.101
slave1机:10.101.26.103(备用master)
slave2机:10.101.26.105

2、大致步骤
A.首先用ssh-keygen实现四台主机之间相互免密钥登录
B.安装MHAmha4mysql-node,mha4mysql-manager 软件包
C.建立master,slave1,slave2之间主从复制
D.管理机manager上配置MHA文件
E.masterha_check_ssh工具验证ssh信任登录是否成功
F.masterha_check_repl工具验证mysql复制是否成功
G.启动MHA manager,并监控日志文件
H.测试master宕机后,是否会自动切换

//A.首先用ssh-keygen实现四台主机之间相互免密钥登录

[manager机]
1.
shell> ssh-keygen -t rsa -b 2048
shell> scp id_rsa.pub root@10.101.26.101:/root/.ssh/
shell> scp id_rsa.pub root@10.101.26.103:/root/.ssh/
shell> scp id_rsa.pub root@10.101.26.105:/root/.ssh/
2.在另外三台机子上用cat xxx >> authorized_keys导入公钥到/root/.ssh/authorized_keys文件中
3.在其它三台机子重复此步骤,使四台机子中的任何两台之间可以免密码登录

//B.安装MHAmha4mysql-node,mha4mysql-manager 软件包
1.安装MHAmha4mysql-node

[manager,master,slave1,slave2]
shell> yum update
shell> yum -y install perl-DBD-MySQL ncftp
shell> wget http://mysql-master-ha.googlecode.com/files/mha4mysql-node-0.53-0.noarch.rpm
sehll> rpm -ivh mha4mysql-node-0.53-0.noarch.rpm

2.安装mha4mysql-manager

[manager]
shell> yum install perl
shell> yum install cpan
shell> rpm -ivh mha4mysql-manager-0.53-0.el6.noarch.rpm
error:
perl(Config::Tiny) is needed by mha4mysql-manager-0.53-0.noarch
perl(Log::Dispatch) is needed by mha4mysql-manager-0.53-0.noarch
perl(Log::Dispatch::File) is needed by mha4mysql-manager-0.53-0.noarch
perl(Log::Dispatch::Screen) is needed by mha4mysql-manager-0.53-0.noarch
perl(Parallel::ForkManager) is needed by mha4mysql-manager-0.53-0.noarch
perl(Time::HiRes) is needed by mha4mysql-manager-0.53-0.noarch
[solution]
shell> wget ftp://ftp.muug.mb.ca/mirror/centos/5.10/os/x86_64/CentOS/perl-5.8.8-41.el5.x86_64.rpm
shell> wget ftp://ftp.muug.mb.ca/mirror/centos/6.5/os/x86_64/Packages/compat-db43-4.3.29-15.el6.x86_64.rpm
shell> wget http://downloads.naulinux.ru/pub/NauLinux/6x/i386/sites/School/RPMS/perl-Log-Dispatch-2.27-1.el6.noarch.rpm
shell> wget http://dl.fedoraproject.org/pub/epel/6/i386/perl-Parallel-ForkManager-0.7.9-1.el6.noarch.rpm
shell> wget http://dl.fedoraproject.org/pub/epel/6/i386/perl-Mail-Sender-0.8.16-3.el6.noarch.rpm
shell> wget http://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-Parallel-ForkManager-0.7.9-1.el6.noarch.rpm perl-Log-Dispatch-2.27-1.el6.noarch.rpm perl-Mail-Sender-0.8.16-3.el6.noarch.rpm perl-Mail-Sendmail-0.79-12.el6.noarch.rpm perl-Time-HiRes-1.9721-136.el6.x86_64.rpm
shell> rpm -ivh mha4mysql-manager-0.53-0.el6.noarch.rpm

//C.管理机manager上配置MHA文件

[manager:100]
1.
shell> mkdir -p /masterha/app1
shell> mkdir /etc/masterha
shell> vim /etc/masterha/app1.cnf
[server default]
user=root    ##mysql管理用戶名
password=123456
manager_workdir=/masterha/app1
manager_log=/masterha/app1/manager.log
remote_workdir=/masterha/app1
ssh_user=root #ssh免密钥登录的帐号名
repl_user=mysync #mysql复制帐号,用来在主从机之间同步二进制日志等
repl_password=q123456
ping_interval=1 #ping间隔,用来检测master是否正常
[server1]
hostname=10.101.26.101
#ssh_port=9999
master_binlog_dir=/data/ndb #mysql数据库目录
candidate_master=1 #master机宕掉后,优先启用这台作为新master
[server2]
hostname=10.101.26.103
#ssh_port=9999
master_binlog_dir=/data/ndb
candidate_master=1
[server3]
hostname=10.101.26.105
#ssh_port=9999
master_binlog_dir=/data/ndb
no_master=1 #设置no_master=1使主机不能成为新master

//D.masterha_check_ssh工具验证ssh信任登录是否成功

[manager:100]
shell> masterha_check_ssh --conf=/etc/masterha/app1.cnf
Sun Mar  2 17:45:38 2014 - [debug]   ok.
Sun Mar  2 17:45:38 2014 - [info] All SSH connection tests passed successfully.

//E.masterha_check_repl工具验证mysql复制是否成功

[manager:154]
shell> masterha_check_repl --conf=/etc/masterha/app1.cnf
---------------------------------------------------------
Sun Mar  2 13:16:57 2014 - [info] Slaves settings check done.
Sun Mar  2 13:16:57 2014 - [info] 
 10.101.26.101 (current master)
 +--10.101.26.103
 +--10.101.26.105
...
MySQL Replication Health is OK.
---------------------------------------------------------------

//F.启动MHA manager,并监控日志文件

[manager:100]
shell> nohup masterha_manager --conf=/etc/masterha/app1.cnf > /tmp/mha_manager.log 2>&1 
shell> tail -f /masterha/app1/manager.log
---------------------------------------------------------------
 10.101.26.101 (current master)
 +--10.101.26.103
 +--10.101.26.105
...
Sun Mar  2 13:09:25 2014 - [info] Ping(SELECT) succeeded, waiting until MySQL doesn't respond..
-----------------------------------------------------------------
##manager.log表明MHA运行良好,正在监控master是否正常工作

//G.测试master(156)宕机后,是否会自动切换

[master:101]
1.shell> /etc/init.d/myqld stop
[manager:100]
2.当掉master后,/masterha/app1/manager.log文件显示:
[error]
-----------------------------------------------------------
Sun Mar  2 13:13:46 2014 - [error][/usr/share/perl5/vendor_perl/MHA/ManagerUtil.pm, ln178] Got ERROR: Use of uninitialized value $msg in scalar chomp at /usr/share/perl5/vendor_perl/MHA/ManagerConst.pm line 90.
-----------------------------------------------------------
3.[slution]
在文件/usr/share/perl5/vendor_perl/MHA/ManagerConst.pm第90行(chomp $msg)前加入一行:
$msg = "" unless($msg);
4.再次检测:
shell> /etc/init.d/mysqld stop
tail -f tail -f /masterha/app1/manager.log
日志文件显示:
-----------------------------------------------------------
----- Failover Report -----
app1: MySQL Master failover 10.101.26.101 to 10.101.26.103 succeeded
Master 10.101.26.101 is down!
Check MHA Manager logs at mycentos4:/masterha/app1/manager.log for details.
Started automated(non-interactive) failover.
The latest slave 10.101.26.103(10.101.26.103:3306) has all relay logs for recovery.
Selected 10.101.26.103 as a new master.
10.101.26.103: OK: Applying all logs succeeded.
10.101.26.105: This host has the latest relay log events.
Generating relay diff files from the latest slave succeeded.
10.101.26.105: OK: Applying all logs succeeded. Slave started, replicating from 10.10.54.155.
10.101.26.103: Resetting slave info succeeded.
Master failover to 10.101.26.103(10.101.26.103:3306) completed successfully.
--------------------------------------------------------
5.
切换主机后,检查replication状态
[master:101]
shell> /etc/init.d/mysqld start
[manager:100]
shell> masterha_check_repl --conf=/etc/masterha/app1.cnf
--------------------------------------------------------------
Sun Mar  2 13:22:11 2014 - [info] Slaves settings check done.
Sun Mar  2 13:22:11 2014 - [info] 
10.101.26.103 (current master)
 +--10.101.26.101
 +--10.101.26.105
...
MySQL Replication Health is OK.
---------------------------------------------------------------
###上面的"10.101.26.103 (current master)" 这句表明master成功切换到103机子上。

至此,MYSQL+MHA数据库高可用配置完成。

发表评论

电子邮件地址不会被公开。 必填项已用*标注