分类归档 运维

MySQL/MariaDB主从、半同步复制原理

一、主从复制原理

当 MySQL 的 Master 节点的数据有更改的时候,Master 会主动通知 Slave,这时 Slave 开启一个 I/O thread 主动来 Master 获取二进制日志,向 Master 请求二进制日志中记录的语句;Master 将二进制日志中记录的语句发给 Slave,Slave 则将这些语句存到中继日志中,进而从中继日志中读取一句,执行一句,直到所有的语句被执行完。而经 SQL 语句从中继日志中读取出来,再一一执行的进程叫做 SQL thread;将这些语句执行完之后,从节点的数据就和主节点的数据相同了,这就是所谓的 MySQL/MariaDB 主从复制。

  • Master 节点必须开启二进制日志功能
  • Slave 节点必须开启中继日志功能
  • Slave 节点需关闭二进制日志功能(默认不配置即可)
  • Master 和 Slave 节点需要配置不同的 server-id
  • Slave 节点需连接到Master节点

二、半同步复制原理

默认情况下,MySQL 5.5/5.6/5.7 和 MariaDB 10.0/10.1 的复制功能都是异步的,异步复制的情况下可以提供最佳的性能。但是如果 Slave 节点没有接收到 Master 节点发送过来的 binlog 日志时,会造成主从节点的数据不一致,甚至在恢复时造成数据丢失。

为了解决异步复制的数据丢失的问题,MySQL 5.5 引入一种半同步复制模式,该模式可以让 Slave 节点接收完 Master 节点发送的 binlog 日志文件并写入自己的中继日志之后,给 Master 节点一个反馈,告诉 Master 已经接收完毕,这时主库线程才返回给当前 session 告知操作完成。当出现超时情况 ( 可配置 ) 时,Master 节点会暂时切换到异步复制模式,直到至少有一个设置为半同步复制模式的 Slave 节点收到信息为止。

半同步复制模式必须在 Master、Slave 节点同时启用,否则 Master 节点默认使用异步复制模式。

MySQL 的半同步是通过加载 Google 为 MySQL 提供的半同步插件 semisync_master.so 和 semisync_slave.so 来实现的。其中前者是 Master 上需要安装的插件,后者是 Slave 上需要安装的插件。
MySQL 的插件位置默认存放在 $basedir/lib/plugin

Redis Cluster + tomcat + mysql

背景

在架设国民认证服务的时候,国民认证厂家程式码是基于 Redis Cluster + tomcat + mysql 部署,因考虑到开发环境,我们要求快速部署、快速开发,所以,我考虑用 docker-compose 进行部署。

根据 Redis官网 的提示,为了让 Docker 与 Redis Cluster 兼容,需要使用 Docker 的 host 网路模式

环境

Docker version 19.03.8

docker-compose version 1.28.5

程式码如下:

version: "3.8"

services:
  redis-6371: # 服务名称
    image: redis # 创建容器时所需的镜像
    container_name: redis-6371 # 容器名称
    restart: always # 容器总是重新启动
    network_mode: "host" # host 网络模式
    volumes: # 数据卷,目录挂载
      - /data/docker-redis/redis-cluster/6371/conf/redis.conf:/etc/redis/redis.conf
      - /data/docker-redis/redis-cluster/6371/data:/data
      - /etc/localtime:/etc/localtime:ro
    command: redis-server /etc/redis/redis.conf # 覆盖容器启动后默认执行的命令

  redis-6372:
    image: redis
    container_name: redis-6372
    network_mode: "host"
    volumes:
      - /data/docker-redis/redis-cluster/6372/conf/redis.conf:/etc/redis/redis.conf
      - /data/docker-redis/redis-cluster/6372/data:/data
      - /etc/localtime:/etc/localtime:ro
    command: redis-server /etc/redis/redis.conf

  redis-6373:
    image: redis
    container_name: redis-6373
    network_mode: "host"
    volumes:
      - /data/docker-redis/redis-cluster/6373/conf/redis.conf:/etc/redis/redis.conf
      - /data/docker-redis/redis-cluster/6373/data:/data
      - /etc/localtime:/etc/localtime:ro
    command: redis-server /etc/redis/redis.conf

  redis-6374:
    image: redis
    container_name: redis-6374
    network_mode: "host"
    volumes:
      - /data/docker-redis/redis-cluster/6374/conf/redis.conf:/etc/redis/redis.conf
      - /data/docker-redis/redis-cluster/6374/data:/data
      - /etc/localtime:/etc/localtime:ro
    command: redis-server /etc/redis/redis.conf

  redis-6375:
    image: redis
    container_name: redis-6375
    network_mode: "host"
    volumes:
      - /data/docker-redis/redis-cluster/6375/conf/redis.conf:/etc/redis/redis.conf
      - /data/docker-redis/redis-cluster/6375/data:/data
      - /etc/localtime:/etc/localtime:ro
    command: redis-server /etc/redis/redis.conf

  redis-6376:
    image: redis
    container_name: redis-6376
    network_mode: "host"
    volumes:
      - /data/docker-redis/redis-cluster/6376/conf/redis.conf:/etc/redis/redis.conf
      - /data/docker-redis/redis-cluster/6376/data:/data
      - /etc/localtime:/etc/localtime:ro
    command: redis-server /etc/redis/redis.conf


  mysql:
    restart: always
    image: mysql:5.7.21
    container_name: FIDO_mysql

    ports:
      - 3306:3306

    environment:
      TZ: Asia/Shanghai
      MYSQL_ROOT_PASSWORD: Admin123

    volumes:
      - /data/FIDO/mysql/mysql.conf.d:/etc/mysql/mysql.conf.d
      - /data/FIDO/mysql/lib/mysql:/var/lib/mysql
      - /etc/localtime:/etc/localtime:ro
      - /data/FIDO/mysql/logs:/logs

  tomcat:
    restart: always
    image: tomcat:8.5
    container_name: FIDO_tomcat

    ports:
      - 8081:8080

    volumes:
      - /data/FIDO/tomcat/webapps:/usr/local/tomcat/webapps
      - /data/FIDO/tomcat/conf/context.xml:/usr/local/tomcat/conf/context.xml
      - /data/FIDO/tomcat/logs:/usr/local/tomcat/logs
      - /etc/localtime:/etc/localtime:ro
    links:
      - mysql:FIDO_mysql

docker配置代理

背景

在一些实验室环境,服务器没有访问外网的权限,需要通过http代理。我们通常会将网络代理直接配置/etc/profile的配置文件中,这对于大部分操作都是可行的。然而,docker命令却使用不了这些代理。比如docker pull时需要从外网下载镜像,就会出现如下错误:

$ sudo docker pull hello-world

Unable to find image 'hello-world:latest' locally
Pulling repository docker.io/library/hello-world
docker: Network timed out while trying to connect to https://index.docker.io/v1/repositories/library/hello-world/images. You may want to check your internet connection or if you are behind a proxy..
See 'docker run --help'.

解决方案

# 创建 /etc/systemd/system/docker.service.d
$ sudo mkdir -p /etc/systemd/system/docker.service.d

# 
$ sudo vim /etc/systemd/system/docker.service.d/http_proxy.conf
[Service]
Environment="HTTP_PROXY=http://${proxy-addr}:${proxy-port}/" "HTTPS_PROXY=https://${proxy-addr}:${proxy-port}/" "NO_PROXY=localhost,127.0.0.1,docker-registry.somecorporation.com"

# 更新配置并重启 docker 服务
$ sudo systemctl daemon-reload && sudo systemctl restart docker.service

CentOS新装系统的后续工作

对于新装 CentOS 系统,我个人会通常做以下工作

1.换源

我一般换 中国科学技术大学镜像源 ,因为阿里源、华为源都放过我鸽子XDDD

2.基础环境

sudo yum install gcc gcc-c++ pcre openssh openssh-devel openssl openssl-devel libtools cmake autoconf tcl ntpdate -y

# 升级内核和所有软体
sudo yum update -y

3.同步时间服务器(server time.ustc.edu.cn)

[[email protected] ~]# cat /etc/ntp.conf | grep -v "^#" | grep -v "^$"
driftfile /var/lib/ntp/drift
restrict default nomodify notrap nopeer noquery
restrict 127.0.0.1
restrict ::1
server time.ustc.edu.cn
includefile /etc/ntp/crypto/pw
keys /etc/ntp/keys
disable monitor

[[email protected] ~]# systemctl start ntpdate.service

部署 MySQL/Mariadb 主从复制

MariaDB Foundation - MariaDB.org

本文主要介绍 MySQL/Mariadb 主从复制部署过程,主从复制原理请看这里

一、实验环境

  • 作业系统:CentOS 7.7
  • mariadb version:10.5.0
服务器角色IP地址主机名应用程式
Master172.50.1.107nacos-mha-s1mariadb
Slave1172.50.1.108nacos-mha-s2mariadb
Slave2172.50.1.109nacos-mha-s3mariadb

二、部署Maridb

三台服务器都要 部署mariadb

三、主从配置

可根据实际情况是否开启半同步配置

配置文件路径:/etc/my.cnf.d/server.cnf

  • master节点
[mariadb]
log-bin
server_id=1
log-basename=master
binlog-format=mixed
relay_log_purge=0  # 关闭中继日志

##### 以下配置为开启 master 半同步复制 #####

rpl_semi_sync_master_enabled=ON
rpl_semi_sync_master_timeout=5000

##### 以上配置为开启 master 半同步复制 #####
  • slave1 节点
[mariadb]
log-bin
server_id=2
log-basename=slave1
binlog-format=mixed
relay_log_purge=0  # 关闭中继日志

##### 以下配置为开启 slave 半同步复制 #####

rpl_semi_sync_slave_enabled=ON

##### 以上配置为开启 slave 半同步复制 #####
  • slave2 节点
[mariadb]
log-bin
server_id=3
log-basename=slave2
binlog-format=mixed
relay_log_purge=0  # 关闭中继日志

##### 以下配置为开启 slave 半同步复制 #####

rpl_semi_sync_slave_enabled=ON

##### 以上配置为开启 slave 半同步复制 #####
  • 三台服务器都要重新启动 mariadb 服务
sudo systemctl restart mariadb.service

四、获取 master 节点 binary log(二进制日志)postion

  • 在 master 节点上,通过mysql -u root -p进入 MySQL 终端,记录 File Position 字段的值
MariaDB [(none)]> SHOW MASTER STATUS;
+--------------------+----------+--------------+------------------+
| File               | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+--------------------+----------+--------------+------------------+
| master-bin.000001  |      330 |              |                  |
+--------------------+----------+--------------+------------------+
1 row in set (0.000 sec)
  • 继续在 master 节点上创建主从复制专有用户并授权
CREATE USER 'replication'@'%' IDENTIFIED BY 'Admin';
GRANT REPLICATION SLAVE ON *.* TO 'replication'@'%';
FLUSH PRIVILEGES;

五、启动 Slave

  • 分别在 Slave 节点上,执行以下语句(为 Slave 指定 Master)
MariaDB [(none)]> CHANGE MASTER TO
MASTER_HOST='172.50.1.107',
MASTER_USER='replication',
MASTER_PASSWORD='Admin123',
MASTER_PORT=3306,
MASTER_LOG_FILE='master-bin.000001',
MASTER_LOG_POS=330,
MASTER_CONNECT_RETRY=10;
  • 在 Slave 节点上 启动 Slave
MariaDB [(none)]> START SLAVE;
  • 在 Slave 节点上 查看 状态 (出现以下 两个 YES 表示成功)
MariaDB [(none)]> SHOW SLAVE STATUS \G
*************************** 1. row ***************************
         
              .........省略.........

              Slave_IO_Running: Yes
             Slave_SQL_Running: Yes
          
              .........省略.........

1 row in set (0.001 sec)

使用MHA实现MySQL/MariaDB高可用

本篇介绍MHA部署过程,MHA工作原理请看这里

一、实验环境

作业系统:CentOS 7.7

服务器角色IP地址主机名应用程式
MHA Manager172.50.1.119basic-platformManager, node, mariadb
MHA Node | MySQL Master172.50.1.107nacos-mha-s1node, mariadb
MHA Node | MySQL Slave1172.50.1.108nacos-mha-s2node, mariadb
MHA Node | MySQL Slave2172.50.1.109nacos-mha-s3node, mariadb

二、部署MHA

MHA安装包托管在 Google Code 上,也可在 GitHub 上下载 mha-managermha-node 安装包

1.依赖环境

如果你准备的是一台全新的服务器,建议对服务器进行常用基础环境包的安装

# MHA Manager 和 MHA node 服务器都需要执行
sudo yum install perl-DBD-MySQL perl-Parallel-ForkManager perl-Log-Dispatch  perl-Config-Tiny -y

2.安装 Manager 节点

# manager 节点上也需要安装 mha-node 组件
sudo yum install mha4mysql-node-0.58-0.el7.centos.noarch.rpm
sudo yum install mha4mysql-manager-0.58-0.el7.centos.noarch.rpm

3.安装 Node 节点

# mysql / mariadb 服务器上都需要安装 mha-node 组件,本实验环境总共 3 台服务器
sudo yum install mha4mysql-node-0.58-0.el7.centos.noarch.rpm

4.配置MHA Manager

  • 首先登陆 Mariadb Master, 创建 mha 用户并授权
CREATE USER 'mha'@'%' IDENTIFIED BY 'Admin123';
GRANT ALL ON *.* TO 'mha'@'%';
FLUSH PRIVILEGES;
  • 然后登陆 MHA Manager 服务器,创建 MHA Manager 配置文件 /etc/mha/mha.cnf ,写入以下内容
[server default]
user=mha
password=Admin123
repl_user=replication  # 特别指出:这是 Mariadb 主从复制账号
repl_password=Admin123 # 特别指出:这是 Mariadb 主从复制密码
ssh_user=root
manager_workdir=/opt/mha
manager_log=/var/log/mha/mha.log
remote_workdir=/opt/mha
ping_interval=3

[server1]
# Master
hostname=172.50.1.107

[server2]
# Slave One
hostname=172.50.1.108

[server3]
# Slave Two
hostname=172.50.1.109
  • 生成密钥对,使得 MHA Manager 通过 SSH 密钥登陆 Mariadb 服务器,三台 Mariadb 服务器也可互相登陆(四台机器都需要执行)
# 说明:
# 1.四台服务器(一台MHA Manager, 三台Mariadb都需要执行)
# 2.建议用 root 用户在 /root 目录下执行以下命令
# 3.为了图方便,每台服务器自身都做了ssh登陆,其实这是不严谨的,看到此文的童鞋勿喷
sudo ssh-keygen -t rsa
sudo ssh-copy-id -i ~/.ssh/id_rsa.pub [email protected] 
sudo ssh-copy-id -i ~/.ssh/id_rsa.pub [email protected]
sudo ssh-copy-id -i ~/.ssh/id_rsa.pub [email protected]
sudo ssh-copy-id -i ~/.ssh/id_rsa.pub [email protected]

5.校验配置

# 在 MHA Manager 服务器上,执行以下命令,进行校验配置

# 校验SSH,需要出现All SSH connection tests passed successfully代表成功。
sudo masterha_check_ssh --conf=/etc/mha/mha.cnf

# 校验主从复制,需要出现MySQL Replication Health is OK代表成功。
sudo masterha_check_repl --conf=/etc/mha/mha.cnf

6.启动 MHA 监测集群

# 校验配置成功之后,在 MHA Manager 服务器上启动 MHA 监测集群
sudo nohup masterha_manager --conf=/etc/mha/mha.cnf < /dev/null > /var/log/mha/mha.log 2>&1 &

7.查看 MHA 监测集群服务状态

sudo masterha_check_status --conf=/etc/mha/mha.cnf

8.停止 MHA 监测集群服务

sudo masterha_check_stop --conf=/etc/mha/mha.cnf

MHA 检测集群日常运维操作,请看这里

MHA架构介绍与工作原理

一、简介

MHA (Master High Availability) 目前是 MySQL 高可用相对成熟的解决方案之一,它由日本DeNA公司 youshimaton(现就职于Facebook公司)开发,是一套优秀的故障切换、主从提升的高可用软件。在 MySQL 故障切换过程中,MHA能做到0~30秒之内自动完成数据库的故障切换操作,并且在进行故障切换的过程中,MHA能最大程度上保证数据库的一致性,以达到真正意义上的高可用。基于标准的MySQL复制(异步/半同步)。


MHA有两部分组成: MHA Manager (管理节点) 和 MHA Node (数据节点)。


MHA Manager 可以单独部署在一台独立机器上管理多个 master-slave 集群,也可以部署在一台 MySQL Slave 上,MHA Manager 探测集群的node节点,当发现 master 出现故障的时候,它可以自动将具有最新数据的slave提升为新的master,然后将所有其它的slave导向新的master上,整个故障转移过程对应用程序是透明的。

二、工作原理

官方文档

架构原理图:

  • 从宕机崩溃的Master上保存二进制日志事件(binlog event);
  • 识别含有最新更新的Slave;
  • 应用差异的中继日志(relay log)到其他Slave;
  • 应用从Master保存的二进制日志事件;
  • 提升一个Slave为新的Master;
  • 使其他的Slave连接新的Master进行复制;

三、MHA软件包

MHA软件由两部分组成,Manager工具包和Node工具包,具体如下。

  1. Manager工具包:
  • masterha_check_ssh:检查MHA的SSH配置情况。
  • masterha_check_repl:检查MySQL复制状况。
  • masterha_manager:启动MHA。
  • masterha_check_status:检测当前MHA运行状态。
  • masterha_master_monitor:检测Master是否宕机。
  • masterha_master_switch:控制故障转移(自动或手动)。
  • masterha_conf_host:添加或删除配置的server信息。
  1. Node工具包(通常由MHA Manager的脚本触发,无需人工操作):
  • save_binary_logs:保存和复制Master的binlog日志。
  • apply_diff_relay_logs:识别差异的中级日志时间并将其应用到其他Slave。
  • filter_mysqlbinlog:去除不必要的ROOLBACK事件(已经废弃)
  • purge_relay_logs:清除中继日志(不阻塞SQL线程)

四、模拟主库failover(故障转移)场景

CentOS安装MySQL/MariaDB

MariaDB Foundation - MariaDB.org

本文总共有两种方式安装 MySQL/MariaDB,分别是 YUM 和 docker-compose 两种方式

一、实验环境

作业系统:CentOS Linux release 7.7.1908 (Core)

mariadb官方配置yum源方法

二、yum 方式安装 mariadb

1. 设置mariadb的yum源

cat <<EOF > /etc/yum.repos.d/mariadb.repo

# MariaDB 10.5 CentOS repository list - created 2021-03-19 07:41 UTC
# http://downloads.mariadb.org/mariadb/repositories/
[mariadb]
name = MariaDB
baseurl = http://yum.mariadb.org/10.5/centos7-amd64
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1

EOF
# 重新构建缓存
sudo yum clean all && sudo yum makecache

2. 安装 mariadb

sudo yum install MariaDB-server MariaDB-client -y

3. 启动 mariadb

# 启动mariadb服务
sudo systemctl start mariadb.service

# 查看mariadb状态
sudo systemctl status mariadb.service

# 设置开机自启
sudo systemctl enable mariadb.service

4. 初始化 mariadb

初始化mariadb的目的:设置mariadb密码、远程ip使用root访问mariadb的权限、删除匿名用户、删除test库等

# 初始化mariadb (mariadb一定要成功启动才可以执行这一步,否则会报错)
[[email protected] ~]# mysql_secure_installation

NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MariaDB
      SERVERS IN PRODUCTION USE!  PLEASE READ EACH STEP CAREFULLY!

In order to log into MariaDB to secure it, we'll need the current
password for the root user. If you've just installed MariaDB, and
haven't set the root password yet, you should just press enter here.

Enter current password for root (enter for none):
OK, successfully used password, moving on...

Setting the root password or using the unix_socket ensures that nobody
can log into the MariaDB root user without the proper authorisation.

You already have your root account protected, so you can safely answer 'n'.

Switch to unix_socket authentication [Y/n] Y
Enabled successfully!
Reloading privilege tables..
 ... Success!


You already have your root account protected, so you can safely answer 'n'.

Change the root password? [Y/n] Y
New password:
Re-enter new password:
Password updated successfully!
Reloading privilege tables..
 ... Success!


By default, a MariaDB installation has an anonymous user, allowing anyone
to log into MariaDB without having to have a user account created for
them.  This is intended only for testing, and to make the installation
go a bit smoother.  You should remove them before moving into a
production environment.

Remove anonymous users? [Y/n] Y
 ... Success!

Normally, root should only be allowed to connect from 'localhost'.  This
ensures that someone cannot guess at the root password from the network.

Disallow root login remotely? [Y/n] n
 ... skipping.

By default, MariaDB comes with a database named 'test' that anyone can
access.  This is also intended only for testing, and should be removed
before moving into a production environment.

Remove test database and access to it? [Y/n] Y
 - Dropping test database...
 ... Success!
 - Removing privileges on test database...
 ... Success!

Reloading the privilege tables will ensure that all changes made so far
will take effect immediately.

Reload privilege tables now? [Y/n] Y
 ... Success!

Cleaning up...

All done!  If you've completed all of the above steps, your MariaDB
installation should now be secure.

Thanks for using MariaDB!

三、docker-compose 方式安装 MySQL

docker-compose.yaml

mysql:
  restart: always
  image: mysql:5.7.21
  container_name: mysql
 
  ports:
    - 3306:3306
 
  environment:
    TZ: Asia/Shanghai
    MYSQL_ROOT_PASSWORD: Admin123
 
  volumes:
    - /data/mysql/mysql.conf.d:/etc/mysql/mysql.conf.d
    - /data/mysql/lib/mysql:/var/lib/mysql
    - /etc/localtime:/etc/localtime:ro
    - /data/mysql/log:/var/log/mysql

/data/mysql/mysql.conf.d/my.cnf

[client]
default-character-set = utf8
port = 3306
 
[mysql]
port = 3306
default-character-set = utf8
 
[mysqld]
port = 3306
character-set-server = utf8
 
# 打开二进制日志
log-bin = mysql-bin
binlog_cache_size = 1M
expire_logs_days = 10
max_binlog_size = 128M
 
# 实例标识,不能重复
server_id = 1
binlog_format=MIXED
read-only=0
auto-increment-increment=10
 
# 自增控制,配置不能重复
auto-increment-offset=1
skip-external-locking
slow-query-log = on
long_query_time = 1
lower_case_table_names = 1
max_connections=1100
max_user_connections=100
max_connect_errors=1000
innodb_buffer_pool_size = 100M
innodb_buffer_pool_instances = 8
innodb_log_file_size = 200M
innodb_log_buffer_size = 16M
innodb_log_files_in_group = 3
innodb_flush_log_at_trx_commit = 0
innodb_lock_wait_timeout = 10
innodb_sync_spin_loops = 40
innodb_max_dirty_pages_pct = 90
innodb_support_xa = 0
innodb_thread_concurrency = 0
innodb_thread_sleep_delay = 500
innodb_concurrency_tickets = 1000
log_bin_trust_function_creators = 1
innodb_flush_method = O_DIRECT
innodb_file_per_table
innodb_read_io_threads = 16
innodb_write_io_threads = 16
innodb_io_capacity = 2000
innodb_file_format = Barracuda
innodb_purge_threads=1
innodb_purge_batch_size = 32
innodb_old_blocks_pct=75
innodb_change_buffering=all
innodb_stats_on_metadata=OFF
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION