MHA-Atlas-MySQL高可用集群

主机名映射

[root@localhost ~]# cat /etc/hosts
127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4
::1 localhost localhost.localdomain localhost6 localhost6.localdomain6
192.168.200.20 mysql-db01
192.168.200.28 mysql-db02
192.168.200.29 mysql-db03

关闭selinux和iptables

[root@mysql-db01 ~]# setenforce 0
[root@mysql-db01 ~]# service iptables stop
iptables: Setting chains to policy ACCEPT: filter [ OK ]
iptables: Flushing firewall rules: [ OK ]
iptables: Unloading modules: [ OK ]

安装（3台都装）

[root@mysql-db01 ~]# yum -y install ncurses-devel
[root@mysql-db01 ~]# yum -y install libaio
[root@mysql-db01 ~]# tar xf mysql-5.6.17-linux-glibc2.5-x86_64.tar.gz -C /usr/local/
[root@mysql-db01 ~]# ln -s /usr/local/mysql-5.6.17-linux-glibc2.5-x86_64 /usr/local/mysql
[root@mysql-db01 ~]# useradd mysql -s /sbin/nologin -M
[root@mysql-db01 ~]# /usr/local/mysql/scripts/mysql_install_db --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data/
[root@mysql-db01 ~]# /bin/cp /usr/local/mysql/support-files/my-default.cnf /etc/my.cnf
[root@mysql-db01 ~]# /bin/cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld
[root@mysql-db01 ~]# ln -s /usr/local/mysql/bin/* /usr/local/bin/
[root@mysql-db01 ~]# which mysqladmin
/usr/local/bin/mysqladmin

加入开机自启动并启动mysql

[root@mysql-db01 ~]# chkconfig mysqld on
[root@mysql-db01 ~]# chkconfig mysqld --list
mysqld 0:off 1:off 2:on 3:on 4:on 5:on 6:off
[root@mysql-db01 ~]# /etc/init.d/mysqld start
Starting MySQL. SUCCESS!
[root@mysql-db01 ~]# netstat -antup | grep mysql
tcp 0 0 :::3306 :::* LISTEN 1258/mysqld

配置密码

[root@mysql-db01 ~]# mysqladmin -uroot password '666666'

主库操作（mysql-db01）

修改配置文件

#修改主库配置文件/etc/my.cnf
[root@mysql-db01 mysql]# cat /etc/my.cnf
[client]
socket = /usr/local/mysql/data/mysql.sock
[mysqld]
lower_case_table_names = 1
default-storage-engine = InnoDB
port = 3306
datadir = /usr/local/mysql/data
character-set-server = utf8
socket = /usr/local/mysql/data/mysql.sock
log_bin = mysql-bin #开启binlog日志
server_id = 1 #设置server_id
innodb_buffer_pool_size = 200M
slave-parallel-workers = 8
thread_cache_size = 600
back_log = 600
slave_net_timeout = 60
max_binlog_size = 512M
key_buffer_size = 8M
query_cache_size = 64M
join_buffer_size = 2M
sort_buffer_size = 2M
query_cache_type = 1
thread_stack = 192K
#重启动MySQL服务
[root@mysql-db01 mysql]# /etc/init.d/mysqld restart
Shutting down MySQL.. SUCCESS!
Starting MySQL. SUCCESS!

创建主从复制用户

mysql> grant replication slave on *.* to rep@'192.168.200.%' identified by '666666';
Query OK, 0 rows affected (0.01 sec)
mysql> select user,host from mysql.user;
+------+---------------+
| user | host |
+------+---------------+
| root | 127.0.0.1 |
| rep | 192.168.200.% |
| root | ::1 |
| | localhost |
| root | localhost |
| | mysql-db01 |
| root | mysql-db01 |
+------+---------------+
7 rows in set (0.00 sec)
mysql> show grants for rep@'192.168.200.%';
+----------------------------------------------------------------------------------------------------------------------------+
| Grants for rep@192.168.200.% |
+----------------------------------------------------------------------------------------------------------------------------+
| GRANT REPLICATION SLAVE ON *.* TO 'rep'@'192.168.200.%' IDENTIFIED BY PASSWORD '*B2B366CA5C4697F31D4C55D61F0B17E70E5664EC' |
+----------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

从库操作(mysql-db02和mysql-db03)

修改配置文件

#修改mysql-db02配置文件(和mysql-db01配置文件一致)
#只需要修改server-id = 5选项
[root@mysql-db02 ~]# cat /etc/my.cnf
[client]
socket = /usr/local/mysql/data/mysql.sock
[mysqld]
lower_case_table_names = 1
default-storage-engine = InnoDB
port = 3306
datadir = /usr/local/mysql/data
character-set-server = utf8
socket = /usr/local/mysql/data/mysql.sock
log_bin = mysql-bin #从binlog也要打开
server_id = 5 #仅需修改此项
innodb_buffer_pool_size = 200M
slave-parallel-workers = 8
thread_cache_size = 600
back_log = 600
slave_net_timeout = 60
max_binlog_size = 512M
key_buffer_size = 8M
query_cache_size = 64M
join_buffer_size = 2M
sort_buffer_size = 2M
query_cache_type = 1
thread_stack = 192K
[root@mysql-db02 ~]# /etc/init.d/mysqld restart #重启mysql
Shutting down MySQL.. SUCCESS!
Starting MySQL. SUCCESS!
1. #修改mysql-db03配置文件(和mysql-db01配置文件一致)
2. #只需要修改server-id = 10选项
3. [root@mysql-db03 ~]# cat /etc/my.cnf
4. [client]
5. socket = /usr/local/mysql/data/mysql.sock
6. [mysqld]
7. lower_case_table_names = 1
8. default-storage-engine = InnoDB
9. port = 3306
10. datadir = /usr/local/mysql/data
11. character-set-server = utf8
12. socket = /usr/local/mysql/data/mysql.sock
13. log_bin = mysql-bin #从binlog也要打开
14. server_id = 10 #只需修改此项
15. innodb_buffer_pool_size = 200M
16. slave-parallel-workers = 8
17. thread_cache_size = 600
18. back_log = 600
19. slave_net_timeout = 60
20. max_binlog_size = 512M
21. key_buffer_size = 8M
22. query_cache_size = 64M
23. join_buffer_size = 2M
24. sort_buffer_size = 2M
25. query_cache_type = 1
26. thread_stack = 192K
27. [root@mysql-db03 ~]# /etc/init.d/mysqld restart #重启mysql
28. Shutting down MySQL.. SUCCESS!
29. Starting MySQL. SUCCESS!

开启GTID

mysql> show global variables like '%gtid%'; #查看GTID是否开启
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| enforce_gtid_consistency | OFF |
| gtid_executed | |
| gtid_mode | OFF |
| gtid_owned | |
| gtid_purged | |
+--------------------------+-------+
5 rows in set (0.07 sec) #没开启

编辑mysql配置文件（主库从库都需要修改）
image_1ct0ajvnj13flq6cf94eidav8m.png-59.4kB

再次查看GTID状态

[root@mysql-db01 ~]# mysql -uroot -p666666
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.17-log MySQL Community Server (GPL)
Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show global variables like '%gtid%';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| enforce_gtid_consistency | ON | # 执行GTID一致
| gtid_executed | |
| gtid_mode | ON | # 开启GTID模块
| gtid_owned | |
| gtid_purged | |
+--------------------------+-------+
5 rows in set (0.00 sec)

开启从库的主从复制功能（mysql-db02,mysql-db03）

mysql> start slave; #开启主从同步功能
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.0.51
Master_User: rep
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000003
Read_Master_Log_Pos: 151
Relay_Log_File: mysql-db02-relay-bin.000002
Relay_Log_Pos: 361
Relay_Master_Log_File: mysql-bin.000003
Slave_IO_Running: Yes #此项yes代表成功
Slave_SQL_Running: Yes #此项yes代表成功
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
#....以下省略若干行....

因为MHA需要relaylog日志，但是mysql会自动删除relaylog日志，所以需要关闭自动删除

#登陆从库
[root@mysql-db02 ~]# mysql -uroot -p123123
#临时禁用自动删除relay log功能
mysql> set global relay_log_purge = 0;
Query OK, 0 rows affected (0.00 sec)
#设置只读
mysql> set global read_only=1;
Query OK, 0 rows affected (0.00 sec)

编辑配置文件/etc/my.cof
image_1ct0crfc11ifvith01j431uac13.png-74.7kB

修改配置文件必须重启服务
/etc/init.d/mysqld restart

部署MHA

环境准备（所有节点mysql-01，mysql-02，mysql-03）

#光盘安装依赖包
[root@mysql-db01 ~]# yum -y install perl-DBD-MySQL
#安装mha4mysql-node-0.56-0.el6.noarch.rpm
[root@mysql-db01 rpm]# rpm -ivh mha4mysql-node-0.56-0.el6.noarch.rpm
Preparing... ########################################### [100%]
1:mha4mysql-node ########################################### [100%]
[root@mysql-db01 rpm]# mysql -uroot -p666666
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.6.17-log MySQL Community Server (GPL)
Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> grant all privileges on *.* to mha@'192.168.200.%' identified by '666666';
Query OK, 0 rows affected (0.12 sec)
mysql> select user,host from mysql.user where user='mha';
+------+---------------+
| user | host |
+------+---------------+
| mha | 192.168.200.% | #主库上创建从库自动复制
+------+---------------+
1 row in set (0.10 sec)
# 3台MsQL都需要安装mha4mysql-node-0.56-0.el6.noarch.rpm

部署管理节点（mha-manager）

在mysql-db03上部署管理节点

#使用阿里云源+epel源
wget -O /etc/yum.repos.d/CentOS-Base.repo http://mirrors.aliyun.com/repo/Centos-6.repo
wget -O /etc/yum.repos.d/epel-6.repo http://mirrors.aliyun.com/repo/epel-6.repo
#安装manager依赖包（需要公网源）
[root@mysql-db03 ~]# yum -y install perl-Config-Tiny epel-release perl-Log-Dispatch perl-Parallel-ForkManager perl-Time-HiRes
#安装manager包
[root@mysql-db03 rpm]# rpm -ivh mha4mysql-manager-0.56-0.el6.noarch.rpm
Preparing... ########################################### [100%]
1:mha4mysql-manager ########################################### [100%]

编辑配置文件

#创建配置文件目录
[root@mysql-db03 ~]# mkdir -p /etc/mha
#创建日志目录
[root@mysql-db03 ~]# mkdir -p /var/log/mha/mha1
#创建配置文件（默认没有）
[root@mysql-db03 ~]# cd /etc/mha/
[root@mysql-db03 mha]# ls
[root@mysql-db03 mha]# vim /etc/mha/mha1.cnf
[root@mysql-db03 mha]# cat /etc/mha/mha1.cnf
[server default]
manager_log=/var/log/mha/mha1/manager #manager管理日志存放路径
manager_workdir=/var/log/mha/mha1 #manager管理日志的目录路径
master_binlog_dir=/usr/local/mysql/data #binlog日志的存放路径
user=mha #管理账户
password=123123 #管理账户密码
ping_interval=2 #存活检查的间隔时间
repl_user=rep #主从复制的授权账户
repl_password=123123 #主从复制的授权账户密码
ssh_user=root #用于ssh连接的账户
[server1]
hostname=192.168.0.51
port=3306
[server2]
#candidate_master=1 #此条暂时注释掉（后面解释）
#check_repl_delay=0 #此条暂时注释掉（后面解释）
hostname=192.168.0.52
port=3306
[server3]
hostname=192.168.0.53
port=3306

配置ssh信任（所有节点mysql-db01,mysql-db02,mysql-db03）

#创建秘钥对，三个都创建，因为要互相免密码登录
[root@mysql-db03 ~]# ssh-keygen -t dsa -P "" -f ~/.ssh/id_dsa
#发送mysql-db03公钥，包括自己
[root@mysql-db03 ~]# ssh-copy-id -i /root/.ssh/id_dsa.pub root@192.168.200.20
[root@mysql-db03 ~]# ssh-copy-id -i /root/.ssh/id_dsa.pub root@192.168.200.28
[root@mysql-db03 ~]# ssh-copy-id -i /root/.ssh/id_dsa.pub root@192.168.200.29
#发送mysql-db02公钥，包括自己
[root@mysql-db02 ~]# ssh-copy-id -i /root/.ssh/id_dsa.pub root@192.168.200.20
[root@mysql-db02 ~]# ssh-copy-id -i /root/.ssh/id_dsa.pub root@192.168.200.28
[root@mysql-db02 ~]# ssh-copy-id -i /root/.ssh/id_dsa.pub root@192.168.200.29
#发送mysql-db01公钥，包括自己
[root@mysql-db01 ~]# ssh-copy-id -i /root/.ssh/id_dsa.pub root@192.168.200.20
[root@mysql-db01 ~]# ssh-copy-id -i /root/.ssh/id_dsa.pub root@192.168.200.28
[root@mysql-db01 ~]# ssh-copy-id -i /root/.ssh/id_dsa.pub root@192.168.200.29

启动测试

ssh检查检测

[root@mysql-db03 mha]# masterha_check_ssh --conf=/etc/mha/mha1.cnf #ssh检查命令
Sat Nov 24 06:21:45 2018 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Sat Nov 24 06:21:45 2018 - [info] Reading application default configuration from /etc/mha/mha1.cnf..
Sat Nov 24 06:21:45 2018 - [info] Reading server configuration from /etc/mha/mha1.cnf..
Sat Nov 24 06:21:45 2018 - [info] Starting SSH connection tests..
Sat Nov 24 06:21:46 2018 - [debug]
Sat Nov 24 06:21:45 2018 - [debug] Connecting via SSH from root@192.168.200.20(192.168.200.20:22) to root@192.168.200.28(192.168.200.28:22)..
Sat Nov 24 06:21:45 2018 - [debug] ok.
Sat Nov 24 06:21:45 2018 - [debug] Connecting via SSH from root@192.168.200.20(192.168.200.20:22) to root@192.168.200.29(192.168.200.29:22)..
Sat Nov 24 06:21:45 2018 - [debug] ok.
Sat Nov 24 06:21:47 2018 - [debug]
Sat Nov 24 06:21:46 2018 - [debug] Connecting via SSH from root@192.168.200.29(192.168.200.29:22) to root@192.168.200.20(192.168.200.20:22)..
Sat Nov 24 06:21:46 2018 - [debug] ok.
Sat Nov 24 06:21:46 2018 - [debug] Connecting via SSH from root@192.168.200.29(192.168.200.29:22) to root@192.168.200.28(192.168.200.28:22)..
Sat Nov 24 06:21:47 2018 - [debug] ok.
Sat Nov 24 06:21:47 2018 - [debug]
Sat Nov 24 06:21:45 2018 - [debug] Connecting via SSH from root@192.168.200.28(192.168.200.28:22) to root@192.168.200.20(192.168.200.20:22)..
Sat Nov 24 06:21:46 2018 - [debug] ok.
Sat Nov 24 06:21:46 2018 - [debug] Connecting via SSH from root@192.168.200.28(192.168.200.28:22) to root@192.168.200.29(192.168.200.29:22)..
Sat Nov 24 06:21:46 2018 - [debug] ok.
Sat Nov 24 06:21:47 2018 - [info] All SSH connection tests passed successfully. #出现这个就表示成功

主从复制检查

（1）错误的主从复制检测
[root@mysql-db03 ~]# masterha_check_repl --conf=/etc/mha/mha1.cnf
如果出现下面的情况
image_1ct20dkp01j56s4rgqkis11ifk9.png-228kB

因此在mysql-db02和mysql-db03上添加主从复制的用户即可。
grant replication slave on *.* to rep@'192.168.200.%' identified by '666666';
再次检查

image_1ct20ikvhuiercl1tdb1dgc2pcp.png-27.6kB

启动MHA

#启动
[root@mysql-db03 ~]# nohup masterha_manager --conf=/etc/mha/mha1.cnf --remove_dead_master_conf --ignore_last_failover < /dev/null > /var/log/mha/mha1/manager.log 2>&1 & #启动后按几次回车，不退出就是没问题。
[root@mysql-db03 ~]# ps -ef | grep perl | grep -v grep
root 5585 2925 0 09:23 pts/1 00:00:00 perl /usr/bin/masterha_manager --conf=/etc/mha/mha1.cnf --remove_dead_master_conf --ignore_last_failover

（1）登陆mysql-db02(192.168.200.28)查看信息状态

#登陆数据库mysql-db02(192.168.200.28)
[root@mysql-db03 ~]# mysql -uroot -p666666
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.200.20 #这是主库IP地址
Master_User: rep
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000003
Read_Master_Log_Pos: 1656
Relay_Log_File: mysql-db02-relay-bin.000004
Relay_Log_Pos: 1796
Relay_Master_Log_File: mysql-bin.000003
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
..以下省略若干内容..

（2）停掉mysql-db01(192.168.200.20)上的MySQL服务

[root@localhost rpm]# /etc/init.d/mysqld stop
Shutting down MySQL...... SUCCESS!

（3）查看mysql-db03上的MySQL从库同步状态

[root@localhost mha]# mysql -uroot -p666666 -e 'show slave status\G'
Warning: Using a password on the command line interface can be insecure.
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.200.28 #现在的主库IP
Master_User: rep
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000003 #binlog日志
Read_Master_Log_Pos: 662 #binlog日志位置
Relay_Log_File: localhost-relay-bin.000002
Relay_Log_Pos: 665
Relay_Master_Log_File: mysql-bin.000003
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
..以下省略若干内容..

（4）查看mysql-db02上的MySQL，主库同步状态。
image_1ct28v6do6o10s51ob010erck91j.png-19.6kB

5）查看mysql-db03上的mha进程状态

[root@mysql-db03 ~]# ps -ef | grep perl | grep -v grep #查询发现mha进程已经没了
[root@mysql-db03 ~]#

（6）查看mha配置文件信息
image_1ct294dqo1093lg119321j1i1n9220.png-40.3kB

进行mha的故障还原测试

（1）将故障宕机的mysql-db01的MySQL服务启动并授权进行从同步

[root@mysql-db01 ~]# /etc/init.d/mysqld start
Starting MySQL. SUCCESS!
[root@mysql-db01 ~]# mysql -uroot -p666666
mysql> CHANGE MASTER TO MASTER_HOST='192.168.200.28', MASTER_PORT=3306, MASTER_AUTO_POSITION=1, MASTER_USER='rep', MASTER_PASSWORD='666666';

（2）将mha配置文件里缺失的部分补全

[root@localhost mha]# cat /etc/mha/mha1.cnf
[server default]
manager_log=/var/log/mha/mha1/manager
manager_workdir=/var/log/mha/mha1
master_binlog_dir=/usr/local/mysql/data
password=666666
ping_interval=2
repl_password=666666
repl_user=rep
ssh_user=root
user=mha
[server1]
hostname=192.168.200.20
port=3306
[server2]
hostname=192.168.200.28
port=3306
[server3]
hostname=192.168.200.29
port=3306

（3）启动mha进程

[root@mysql-db03 ~]# nohup masterha_manager --conf=/etc/mha/mha1.cnf --remove_dead_master_conf --ignore_last_failover < /dev/null > /var/log/mha/mha1/manager.log 2>&1 &
[root@localhost mha]# ps -ef |grep perl
root 4541 934 0 23:39 pts/0 00:00:00 perl /usr/bin/masterha_manager --conf=/etc/mha/mha1.cnf --remove_dead_master_conf --ignore_last_failover

（4）停掉mysql-db02上的MySQL服务

[root@localhost ~]# /etc/init.d/mysqld stop
Shutting down MySQL...... SUCCESS!

（5）查看mysql-db03上的主从同步状态：
image_1ct2bdebd11n6guq1q683o81pbq2q.png-40.6kB
（6）启动mysql-db02上的MySQL服务

[root@mysql-db02 ~]# /etc/init.d/mysqld start
Starting MySQL. SUCCESS!
[root@mysql-db02 ~]# mysql -uroot -p666666
mysql> CHANGE MASTER TO MASTER_HOST='192.168.200.20', MASTER_PORT=3306, MASTER_AUTO_POSITION=1, MASTER_USER='rep', MASTER_PASSWORD='666666';
mysql> start slave;
mysql> show slave status\G #查看从同步状态

（7）再次补全mha配置文件后，启动mha进程

[root@localhost mha]# cat /etc/mha/mha1.cnf
[server default]
manager_log=/var/log/mha/mha1/manager
manager_workdir=/var/log/mha/mha1
master_binlog_dir=/usr/local/mysql/data
password=666666
ping_interval=2
repl_password=666666
repl_user=rep
ssh_user=root
user=mha
[server1]
hostname=192.168.200.20
port=3306
[server2]
hostname=192.168.200.28
port=3306
[server3]
hostname=192.168.200.29
port=3306
[root@mysql-db03 ~]# nohup masterha_manager --conf=/etc/mha/mha1.cnf --remove_dead_master_conf --ignore_last_failover < /dev/null > /var/log/mha/mha1/manager.log 2>&1 &
[root@localhost mha]# ps -ef | grep perl | grep -v grep
root 5027 934 0 Nov24 pts/0 00:00:00 perl /usr/bin/masterha_manager --conf=/etc/mha/mha1.cnf --remove_dead_master_conf --ignore_last_failover

posted on 2018-11-25 11:15 灬华灬阅读(178) 评论(0) 编辑收藏举报

刷新页面返回顶部

导航

公告

MHA-Atlas-MySQL高可用集群

主机名映射

关闭selinux和iptables

安装（3台都装）

加入开机自启动并启动mysql

配置密码

主库操作（mysql-db01）

修改配置文件

从库操作(mysql-db02和mysql-db03)

修改配置文件

开启GTID

开启从库的主从复制功能（mysql-db02,mysql-db03）

部署MHA

环境准备（所有节点mysql-01，mysql-02，mysql-03）

部署管理节点（mha-manager）

在mysql-db03上部署管理节点

编辑配置文件

配置ssh信任（所有节点mysql-db01,mysql-db02,mysql-db03）

启动测试

ssh检查检测

主从复制检查

启动MHA

进行mha的故障还原测试