MySQL高可用机制和分库分表

目录

• 主从架构
• MySQL高可用工具
• 分库分表

主从架构

为什么需要主从架构

1、保证数据安全
如果DB里面的数据非常重要，不能丢失就可以使用主从架构，从DB作为主DB的数据备份，甚至基于主从搭建互主、多主的架构
2、实现读写分离
日常使用中大部分的业务对DB的操作都是读多写少，读写分离，主服务只负责处理写请求，从服务负责处理读请求大大降低DB的压力、提高性能
3、提供高可用服务
主服务宕机后，从服务切换为主服务继续提供服务，主从切换依赖中间件来实现

同步机制实现原理

主从同步一般是通过binlog日志来完成，在主服务上开启binlog，这样在主服务上的每一个数据库操作都会记录到binlog日志，从服务上有一个IO线程，负责和主服务建立TCP长连接，接收主服务传输的binlog，主服务上有一个IO dump线程，负责将binlog日志传输给从服务，从服务的IO线程会把接收的binlog日志写入自己的Relaylog中，然后从服务另有一个SQL线程负责读取relaylog，进行操作重演，解析日志并执行命令还原数据
从服务可以用redis、kafka等其他组件模拟实现数据缓存同步
注意：
主服务和从服务的MySQL版本建议一致
主服务和从服务所在服务器的时间要一致

搭建主从集群

1、配置master服务器，打开配置文件/etc/my.conf

[mysqld]
server-id=47
#开启binlog
log_bin=master-bin
log_bin-index=master-bin.index
skip-name-resolve
# 设置连接端口
port=3306
# 设置mysql的安装目录
basedir=/usr/local/mysql
# 设置mysql数据库的数据的存放目录
datadir=/usr/local/mysql/mysql-files
# 允许最大连接数
max_connections=200
# 允许连接失败的次数。
max_connect_errors=10
# 服务端使用的字符集默认为UTF8
character-set-server=utf8
# 创建新表时将使用的默认存储引擎
default-storage-engine=INNODB
# 默认使用“mysql_native_password”插件认证
#mysql_native_password
default_authentication_plugin=mysql_native_password

server-id：节点的唯一标识，集群中每个节点的标识都不一样
log_bin：开启binlog日志记录，指定日志存放的位置，默认放在data目录下
log_bin_index：指定索引文件的位置

重启MySQL服务，service mysqld restart

给root用户分配权限(实际生产中一般是专门创建一个用户来负责主从同步)

#登录主数据库
mysql -u root -p
GRANT REPLICATION SLAVE ON *.* TO 'root'@'%';
flush privileges;
#查看主节点同步状态：
show master status;

*************************** 1. row ***************************
             File: mysql-bin.000001
         Position: 156
     Binlog_Do_DB:
 Binlog_Ignore_DB:

结果集中的File和Position记录的是当前使用的binlog日志文件名和文件中的索引，从节点需要指定这两个参数为同步的初始值，Binlog_Do_DB和Bin_Ignore_DB是表示需要记录binlog文件的库以及不需要记录binlog文件的库，默认是记录全库日志
2、配置slave服务

[mysqld]
#主库和从库需要不一致
server-id=48
#打开MySQL中继日志
relay-log-index=slave-relay-bin.index
relay-log=slave-relay-bin
#打开从服务二进制日志
log-bin=mysql-bin
#使得更新的数据写进二进制日志中
log-slave-updates=1
# 设置3306端口
port=3306
# 设置mysql的安装目录
basedir=/usr/local/mysql
# 设置mysql数据库的数据的存放目录
datadir=/usr/local/mysql/mysql-files
# 允许最大连接数
max_connections=200
# 允许连接失败的次数。
max_connect_errors=10
# 服务端使用的字符集默认为UTF8
character-set-server=utf8
# 创建新表时将使用的默认存储引擎
default-storage-engine=INNODB
# 默认使用“mysql_native_password”插件认证
#mysql_native_password
default_authentication_plugin=mysql_native_password

启动MySQL服务，设置同步

#登录从服务
mysql -u root -p;
#设置同步主节点：
CHANGE MASTER TO
MASTER_HOST='xxx.xxx.xxx.xxx',
MASTER_PORT=3306,
MASTER_USER='root',
MASTER_PASSWORD='root',
MASTER_LOG_FILE='master-bin.000001',
MASTER_LOG_POS=156
GET_MASTER_PUBLIC_KEY=1;
#开启slave
start slave;
#查看主从同步状态
show slave status;
或者用 show slave status \G; 这样查看比较简洁

*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: XXX.XXX.XXX.XXX
                  Master_User: XXX
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 156
               Relay_Log_File: slave-relay-bin
                Relay_Log_Pos: 102
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

MASTER_LOG_FILE和MASTER_LOG_POS必须和从master节点查到的数据保持一致，Slave_IO_Running和Slave_SQL_Running都是Yes表示同步正常

全库同步和部分同步

实际生产环境中，一般只对重要的数据库做同步，只需要修改master节点和slave节点的配置即可
打开master的my.conf，配置以下参数

#需要同步的二进制数据库名
binlog-do-db=masterdb
#只保留7天的二进制日志，以防磁盘被日志占满(可选)
expire-logs-days  = 7
#不备份的数据库
binlog-ignore-db=testdb1
binlog-ignore-db=testdb2

打开slave节点的my.conf，修改以下配置

#如果salve库名称与master库名相同，使用本配置
replicate-do-db = masterdb
#如果master库名[mastdemo]与salve库名[mastdemo01]不同，使用以下配置[需要做映射]
replicate-rewrite-db = masterdb -> slavedb
#如果不是要全部同步[默认全部同步]，则指定需要同步的表
replicate-wild-do-table=slavedb.t_dict
replicate-wild-do-table=slavedb.t_num

GTID同步

GTID的本质也是基于binlog日志实现同步，GTID即全局事务ID，全局唯一且趋势递增，首先从数据库会通知主数据库已经执行了的事务的GTID值，然后主数据库会将所有没有执行的事务发送到从数据库上执行，使用GTID的复制可以保证同一个事务在指定的从数据库只执行一次，避免由于偏移量导致的数据不一致
主数据库配置：

gtid_mode=on
enforce_gtid_consistency=on
log_bin=on
server_id=单独设置一个
binlog_format=row

从数据库配置：

gtid_mode=on
enforce_gtid_consistency=on
log_slave_updates=1
server_id=单独设置一个，不能和主数据库重复

半同步复制

MySQL主从复制默认采用异步复制，master执行完请求后，写入binlog日志，然后就返回success，由dump线程异步发送binlog给slave，但是如果在发送binlog的时候master宕机，此时就有可能丢失数据，为了解决这个问题，引入了半同步复制机制，在半同步复制模式下，master执行完请求后，等待至少一个slave写入relay log中后才会返回success，默认等待10米秒，如果10秒后没有收到slave的ack确认，则降级为异步复制，半同步复制相比异步复制能有效提高数据安全性，但是也无法完全保证数据能否复制成功，另外半同步机制也会造成master响应延迟。

MySQL高可用工具

• MMM
• MHA
• MGR

分库分表

分库分表的作用

分库分表是为了解决数据库数据量过大导致性能下降的问题，将单个数据库拆分为多个数据库，单个表拆分成若干个表

拆分方式

分库和分表统称为数据分片，从拆分角度上分为两种，垂直分片和水平分片

• 垂直分片
  按照业务拆分，又称纵向分片，核心理念就是专库专用，从业务的角度上将负责相同业务的表拆分到不同的数据库和表中，垂直分片可以缓解访问压力，但是无法解决单表数据量过大的问题
• 水平分片
  通过某个字段拆分数据，又称横向分片，通过某个字段按照一定的规则拆分数据到不同的表中，每个数据片包含一部分数据

常用的分片策略有取模、按时间分、按范围分等等

分库分表带来的问题

• 分布式事务
• 跨数据库关联查询
• 跨数据库分页、聚合
• 主键重复
• 运维

常用分库分表中间件

• shardingsphere
• mycat
• DBLE

本文地址：https://blog.csdn.net/weixin_42276755/article/details/112234875