标签归档:mysql

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

部署 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 'Admin123';
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)

六、docker-compose版部署

docker-compose.yaml配置文件

version: '3'
services:
  mysql_master:
    image: mysql:5.7.21
    container_name: MessageCenter_MySQL_Master
    ports:
      - 3307:3306
    environment:
      TZ: Asia/Shanghai
      MYSQL_ROOT_PASSWORD: Admin123
    volumes:
      - /data/MessageCenter_MySQL/master/mysql/mysql.conf.d:/etc/mysql/mysql.conf.d
      - /data/MessageCenter_MySQL/master/mysql/lib/mysql:/var/lib/mysql
      - /etc/localtime:/etc/localtime:ro
      - /data/MessageCenter_MySQL/master/mysql/log:/var/log/mysql
    restart: always
	
  mysql_slave1:
    image: mysql:5.7.21
    container_name: MessageCenter_MySQL_Slave1
    ports:
      - 3308:3306
    environment:
      TZ: Asia/Shanghai
      MYSQL_ROOT_PASSWORD: Admin123
    volumes:
      - /data/MessageCenter_MySQL/slave1/mysql/mysql.conf.d:/etc/mysql/mysql.conf.d
      - /data/MessageCenter_MySQL/slave1/mysql/lib/mysql:/var/lib/mysql
      - /etc/localtime:/etc/localtime:ro
      - /data/MessageCenter_MySQL/slave1/mysql/log:/var/log/mysql
    restart: always
	
  mysql_slave2:
    image: mysql:5.7.21
    container_name: MessageCenter_MySQL_Slave1
    ports:
      - 3309:3306
    environment:
      TZ: Asia/Shanghai
      MYSQL_ROOT_PASSWORD: Admin123
    volumes:
      - /data/MessageCenter_MySQL/slave2/mysql/mysql.conf.d:/etc/mysql/mysql.conf.d
      - /data/MessageCenter_MySQL/slave2/mysql/lib/mysql:/var/lib/mysql
      - /etc/localtime:/etc/localtime:ro
      - /data/MessageCenter_MySQL/slave2/mysql/log:/var/log/mysql
    restart: always

my.cnf配置文件样本(注意修改server_id和read-only的值)

[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
replicate-ignore-db=mysql
replicate-ignore-db=sys
replicate-ignore-db=information_schema
replicate-ignore-db=performance_schema
skip-external-locking
lower_case_table_names = 1
max_connections=1000
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

使用MHA实现MySQL/MariaDB高可用

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

一、作业环境

  • 作业系统:CentOS 7.7
服务器角色 IP地址 主机名 应用程式分布
MHA Manager 172.50.1.119 basic-platform Manager, node, mariadb
MHA Node & MySQL Master 172.50.1.107 nacos-mha-s1 node, mariadb
MHA Node & MySQL Slave1 172.50.1.108 nacos-mha-s2 node, mariadb
MHA Node & MySQL Slave2 172.50.1.109 nacos-mha-s3 node, mariadb

二、让我们开始吧

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

1.依赖环境

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

  • MHA 是用 Perl 编写的,需要安装 Perl 依赖 ( Tips: 如果你的服务器在中国,那么建议将 YUM 源设置成 中国科学技术大学源,如何设置请点击 这里)
# 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 root@172.50.1.107 
sudo ssh-copy-id -i ~/.ssh/id_rsa.pub root@172.50.1.108
sudo ssh-copy-id -i ~/.ssh/id_rsa.pub root@172.50.1.109
sudo ssh-copy-id -i ~/.ssh/id_rsa.pub root@172.50.1.119

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 (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一定要成功启动才可以执行这一步,否则会报错)
[root@localhost ~]# 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