[后端] MySQL-主从复制和读写分离

发布时间:2024年01月13日

在高访问的业务场景中,读操作远比写操作多,如果读请求和写请求都在一个库上,那么这个库的压力是巨大的,这时,可以将大多数读请求分担到多个从库上,进而极大降低主库压力,同时也能提高整个数据库负载能力。

优缺点分析

主从架构的优势在于,在读操作远远多于写的情况下,从库能够极大减轻主库的压力,同时能够极大提高整个数据库集群的负载能力;当然,这种架构也存在一个十分明显的缺点,那就是主从延迟,尤其在高并发下往主库写入大量数据的时候,延迟会十分严重,在那些对延迟要切比较严格的场景,需要慎重使用。

MySQL主从复制

binlog 日志,是MySQL的二进制日志,可以说是MySQL最重要的日志,它以事件形式记录了所有的DDL和DML(除了数据查询语句)语句,还包含语句执行所消耗的时间,一般来说,开启二进制日志会有1%的性能损耗。它有两个主要使用场景:

  • 使用mysqlbinlog工具来实现数据恢复。
  • 主服务器开启二进制日志,以实现主从复制。

binlog日志包括两个部分的文件,二进制日志索引文件(文件后缀名为 .index)用于记录所有的二进制文件;另一个是二进制日志文件(文件名后缀为 .0000*)记录数据库所有的DDL和DML语句事件。开启binlog日志只需要修改MySQL配置文件 my.cnf :

# /usr/local/mysql/etc/my.cnf
# 在 [mysqld] 区块添加如下配置
# 值 mysql-bin 是日志的基本名或前缀名
log-bin=mysql-bin

保存后重启mysqld服务使配置生效,登录mysql服务器,使用 show variables like 'log_%' 命令查看配置是否生效:

mysql> show variables like 'log_%'; 
+----------------------------------------+---------------------------------------+
| Variable_name                          | Value                                 |
+----------------------------------------+---------------------------------------+
| log_bin                                | ON                                    | 
| log_bin_basename                       | /usr/local/mysql/data/mysql-bin       |
| log_bin_index                          | /usr/local/mysql/data/mysql-bin.index |
| log_bin_trust_function_creators        | OFF                                   |
| log_bin_use_v1_row_events              | OFF                                   |
| log_error                              | /usr/local/mysql/data/martin.err      |
| log_output                             | FILE                                  |
| log_queries_not_using_indexes          | OFF                                   |
| log_slave_updates                      | OFF                                   |
| log_slow_admin_statements              | OFF                                   |
| log_slow_slave_statements              | OFF                                   |
| log_throttle_queries_not_using_indexes | 0                                     |
| log_warnings                           | 1                                     |
+----------------------------------------+---------------------------------------+

其中 log_bin 表示是否开启二进制日志,ON表示开启;log_bin_basename 表示二进制日志文件的基本名或前缀名;log_bin_index表示二进制日志索引文件的位置。

可以用MySQL自带命令操作binlog日志,常用的binlog日志操作命令:

# 查看所有的binlog日志列表
mysql> show master logs
# 查看master状态,即最新一个binlog日志的编号名称,及其最后一个操作事件pos结束点的值
mysql> show master status
# 刷新binlog日志,自此刻开始产生一个新编号的binlog日志
mysql> flush logs
# 清空所有binlog日志
mysql> reset master

binlog是二进制文件,普通的文件查看器都无法打开,可以MySQL自带的mysqlbinlog命令查看,但更推荐使用MySQL的语句show binlog events查看,该命令的常见用法:

# 最简单的使用,显示全部binlog日志
mysql> show binlog events
# 显示指定binlog文件中记录
mysql> show binlog events in 'mysql-bin.000001'
# 指定从哪个pos点开始查起
mysql> show binlog events from 123
# 设置偏移量和查询条数(偏移量2,条数10)
mysql> show binlog events limit 2,10

配置master

对master的配置主要分为两步,一是修改配置文件,开启二进制日志,重启使配置生效,常用的配置如下:

# 开启binlog日志
log-bin=mysql-bin
# 设置不记录的库
binlog-ignore-db=mysql
# 设置需要记录的库
binlog-do-db=mydb
# 节点ID,确保唯一
server-id=1
# 二进制文件的格式
binlog_format=mixed

二是添加一个用于slave节点登录的用户,使用以下命令创建用户:

# 创建用户
CREATE USER 'slave_user'@'%' IDENTIFIED BY 'slavepasswd'
# 为该用户赋予权力
GRANT replication SLAVE ON *.* TO 'slave_user'@'%'
# 刷新权限信息
FLUSH PRIVILEGES

以上便完成了master节点的配置,但需要使用如下命令查看配置是否生效:

# 查看主节点的状态
show master status
# 命令响应内容举例如下
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000005 |     120  |              | mysql            |                   |
+------------------+----------+--------------+------------------+-------------------+
# 响应值可以在配置从节点时用到

配置slave

对slave的配置主要分为两步,第一步是修改配置文件,重启使配置生效,常用配置为:

# 节点id,确保唯一
server-id=2
# 开启二进制日志
log-bin=mysql-bin
# 开启中继日志
relay-log=mysql-relay-log
# 设置不复制的表
replicate-wild-ignore-table=mysql.%
# 设置不复制的库
replicate-ignore-db=mysql
# 设置需要复制的库
replicate-do-db=mydb

第二步是通过MySQL命令配置master节点信息:

# 配置master节点信息
mysql> change master to 
MASTER_HOST = 'master节点的IP',
MASTER_USER = 'master中slave账户',
MASTER_PASSWORD = 'master中slave账户的密码',
MASTER_PORT = 'master节点的端口',
MASTER_LOG_FILE = 'mysql-bin.000005',
MASTER_LOG_POS = 120,
MASTER_RETRY_COUNT = 60,
MASTER_HEARTBEAT_PERIOD = 10000;
### 上命令相关参数解析
### MASTER_LOG_FILE 与 master 节点的 show master status 显示结果的 File 保持一致
### MASTER_LOG_POS 与 master 节点的 show master status 显示结果的 Position 保持一致

# 启动 slave 节点
mysql> start slave
# 检查从机状态
mysql> show slave status
# 关闭 slave 节点
mysql> stop slave

需要注意的是,如果master服务器中已存在应用数据,需要进行以下处理:

# 锁定表,防止有数据写入
mysql> flush tables with read lock
# 复制master的数据至slave中
# 取消锁定
mysql> unlock tables

MySQL读写分离

读写分离的实现一般是通过中间件完成的,常用的读写分离中间件有很多,此处以其中的MyCat为例。

安装JDK

Mycat是基于Java运行的,因此需要安装JDK环境,centos上的安装过程如下:

# 安装JDK
yum install -y java-1.8.0-openjdk java-1.8.0-openjdk-devel
# 检测安装是否成功
java -version
# 配置环境变量
vi /etc/profile
# 在vi的编辑模式下,输入如下内容:
JAVA_HOME=/user/lib/jvm/java-1.8.0-openjdk-1.8.0.302.b08-0.el7_9.x86_64
PATH=$PATH:$JAVA_HOME/bin
CLASSPATH=.:$JAVA_HOME/lib/dt.jar:$JAVA_HOME/lib/tools.jar
export JAVA_HOME CLASSPATH PATH
# 保存上述内容,使用source命令使配置生效
source /etc/profile
# 验证环境变量是否配置成功
echo $JAVA_PATH
echo $PATH
echo $CLASSPATH

安装Mycat

先从mycat官网下载mycat的安装包,官网地址为 dl.mycat.org.cn ,然后解压。可以使用如下命令完成这些操作

# 下载mycat
wget http://dl.mycat.org.cn/Mycat-server-1.4-beta-20150604171601-linux.tar.gz
# 解压并安装mycat
tar -vxf Mycat-server-1.4-beta-20150604171601-linux.tar.gz -C /usr/local/

配置Mycat

mycat的配置文件位于安装目录下的conf目录中,主要的配置文件有:

server.xml

用于定义用户及系统相关变量,其中user标签用于配置用户节点,主要的属性为:

  1. name :登录的用户名
  2. password :登录的密码
  3. schemas :逻辑库名称,并非真实库名称,而是schema.xml中文件中的schema标签定义的逻辑库名称。
<user name="root" defaultAccount="true">
	<property name="password">123456</property>
    <property name="schemas">dbtest</property>
    <property name="readOnly">false</property>
</user>
schema.xml

定义逻辑库、表、分片节点等内容,常用的配置有:

  1. schema :逻辑库配置,其常用的属性有:
<mycat:schema xmlns:mycat="http://io.mycat/">
    <!-- 逻辑库配置 -->
	<schema name="dbtest" checkSQLschema="false" sqlMaxLimit="1000" dataNode="db_node">	
        <!-- 逻辑表配置(非必须) -->
    	<table name="tbtest1" dataNode="dn1,dn2"></table>
    </schema>
</mycat:schema>

参数解析:

参数解析
checkSQLschema是否开启SQL的库检查机制,若开启,会去掉传入SQL中表名前的数据库名称
sqlMaxLimit限制响应的数据条数,相当于在SQL语句末尾添加 limit 语句
dataNode定义使用的dataNode节点的名称
  1. dataNode :数据节点配置,定义了逻辑数据库对应的物理数据库节点,配置举例如下:
<mycat:schema xmlns:mycat="http://io.mycat/">
	<dataNode name="db_node" dataHost="db_host" database="mydb"/>
</mycat:schema>

参数解析:

参数解析
name名称,唯一标识
dataHost指定host,属性值是引用dataHost标签上定义的name属性
database真实库名称
  1. dataHost :数据主机配置,定义了具体的数据库实例,读写分离配置和心跳语句,配置举例如下:
<dataHost name="db_host" maxCon="1000" minCon="10" balance="0" writeType="0" dbType="mysql" dbDriver="native">
    <!-- 心跳语句 -->
	<heartbeat>select user()</heartbeat>
    <!-- 读写分离配置 -->
    <writeHost host="host1" url="localhost:3306" user="root" password="123456">
    	<readHost host="host2" url="localhost:3307" user="root" password="123456" />
    </writeHost>
</dataHost>

参数解析

标签参数解析
dataHostname名称,唯一标识
dataHostmaxCon连接池的实例的最大数量
dataHostminCon连接池的实例的最小数量
dataHostbalance负载均衡类型
0 -> 不开启读写分离机制;
1 -> 除了第一个writeHost外,其他全部readHost和writeHost都参与select的负载均衡,也就是说,第一个writeHost只负责写不负责读;
2 -> select请求会随机发送到任意一个writeHost或readHost;
3 -> select请求只发送至readHost;
dataHostwriteType负载均衡写操作类型,取值有2种:
0 -> 所有写操作发送到配置的第一个writeHost,第一个挂了,则切换到还生存的第二个writeHost;重启后以重启前的writeHost为准,切换日志记录在配置文件 dnindex.properties中;
1 -> 所有写操作都随机发送到配置的writeHost中,1.5 以后废弃不推荐;
dataHostdbType指定后端连接的数据库类型,目前支持 mysql、mongodb、oracle、spark等
dataHostdbDriver指定使用的驱动类型,目前可选值有:native和JDBC。native采用二进制的mysql协议,目前仅支持mysql和maridb,其他类型的数据库则需要JDBC驱动来支持。
dataHostswitchType切换类型,决定切换机制,可选值有:
-1 -> 不自动切换
0 -> 默认值,自动切换
1 -> 基于主从同步的状态决定是否切换,心跳语句是 show slave status
2 -> 基于MySQL galary cluster的切换机制,适合集群。
heartbeat该标签用于指定心跳语句;
writeHosthost名字,唯一标识
writeHosturl物理主机的URL,带端口;
writeHostuser物理数据库登录用户名
writeHostpassword物理数据库登录密码

启动mycat

一般采用两种方式启动mycat,一种是控制台启动,在安装目录的bin下使用命令 ./mycat console ,这种方式可以第一时间开到启动日志,方便定位问题;第二种是后台启动,在安装目录的bin下使用命令 ./mycat start命令实现。

避坑指南

问题描述使用mysql客户端连接mycat时,连接失败,报错:access denied for user xxx
原因分析这是由于使用的MySQL客户端是8以上的版本,而mycat只支持 5.x 版本的登录方式
解决方案使用5.x版本的MySQL客户端进行连接
问题描述使用Navicat连接Mycat时,能够建立连接,但打开连接时报错:No MYCAT database selected
原因分析navicat对Mycat的兼容性较弱
解决方案暂无
文章来源:https://blog.csdn.net/hacos/article/details/135558779
本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。