数据库 (六) – 复制与读写分离

一、MySQL复制

  • 扩展方式: Scale Up ,Scale Out
  • 复制的功用
    • 数据分布
    • 负载均衡读
    • 备份
    • 高可用和故障切换
    • MySQL升级测试

一主一从

一主多从

1.1 mysql 分片

  • MySQ L垂直分区

  • MySQL 水平分片(Sharding)

  • 对应 shard 中查询相关数据

1.1 主从复制

  • 主从复制线程:
    • 主节点:
      dump Thread:为每个Slave的I/O Thread启动一个dump线程,用于向其
      发送binary log events
    • 从节点
      I/O Thread:向Master请求二进制日志事件,并保存于中继日志中
      SQL Thread:从中继日志中读取日志事件,在本地完成重放
  • 跟复制功能相关的文件
    • master.info:用于保存slave连接至master时的相关信息,例如账号、密码、服务器地址等
    • relay-log.info:保存在当前slave节点上已经复制的当前二进制日志和本地replay log日志的对应关系
  • 主从复制特点
    • 异步复制
    • 主从数据不一致比较常见
  • 复制架构
    • Master/Slave, Master/Master, 环状复制
    • 一主多从
    • 从服务器还可以再有从服务器
    • 一从多主:适用于多个不同数据库
  • 复制需要考虑二进制日志事件记录格式
    • STATEMENT(5.0之前)
    • ROW(5.1之后,推荐)
    • MIXED
  • 参考
    https://dev.mysql.com/doc/refman/8.0/en/replication-configuration.html
    https://mariadb.com/kb/en/setting-up-replication/

MySQL复制模型

1.1.1 主从复制原理

  • (1) 在 slave 服务器上 执行start slave 命令,开启复制开关,开始进行主从复制
  • (2) 此时, slave 服务器的 I/O 线程会通过 master 上已经授权的复制用户权限 请求连接 master 服务器, 并请求从指定 binlog 日志文件的指定位置 (日志文件名和位置就是在配置主从复制服务时执行 change master 命令指定的)之后开始发送 binlog 日志内容,
  • (3) master 服务器接收到来自 slave 服务器的 I/O 线程的请求后, master 上负责复制的 I/O 线程会根据 slave 服务器的 I/O 线程请求的信息分批读取指定 binlog 日志文件指定位置之后的 binlog 日志信息, 然后返回给 slave 端的 I/O 线程。
  • (4) 返回的信息有
    • binlog 日志信息
    • master 服务器记录的新的 binlog 文件名称,以及在新的 binlog 中的下一个指定更新位置
  • (5) 当 slave 服务器的 i/o 线程 获取到 master 服务器上的 I/0 线程发送的日志内容(日志文件 和 日志位置点)后,
    • 首先会将 binlog 日志内容 依次 写到 slave 自身的 relay-log (中继日志)文件的最末端,格式: mysql-relay-bin.xxxx,
    • 其次将新的 bin-log 文件名和文件位置记录到 master-info 文件中,以便一下次读取 master 端 bin-log 日志时能够告诉 master 服务器从新 bin-log 日志指定的文件和位置开始请求新的 bin-log 日志内容
  • (6) slave 服务器的 sql 线程会实时的检测本地的 relay-log 中 I/O线程新增加的日志内容,然后及时的把 relay log 文件中的内容解析成 sql 语句,并在 relay-log.info 中记录当前应用中继日志的文件名和位置点
  • (7) 经历了上面的过程, 就可以保证 master 端 和 slave 端执行了相同的 sql 语句,当复制状态正常时, master 和 slave 端的数据是完全一样的

1.1.2 主从复制实验

#主节点配置:
# 启用二进制日志
vim /etc/my.cnf
    [mysqld]
    server_id=1   # 为当前节点设置一个全局惟一的ID号,整数即可
    log-bin=/data/binlog # 也可以不写路径
service mysqld restart # 重启mysql

# 创建有复制权限的用户账号
GRANT REPLICATION SLAVE ON *.* TO 'repluser'@'%' IDENTIFIED BY '123456';


# 从节点配置:
# 启动中继日志
vim /etc/my.cnf
    [mysqld]
    server_id=2   # 为当前节点设置一个全局惟的ID号
    read_only=ON   # 设置数据库只读
    relay_log=relay-log    #relay log 的文件路径,默认值 hostname-relay-bin
    relay_log_index=relay-log.index   # 默认值 hostname-relay-bin.index
service mysqld restart # 重启mysql

# 使用有复制权限的用户账号连接至主服务器,并启动复制线程
# 查看 MASTER_LOG_FILE 与 MASTER_LOG_POS
mysql> show master status;
+---------------+----------+--------------+------------------+-------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| logbin.000001 |      154 |              |                  |                   |
+---------------+----------+--------------+------------------+-------------------+

mysql> CHANGE MASTER TO
        MASTER_HOST='192.168.7.7',
        MASTER_USER='repluser',
        MASTER_PASSWORD='123456',
        MASTER_LOG_FILE='logbin.000001',
        MASTER_LOG_POS=154;
mysql> START SLAVE;


# 注意:
如果主节点已经运行了一段时间,且有大量数据时,如何配置并启动slave节点
    通过备份恢复数据至从服务器
    复制起始位置为备份时,二进制日志文件及其POS
如果要启用级联复制,需要在从服务器启用以下配置
    [mysqld]
    log_bin
    log_slave_updates

# 复制架构中应该注意的问题:
1、限制从服务器为只读
  在从服务器上设置read_only=ON
    注意:此限制对拥有SUPER权限的用户均无效
  阻止所有用户, 包括主服务器复制的更新
    mysql> FLUSH TABLES WITH READ LOCK;

2、RESET SLAVE :从服务器清除master.info ,relay-log.info, relay log ,开
始新的relay log
    RESET SLAVE ALL:清除所有从服务器上设置的主服务器同步信息,如
PORT, HOST, USER和 PASSWORD 等
   注意:以上都需要先STOP SLAVE

3、sql_slave_skip_counter = N 从服务器忽略几个主服务器的复制事件,global变量

4、如何保证主从复制的事务安全
  参看https://mariadb.com/kb/en/library/server-system-variables/
  在master节点启用参数:
    sync_binlog=1 每次写后立即同步二进制日志到磁盘,性能差
    如果用到的为InnoDB存储引擎:
    innodb_flush_log_at_trx_commit=1 每次事务提交立即同步日志写磁盘
    innodb_support_xa=ON 默认值,分布式事务MariaDB10.3.0废除
    sync_master_info=# #次事件后master.info同步到磁盘
  在slave节点启用服务器选项:
    skip-slave-start=ON 不自动启动slave
  在slave节点启用参数:
    sync_relay_log=# #次写后同步relay log到磁盘
    sync_relay_log_info=#  #次事务后同步relay-log.info到磁盘

1.2 主主复制

  • 主主复制:互为主从
    • 容易产生的问题:数据不一致;因此慎用
    • 考虑要点:自动增长id
      • 配置一个节点使用奇数id
        auto_increment_offset=1 开始点
        auto_increment_increment=2 增长幅度
      • 另一个节点使用偶数id
        auto_increment_offset=2
        auto_increment_increment=2
  • 主主复制的配置步骤:
    • (1) 各节点使用一个惟一server_id
    • (2) 都启动binary log和relay log
    • (3) 创建拥有复制权限的用户账号
    • (4) 定义自动增长id字段的数值范围各为奇偶
    • (5) 均把对方指定为主节点,并启动复制线程

1.3 半同步复制

默认情况下,MySQL的复制功能是异步的,异步复制可以提供最佳的性能,主库把binlog日志发送给从库即结束,并不验证从库是否接收完毕。这意味着当主服务器或从服务器端发生故障时,有可能从服务器没有接收到主服务器发送过来的binlog日志,这就会造成主服务器和从服务器的数据不一致,甚至在恢复时造成数据的丢失

  • 半同步复制实现
# 主服务器
INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so'; # 安装插件
# 配置
SET GLOBAL rpl_semi_sync_master_enabled=1;
SET GLOBAL rpl_semi_sync_master_timeout = 1000;  #超时长1s
SHOW GLOBAL VARIABLES LIKE '%semi%';
SHOW GLOBAL STATUS LIKE '%semi%';

# 从服务器配置:
INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
SET GLOBAL rpl_semi_sync_slave_enabled=1;

1.4 复制过滤器

  • 让从节点仅复制指定的数据库,或指定数据库的指定表
  • 注意:在使用复制过滤器的时候,主服务器需要使用USE DATABASE_NAME,再对表进行操作,否则复制过滤器不起作用。

  • 两种实现方式

    • 方式1: 服务器选项:主服务器仅向二进制日志中记录与特定数据库相关的事件
      注意:此项和binlog_format相关
      参看:https://mariadb.com/kb/en/library/mysqld-options/#-binlog-ignore-db
      binlog-do-db = 数据库白名单列表,多个数据库需多行实现
      binlog-ignore-db = 数据库黑名单列表
      问题:基于二进制还原将无法实现;不建议使用
    • 方式2: 从服务器SQL_THREAD在relay log中的事件时,仅读取与特定数据库(特定表)相关的事件并应用于本地
      问题:会造成网络及磁盘IO浪费
  • 从服务器上的复制过滤器相关变量

#指定复制库的白名单
replicate_do_db= DATABASE_NAME

#指定复制库黑名单
replicate_ignore_db= DATABASE_NAME

#指定复制表的白名单
replicate_do_table= TABLE_NAME

#指定复制表的黑名单
replicate_ignore_table= TABLE_NAME

#支持通配符
replicate_wild_do_table= foo%.bar%
replicate_wild_ignore_table=

1.5 MySQL 复制加密

  • 基于SSL复制:
    在默认的主从复制过程或远程连接到MySQL/MariaDB所有的链接通信中的数据都是明文的,外网里访问数据或则复制,存在安全隐患。通过SSL/TLS加密的方式进行复制的方法,来进一步提高数据的安全性
  • 配置实现:参看:https://mariadb.com/kb/en/library/replication-with-secure-connections/
    • 主服务器开启SSL,配置证书和私钥路径 并且创建一个要求必须使用SSL连接的复制账号 mysql>GRANT REPLICATION SLAVE ON . TO ‘repluser’@’192.168.8.%’IDENTIFIED BY ‘magedu’ REQUIRE SSL;
    • 从服务器使用CHANGER MASTER TO 命令时指明ssl相关选项
# Master 服务器配置
vim /etc/my.cnf
    [mysqld]
    log-bin
    server_id=1
    ssl
    ssl-ca=/etc/my.cnf.d/ssl/cacert.pem
    ssl-cert=/etc/my.cnf.d/ssl/master.crt
    ssl-key=/etc/my.cnf.d/ssl/master.key

# Slave 服务器配置
mysql>
CHANGE MASTER TO
MASTER_HOST='MASTERIP',
MASTER_USER='rep',
MASTER_PASSWORD='centos',
MASTER_LOG_FILE='mariadb-bin.000001',
MASTER_LOG_POS=245,
MASTER_SSL=1,
MASTER_SSL_CA = '/etc/my.cnf.d/ssl/cacert.pem',
MASTER_SSL_CERT = '/etc/my.cnf.d/ssl/slave.crt',
MASTER_SSL_KEY = '/etc/my.cnf.d/ssl/slave.key';

1.6 复制的监控和维护

# 清理日志
PURGE { BINARY | MASTER } LOGS { TO 'log_name' | BEFORE datetime_expr }
RESET MASTER
RESET SLAVE

# 复制监控
SHOW MASTER STATUS
SHOW BINARY LOGS
SHOW BINLOG EVENTS
SHOW SLAVE STATUS
SHOW PROCESSLIST

# 从服务器是否落后于主服务
Seconds_Behind_Master:0

# 如何确定主从节点数据是否一致
percona-tools

# 数据不一致如何修复
删除从数据库,重新复制

1.7 GTID复制

  • GTID复制:(global transaction id 全局事务标识符) MySQL5.6版本开始支持,GTID复制不像传统的复制方式(异步复制、半同步复制)需要找到binlog和POS点,只需知道master的IP、端口、账号、密码即可。开启GTID后,执行change master to master_auto_postion=1即可,它会自动寻找同步
  • GTID 架构
    GTID = server_uuid:transaction_id,在一组复制中,全局唯一 server_uuid 来源于 auto.cnf
  • GTID 服务器相关选项
    gtid_mode gtid模式
    enforce_gtid_consistency 保证GTID安全的参数

GTID在binlog中的结构和GTID event 结构

GTID配置示例

# 主服务器
vim /etc/my.cnf
    [mysqld]
    server-id=1
    log-bin=mysql-bin
    gtid_mode=ON
    enforce_gtid_consistency
    mysql> grant replication slave on *.* to 'repluser'@'192.168.7.%' identified by '123456';

# 从服务器
vim /etc/my.cnf
    [mysqld]
    server-id=2
    gtid_mode=ON
    enforce_gtid_consistency
    mysql>CHANGE MASTER TO MASTER_HOST='192.168.7.17',
    MASTER_USER='repluser',
    MASTER_PASSWORD='123456',
    MASTER_PORT=3306,
    MASTER_AUTO_POSITION=1;
    mysql>start slave;

二、MySQL读写分离

2.1 读写分离案例

mysql-proxy:Oracle:https://downloads.mysql.com/archives/proxy/
Atlas,Qihoo:https://github.com/Qihoo360/Atlas
dbproxy,美团:https://github.com/Meituan-Dianping/DBProxy
Cetus,网易乐得:https://github.com/Lede-Inc/cetus
Amoeba:https://sourceforge.net/projects/amoeba/
Cobar,阿里巴巴,Amoeba的升级版:https://github.com/alibaba/cobar
Mycat,基于Cobar:http://www.mycat.io/
ProxySQL:https://proxysql.com/

2.2 ProxySQL

  • ProxySQL: MySQL中间件
  • 两个版本:官方版和 percona 版,percona 版是基于官方版基础上修改C++语言开发,轻量级但性能优异(支持处理千亿级数据)具有中间件所需的绝大多数功能,包括:
    • 多种方式的读/写分离
    • 定制基于用户、基于schema、基于语句的规则对SQL语句进行路由
    • 缓存查询结果
    • 后端节点监控
  • 官方站点:https://proxysql.com/
  • 官方手册:https://github.com/sysown/proxysql/wiki
  • 准备:
    实现读写分离前,先实现主从复制
    注意:slave节点需要设置read_only=1
  • ProxySQL组成:
    • 服务脚本:/etc/init.d/proxysql
    • 配置文件:/etc/proxysql.cnf
    • 主程序:/usr/bin/proxysql
    • 基于SQLITE的数据库文件:/var/lib/proxysql/
  • 启动ProxySQL:service proxysql start
    • 启动后会监听两个默认端口
      6032:ProxySQL的管理端口
      6033:ProxySQL对外提供服务的端口
  • 使用mysql客户端连接到ProxySQL的管理接口6032,默认管理员用户和密码都是admin:

2.2.1 ProxySQL安装

# 基于YUM仓库安装
cat <<EOF | tee /etc/yum.repos.d/proxysql.repo
[proxysql_repo]
name= ProxySQL YUM repository
baseurl= http://repo.proxysql.com/ProxySQL/proxysql-2.0.x/centos/\$releasever
gpgcheck=1
gpgkey=http://repo.proxysql.com/ProxySQL/repo_pub_key
EOF

yum install proxysql
# 基于RPM下载安装
https://github.com/sysown/proxysql/releases

2.2.2 ProxySQL 实现读写分离

  • 数据库说明:
  • main 是默认的”数据库”名,表里存放后端db实例、用户验证、路由规则等信息。表名以 runtime_开头的表示proxysql当前运行的配置内容,不能通过dml语句修改,只能修改对应的不以 runtime_ 开头的(在内存)里的表,然后 LOAD 使其生效,SAVE 使其存到硬盘以供下次重启加载
  • disk 是持久化到硬盘的配置,sqlite数据文件
  • stats 是proxysql运行抓取的统计信息,包括到后端各命令的执行次数、流量、processlist、查询种类汇总/执行时间,等等
  • monitor 库存储 monitor 模块收集的信息,主要是对后端db的健康/延迟检查
  • 在main和monitor数据库中的表, runtime_开头的是运行时的配置,不能修改,只能修改非runtime_表
    • 修改后必须执行LOAD … TO RUNTIME才能加载到RUNTIME生效
    • 执行save … to disk 才将配置持久化保存到磁盘,即保存在proxysql.db文件中global_variables 有许多变量可以设置,其中就包括监听的端口、管理账号等
      参考: https://github.com/sysown/proxysql/wiki/Global-variables
      https://blog.csdn.net/weixin_42758707/article/details/95657214?spm=1001.2014.3001.5501
暂无评论

发送评论 编辑评论


				
|´・ω・)ノ
ヾ(≧∇≦*)ゝ
(☆ω☆)
(╯‵□′)╯︵┴─┴
 ̄﹃ ̄
(/ω\)
∠( ᐛ 」∠)_
(๑•̀ㅁ•́ฅ)
→_→
୧(๑•̀⌄•́๑)૭
٩(ˊᗜˋ*)و
(ノ°ο°)ノ
(´இ皿இ`)
⌇●﹏●⌇
(ฅ´ω`ฅ)
(╯°A°)╯︵○○○
φ( ̄∇ ̄o)
ヾ(´・ ・`。)ノ"
( ง ᵒ̌皿ᵒ̌)ง⁼³₌₃
(ó﹏ò。)
Σ(っ °Д °;)っ
( ,,´・ω・)ノ"(´っω・`。)
╮(╯▽╰)╭
o(*////▽////*)q
>﹏<
( ๑´•ω•) "(ㆆᴗㆆ)
😂
😀
😅
😊
🙂
🙃
😌
😍
😘
😜
😝
😏
😒
🙄
😳
😡
😔
😫
😱
😭
💩
👻
🙌
🖕
👍
👫
👬
👭
🌚
🌝
🙈
💊
😶
🙏
🍦
🍉
😣
Source: github.com/k4yt3x/flowerhd
颜文字
Emoji
小恐龙
花!
上一篇
下一篇