环境规划:
节点说明 主机名 IP地址
管理节点 tong3 192.168.1.249
主节点 tong2 192.168.1.248
主节点 tong1 192.168.1.247
mysql dba技术群 378190849
武汉-linux运维群 236415619
1.网络和主机名配置
设置每个主机的IP地址和/etc/hosts文件互相解析
[root@tong1 ~]# cat /etc/hosts
192.168.1.247 tong1192.168.1.248 tong2192.168.1.249 tong3[root@tong1 ~]# ping tong1 -c1 --网络必须ping通PING tong1 (192.168.1.247) 56(84) bytes of data.64 bytes from localhost (192.168.1.247): icmp_seq=1 ttl=64 time=0.021 ms--- tong1 ping statistics ---1 packets transmitted, 1 received, 0% packet loss, time 0msrtt min/avg/max/mdev = 0.021/0.021/0.021/0.000 ms[root@tong1 ~]# ping tong2 -c1PING tong2 (192.168.1.248) 56(84) bytes of data.64 bytes from tong2 (192.168.1.248): icmp_seq=1 ttl=64 time=0.109 ms--- tong2 ping statistics ---1 packets transmitted, 1 received, 0% packet loss, time 0msrtt min/avg/max/mdev = 0.109/0.109/0.109/0.000 ms[root@tong1 ~]# ping tong3 -c1PING tong3 (192.168.1.249) 56(84) bytes of data.64 bytes from tong3 (192.168.1.249): icmp_seq=1 ttl=64 time=0.124 ms--- tong3 ping statistics ---1 packets transmitted, 1 received, 0% packet loss, time 0msrtt min/avg/max/mdev = 0.124/0.124/0.124/0.000 ms[root@tong1 ~]#2.安装mha管理软件mha manager
tong3管理节点:
[root@tong3 ~]# yum install perl-DBD-MySQL cpan --安装perl工具
[root@tong3 ~]# tar xvf mha4mysql-manager-0.53.tar.gz -C /usr/local/
[root@tong3 ~]# cd /usr/local/mha4mysql-manager-0.53/
[root@tong3 mha4mysql-manager-0.53]# perl Makefile.PL
[root@tong3 mha4mysql-manager-0.53]# echo $?
0[root@tong3 mha4mysql-manager-0.53]# make && make install[root@tong3 mha4mysql-manager-0.53]# echo $?
0[root@tong3 mha4mysql-manager-0.53]#3.在各数据节点安装mha node
[root@tong2 ~]# tar xvf mha4mysql-node-0.53.tar.gz
[root@tong2 ~]# cd mha4mysql-node-0.53[root@tong2 mha4mysql-node-0.53]# yum install perl-DBD-mysql cpan -y[root@tong2 mha4mysql-node-0.53]# perl Makefile.PL
[root@tong2 mha4mysql-node-0.53]# make && make install[root@tong2 mha4mysql-node-0.53]# echo $?
0[root@tong2 mha4mysql-node-0.53]#4.各节点ssh互相信任
[root@tong3 ~]# ssh-keygen -t dsa
[root@tong3 ~]# cd .ssh
[root@tong3 .ssh]# cat id_dsa.pub > authorized_keys
[root@tong3 .ssh]# scp 192.168.1.247:/root/.ssh/id_dsa.pub 247
[root@tong3 .ssh]# scp 192.168.1.248:/root/.ssh/id_dsa.pub 248
[root@tong3 .ssh]# cat 248 247 >> authorized_keys
[root@tong3 .ssh]# scp authorized_keys 192.168.1.248:/root/.ssh/authorized_keys 100% 1800 1.8KB/s 00:00 [root@tong3 .ssh]# scp authorized_keys 192.168.1.247:/root/.ssh/root@192.168.1.247's password: authorized_keys 100% 1800 1.8KB/s 00:00 [root@tong3 .ssh]# ssh tong1 dateTue Apr 28 12:57:02 CST 2015[root@tong3 .ssh]# ssh tong2 dateTue Apr 28 12:59:57 CST 2015[root@tong3 .ssh]# ssh tong3 dateTue Apr 28 12:57:25 CST 2015[root@tong3 .ssh]#5.在管理节点编辑配置文件
[root@tong3 .ssh]# mkdir /etc/mysqlmha
[root@tong3 .ssh]# cd /etc/mysqlmha/[root@tong3 mysqlmha]# cp -a /usr/local/mha4mysql-manager-0.53/samples/* .[root@tong3 mysqlmha]# vim conf/app1.cnf [server default]manager_workdir=/var/log/masterha/app1manager_log=/var/log/masterha/app1/manager.loguser=root1 --远程登陆用户password=systemssh_user=rootrepl_user=repl_user --复制用户repl_password=system!#%246ping_interval=1 --心跳检测[server1]hostname=192.168.1.249master_binlog_dir="/usr/local/mysql-5.6.23/data/"# candidate_master=1no_master=1 --不能切换成主数据库[server2]hostname=192.168.1.248master_binlog_dir="/usr/local/mysql-5.6.23/data/" --二进制日志文件存放candidate_master=1 --可以切换成主数据库[server4]hostname=192.168.1.247master_binlog_dir="/usr/local/mysql-5.6.23/data/"candidate_master=1 -可以切换成主数据库[root@tong3 ~]# masterha_check_ssh --conf=/etc/mysqlmha/conf/app1.cnf
Tue Apr 28 15:39:21 2015 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.Tue Apr 28 15:39:21 2015 - [info] Reading application default configurations from /etc/mysqlmha/conf/app1.cnf..Tue Apr 28 15:39:21 2015 - [info] Reading server configurations from /etc/mysqlmha/conf/app1.cnf..Tue Apr 28 15:39:21 2015 - [info] Starting SSH connection tests..Tue Apr 28 15:39:22 2015 - [debug] Tue Apr 28 15:39:21 2015 - [debug] Connecting via SSH from root@192.168.1.249(192.168.1.249:22) to root@192.168.1.248(192.168.1.248:22)..Tue Apr 28 15:39:22 2015 - [debug] ok.Tue Apr 28 15:39:22 2015 - [debug] Connecting via SSH from root@192.168.1.249(192.168.1.249:22) to root@192.168.1.247(192.168.1.247:22)..Tue Apr 28 15:39:22 2015 - [debug] ok.Tue Apr 28 15:39:23 2015 - [debug] Tue Apr 28 15:39:22 2015 - [debug] Connecting via SSH from root@192.168.1.248(192.168.1.248:22) to root@192.168.1.249(192.168.1.249:22)..Tue Apr 28 15:39:22 2015 - [debug] ok.Tue Apr 28 15:39:22 2015 - [debug] Connecting via SSH from root@192.168.1.248(192.168.1.248:22) to root@192.168.1.247(192.168.1.247:22)..Tue Apr 28 15:39:23 2015 - [debug] ok.Tue Apr 28 15:39:24 2015 - [debug] Tue Apr 28 15:39:22 2015 - [debug] Connecting via SSH from root@192.168.1.247(192.168.1.247:22) to root@192.168.1.249(192.168.1.249:22)..Tue Apr 28 15:39:23 2015 - [debug] ok.Tue Apr 28 15:39:23 2015 - [debug] Connecting via SSH from root@192.168.1.247(192.168.1.247:22) to root@192.168.1.248(192.168.1.248:22)..Tue Apr 28 15:39:23 2015 - [debug] ok.Tue Apr 28 15:39:24 2015 - [info] All SSH connection tests passed successfully.[root@tong3 ~]#6.修改数据库配置文件和创建用户
在所有节点创建相同的用户:
mysql> grant all privileges on *.* to root1@'192.168.1.%' identified by 'system';
mysql> grant replication slave,replication client on *.* to repl_user@'192.168.1.%' identified by 'system!#%246';
tong1节点:
[root@tong1 ~]# vim /etc/my.cnf
basedir = /usr/local/mysql-5.6.23
datadir = /usr/local/mysql-5.6.23/dataport = 3306server_id = 20socket = /tmp/mysql.sockreplicate-do-db=tongreplicate-ignore-db=mysqllog-bin=mysql-binlog-bin-index=mysql-bin-indexauto_increment_offset=1auto_increment_increment=2relay_log_purge=0read-only=1[root@tong1 ~]#
tong2节点:
[root@tong2 ~]# vim /etc/my.cnf
basedir = /usr/local/mysql-5.6.23
datadir = /usr/local/mysql-5.6.23/dataport = 3306server_id = 10socket = /tmp/mysql.sockreplicate-do-db=tongreplicate-ignore-db=mysqllog-bin=mysql-binlog-bin-index=mysql-bin-indexauto_increment_offset=2auto_increment_increment=2read-only=1relay_log_purge=0[root@tong2 ~]#
tong3节点:
[root@tong3 ~]# vim /etc/my.cnf
basedir = /usr/local/mysql-5.6.23
datadir = /usr/local/mysql-5.6.23/dataport = 3306server_id = 30socket = /tmp/mysql.sockreplicate-do-db=tongreplicate-ignore-db=mysql[root@tong3 ~]#
7.将tong1和tong2搭建为主主模式
tong1主机:
[root@tong1 .ssh]# mysql -u root -p
Enter password: Welcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 60Server version: 5.6.23-log MySQL Community Server (GPL)Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql> change master to master_host='192.168.1.248',master_port=3306,master_user='repl_user',master_password='system!#%246',master_log_file='mysql-bin.000010',master_log_pos=120;Query OK, 0 rows affected, 2 warnings (0.45 sec)
mysql> start slave;
Query OK, 0 rows affected (0.05 sec)mysql>tong1主机:
[root@tong2 .ssh]# mysql -u root -p
Enter password: Welcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 60Server version: 5.6.23-log MySQL Community Server (GPL)Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql> change master to master_host='192.168.1.247',master_port=3306,master_user='repl_user',master_password='system!#%246',master_log_file='mysql-bin.000010',master_log_pos=120;Query OK, 0 rows affected, 2 warnings (0.45 sec)
mysql> start slave;
Query OK, 0 rows affected (0.05 sec)mysql>8.检查复制是否有错
[root@tong3 ~]# masterha_check_repl --conf=/etc/mysqlmha/conf/app1.cnf
Tue Apr 28 15:53:23 2015 - [info] Checking replication health on 192.168.1.249..
Tue Apr 28 15:53:23 2015 - [info] ok.Tue Apr 28 15:53:23 2015 - [info] Checking replication health on 192.168.1.248..Tue Apr 28 15:53:23 2015 - [info] ok.Tue Apr 28 15:53:23 2015 - [warning] master_ip_failover_script is not defined.Tue Apr 28 15:53:23 2015 - [warning] shutdown_script is not defined.Tue Apr 28 15:53:23 2015 - [info] Got exit code 0 (Not master dead).MySQL Replication Health is OK.[root@tong3 mysqlmha]# nohup masterha_manager --conf=/etc/mysqlmha/conf/app1.cnf >> /tmp/mha_manager 2>&1 &
[root@tong3 mysqlmha]# jobs
[1]+ Running nohup masterha_manager --conf=/etc/mysqlmha/conf/app1.cnf >> /tmp/mha_manager 2>&1 &[root@tong3 mysqlmha]# masterha_check_status --conf=/etc/mysqlmha/conf/app1.cnf app1 (pid:24330) is running(0:PING_OK), master:192.168.1.247 --此时主节点在247服务器上[root@tong3 mysqlmha]#9.故障测试
tong1节点:
[root@tong1 ~]# /etc/init.d/mysqld stop --停掉tong1节点的数据库
Shutting down MySQL............ SUCCESS! [root@tong2 ~]# /etc/init.d/mysqld start --节点必须启动才能做切换Starting MySQL. SUCCESS!
[root@tong2 ~]#查看tong2主机的日志状态,将tong1节点必须要同步到tong2节点上才可以切换(change master to master_host='192.168.1.248,master_ ....................)
tong3节点:
[root@tong3 mysqlmha]# rm -rf /var/log/masterha/app1/app1.failover.complete
[1]+ Done nohup masterha_manager --conf=/etc/mysqlmha/conf/app1.cnf >> /tmp/mha_manager 2>&1 --必须删除管理节点的监控文件[root@tong3 mysqlmha]# nohup masterha_manager --conf=/etc/mysqlmha/conf/app1.cnf >> /tmp/mha_manager 2>&1 &
[root@tong3 ~]# masterha_check_status --conf=/etc/mysqlmha/conf/app1.cnf
app1 (pid:27870) is running(0:PING_OK), master:192.168.1.248[root@tong3 ~]#