mysql+mha高可用搭建
主库：172.25.254.125备库：172.25.254.225
管理节点：172.25.254.126
在开始之前，请先配置好服务器间的时间同步和名称解析
一:在数据库节点安装mha node
# 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> Preparing... ###########################################
1:epel-release ###########################################
# ls /etc/yum.repos.d/
base.repoepel.repoepel-testing.reporhel-debuginfo.repo
# yum -y install perl-DBD-MySQLncftp
#wget mha4mysql-node-0.52.tar.gz -P /usr/local/src/tarbag/
# cd /usr/local/src/tarbag/
# tar -zxvpf mha4mysql-node-0.52.tar.gz -C ../software/
# cd ../software/mha4mysql-node-0.52/
# perl Makefile.PL
# make && make install
二：管理节点
1：按照步骤一安装mha node
2:安装mha manager
# yum -y install perl-Config-Tiny perl-Params-Validate perl-Log-Dispatch perl-Parallel-ForkManager
# wget mha4mysql-manager-0.52.tar.gz -P /usr/local/src/tarbag/
# cd /usr/local/src/tarbag/
# tar -zxvpf mha4mysql-manager-0.52.tar.gz -C ../software/
# cd ../software/mha4mysql-manager-0.52/
# perl Makefile.PL
# make && make install
3:编辑配置文件
# mkdir /etc/masterha
# mkdir -p /masterha/app1
# cp samples/conf/* /etc/masterha/
# cat /etc/masterha/app1.cnf
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=""
hostname=172.25.254.225
master_binlog_dir="/mydata"
candidate_master=1
hostname=172.25.254.126
master_binlog_dir="/mydata"
candidate_master=1
4:配置manager节点和node节点以及node节点间的ssh公钥信任
# ssh-keygen -t rsa
# ssh-copy-id -i /root/.ssh/id_rsa.pub root@172.25.254.225
# ssh-copy-id -i /root/.ssh/id_rsa.pub root@172.25.254.126
# ssh 172.25.254.225 "ifconfig |grep 'inet addr' |head -1"
inet addr:172.25.254.225Bcast:172.25.254.255Mask:255.255.255.0
# ssh 172.25.254.126 "ifconfig |grep 'inet addr' |head -1"
inet addr:172.25.254.126Bcast:172.25.254.255Mask:255.255.255.0
5：测试ssh连接
# masterha_check_ssh --conf=/etc/masterha/app1.cnf
Wed Jun6 11:11:25 2012 - Global configuration file /etc/masterha_default.cnf not found. Skipping.
Wed Jun6 11:11:25 2012 - Reading application default configurations from /etc/masterha/app1.cnf..
Wed Jun6 11:11:25 2012 - Reading server configurations from /etc/masterha/app1.cnf..
Wed Jun6 11:11:25 2012 - Starting SSH connection tests..
Wed Jun6 11:11:25 2012 -
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: 1Changed: 1Warnings: 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)
Rows matched: 1Changed: 1Warnings: 0
mysql> update user set password=PASSWORD('123456') where user='root' and host='192.168.123.%';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1Changed: 0Warnings: 0
mysql> flush privileges;
Query OK, 0 rows affected (0.02 sec)
# whereis mysqlbinlog
mysqlbinlog: /usr/bin/mysqlbinlog
# mv /usr/bin/mysql* /tmp
# mv /usr/bin/mysql* /tmp
# ln -s /usr/local/mysql5.5.25/bin/* /usr/local/bin/
# ln -s /usr/local/mysql5.5.25/bin/* /usr/local/bin/
# masterha_check_repl --conf=/etc/masterha/app1.cnf
Wed Jun6 12:39:03 2012 - Global configuration file /etc/masterha_default.cnf not found. Skipping.
Wed Jun6 12:39:03 2012 - Reading application default configurations from /etc/masterha/app1.cnf..
Wed Jun6 12:39:03 2012 - Reading server configurations from /etc/masterha/app1.cnf..
Wed Jun6 12:39:03 2012 - MHA::MasterMonitor version 0.52.
Wed Jun6 12:39:03 2012 - Dead Servers:
Wed Jun6 12:39:03 2012 - Alive Servers:
Wed Jun6 12:39:03 2012 - 192.168.123.13(192.168.123.13:3306)
Wed Jun6 12:39:03 2012 - 192.168.123.14(192.168.123.14:3306)
Wed Jun6 12:39:03 2012 - Alive Slaves:
Wed Jun6 12:39:03 2012 - 192.168.123.14(192.168.123.14:3306)Version=5.5.25-log (oldest major version between slaves) log-bin:enabled
Wed Jun6 12:39:03 2012 - Replicating from 192.168.123.13(192.168.123.13:3306)
Wed Jun6 12:39:03 2012 - Primary candidate for the new Master (candidate_master is set)
Wed Jun6 12:39:03 2012 - Current Alive Master: 192.168.123.13(192.168.123.13:3306)
Wed Jun6 12:39:03 2012 - Checking slave configurations..
Wed Jun6 12:39:03 2012 - read_only=1 is not set on slave 192.168.123.14(192.168.123.14:3306).
Wed Jun6 12:39:03 2012 - relay_log_purge=0 is not set on slave 192.168.123.14(192.168.123.14:3306).
Wed Jun6 12:39:03 2012 - Checking replication filtering settings..
Wed Jun6 12:39:03 2012 - binlog_do_db= bbs,test, binlog_ignore_db= mysql
Wed Jun6 12:39:03 2012 - Replication filtering check ok.
Wed Jun6 12:39:03 2012 - Starting SSH connection tests..
Wed Jun6 12:39:05 2012 - All SSH connection tests passed successfully.
Wed Jun6 12:39:05 2012 - Checking MHA Node version..
Wed Jun6 12:39:05 2012 - Version check ok.
Wed Jun6 12:39:05 2012 - Checking SSH publickey authentication and checking recovery script configurations on the current master..
Wed Jun6 12:39:05 2012 - 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 Jun6 12:39:05 2012 - Connecting to root@172.25.254.125(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 Jun6 12:39:06 2012 - Master setting check done.
Wed Jun6 12:39:06 2012 - Checking SSH publickey authentication and checking recovery script configurations on all alive slave servers..
Wed Jun6 12:39:06 2012 - 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 Jun6 12:39:06 2012 - Connecting to root@172.25.254.225(172.25.254.225)..
Checking slave recovery environment settings..
Opening /mydata/relay-log.info ... ok.
>
Temporary> Testing mysql connection and privileges.. done.
Testing mysqlbinlog output.. done.
Cleaning up test file(s).. done.
Wed Jun6 12:39:06 2012 - Slaves settings check done.
Wed Jun6 12:39:06 2012 - Checking replication health on 172.25.254.225
Wed Jun6 12:39:06 2012 - ok.
Wed Jun6 12:39:06 2012 - master_ip_failover_script is not defined.
Wed Jun6 12:39:06 2012 - shutdown_script is not defined.
Wed Jun6 12:39:06 2012 - Got exit code 0 (Not master dead).
MySQL Replication Health is OK.
7：启动管理节点进程
# nohup masterha_manager --conf=/etc/masterha/app1.cnf > /tmp/mha_manager.log< /dev/null 2>&1 &
25516
# 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)
# service mysqld stop
Shutting down MySQL...
2：在管理节点上观察日志输出
# tail -f /masterha/app1/manager.log
Wed Jun6 14:50:48 2012 -
192.168.123.13 (current master)
+--192.168.123.14
Wed Jun6 14:50:48 2012 - master_ip_failover_script is not defined.
Wed Jun6 14:50:48 2012 - shutdown_script is not defined.
Wed Jun6 14:50:48 2012 - Set master ping interval 1 seconds.
Wed Jun6 14:50:48 2012 - secondary_check_script is not defined. It is highly recommended setting it to check master reachability from two or more routes.
Wed Jun6 14:50:48 2012 - Starting ping health check on 172.25.254.125(172.25.254.125:3306)..
Wed Jun6 14:50:48 2012 - Ping succeeded, sleeping until it doesn't respond..
Wed Jun6 14:51:32 2012 - Got error on MySQL ping: 2006 (MySQL server has gone away)
Wed Jun6 14:51:32 2012 - HealthCheck: SSH to 172.25.254.125 is reachable.
Wed Jun6 14:51:33 2012 - Got error on MySQL connect: 2013 (Lost connection to MySQL server at 'reading initial communication packet', system error: 111)
Wed Jun6 14:51:33 2012 - Connection failed 1 time(s)..
Wed Jun6 14:51:34 2012 - Got error on MySQL connect: 2013 (Lost connection to MySQL server at 'reading initial communication packet', system error: 111)
Wed Jun6 14:51:34 2012 - Connection failed 2 time(s)..
Wed Jun6 14:51:35 2012 - Got error on MySQL connect: 2013 (Lost connection to MySQL server at 'reading initial communication packet', system error: 111)
Wed Jun6 14:51:35 2012 - Connection failed 3 time(s)..
Wed Jun6 14:51:35 2012 - Master is not reachable from health checker!
Wed Jun6 14:51:35 2012 - Master 172.25.254.125(172.25.254.125:3306) is not reachable!
Wed Jun6 14:51:35 2012 - SSH is reachable.
Wed Jun6 14:51:35 2012 - 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 Jun6 14:51:35 2012 - Global configuration file /etc/masterha_default.cnf not found. Skipping.
Wed Jun6 14:51:35 2012 - Reading application default configurations from /etc/masterha/app1.cnf..
Wed Jun6 14:51:35 2012 - 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 |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
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
>
> 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
> 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
1 row in set (0.00 sec)
ERROR:
No query specified
页:
[1]