mysql offset运行原理

转载

mob64ca1413c518 2024-11-06 19:18:55

文章标签 mysql offset运行原理 Mycat 数据库读写分离 mysql读写分离 mysql负载均衡 文章分类 MySQL 数据库

mysql offset运行原理_mysql读写分离

使用 Mycat 实现 M-S-S 模式读写分离

实验环境
Mycat 192.168.2.2 CentOS Linux release 8.3.2011
Mysql Master 192.168.2.3 CentOS Linux release 7.5.1804
Mysql 中继 192.168.2.5 CentOS Linux release 7.6.1810
Mysql slave 192.168.2.6 CentOS Linux release 7.6.1810
firewalld、iptables以及 selinux 均为关闭状态
Mysql 版本统一使用 mysql-5.7.25
分别在 2.3 2.5 2.6
yum install -y ./mysql-5.7/mysql*.rpm

mysql offset运行原理_mysql负载均衡_02

mysql offset运行原理_数据库读写分离_03

mysql offset运行原理_数据库读写分离_04

安装完成后启动mysql
systemctl enable mysqld --now

mysql offset运行原理_mysql读写分离_05

mysql offset运行原理_mysql读写分离_06

mysql offset运行原理_数据库读写分离_07

关闭密码强度审计插件
echo "validate-password=OFF">> /etc/my.cnf
systemctl restart mysqld
分别修改3台服务器的 root 用户密码
grep "password" /var/log/mysqld.log
mysql -uroot -p'Dlr%wM?<a6pC'
mysql> set password for root@localhost = password('123456');
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql>

mysql offset运行原理_数据库读写分离_08

mysql offset运行原理_mysql读写分离_09

mysql offset运行原理_数据库读写分离_10

重新登陆后，在主服务器 192.168.2.3 上授权用户
mysql -uroot -p'123456'
>grant replication slave on *.* to slave@'192.168.2.%' identified by '123456';
>create database mydata;
>use mydata;
>create table T1(id int,name varchar(20));
>insert into T1 values(1,'zhangsan');

mysql offset运行原理_mysql offset运行原理_11

退出数据库，导出刚才创建的数据库
mysqldump -u root -p -B mydata > /tmp/mysql_mydata.sql

mysql offset运行原理_Mycat_12

mysql offset运行原理_mysql读写分离_13

编辑配置文件 /etc/my.cnf
vim /etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-bin=mysql-bin-master
server-id=1
binlog-do-db=mydata #指定要同步的库
binlog-ignore-db=mysql #忽略要同步的库
sync-binlog=1 #执行 N 次写入 binlog 后，与硬盘同步
binlog-format=row #binlog 保存方式，记录哪条数据被修改了

mysql offset运行原理_数据库读写分离_14

查看log_bin系统变量的设置
>show variables like 'log_bin';

接下来部署 slave 中继服务器 192.168.2.5

导入刚才导出的数据库文件
scp root@192.168.2.3:/tmp/mysql_mydata.sql /tmp
mysql -uroot -p123456 < /tmp/mysql_mydata.sql
mysql -uroot -p123456 -e "select * from mydata.T1"

mysql offset运行原理_mysql读写分离_15

编辑配置文件 /etc/my.cnf
vim /etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
server-id=2
log-bin=mysql-bin-slave1
log-slave-updates=1
binlog-format=row
log-slave-updates 把它从 relay-log 当中读取出来的二进制日志并且这本机上执行的操作也记录这自己的二进制日志里面，这样才能使第三台 slave 通过中继 slave 读取到相应数据变化

mysql offset运行原理_Mycat_16

开始配置主从同步

在中继服务器 192.168.2.5 上

mysql -uroot -p123456
>stop slave;
>change master to master_host='192.168.2.3',master_user='slave',master_password='123456';
>start slave;
>show slave status \G

mysql offset运行原理_mysql offset运行原理_17

mysql offset运行原理_mysql读写分离_18

测试在 192.168.2.3 主服务器上插入数据

mysql offset运行原理_数据库读写分离_19

然后在从服务器 192.168.2.5 上查看

mysql offset运行原理_mysql负载均衡_20

手动刷新 bin-log
>flush logs

mysql offset运行原理_数据库读写分离_21

mysql offset运行原理_mysql offset运行原理_22

在 192.168.2.5 上添加授权用户

mysql>grant replication slave on *.* to slave@'192.168.2.%' identified by '123456';
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql> select user,host from mysql.user;

mysql offset运行原理_数据库读写分离_23

接下来部署 slave 192.168.2.6

导入数据库
scp root@192.168.2.5:/tmp/mysql_mydata.sql /tmp
mysql -uroot -p < /tmp/mysql_mydata.sql

mysql offset运行原理_mysql offset运行原理_24

编辑配置文件 /etc/my.cnf
vim /etc/my.cnf
[mysqld]

datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
server-id=3
log-bin=mysql-bin-slave2
binlog-format=row

mysql offset运行原理_mysql读写分离_25

指定 slave 中继服务 192.168.2.5

mysql -uroot -p123456
>stop slave;
>change master to master_host='192.168.2.5',master_user='slave',master_password='123456';
>flush privileges;
>start slave;

mysql offset运行原理_mysql offset运行原理_26

mysql offset运行原理_mysql负载均衡_27

mysql offset运行原理_mysql offset运行原理_28

测试在主服务器上插入数据

mysql offset运行原理_mysql offset运行原理_29

在中继和从服务器上查看

mysql offset运行原理_数据库读写分离_30

mysql offset运行原理_Mycat_31

模拟故障：
由于历史遗留问题，MySQL 主从库的表结构不一致，主库的某个表 tableA 比从库表 tableA 少了一个字段。
当尝试在主库上更改表结构时，这行 alter 语句会随着 binlog 同步到从库，如果从库执行这行语句时出错，主从同步线程就会自动停止，那样只能人为手动处理错误，然后再启动 slave 上的主从同步线程。

首先在从库 192.168.2.6 上添加这个字段：
>alter table mydata.T1 add age int default 0 after name;
然后在主库 192.168.2.3
>alter table mydata.T1 add age int default 0 after name;
查看从库 192.168.2.6 的 slave 状态
mysql> show slave status \G

*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                   Master_Host: 192.168.2.5
                   Master_User: slave
                   Master_Port: 3306
                 Connect_Retry: 60
               Master_Log_File: mysql-bin-slave1.000001
           Read_Master_Log_Pos: 1301
                Relay_Log_File: c7_2_6-relay-bin.000002
                 Relay_Log_Pos: 1337
         Relay_Master_Log_File: mysql-bin-slave1.000001
             Slave_IO_Running: Yes
             Slave_SQL_Running: No
               Replicate_Do_DB: 
           Replicate_Ignore_DB: 
            Replicate_Do_Table: 
        Replicate_Ignore_Table: 
       Replicate_Wild_Do_Table: 
   Replicate_Wild_Ignore_Table: 
                    Last_Errno: 1060

Last_Error: Error 'Duplicate column name 'age'' on query. Default database: ''. Query: 'alter table mydata.T1 add age int default 0 after name'

解决办法

首先在主库上停止 bin-log
mysql> set sql_log_bin=off;
然后再执行 alter 语句
mysql> alter table T1 add sex int default 0 after name;
再开启 bin-log
mysql> set sql_log_bin=on;
mysql> set sql_log_bin=off;
Query OK, 0 rows affected (0.00 sec)

mysql> alter table mydata.T1 add sex int default 0 after name;
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> set sql_log_bin=on;
Query OK, 0 rows affected (0.00 sec)
然后在从库查看
mysql> show slave status\G
还是 no 是因为上次的 alter 没有同步
做如下操作恢复
mysql> stop slave;
change master to master_log_file='mysql-bin-slave1.000001',master_log_pos=1301;
mysql> start slave;
mysql> show slave status \G

mysql offset运行原理_数据库读写分离_32

mysql offset运行原理_Mycat_33

在主库上插入数据

mysql offset运行原理_mysql负载均衡_34

mysql offset运行原理_Mycat_35

然后在 192.168.2.2 上配置 Mycat

详细设置请参阅：
编辑 /usr/local/mycat/conf/server.xml 配置文件
vim /usr/local/mycat/conf/server.xml
<?xml version="1.0" encoding="UTF-8"?>

<!DOCTYPE mycat:server SYSTEM "server.dtd">
<mycat:server xmlns:mycat="http://io.mycat/">
<system>
<property name="nonePasswordLogin">1</property> 
<property name="ignoreUnknownCommand">0</property>
<property name="useHandshakeV10">1</property>
<property name="removeGraveAccent">1</property>
<property name="useSqlStat">0</property> 
<property name="useGlobleTableCheck">0</property> 
<property name="sqlExecuteTimeout">300</property> 
<property name="sequenceHandlerType">1</property>


<property name="sequnceHandlerPattern">(?:(\s*next\s+value\s+for\s*MYCATSEQ_(\w+))(,|\)|\s)*)+</property>
<property name="subqueryRelationshipCheck">false</property> 
<property name="sequenceHanlderClass">io.mycat.route.sequence.handler.HttpIncrSequenceHandler</property>
 
 



<property name="processorBufferPoolType">0</property>

"/usr/local/mycat/conf/server.xml" [dos] 134L, 6443C




<user name="gf" defaultAccount="true"> user name 定义 mycat 管理账号，名称可以自定义，无需创建该用户
<property name="password">123456</property>
<property name="schemas">mydata</property>
<property name="defaultSchema">mydata</property>



</user>
<user name="user">
<property name="password">user</property>
<property name="schemas">mydata</property>
<property name="readOnly">true</property>
<property name="defaultSchema">mydata</property>
</user>
</mycat:server>

添加 slave 从服务器为读服务器

vim /usr/local/mycat/conf/schema.xml
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
<schema name="mydata" checkSQLschema="false" sqlMaxLimit="100" dataNode='dn1'>
</schema>
<dataNode name="dn1" dataHost="dthost" database="mydata"/>
<dataHost name="dthost" maxCon="500" minCon="10" balance="1" writeType="0" dbType="mysql" dbDriver="native" switchType="-1" slaveThreshold="100">
<heartbeat>show slave status</heartbeat>
<writeHost host="c7_2_3" url="192.168.2.3:3306" user="mycat" password="123456">
</writeHost>
<writeHost host="c7_2_5" url="192.168.2.5:3306" user="mycat" password="123456">
<readHost host="c7_2_6" url="192.168.2.6:3306" user="mycat" password="123456"></readHost>
</writeHost>
</dataHost>
</mycat:schema>

重启服务

mysql offset运行原理_Mycat_36

尝试登陆 Mycat
mysql -ugf -p -h192.168.2.2 -P8066
配置文件中指定了不需要密码登陆，直接回车即可

mysql offset运行原理_Mycat_37

查看读写记录
mysql -uroot -p123456 -h192.168.2.2 -P9066 -e "show @@datasource"

mysql offset运行原理_mysql offset运行原理_38

测试读写分离

mysql offset运行原理_数据库读写分离_39

mysql offset运行原理_mysql负载均衡_40

mysql offset运行原理_Mycat_41

mysql offset运行原理_mysql负载均衡_42

mysql offset运行原理_mysql负载均衡_43

mysql offset运行原理_mysql offset运行原理_44

mysql offset运行原理_mysql负载均衡_45

mysql offset运行原理_数据库读写分离_46

mysql offset运行原理_mysql offset运行原理_47

测试主库故障

mysql offset运行原理_mysql读写分离_48

mysql offset运行原理_数据库读写分离_49

mysql offset运行原理_Mycat_50

mysql offset运行原理_mysql负载均衡_51

mysql offset运行原理_数据库读写分离_52

mysql offset运行原理_mysql负载均衡_53

mysql offset运行原理_Mycat_54

Mysql日志类型

二进制日志
事务日志
一般查询日志
中继日志
慢查询日志

二进制日志

二进制日志通常记录的是可能潜在引起数据库发生改变的操作，每一个操作我们称为一个event。
二进制日志记录一个event的时候，通常还会记录timestamp,position(偏移量offset)，server-id，event本身。
二进制日志的数据存储形式，形如mysql-bin.xxxxxx这种，二进制日志除了mysql-bin.xxxxxx之外，还有mysql-bin.index(二进制日志索引文件)
二进制日志滚动条件：容量达到定义的最大上限，flush logs ,服务器重启
二进制日志删除：一般不建议用rm直接删除，建议用mysql的PURGE命令清除
语法：PURGE {MASTER | BINARY} LOGS TO 'log_name' # 删除指定的日志
PURGE {MASTER | BINARY} LOGS BEFORE 'date' # 删除日期之前的日志，BEFORE变量的date自变量可以为'YYYY-MM-DD hh:mm:ss'格式
如：（MASTER 和BINARY 在这里都是等效的）
PURGE MASTER LOGS TO 'test-bin.000001';
PURGE MASTER LOGS BEFORE '2011-01-0100:00:00';
二进制日志格式：statement(mysql官方不推荐)
row(mysql 5.6以后推荐)
mixed
二进制日志查看：
查看当前mysql使用的二进制文件及处在哪个position上：SHOW MASTER STATUS;
查看当前mysql上使用的二进制文件列表：SHOW BINARY LOGS;
查看某个二进制日志文件的具体内容：SHOW BINARY EVENTS IN 'mysql-bin.1234567';
二进制日志用途：
二进制日志可以即时点还原，因为二进制日志中记录的是潜在引起数据库发生改变的操作。要注意的是，通过二进制日志恢复的数据可能跟原始数据不一样，在多颗cpu并行工作的情况下，会同时执行多个事务，如果mysql的隔离级别较低，事务之间可能交叉执行(互相影响)，当前能够被使用的二进制日志只有一个，写入二进制日志的方式是串行写入，而事务是并行执行的，事务执行的次序和记录在二进制日志中的次序可能不一致。

Mysql隔离级别：(由低到高)

READ-UNCOMMITTED
READ-COMMITTED
REPEATABLE-READ(缺省隔离级别)
SERIALIZABLE
如果你的mysql隔离级别是REPEATABLE-READ，二进制日志格式是statement，在某些场景下绝对会出现通过二进制日志恢复的数据和原始数据不一致的情况。
如果你的mysql隔离级别是READ-COMMITED，二进制日志格式推荐row

主从复制原理

mysql offset运行原理_mysql读写分离_55

主从复制模式

mysql支持一主多从
同步: 确保event发送到所有的slave
半同步: 只要本地event写入本地的二进制日志文件中即可，但是不确保event一定发送到slave，它是不管的
异步: 确保event发送到其中一个slave
注：mysql 5.5 之前是不支持半同步的(google贡献的mysql半同步补丁)

多级复制

一个从可以是某个主的从，也可以是某个从的从。

mysql offset运行原理_Mycat_56

中继日志(relay log)不能发送给其它节点

复制的作用：

实现备份

高可用

异地容灾

分摊负载(scale out)

Mysql一主多从、读写分离架构

mysql offset运行原理_mysql负载均衡_57

分析：这种架构随着mysql从服务器的增加会消耗mysql主服务器的性能(cpu、IO、内存)，因为从服务器直接接受二进制日志中的event。有几个从服务器接受主服务器发送的event，相应地mysql主服务器就要启几个线程，这些mysql线程各自独立地从二进制日志中读取数据，读完后发送给对应的mysql从服务器，所以下面引入新架构。

Mysql一主多从、读写分离、多级复制架构

mysql offset运行原理_mysql offset运行原理_58

分析："主的从，从的主" mysql server实际上它只是起把二进制日志的event从主服务器发送到从服务器的中间件而已，实在没有必要把数据持久化存储下来，浪费IO。但是从中继日志中读取的event不在本地执行写入数据文件是不会记录到二进制日志文件中的，没有二进制日志就不能发送event给从服务器了。
解决思路:

mysql有种存储引擎叫black hole，功能类似于linux的/dev/null，"主的从，从的主" mysql 数据库使用black hole存储引擎，这样从中继日志中读取的event在本地执行完后，数据并没有保存下来。

mysql实际上支持双主模型
双主模型可以减轻读操作，但是无法减轻写操作,所有在第一个节点的写操作，第二个节点也同样要执行一遍，不然就出现数据不一致了。

一般来说，在生产环境中绝对不建议使用双主模型！

Mysql集群规模扩大

当一个服务器承受的压力过大的时候，两种方式提升它的性能：
scale on：纵向扩展，增加单台服务器的性能
scale out: 横向扩展，增加服务器分摊负载
当规模越来越大的时候，我们的主服务器怎么都无法承担写操作的时候，怎么办？
数据库服务器之所以压力大，那是因为库里面有很多张表，每个表可能都需要读写操作；双主也无法减轻写操作，需要垂直拆分(分库)，就是将一个大的数据库拆分成n个小的数据库，把那些查询(联合查询)或操作的时候相关联的表放在一起，每一个小的数据库放在一个物理服务器上；
当需要对某张表操作的时候，我们只需要联系这张表所在的数据库，但是垂直拆分治标不治本，数据库的数据也是有热区的，比如说我们有50G的数据，其中有2G的数据最繁忙，而这2G的数据都在同一张表上，这时候就需要水平拆分(分表)了，分表的目的就是把热区数据分开

mysql offset运行原理_数据库读写分离_59