一、部署MHA+Atlas实现Mysql集群的高可用及读写分离
1.1 环境说明
|
主机 |
IP地址 |
备注 |
|
Mysql-proxy |
192.168.1.10 |
Atlas，提供Mysql的访问代理，实现主从的读写分离 |
|
db01 |
192.168.1.11 |
基于GTID主从的主库 |
|
db02 |
192.168.1.12 |
基于GTID主从的从库 |
|
db03 |
192.168.1.13 |
基于GTID主从的从库 |
|
MHA-Manager |
192.168.1.14 |
MHA，实现mysql主库的故障切换 |
1.2 搭建基于GTID的主从复制环境
1.2.1 Mysql安装
使用二进制安装，执行主机db01、db02、db03
安装相关依赖
yum install -y ncurses-devel libaio-devel gcc gcc-c++ numactl libaio glibc cmake autoconf
下载软件包
wget https://mirrors.tuna.tsinghua.edu.cn/mysql/downloads/MySQL-5.7/mysql-5.7.36-el7-x86_64.tar.gz
解压并创建软链接
tar -zxf mysql-5.7.36-el7-x86_64.tar.gz -C /usr/local ln -s /usr/local/mysql-5.7.36-el7-x86_64 /usr/local/mysql
添加到环境变量
echo "export PATH=$PATH:/usr/local/mysql/bin/" > /etc/profile.d/mysql.sh source /etc/profile
准备数据目录及日志文件
mkdir /usr/local/mysql/data touch /var/log/mysqld.log
创建mysql进程用户
useradd -M -s /sbin/nologin mysql
目录及文件授权
chown -R mysql.mysql /usr/local/mysql chown mysql.mysql /var/log/mysqld.log
编缉my.cnf配置文件，其中02及lb03的server-id分别设置为2和3，不能与b01相同
cat > /etc/my.cnf << EOF [mysqld] basedir=/usr/local/mysql datadir=/usr/local/mysql/data/ socket=/tmp/mysql.sock log-error=/var/log/mysql.log log-bin=mysql-bin binlog_format=row skip-name-resolve server-id=1 gtid_mode=on enforce-gtid-consistency=on log-slave-updates=1 EOF
初始化实例
mysqld --initialize-insecure --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data
复制启动脚本到启动目录并授权
cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld
chmod +x /etc/init.d/mysqld
启动
/etc/init.d/mysqld start
设置root密码
mysqladmin -uroot -p password "123456"
1.2.2 构建主从复制
创建主从复制账号，执行主机lb01
mysql -uroot -p123456 -e “grant replication slave on *.* to dbsync@’192.168.1.%’ identified by ‘123456’;”
从库配置，执行主机lb02、lb03
登录mysql控制台
mysql -uroot -p 123456
写入主库信息
change master to master_host=’192.168.1.11’,master_port=3306,master_user=’dbsync’,master_password=’123456’,master_auto_position=1;
禁止relaylog 自动删除
set global relay_log_purge = 0; 并在my.cnf配置文件[mysqld]标签下添加 relay_log_purge = 0
开启从库
start slave;
检查IO线程以及SQL线程是否正常
show slave status\G;
1.3 搭建MHA高可用
配置主机互信，执行主机MHA-Manager、lb01、lb02、lb03，注意将xx替换为目标IP
ssh-keygen sshpass -p123456 ssh-copy-id root@192.168.1.xx -o StrictHostKeyChecking
安装MHA客户端，执行主机MHA-Manager、lb01、lb02、lb03
安装依赖
yum -y install perl-DBD-MySQL
下载并安装MHA-node软件包
wget https://qiniu.wsfnk.com/mha4mysql-node-0.58-0.el7.centos.noarch.rpm rpm -ivh mha4mysql-node-0.58-0.el7.centos.noarch.rpm
创建软链接，确保与MHA脚本执行路径一致，执行主机lb01、lb02、lb03
ln -s /usr/local/mysql/bin /usr/bin
ln -s /usr/local/mysql/bin/mysqlbinlog /usr/bin/mysqlbinlog
安装MHA管理端，执行主机MHA-Manager
安装依赖
yum install -y perl-Config-Tiny perl-Log-Dispatch cpan perl-Parallel-ForkManager perl-Time-HiRes
下载并安装MHA-manager软件
wget https://qiniu.wsfnk.com/mha4mysql-manager-0.58-0.el7.centos.noarch.rpm rpm -ivh mha4mysql-manager-0.58-0.el7.centos.noarch.rpm
编缉配置文件，添加mysql主机信息
mkdir /etc/mha && mkdir /var/log/mha/app1 cat >>/etc/mha/app1.cnf<<EOF [server default] manager_log=/var/log/mha/app1/manager manager_workdir=/var/log/mha/app1 master_binlog_dir=/data/binlog user=mha password=mha ping_interval=2 repl_user=dbrsync repl_password=123456 ssh_user=root [server1] hostname=192.168.1.11 port=3306 [server2] hostname=192.168.1.12 port=3306 [server3] hostname=192.168.1.13 port=3306 EOF
创建mha管理用户，执行主机db01
mysql -uroot -p123456 -e " grant all privileges on *.* to mha@'192.168.1.%' identified by 'mha'; flush privileges;"
检查互信
masterha_check_ssh --conf=/etc/mha/app1.cnf
检查主从
masterha_check_repl --conf=/etc/mha/app1.cnf
启动MHA
nohup masterha_manager --conf=/etc/mha/app1.cnf --remove_dead_master_conf --ignore_last_failover < /dev/null > /var/log/mha/app1/manager.log 2>&1 &
检查MHA
masterha_check_status --conf=/etc/mha/app1.cnf
1.4 配置MHA应用透明
为主库添加VIP，执行主机lb01
ifconfig eth0:1 192.168.1.15/24
创建配置文件，修改vip与主库lb01一致，执行主机MHA-Manager
cat > /usr/local/bin/master_ip_failover << 'EOF' #!/usr/bin/env perl use strict; use warnings FATAL => 'all'; use Getopt::Long; my ( $command, $ssh_user, $orig_master_host, $orig_master_ip, $orig_master_port, $new_master_host, $new_master_ip, $new_master_port ); my $vip = '192.168.1.15/24'; my $key = '1'; my $ssh_start_vip = "/sbin/ifconfig eth0:$key $vip"; my $ssh_stop_vip = "/sbin/ifconfig eth0:$key down"; GetOptions( 'command=s' => \$command, 'ssh_user=s' => \$ssh_user, 'orig_master_host=s' => \$orig_master_host, 'orig_master_ip=s' => \$orig_master_ip, 'orig_master_port=i' => \$orig_master_port, 'new_master_host=s' => \$new_master_host, 'new_master_ip=s' => \$new_master_ip, 'new_master_port=i' => \$new_master_port, ); exit &main(); sub main { print "\n\nIN SCRIPT TEST====$ssh_stop_vip==$ssh_start_vip===\n\n"; if ( $command eq "stop" || $command eq "stopssh" ) { my $exit_code = 1; eval { print "Disabling the VIP on old master: $orig_master_host \n"; &stop_vip(); $exit_code = 0; }; if ($@) { warn "Got Error: $@\n"; exit $exit_code; } exit $exit_code; } elsif ( $command eq "start" ) { my $exit_code = 10; eval { print "Enabling the VIP - $vip on the new master - $new_master_host \n"; &start_vip(); $exit_code = 0; }; if ($@) { warn $@; exit $exit_code; } exit $exit_code; } elsif ( $command eq "status" ) { print "Checking the Status of the script.. OK \n"; exit 0; } else { &usage(); exit 1; } } sub start_vip() { `ssh $ssh_user\@$new_master_host \" $ssh_start_vip \"`; } sub stop_vip() { return 0 unless ($ssh_user); `ssh $ssh_user\@$orig_master_host \" $ssh_stop_vip \"`; } sub usage { print "Usage: master_ip_failover --command=start|stop|stopssh|status --orig_master_host=host --orig_master_ip=ip --orig_master_port=port --new_master_host=host --new_master_ip=ip --new_master_port=port\n"; } EOF
授权
chmod +x /usr/local/bin/master_ip_failover
转换为unix格式编码
dos2unix /usr/local/bin/master_ip_failover
配置MHA配置文件，在[server defaut]标签加入脚本路径
master_ip_failover_script=/usr/local/bin/master_ip_failover
重启MHA
masterha_stop --conf=/etc/mha/app1.cnf nohup masterha_manager --conf=/etc/mha/app1.cnf --remove_dead_master_conf --ignore_last_failover < /dev/null > /var/log/mha/app1/manager.log 2>&1 &
1.5 配置binlogServer进行外部数据补偿
编缉MHA配置文件，添加binlog服务器，执行主机MHA-Manager，需安装Mysql工具。
vim /etc/mha/app1.conf
……
[binlog1]
no_master=1
hostname=192.168.1.14
master_binlog_dir=/data/mysql/binlog
创建相关目录并授权
mkdir -p /data/mysql/binlog useradd -M -s /sbin/nologin mysql chown -R mysql.mysql /data/mysql/*
拉取数据，binlog名称与主库保持一致
cd /data/mysql/binlog/ mysqlbinlog -R --host=192.168.1.11 --user=mha --password=mha --raw --stop-never mysql-bin.000001 &
重启MHA
masterha_stop --conf=/etc/mha/app1.cnf nohup masterha_manager --conf=/etc/mha/app1.cnf --remove_dead_master_conf --ignore_last_failover < /dev/null > /var/log/mha/app1/manager.log 2>&1 &
1.6 配置读写分离
下载并安装Atlat中间件，执行主机Mysql-proxy
wget https://github.com/Qihoo360/Atlas/releases/download/2.2.1/Atlas-2.2.1.el6.x86_64.rpm rpm -ivh Atlas-2.2.1.el6.x86_64.rpm
添加生产用户，执行主机lb01
mysql -uroot -p123456 -e "grant all privileges on *.* to www@'192.168.1.%' identified by '123456';"
生成mha管理用户密码、生产用户密码与主从复制用户密码的密文
/usr/local/mysql-proxy/bin/encrypt mha /usr/local/mysql-proxy/bin/encrypt 123456
编缉配置文件，添加Mysql主从复制、MHA管理、生产用户以及对应密码密文，添加主库为写库，从库为读库
vim /usr/local/mysql-proxy/conf/test.cnf admin-username = user admin-password = pwd …… proxy-backend-addresses = 192.168.1.15:3306 proxy-read-only-backend-addresses = 192.168.1.12:3306,192.168.1.13:3306 pwds = repl:3yb7jEku3h8=,mha:O2jBXONX075=,www:O2jBXONX075= ……
启动Atlas
/usr/local/mysql-proxy/bin/mysql-proxyd test start
浙公网安备 33010602011771号