mysql+mha高可用搭建

主库：172.25.254.125

备库：172.25.254.225

管理节点：172.25.254.126

在开始之前，请先配置好服务器间的时间同步和名称解析

一:在数据库节点安装mha node

[root@dg53 ~]# rpm -ivh epel-release-5-4.noarch.rpm  

Retrieving epel-release-5-4.noarch.rpm  

warning: /var/tmp/rpm-xfer.yqwfYT: Header V3 DSA signature: NOKEY, key ID 217521f6  

Preparing...                ########################################### [100%]  

   1:epel-release           ########################################### [100%]  

[root@dg53 ~]# ls /etc/yum.repos.d/  

base.repo  epel.repo  epel-testing.repo  rhel-debuginfo.repo  

[root@dg53 ~]# yum -y install perl-DBD-MySQL  ncftp  

[root@dg53 ~]#  wget mha4mysql-node-0.52.tar.gz -P /usr/local/src/tarbag/  

[root@dg53 ~]# cd /usr/local/src/tarbag/  

[root@dg53 tarbag]# tar -zxvpf mha4mysql-node-0.52.tar.gz -C ../software/  

[root@dg53 tarbag]# cd ../software/mha4mysql-node-0.52/  

[root@dg53 mha4mysql-node-0.52]# perl Makefile.PL   

[root@dg53 mha4mysql-node-0.52]# make && make install 

二：管理节点

1：按照步骤一安装mha node

2:安装mha manager

[root@dg55 ~]# yum -y install perl-Config-Tiny perl-Params-Validate perl-Log-Dispatch perl-Parallel-ForkManager  

[root@dg55 ~]# wget mha4mysql-manager-0.52.tar.gz -P /usr/local/src/tarbag/  

[root@dg55 ~]# cd /usr/local/src/tarbag/  

[root@dg55 tarbag]# tar -zxvpf mha4mysql-manager-0.52.tar.gz -C ../software/  

[root@dg55 tarbag]# cd ../software/mha4mysql-manager-0.52/  

[root@dg55 mha4mysql-manager-0.52]# perl Makefile.PL  

[root@dg55 mha4mysql-manager-0.52]# make && make install 

3:编辑配置文件

[root@dg55 mha4mysql-manager-0.52]# mkdir /etc/masterha  

[root@dg55 mha4mysql-manager-0.52]# mkdir -p /masterha/app1  

[root@dg55 mha4mysql-manager-0.52]# cp samples/conf/* /etc/masterha/  

[root@dg55 mha4mysql-manager-0.52]# cat /etc/masterha/app1.cnf   

[server default]  

manager_workdir=/masterha/app1  

manager_log=/masterha/app1/manager.log  

user=root 

password=123456 

ssh_user=root 

repl_user=r_test 

repl_password=123456 

ping_interval=1 

shutdown_script="" 

#master_ip_failover_script="/usr/local/bin/master_ip_failover" 

master_ip_online_change_script="" 

report_script="" 

[server1]  

hostname=172.25.254.225  

master_binlog_dir="/mydata" 

candidate_master=1 

[server2]  

hostname=172.25.254.126

4:配置manager节点和node节点以及node节点间的ssh公钥信任

[root@dg55 ~]# ssh-keygen -t rsa  

[root@dg55 ~]# ssh-copy-id -i /root/.ssh/id_rsa.pub [email protected]  

[root@dg55 ~]# ssh-copy-id -i /root/.ssh/id_rsa.pub [email protected]  

[root@dg55 ~]# ssh 172.25.254.225 "ifconfig |grep 'inet addr' |head -1"  

          inet addr:172.25.254.225  Bcast:172.25.254.255  Mask:255.255.255.0  

[root@dg55 ~]# ssh 172.25.254.126 "ifconfig |grep 'inet addr' |head -1"  

          inet addr:172.25.254.126  Bcast:172.25.254.255  Mask:255.255.255.0 

5：测试ssh连接

[root@dg55 ~]# masterha_check_ssh --conf=/etc/masterha/app1.cnf   

Wed Jun  6 11:11:25 2012 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.  

Wed Jun  6 11:11:25 2012 - [info] Reading application default configurations from /etc/masterha/app1.cnf..  

Wed Jun  6 11:11:25 2012 - [info] Reading server configurations from /etc/masterha/app1.cnf..  

Wed Jun  6 11:11:25 2012 - [info] Starting SSH connection tests..  

Wed Jun  6 11:11:25 2012 - [debug]    

6：测试主从复制情况，默认使用root用户连接

mysql> select user,host,password from mysql.user;  

+--------+----------------+-------------------------------------------+  

| user   | host           | password                                  |  

| root   | localhost      |                                           |   

| root   | dg53.yang.com  |                                           |   

| root   | 127.0.0.1      |                                           |   

| root   | ::1            |                                           |   

|        | localhost      |                                           |   

|        | dg53.yang.com  |                                           |   

| r_test | 192.168.123.14 | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |   

7 rows in set (0.08 sec)  

mysql> use mysql;  

Database changed  

mysql> update user set host='192.168.123.%' where user='r_test';  

Query OK, 1 row affected (0.05 sec)  

Rows matched: 1  Changed: 1  Warnings: 0  

mysql> commit;  

Query OK, 0 rows affected (0.01 sec)  

mysql> update user set host='192.168.123.%' where host='localhost' and user='root' and password='';  

Query OK, 0 rows affected (0.00 sec)  

mysql> update user set password=PASSWORD('123456') where user='root' and host='192.168.123.%';  

Rows matched: 1  Changed: 0  Warnings: 0  

mysql> flush privileges;  

Query OK, 0 rows affected (0.02 sec)  

[root@dg53 ~]# whereis mysqlbinlog  

mysqlbinlog: /usr/bin/mysqlbinlog  

[root@dg53 ~]# mv /usr/bin/mysql* /tmp  

[root@dg54 ~]# mv /usr/bin/mysql* /tmp  

[root@dg53 ~]# ln -s /usr/local/mysql5.5.25/bin/* /usr/local/bin/  

[root@dg54 ~]# ln -s /usr/local/mysql5.5.25/bin/* /usr/local/bin/  

[root@dg55 ~]# masterha_check_repl --conf=/etc/masterha/app1.cnf   

Wed Jun  6 12:39:03 2012 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.  

Wed Jun  6 12:39:03 2012 - [info] Reading application default configurations from /etc/masterha/app1.cnf..  

Wed Jun  6 12:39:03 2012 - [info] Reading server configurations from /etc/masterha/app1.cnf..  

Wed Jun  6 12:39:03 2012 - [info] MHA::MasterMonitor version 0.52.  

Wed Jun  6 12:39:03 2012 - [info] Dead Servers:  

Wed Jun  6 12:39:03 2012 - [info] Alive Servers:  

Wed Jun  6 12:39:03 2012 - [info]   192.168.123.13(192.168.123.13:3306)  

Wed Jun  6 12:39:03 2012 - [info]   192.168.123.14(192.168.123.14:3306)  

Wed Jun  6 12:39:03 2012 - [info] Alive Slaves:  

Wed Jun  6 12:39:03 2012 - [info]   192.168.123.14(192.168.123.14:3306)  Version=5.5.25-log (oldest major version between slaves) log-bin:enabled  

Wed Jun  6 12:39:03 2012 - [info]     Replicating from 192.168.123.13(192.168.123.13:3306)  

Wed Jun  6 12:39:03 2012 - [info]     Primary candidate for the new Master (candidate_master is set)  

Wed Jun  6 12:39:03 2012 - [info] Current Alive Master: 192.168.123.13(192.168.123.13:3306)  

Wed Jun  6 12:39:03 2012 - [info] Checking slave configurations..  

Wed Jun  6 12:39:03 2012 - [warning]  read_only=1 is not set on slave 192.168.123.14(192.168.123.14:3306).  

Wed Jun  6 12:39:03 2012 - [warning]  relay_log_purge=0 is not set on slave 192.168.123.14(192.168.123.14:3306).  

Wed Jun  6 12:39:03 2012 - [info] Checking replication filtering settings..  

Wed Jun  6 12:39:03 2012 - [info]  binlog_do_db= bbs,test, binlog_ignore_db= mysql 

Wed Jun  6 12:39:03 2012 - [info]  Replication filtering check ok.  

Wed Jun  6 12:39:03 2012 - [info] Starting SSH connection tests..  

Wed Jun  6 12:39:05 2012 - [info] All SSH connection tests passed successfully.  

Wed Jun  6 12:39:05 2012 - [info] Checking MHA Node version..  

Wed Jun  6 12:39:05 2012 - [info]  Version check ok.  

Wed Jun  6 12:39:05 2012 - [info] Checking SSH publickey authentication and checking recovery script configurations on the current master..  

Wed Jun  6 12:39:05 2012 - [info]   Executing command: save_binary_logs --command=test --start_file=mysql-bin.000011 --start_pos=4 --binlog_dir=/mydata --output_file=/var/tmp/save_binary_logs_test --manager_version=0.52   

Wed Jun  6 12:39:05 2012 - [info]   Connecting to [email protected](172.25.254.125)..   

  Creating /var/tmp if not exists..    ok.  

  Checking output directory is accessible or not..  

   ok.  

  Binlog found at /mydata, up to mysql-bin.000011  

Wed Jun  6 12:39:06 2012 - [info] Master setting check done.  

Wed Jun  6 12:39:06 2012 - [info] Checking SSH publickey authentication and checking recovery script configurations on all alive slave servers..  

Wed Jun  6 12:39:06 2012 - [info]   Executing command : apply_diff_relay_logs --command=test --slave_user=root --slave_host=172.25.254.225 --slave_ip=172.25.254.225 --slave_port=3306 --workdir=/var/tmp --target_version=5.5.25-log --manager_version=0.52 --relay_log_info=/mydata/relay-log.info  --slave_pass=xxx 

Wed Jun  6 12:39:06 2012 - [info]   Connecting to [email protected](172.25.254.225)..   

  Checking slave recovery environment settings..  

    Opening /mydata/relay-log.info ... ok.  

    Relay log found at /mydata, up to dg54-relay-bin.000019  

    Temporary relay log file is /mydata/dg54-relay-bin.000019  

    Testing mysql connection and privileges.. done.  

    Testing mysqlbinlog output.. done.  

    Cleaning up test file(s).. done.  

Wed Jun  6 12:39:06 2012 - [info] Slaves settings check done.    

Wed Jun  6 12:39:06 2012 - [info] Checking replication health on 172.25.254.225 

Wed Jun  6 12:39:06 2012 - [info]  ok.  

Wed Jun  6 12:39:06 2012 - [warning] master_ip_failover_script is not defined.  

Wed Jun  6 12:39:06 2012 - [warning] shutdown_script is not defined.  

Wed Jun  6 12:39:06 2012 - [info] Got exit code 0 (Not master dead).  

MySQL Replication Health is OK. 

7：启动管理节点进程

[root@dg55 ~]# nohup masterha_manager --conf=/etc/masterha/app1.cnf > /tmp/mha_manager.log  < /dev/null 2>&1 &  

[1] 25516  

[root@dg55 ~]# masterha_check_status --conf=/etc/masterha/app1.cnf   

app1 (pid:25516) is running(0:PING_OK), master:172.25.254.125 

三：测试failover过程

1：当前主库为172.25.254.125，关闭主库

mysql> show slave hosts;  

+-----------+------+------+-----------+  

| Server_id | Host | Port | Master_id |  

|         2 |      | 3306 |         1 |  

1 row in set (0.00 sec)  

[root@dg53 ~]# service mysqld stop  

Shutting down MySQL...[  OK  ] 

2：在管理节点上观察日志输出

[root@dg55 ~]# tail -f /masterha/app1/manager.log 

Wed Jun  6 14:50:48 2012 - [info] 

192.168.123.13 (current master)

 +--192.168.123.14

Wed Jun  6 14:50:48 2012 - [warning] master_ip_failover_script is not defined.

Wed Jun  6 14:50:48 2012 - [warning] shutdown_script is not defined.

Wed Jun  6 14:50:48 2012 - [info] Set master ping interval 1 seconds.

Wed Jun  6 14:50:48 2012 - [warning] secondary_check_script is not defined. It is highly recommended setting it to check master reachability from two or more routes.

Wed Jun  6 14:50:48 2012 - [info] Starting ping health check on 172.25.254.125(172.25.254.125:3306)..

Wed Jun  6 14:50:48 2012 - [info] Ping succeeded, sleeping until it doesn't respond..

Wed Jun  6 14:51:32 2012 - [warning] Got error on MySQL ping: 2006 (MySQL server has gone away)

Wed Jun  6 14:51:32 2012 - [info] HealthCheck: SSH to 172.25.254.125 is reachable.

Wed Jun  6 14:51:33 2012 - [warning] Got error on MySQL connect: 2013 (Lost connection to MySQL server at 'reading initial communication packet', system error: 111)

Wed Jun  6 14:51:33 2012 - [warning] Connection failed 1 time(s)..

Wed Jun  6 14:51:34 2012 - [warning] Got error on MySQL connect: 2013 (Lost connection to MySQL server at 'reading initial communication packet', system error: 111)

Wed Jun  6 14:51:34 2012 - [warning] Connection failed 2 time(s)..

Wed Jun  6 14:51:35 2012 - [warning] Got error on MySQL connect: 2013 (Lost connection to MySQL server at 'reading initial communication packet', system error: 111)

Wed Jun  6 14:51:35 2012 - [warning] Connection failed 3 time(s)..

Wed Jun  6 14:51:35 2012 - [warning] Master is not reachable from health checker!

Wed Jun  6 14:51:35 2012 - [warning] Master 172.25.254.125(172.25.254.125:3306) is not reachable!

Wed Jun  6 14:51:35 2012 - [warning] SSH is reachable.

Wed Jun  6 14:51:35 2012 - [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..

Wed Jun  6 14:51:35 2012 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.

Wed Jun  6 14:51:35 2012 - [info] Reading application default configurations from /etc/masterha/app1.cnf..

Wed Jun  6 14:51:35 2012 - [info] Reading server configurations from /etc/masterha/app1.cnf..

3：在原从库192.168.123.14上查看结果

mysql> show master status;  

+------------------+----------+--------------+------------------+  

| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |  

| mysql-bin.000023 |      107 | bbs,test     | mysql            |  

mysql> show slave status\G;  

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

               Slave_IO_State:   

                  Master_Host: 172.25.254.125

                  Master_User: r_test  

                  Master_Port: 3306  

                Connect_Retry: 60  

              Master_Log_File:   

          Read_Master_Log_Pos: 4  

               Relay_Log_File: dg54-relay-bin.000001  

                Relay_Log_Pos: 4  

        Relay_Master_Log_File:   

             Slave_IO_Running: No  

            Slave_SQL_Running: No  

              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: 0  

              Relay_Log_Space: 126  

              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: NULL  

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  

ERROR:   

No query specified 

本文转自铁骑传说51CTO博客，原文链接： http://blog.51cto.com/ybzbfs/1953870，如需转载请自行联系原作者