MySQL实战-从0到1搭建MySQL MHA生产环境

       在 MySQL 故障切换过程中，MHA 能做到在0~30秒之内自动完成数据库的故障切换操作，并且在进行故障切换的过程中，MHA 能在最大程度上保证数据的一致性，以达到真正意义上的高可用。2021-02-09 13:30:35

涉及技术

1、MySQL生产环境安装配置

2、MySQL主从搭建

3、MySQLMHA搭建
4、binlog server 安装配置

5、VIP 的使用

6、故障模拟

7、故障恢复


本文章将MHA所有应用到的技术全部整合在一起，复制粘贴即可使用在

复制

环境

 centos7.5

IP规划

10.0.0.51      主库     mha-node     db01
10.0.0.52      从库     mha-node     db02
10.0.0.53      从库     mha-manager  db03
复制

所有安装包

http://www.fanxin66.cn:88/

基础环境配置

关闭防火墙  firwalld  iptable  selinux --->所有节点执行

systemctl stop firewalld.service 
systemctl disable  firewalld.service
#查看selinux
getenforce 
#关闭selinux
setenforce 0  
#永久关闭selinux
sed -i  's#SELINUX=enforcing#SELINUX=disabled#g'   /etc/selinux/config
复制

配置阿里源--->所有节点执行

#备份
mv /etc/yum.repos.d/CentOS-Base.repo /etc/yum.repos.d/CentOS-Base.repo.backup
#配置源
curl -o /etc/yum.repos.d/CentOS-Base.repo https://mirrors.aliyun.com/repo/Centos-7.repo
#清除缓存
yum clean all
复制

###############################################################################################################

MySQL实例安装

#安装常用命令及依赖--->所有节点执行

yum install   -y  net-tools lrzsz  vim  libaio-devel \
tree  libaio-devel lsof sysstat  bash-completion  bash-completion-extras
复制

#卸载mariadb--->所有节点执行

yum remove  mariadb*  -y
复制

创建MySQL软件及数据目录--->所有节点执行

mkdir  /mysql/{tools,tar}  -p
mkdir  /mydata/3306/{binlog,data,etc,lock,log,pid,socket,tmp}  -p
复制

将安装包上传到  mysql/tar 目录--->所有节点执行

解压MySQL包--->所有节点执行

cd      /mysql/tar
tar xf  /mysql/tar/mysql-5.7.20-linux-glibc2.12-x86_64.tar.gz
复制

移动tar包到mysql目录--->所有节点执行

mv /mysql/tar/mysql-5.7.20-linux-glibc2.12-x86_64  /mysql/mysql57
复制

配置环境变量--->所有节点执行

cat  >/etc/profile.d/mysql.sh<<'EOF'
export PATH=/mysql/mysql57/bin:$PATH
EOF
#修改权限
chmod 700 /etc/profile.d/mysql.sh
#生效环境变量
source /etc/profile.d/mysql.sh
复制

创建所需用户--->所有节点执行

useradd mysql
复制

添加配置文件  (三台机器server_id不能相同)--->所有节点执行

cat  >/mydata/3306/etc/my.cnf<<'EOF'
[client]
socket=/mydata/3306/socket/mysql.sock
[mysql]
socket=/mydata/3306/socket/mysql.sock
user=root
password=123456
prompt='\u@\d>\_'
default-character-set=utf8mb4
no-auto-rehash  
show-warnings  #查看告警信息

[mysqld]
# GENERAL #
lower_case_table_names         = 1
character_set_server           = utf8mb4
collation_server               = utf8mb4_general_ci
user                           = mysql
default_storage_engine         = InnoDB
port                           = 3306
socket                         = /mydata/3306/socket/mysql.sock
pid-file                       = /mydata/3306/pid/mysql.pid
log_timestamps                 = SYSTEM
default_time_zone              = +08:00
secure_file_priv               = ''
local_infile                   = ON

# MyISAM #
key_buffer_size                = 128M
myisam_sort_buffer_size        = 8M
read_rnd_buffer_size           = 262144

# SAFETY #
back_log                       = 1024
bind-address                   = 0.0.0.0
innodb_strict_mode             = 1
max_allowed_packet             = 32M
max_connect_errors             = 1000000
interactive_timeout            = 900
wait_timeout                   = 900
skip_grant_tables              = 0
skip_name_resolve              = 1
host_cache_size                = 0
sql_mode                       = TRADITIONAL
sysdate_is_now                 = 1
transaction_isolation          = REPEATABLE-READ
explicit_defaults_for_timestamp = 1

# DATA STORAGE #
basedir                        = /mysql/mysql57
datadir                        = /mydata/3306/data
tmpdir                         = /mydata/3306/tmp

# BINARY LOGGING #
#max_binlog_files              = 100
binlog_cache_size              = 1M
binlog_format                  = ROW
expire_logs_days               = 7
max_binlog_size                = 512M
max_binlog_cache_size          = 4G
log_bin                        = /mydata/3306/binlog/mysql-bin
log_bin_index                  = /mydata/3306/binlog/mysql-bin.index
sync_binlog                    = 1
innodb_support_xa              = 1
master-info-repository         = TABLE

# REPLICATION #
log_slave_updates              = 1
read_only                      = 1
relay_log                      = /mydata/3306/binlog/relay-bin
relay_log_index                = /mydata/3306/binlog/relay-bin.index
server_id                      = 330651
slave_skip_errors              = OFF
skip_slave_start
slave_net_timeout              = 60
relay-log-info-repository      = TABLE
slave_compressed_protocol      = OFF   #5.6版本、5.7.21+、8.0.4+可以设置为ON

# CACHES AND LIMITS #
max_connections                = 2000
max_heap_table_size            = 128M
tmp_table_size                 = 128M
open_files_limit               = 65535
query_cache_limit              = 1M
query_cache_min_res_unit       = 4096
query_cache_size               = 0
query_cache_type               = 0
join_buffer_size               = 1M
read_buffer_size               = 1M
sort_buffer_size               = 1M
table_definition_cache         = 4096
table_open_cache               = 4000
thread_cache_size              = 1000

# INNODB #
innodb_autoextend_increment    = 8
innodb_buffer_pool_instances   = 1
innodb_buffer_pool_size        = 4G
innodb_buffer_pool_load_at_startup  = ON
innodb_buffer_pool_dump_at_shutdown = ON
innodb_data_file_path          = ibdata1:1024M:autoextend
innodb_fast_shutdown           = 1
innodb_file_format             = Barracuda
innodb_file_format_max         = Barracuda
innodb_file_per_table          = 1
innodb_force_recovery          = 0
innodb_flush_log_at_trx_commit = 1
innodb_flush_method            = O_DIRECT
innodb_lock_wait_timeout       = 50
innodb_log_buffer_size         = 8M
innodb_log_file_size           = 1024M
innodb_log_files_in_group      = 4
innodb_open_files              = 10000
innodb_purge_threads           = 4
innodb_read_io_threads         = 8
innodb_write_io_threads        = 8
innodb_sync_spin_loops         = 30
innodb_thread_concurrency      = 24
innodb_print_all_deadlocks     = 1

# LOGGING #
general_log                    = 0
general_log_file               = /mydata/3306/log/mysql-general.log
log_error                      = /mydata/3306/log/mysql-error.log
log_queries_not_using_indexes  = 0
long_query_time                = 1
slow_query_log                 = 1
slow_query_log_file            = /mydata/3306/log/mysql-slow.log

# GTID
enforce_gtid_consistency       = ON
gtid_mode                      = ON
binlog_gtid_simple_recovery    = TRUE

# SEMI-SYNC
plugin-load = "rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"
rpl_semi_sync_master_enabled = ON
rpl_semi_sync_slave_enabled = ON
rpl_semi_sync_master_timeout = 10000
rpl_semi_sync_master_trace_level = 32
rpl_semi_sync_slave_trace_level = 32
rpl_semi_sync_master_wait_no_slave = OFF

##parallel ##
binlog_group_commit_sync_delay          = 0
binlog_group_commit_sync_no_delay_count = 0
slave_parallel_type                     = logical_clock
slave_parallel_workers                  = 16
EOF
ln -s /mydata/3306/etc/my.cnf  /etc/my.cnf
复制

创建error文件--->所有节点执行

touch /mydata/3306/log/mysql-error.log
复制

授权mysql数据目录--->所有节点执行

chown -R mysql.mysql /mydata/*
复制

初始化MySQL--->所有节点执行

mysqld  --initialize-insecure  --user=mysql --basedir=/mysql/mysql57 --datadir=/mydata/3306/data
复制

拷贝启动脚本--->所有节点执行

cp -a /mysql/mysql57/support-files/mysql.server   /etc/init.d/mysqld
复制

修改脚本--->所有节点执行

cat  >>/etc/init.d/mysqld<<'EOF'
basedir=/mysql/mysql57
datadir=/mydata/3306/data
EOF
复制

启动实例--->所有节点执行

/etc/init.d/mysqld  start
复制

登录mysql  --->没密码--->所有节点执行

mysql -uroot -p   #直接回车
复制

修改密码--->所有节点执行

alter user root@localhost identified by '123456';
flush  privileges;
复制

以上为安装MySQL实例 3台机器都执行以上操作

###############################################################################################################

搭建主从（GTID）

创建备份目录--->所有节点执行

mkdir /backup
复制

创建主从复制用户--->db01主库执行

grant replication slave  on *.*  to repl@'10.0.0.%' identified by '123456';
flush privileges;
复制

全备主库 --->db01主库执行

mysqldump \
--user=root \
--password=123456 \
-S /mydata/3306/socket/mysql.sock \
-A --master-data=2 \
--single-transaction  -R \
--triggers >/backup/full.sql

####另一种方法###########################################################################
#或者用innobackupex 备份 然后SCP 到从库的data 目录也行
innobackupex \
--defaults-file=/etc/my.cnf \
--user=root \
--password=123456 \
--host=localhost \
--port=3306 \
--ftwrl-wait-query-type=all \
--ftwrl-wait-timeout=10 \
--ftwrl-wait-threshold=2 \
--kill-long-queries-timeout=20 \
--kill-long-query-type=select \
--tmpdir=/tmp  \
--extra-lsndir=/tmp \
--slave-info \
--no-timestamp /backup/full

#打包备份
cd  /backup
tar  zcpf  full.tar.gz  full

#发送到从库
scp  /data/full.tar.gz   root@10.0.0.52:/data/
scp  /data/full.tar.gz   root@10.0.0.53:/data/

#解压 ——>从库
cd /backup
tar xf full.tar.gz

#回滚事务
innobackupex --apply-log /backup/full

#删除旧数据
cd /mydata/3306/
rm -rf binlog/*  data/*  lock/* log/*  pid/*  socket/*  tmp/*

#将数据移到data目录
mv /data/full/*  /mydata/3306/data/

#创建error文件
touch /mydata/3306/log/mysql-error.log

#授权
chown -R mysql:mysql  /mydata/

#启动
/etc/init.d/mysqld start 
#############   完    #############################################################################
复制

将全备上传到从库--->db01主库执行

scp  /backup/full.sql root@10.0.0.52:/backup
scp  /backup/full.sql root@10.0.0.53:/backup
复制

从库执行--->db02/db03从库库执行

source /backup/full.sql
复制

从库指定主库信息--->db02/db03从库库执行

change master to 
master_host='10.0.0.51',
master_user='repl',
master_port=3306,
master_password='123456' ,
MASTER_AUTO_POSITION=1;
#开启主从
start slave;
#查看主从信息
show slave status\G
复制

以上为主从复制搭建

###############################################################################################################

MHA搭建

软链接命令 ：不然会报错--->所有节点执行

ln -s /mysql/mysql57/bin/mysqlbinlog    /usr/bin/mysqlbinlog
ln -s /mysql/mysql57/bin/mysql          /usr/bin/mysql
复制

配置免密登录--->所有节点执行

rm -rf /root/.ssh 
ssh-keygen  #一路回车
cd /root/.ssh 
mv id_rsa.pub authorized_keys
scp  -r  /root/.ssh  10.0.0.51:/root 
scp  -r  /root/.ssh  10.0.0.52:/root 
scp  -r  /root/.ssh  10.0.0.53:/root
复制

各节点验证 --->所有节点执行

ssh 10.0.0.51 date
ssh 10.0.0.52 date
ssh 10.0.0.53 date
复制

下载MHA

mha官网：https://code.google.com/archive/p/mysql-master-ha/

github下载地址：https://github.com/yoshinorim/mha4mysql-manager/wiki/Downloads

博客下载：  http://www.fanxin66.cn:88/mha/

将安装包上传到  /mysql/tar/  下--->所有节点执行

所有节点安装MHA-Node依赖和安装包--->所有节点执行

yum install perl-DBD-MySQL -y
rpm -ivh /mysql/tar/mha4mysql-node-0.56-0.el6.noarch.rpm
复制

在db01主库中创建mha需要的用户--->db01主库执行

grant all privileges on *.* to mha@'10.0.0.%' identified by '123456';
flush privileges;
复制

MHA-Manager软件安装--->db03从库执行

yum install -y perl-Config-Tiny epel-release perl-Log-Dispatch perl-Parallel-ForkManager perl-Time-HiRes perl-Log-Dispatch perl-Parallel-ForkManager dos2unix
rpm -ivh /mysql/tar/mha4mysql-manager-0.56-0.el6.noarch.rpm
复制

创建MHA配置目录--->db03从库执行

#配置文件目录
mkdir -p /etc/mha
#日志目录
mkdir -p /var/log/mha/app1
复制

配置VIP--->db03从库执行

#将master_ip_failover脚本 放到/usr/local/bin 下面

#修改配置文件为自己想用的IP
vim  /usr/local/bin/master_ip_failover
my $vip = '10.0.0.55/24';                               #自己的VIP
my $key = '1';
my $ssh_start_vip = "/sbin/ifconfig eth0:$key $vip";    #自己网卡名字
my $ssh_stop_vip = "/sbin/ifconfig eth0:$key down";     #自己网卡名字

#转换格式
dos2unix /usr/local/bin/master_ip_failover 

#赋予执行权限
chmod +x /usr/local/bin/master_ip_failover
复制

绑定VIP--->db01主库执行

#手工在主库上绑定vip，注意一定要和配置文件中的ethN一致，我的是ens33:1(1是key指定的值)
ifconfig ens33:1 10.0.0.55/24
#检查是否绑定成功
ip  a
inet 10.0.0.55/24 brd 10.0.0.255 scope global secondary ens33:1
复制

配置binlog-server--->db03从库执行

#创建目录
mkdir -p /data/mysql/binlog
chown -R mysql.mysql /data/mysql/*

#将主库binlog拉过来（从000001开始拉，之后的binlog会自动按顺序过来）

#拉取主库binlog日志
cd /data/mysql/binlog/     #----->必须进入到自己创建好的目录-->必须从主库正在用的binlog文件开始拉取
mysqlbinlog  -R --host=10.0.0.51 --user=mha --password=123456 --raw  --stop-never mysql-bin.000001 &

#检查状态
ps -ef|grep [m]ysqlbinlog
root      16305   1439  0 22:36 pts/0    00:00:00 mysqlbinlog -R --host=10.0.0.53 --user=mha --password=x xxxxxx --raw --stop-never mysql-bin.000003

ll
-rw-r-----. 1 root root 274 Feb 10 22:36 mysql-bin.000003
复制

编写MHA配置文件--->db03从库执行

cat  >/etc/mha/app1.cnf<<'EOF'
[binlog1]
no_master=1
hostname=10.0.0.53
master_binlog_dir=/data/mysql/binlog
[server default]
master_ip_failover_script=/usr/local/bin/master_ip_failover
manager_log=/var/log/mha/app1/manager.log
manager_workdir=/var/log/mha/app1
master_binlog_dir=/data/binlog
user=mha
password=123456
ping_interval=2
repl_password=123456
repl_user=repl
ssh_user=root
[server1]
hostname=10.0.0.51
port=3306
[server2]
hostname=10.0.0.52
port=3306
[server3]
hostname=10.0.0.53
port=3306
EOF
复制

状态检查--->db03从库执行

#ssh检查
masterha_check_ssh  --conf=/etc/mha/app1.cnf 
Wed Feb 10 00:23:07 2019 - [info] All SSH connection tests passed successfully.
#主从状态检查
masterha_check_repl --conf=/etc/mha/app1.cnf
MySQL Replication Health is OK.
复制

开启MHA--->db03从库执行

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状态--->db03从库执行

masterha_check_status --conf=/etc/mha/app1.cnf
app1 (pid:4793) is running(0:PING_OK), master:10.0.0.51
复制

故障模拟及处理

观察 manager  日志--->db03从库执行

tail -f /var/log/mha/app1/manager.log
复制

停主库 db01--->db01主库执行

/etc/init.d/mysqld stop
复制

观察 manager  日志--->db03从库执行

tail -f /var/log/mha/app1/manager.log
Master failover to 10.0.0.52(10.0.0.52:3306) completed successfully. #最后提示切换到了 52
复制

登录db03--->db03从库执行

#查询主库信息
show slave status\G
*************************** 1. row ***************************
                  Master_Host: 10.0.0.52
                  Master_User: repl   
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
复制

登录db02--->db02执行（新主库）

#检查VIP是否漂移
ip a
    inet 10.0.0.52/24 brd 10.0.0.255 scope global noprefixroute ens33
       valid_lft forever preferred_lft forever
    inet 10.0.0.55/24 brd 10.0.0.255 scope global secondary ens33:1
复制

环境恢复

流程：

    1.修改配置文件--->db03执行

    2.修复旧主库--->db01执行

    3.在旧主库上指定新主库信息--->db01执行

    4.检查状态--->db03执行

    5.开启MHA--->db03执行

    6.查看进程--->db03执行

编辑配置文件

cat  >/etc/mha/app1.cnf<<'EOF'
[binlog1]
no_master=1
hostname=10.0.0.53
master_binlog_dir=/data/mysql/binlog
[server default]
master_ip_failover_script=/usr/local/bin/master_ip_failover
manager_log=/var/log/mha/app1/manager.log
manager_workdir=/var/log/mha/app1
master_binlog_dir=/data/binlog
user=mha
password=123456
ping_interval=2
repl_password=123456
repl_user=repl
ssh_user=root
[server1]
hostname=10.0.0.51
port=3306
[server2]
hostname=10.0.0.52
port=3306
[server3]
hostname=10.0.0.53
port=3306
EOF
复制

修复旧主库 db01

/etc/init.d/mysqld start
复制

指定新主库信息 并开启主从

#MHA上获取指定位置信息
grep 'CHANGE MASTER TO'  /var/log/mha/app1/manager.log

#旧主库修复好后指定位置新主库信息
CHANGE MASTER TO 
MASTER_HOST='10.0.0.52',
MASTER_PORT=3306, 
MASTER_AUTO_POSITION=1, 
MASTER_USER='repl', 
MASTER_PASSWORD='123456';
start slave;
show slave status\G
复制

检查状态

#ssh检查
masterha_check_ssh  --conf=/etc/mha/app1.cnf 
#主从状态检查
masterha_check_repl --conf=/etc/mha/app1.cnf
复制

拉取新主库binlog日志

#拉取主库binlog日志
chown -R mysql.mysql /data/mysql/*
cd /data/mysql/binlog/     #----->必须进入到自己创建好的目录-->必须从主库正在用的binlog文件开始拉取
mysqlbinlog  -R --host=10.0.0.51 --user=mha --password=123456 --raw  --stop-never mysql-bin.000001 &
复制

开启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 &
复制

查看进程

ps -ef|grep [m]ha
root       5159   4935  0 00:43 pts/1    00:00:00 perl /usr/bin/masterha_manager --conf=/etc/mha/app1.cnf --remove_dead_master_conf --ignore_last_failover
复制

Manager额外参数介绍

说明：
主库宕机谁来接管？
1. 所有从节点日志都是一致的，默认会以配置文件的顺序去选择一个新主。
2. 从节点日志不一致，自动选择最接近于主库日志量的从库
3. 如果对于某节点设定了权重（candidate_master=1），权重节点会优先选择。
但是此节点日志量落后主库100M日志的话，也不会被选择。可以配合check_repl_delay=0，关闭日志量的检查，强制选择候选节点。


(1)  ping_interval=1
#设置监控主库，发送ping包的时间间隔，尝试三次没有回应的时候自动进行failover

(2) candidate_master=1
#设置为候选master，如果设置该参数以后，发生主从切换以后将会将此从库提升为主库，即使这个主库不是集群中事件最新的slave

(3)check_repl_delay=0
#默认情况下如果一个slave落后master 100M的relay logs的话，
MHA将不会选择该slave作为一个新的master，因为对于这个slave的恢复需要花费很长时间，通过设置check_repl_delay=0,MHA触发切换在选择一个新的
master的时候将会忽略复制延时，这个参数对于设置了candidate_master=1 的主机非常有用，因为这个候选主在切换的过程中一定是新的master
复制

故障处理

主库宕机，binlogserver 自动停掉，manager 也会自动停止。

处理思路：

1、重新获取新主库的binlog到binlogserver中

2、重新配置文件binlog server信息

3、最后再启动MHA

3.管理员在高可用架构维护的职责

1. 搭建：MHA+VIP+SendReport+BinlogServer

2. 监控及故障处理

3.  高可用架构的优化

 核心是：尽可能降低主从的延时，让MHA花在数据补偿上的时间尽量减少。

5.7 版本，开启GTID模式，开启从库SQL并发复制。

欢迎关注、转发。