mysql高可用

测试环境

[root@localhost home]# cat /etc/redhat-release
CentOS Linux release 7.8.2003 (Core)

ntp时间同步

ntp服务安装：yum install ntp
配置文件：/etc/ntp.conf
同步时间：ntpdate <server ip>

mysql主从或主主复制

优点:

• 架构比较简单，使用原生半同步复制作为数据同步的依据；
• 双节点，没有主机宕机后的选主问题，直接切换即可；
• 双节点，需求资源少，部署简单；

缺点：

• 完全依赖于半同步复制，如果半同步复制退化为异步复制，数据一致性无法得到保证；
• 需要额外考虑keepalived的高可用机制。

使用场景

• 数据一致性不能保证，只有双节点，占用资源少，主节点宕机不存在选主的问题。

mysql主从复制

由master到slave单向同步

mysql master配置

master防火墙设置:

#开放ntp服务端口
firewall-cmd --add-port=123/udp
#开放mysql端口
firewall-cmd --add-port=3306/tcp

修改master mysql配置文件（修改后需重启mysql）：

#在/etc/my.cnf中添加：
[mysqld]
log-bin				=	master-bin
server-id			=	1
binlog-do-db		=	db1	#需要复制的数据库，如果复制多个数据库，重复设置这个选项即可
binlog-ignore-db	=	db2	#不需要复制的数据库，如果复制多个数据库，重复设置这个选项即可

进入mysql给从服务器授权（需根据从服务器ip段，示例中为：192.168.10）：

mysql> grant replication slave on *.* to root@'192.168.10.%' identified by '123456';

查看master的bin-log日志文件名称和position，后续从服务器需配置：

mysql> show master status;
+-------------------+----------+--------------+------------------+-------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------+----------+--------------+------------------+-------------------+
| master-bin.000003 |     1222 |              |                  |                   |
+-------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

导出master数据库：

#全部数据库
mysqldump -uroot -p --all-databases >`pwd`/master.sql	#导出
mysql -uroot -p <`pwd`/master.sql	#导入

#指定数据库
mysqldump -uroot -p test >`pwd`/master.sql	#导出
mysql -uroot -p test <`pwd`/master.sql #导入，test库如果没有需先创建

#备份指定数据库指定表(多个表以空格间隔)
mysqldump -uroot -p test table1 table2 >`pwd`/master.sql	#导出
mysql -uroot -p test <`pwd`/master.sql #test库如果没有需先创建	#导入

#备份指定所有数据库排除某些表
mysqldump -uroot -p --all-databases --ignore-table=test.table1 --ignore-table=test.table2 >`pwd`/master.sql	#导出
mysql -uroot -p <`pwd`/master.sql	#导入

mysql slave配置

ntp同步master时间：

ntpdate <master ip>

修改slave mysql配置文件（修改后需重启mysql）：

#在/etc/my.cnf中添加：
[mysqld]
log-bin				=	mysql-bin
server-id			=	2	#id不能和master一样
replicate-do-db		=	db1	#需要复制的数据库名，如果复制多个数据库，重复设置这个选项即可
replicate-ignore-db	=	db2	#不需要复制的数据库名，如果复制多个数据库，重复设置这个选项即可

将master导出的数据导入slave中：

mysql -uroot -p <`pwd`/master.sql

进入mysql开启slave

mysql> change master to master_host='<master ip>',master_user='root',master_password='123456',master_log_file='<master log file>',master_log_pos=<master log pos>;
mysql> start slave;
mysql> show slave status\G;	#查看slave状态

注意slave状态中这两个状态为Yes才正常

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

验证

在master中的设置了同步的数据库中创建表，slave中也会有

mysql主主复制+keepalived

后续的集群都可用keepalived提供一个统一的接口访问。

mysql主主复制

主从双向同步：在主从的基础上将master设置为slave的从。

keepalived下载安装

下载地址：https://www.keepalived.org/software/keepalived-2.2.2.tar.gz

最新版本下载：https://www.keepalived.org/index.html

编译

tar -xzvf keepalived-2.2.2.tar.gz
cd keepalived-2.2.2
./configure && make	#生成的执行程序：./keepalived/keepalived
#配置文件：./keepalived/etc/keepalived/keepalived.conf, 开机自启脚本：./keepalived/etc/init.d/keepalived
#可以新建一个文件夹将执行程序和配置文件放一起

配置文件修改

更多参数参考：https://www.keepalived.org/manpage.html

! Configuration File for keepalived

#全局配置
global_defs {
   #keepalived在发生切换时需要发送email到的对象，一行一个
   notification_email {
        sysadmin@fire.loc
   }
   #发件人
   notification_email_from Alexandre.Cassen@firewall.loc
   #smtp服务器地址
   smtp_server 127.0.0.1
   #smtp连接超时时间
   smtp_connect_timeout 30
   #机器标识符，不一定是hostname，在一个集群中唯一
   router_id LVS_164
   #vrrp的一些默认配置
   vrrp_skip_check_adv_addr
   vrrp_strict #如果发现其他机器访问不了虚拟ip可以注释这行
   vrrp_garp_interval 0
   vrrp_gna_interval 0
}

#监控多个网段的实例，实际可以不用配置
vrrp_sync_group VG_1{
    group {
        VI_1 #实例名
    }
    #切换到master执行的脚本
    notify_master /path/xx.sh
    #切换到backup执行的脚本
    netify_backup /path/xx.sh
    #故障时执行的脚本
    notify_fault "path/xx.sh VG_1"
    notify /path/xx.sh 
    #使用global_defs中提供的邮件地址和smtp服务器发送邮件通知
    smtp_alert
}

#实例group中的实例
vrrp_instance VI_1 {
    #初始状态 MASTER|BACKUP
    state BACKUP
    #实例绑定的网卡
    interface ens33
    #VPID标记
    virtual_router_id 51
    #优先级，高优先级竞选为master
    priority 100
    #检查间隔，默认1秒
    advert_int 1
    #设置认证
    authentication {
        #认证方式
        auth_type PASS
        #认证密码
        auth_pass 1111
    }
    #设置vip
    virtual_ipaddress {
        192.168.10.37
    }
}

#虚拟服务器 需用一个未使用的ip
virtual_server 192.168.10.37 3306 {
    #检查时间间隔，默认60s
    delay_loop 6
    #lvs调度算法rr|wrr|lc|wlc|lblc|sh|dh
    lb_algo rr
    #负载均衡转发规则NAT|DR|RUN
    lb_kind NAT
    #会话保持时间
    persistence_timeout 50
    #使用的协议
    protocol TCP

    #真实IP地址
    real_server 192.168.10.164 3306 {
        #默认为1,0为失效,越高，lvs就越优先访问
        weight 1
        #在检测到server up后执行脚本
        #notify_up <STRING>|<QUOTED-STRING> [username [groupname]]
        #在检测到server down后执行脚本
        #notify_down <STRING>|<QUOTED-STRING> [username [groupname]]
        #健康检查类型HTTP_GET|SSL_GET|TCP_CHECK|SMTP_CHECK|DNS_CHECK|MISC_CHECK|BFD_CHECK|UDP_CHECK|PING_CHECK|FILE_CHECK
        TCP_CHECK {
            #连接超时时间
            connect_timeout 3
            #重连次数
            nb_get_retry 3
            #重连间隔时间
            delay_before_retry 3
            #健康检查的端口的端口
            connect_port 3306
        }
        #不常用
        HTTP_GET|SSL_GET {
            #检查url，可以指定多个
            url { 
                path /
                digest ff20ad2481f97b1754ef3e12ecd3a9cc
            }
            url {
                path /mrtg/
                digest 9b3a0c85a887a256d6939da88aabd8cd
            }
            connect_timeout 3
            retry 3
            delay_before_retry 3
        }
    }
}

keepalived master配置示例：

global_defs {
    notification_email {
        sysadmin@fire.loc
    }
    notification_email_from Alexandre.Cassen@firewall.loc
    smtp_server 127.0.0.1
    smtp_connect_timeout 30
    router_id LVS_10_164
    vrrp_skip_check_adv_addr
    vrrp_strict
}

vrrp_instance VI_1 {
    state MASTER
    interface ens33
    virtual_router_id 51
    priority 100
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass 1111
    }
    virtual_ipaddress {
        192.168.10.37
    }
}

virtual_server 192.168.10.37 3306 {
    delay_loop 6
    lb_algo rr
    lb_kind NAT
    persistence_timeout 50
    protocol TCP
    real_server 192.168.10.164 3306 {
        weight 1
        notify_down /root/keepalived/shutdown.sh #kill keepalived进程：pidof keepalived | xargs kill
        TCP_CHECK {
            connect_timeout 3
            delay_before_retry 3
            connect_port 3306
        }
    }
}

keepalived slave配置与master区别：

10c10
<    router_id LVS_10_164
---
>    router_id LVS_10_140
16c16
<     state MASTER
---
>     state BACKUP
19c19
<     priority 100
---
>     priority 50
37c37
<     real_server 192.168.10.164 3306 {
---
>     real_server 192.168.10.140 3306 {

防火墙配置

vrrp详解：https://blog.csdn.net/yiluyangguang1234/article/details/49981555

防火墙放过vrrp协议，MASTER和BACKUP都要配置

#VRRP组播使用的目的地址是由IANA标准指定的多点广播地址224.0.0.18。协议默认为组播每秒发送一次，该时间间隔是可以配置的。
firewall-cmd --direct --permanent --add-rule ipv4 filter INPUT 0 --destination 224.0.0.18 --protocol vrrp -j ACCEPT
firewall-cmd --direct --permanent --add-rule ipv4 filter OUTPUT 0 --destination 224.0.0.18 --protocol vrrp -j ACCEPT
firewall-cmd --reload

基于Galera协议

基于Galera的高可用方案主要有MariaDB Galera Cluster和Percona XtraDB Cluster（简称PXC），目前PXC用的会比较多一些。

优点：

• 多主结构：数据在任何时候读写都是最新的（比如：数据的更新操作，当一个节点收到请求后都需要与其他节点进行校验，校验之后在更新本地数据，最后在同步）
• 故障切换：在数据库出现故障时，因为支持多点写入，切换容易
• 热插拔：在服务期间，如果数据库挂了，只需要停止该节点的数据库服务，不影整个集群（其他节点）。而且如果该节点修复后，只需启动数据库服务即可自动加入集群环境（原集群配置环境还存在）
• 同步复制：在集群种的不同节点之间数据同步，没有延迟，而且单个节点数据库挂了之后，数据不会丢失
• 并发复制：支持并行执行，提升性能
• 自动节点克隆：在新增节点时，增量数据（即在基础数据上发送变化的数据）或基础数据无需手动备份提供，因为新增节点上mysql服务一旦开启，它将自动拉取在线节点数据。

缺点：

• 新节点加入需要全量拷贝数据，有时会导致数据同步的提供者无法提供读写，只有等待整个拷贝完成。
• 只支持innodb存储引擎
• 而且集群的性能取决于集群中性能最差的节点的性能（全局校验过程）

使用场景

• 对数据一致性和完整性要求特别高的场景
• 多点写入
• 不关注高性能

MariaDB Galera Cluster

安装MariaDB Galera Cluster

本机直接安装

如果通过rpm包装过mysql需先卸载：

rpm -qa | grep mysql | xargs rpm -e --nodeps

配置MariaDB-Galera-server下载源（也可以在官网下载rpm，但需自己解决依赖问题）：

cat <<EOF>/etc/yum.repos.d/galera.repo
[mysql]
baseurl=http://yum.mariadb.org/10.0/centos7-amd64
gpgcheck=0
enabled=1
EOF

安装：

yum install MariaDB-Galera-server -y

配置修改

修改配置文件（/etc/my.cnf.d/server.cnf），所有节点都需修改：

[galera]
wsrep_provider=/usr/lib64/galera/libgalera_smm.so	#库文件路径
wsrep_cluster_address="gcomm://192.168.10.164,192.168.10.140"	#gcomm协议，集群主机
binlog_format=row

启动

先在一个节点启动：

/usr/bin/mysqld_safe --datadir=/var/lib/mysql --pid-file=/var/lib/mysql/localhost.localdomain.pid --wsrep-new-cluster

后续启动其他节点

/usr/bin/mysqld_safe --datadir=/var/lib/mysql --pid-file=/var/lib/mysql/localhost.localdomain.pid

在其中一台中执行安全脚本（同步数据），按提示操作

mysql_secure_installation

docker安装

下载mariadb:10.6.4镜像：

docker pull mariadb:10.6.4

获取默认mariadb配置文件（放置到/data/mysql_conf中）：

docker run -itd --privileged -e MYSQL_ROOT_PASSWORD=123456 --name test mariadb:10.6.4
mkdir -p /data/mysql_conf
docker cp test:/etc/mysql /data/mysql_conf
docker rm -f test
rm /data/mysql_conf/mysql/my.cnf -f
cp /data/mysql_conf/mysql/mariadb.cnf /data/mysql_conf/mysql/my.cnf

添加集群配置文件：

#vim galera_cluster.cnf输入如下内容（涉及到ip部分需修改）：
[mysqld]
binlog_format=ROW
default_storage_engine=InnoDB
innodb_autoinc_lock_mode=2
innodb_flush_log_at_trx_commit=0
server-id=241 #集群内不同的mariadb服务器该值不能相同

[galera]
wsrep_on=ON
wsrep_provider=/usr/lib/libgalera_smm.so

wsrep_node_name=galera-node-215 #当前节点名字
wsrep_node_address=192.168.11.215 #本机地址
wsrep_cluster_name=galera-cluster #集群名称
wsrep_cluster_address=gcomm://192.168.11.216,192.168.11.215,192.168.11.241 #galera集群url，格式为'gcomm://'，一般填写集群内所有节点的ip:port（port默认4567），替换为集群内所有需要安装mariadb的ip
wsrep_sst_method=rsync #集群数据同步的方式一般为rsync

将配置移动到默认配置中：

mv galera_cluster.cnf /data/mysql_conf/mysql/conf.d/

启动服务

#新建数据存放目录：
mkdir /data/mysql -p
#输入如下命令（可根据实际需要修改mariadb监听端口及登录密码，主节点需在命令最后边加上参数--wsrep-new-cluster）：
#主节点：
docker run -itd --privileged -e MYSQL_ROOT_PASSWORD=123456 -e MYSQL_TCP_PORT=3308 --network=host -v /data/mysql:/var/lib/mysql -v /data/mysql_conf/mysql:/etc/mysql --name mariadb mariadb:10.6.4 --wsrep-new-cluster
#从节点：
docker run -itd --privileged -e MYSQL_ROOT_PASSWORD=123456 -e MYSQL_TCP_PORT=3308 --network=host -v /data/mysql:/var/lib/mysql -v /data/mysql_conf/mysql:/etc/mysql --name mariadb mariadb:10.6.4

日志查看：
docker logs -f mariadb

故障恢复：

• 所有节点断电后恢复：
• 查看所有节点的文件/data/mysql/grastate.dat，内容如下：
  
• 找到seqno值最大的节点（注意数据库开启状态或者异常关闭时seqno值为-1，值最大的节点表示其为最后关闭的节点）
• 将其设为主节点，并将该该节点/data/mysql/grastate.dat文件中的safe_to_bootstrap值由0改为1
• 在所有节点中（不涉及到主从切换的可以不用删除）输入 docker rm mariadb -f 命令删除所有节点的mariadb容器
• 最后重新创建容器

存在问题：

主节点宕机后，无法重新加入到集群中（解决办法可参考故障恢复）

Percona Xtradb Cluster

安装

rpm下载安装：

rpm -ivh http://mirrors.ustc.edu.cn/percona/tools/yum/release/7/RPMS/x86_64/percona-xtrabackup-24-2.4.22-1.el7.x86_64.rpm
rpm -ivh http://mirrors.ustc.edu.cn/percona/pxc-57/yum/release/7/RPMS/x86_64/Percona-XtraDB-Cluster-client-57-5.7.33-31.49.1.el7.x86_64.rpm
rpm -ivh http://mirrors.ustc.edu.cn/percona/pxc-57/yum/release/7/RPMS/x86_64/Percona-XtraDB-Cluster-server-57-5.7.33-31.49.1.el7.x86_64.rpm
rpm -ivh http://mirrors.ustc.edu.cn/percona/pxc-57/yum/release/7/RPMS/x86_64/Percona-XtraDB-Cluster-shared-57-5.7.33-31.49.1.el7.x86_64.rpm

配置文件修改：

#vim /etc/percona-xtradb-cluster.conf.d/mysqld.cnf
[mysqld]
server-id=1

#vim /etc/percona-xtradb-cluster.conf.d/wsrep.cnf
wsrep_cluster_address=gcomm://192.168.10.140,192.168.10.164 
wsrep_node_address=192.168.1.145 #该节点ip
wsrep_node_name=pxc-cluster-node-1 #各节点区分
wsrep_sst_auth="sstuser:sstuser" #取消注释并修改  数据同步时使用的账号和密码

启动

先启动一个节点（启动完后在操作其他节点）：

systemctl start mysql@bootstrap.service
#查看启动日志/var/log/mysqld.log，失败后可查看出错原因，成功后可查看密码
#[Note] [MY-010454] [Server] A temporary password is generated for root@localhost: ir/oB=CdX5W(
mysql -uroot -p'ir/oB=CdX5W('

#sql中操作：
set password=password('123456'); #设置密码
create user 'sstuser'@'localhost' identified by '123456';#创建数据同步时使用的账号和密码
grant all privileges on *.* to 'sstuser'@'%' identified by '123456';#增加外部访问
flush privileges;

启动节点后再启动剩余节点：

[root@localhost ~]# systemctl start mysql