mysql table derived2 还能优化吗 mysql lead lag

转载

mob64ca1400bfa8 2023-11-01 19:28:24

文章标签 mysql 数据库 sql 数据 SQL 文章分类 MySQL 数据库

一、mysql三级级联模式

现在用server1作为主机，server2作为server1的从机，server3作为server2的从机，也就是说server2既是主机也是从机。

show processlist：显示正在运行的线程；

binlog dump线程 :把主的mysql的二进制日志（当变更时）发送给slave

mysql table derived2 还能优化吗 mysql lead lag_SQL

MySQL复制技术中，涉及到三个线程，分别为binlog Dump线程，IO线程，SQL回放线程；

I/O线程
IO线程位于slave端，其作用就是作为一个客户端，建立到master实例上的TCP链接，并且发送认证信息，binlog同步协议信息，然后实时的去同步master发送的binlog，并且设置有超时时间，为slave_net_timeout，如果在超过设置时间内没有收到master发送的日志信息，则认为主从之间的网络异常，或者master异常，进行网络重试。其生命周期开始于start slave或者start slave io_thread,结束于stop slave或者stop slave io_thread;
Dump线程
Dump线程位于master实例，此线程与普通线程类似，只不过接收到的是slave发送的binlog dump命令，所以，会发送binlog，而不是发送其他的数据给slave端。其生命周期开始于slave的start slave或者start slave io_thread,结束于stop slave或者stop slave io_thread。
SQL线程
SQL回放线程位于slave实例，主要作用就是读取relay log，进行回放操作。并且将回放的日志进行存储，这样其余slave 就可以从该slave上取数据。其生命周期开始于start slave或者start slave sql_thread,结束于stop slave或者stop slave sql_thread;

mysql table derived2 还能优化吗 mysql lead lag_数据库_02

查看slave的状态

mysql table derived2 还能优化吗 mysql lead lag_SQL_03

同样的，首先将server1主机的/usr/local/mysql安装目录、/etc/my.cnf配置文件、/etc/init.d/启动文件发送给server3

mysql table derived2 还能优化吗 mysql lead lag_数据库_04

mysql table derived2 还能优化吗 mysql lead lag_数据_05

server3把mysql命令加入全局环境变量

mysql table derived2 还能优化吗 mysql lead lag_SQL_06

mysql table derived2 还能优化吗 mysql lead lag_mysql_07

使修改后的配置文件生效

mysql table derived2 还能优化吗 mysql lead lag_SQL_08

server3修改mysql的配置文件 /etc/my.cnf；

mysql table derived2 还能优化吗 mysql lead lag_SQL_09

server-id：设置本机的服务id号为3；

log-slave-updates 参数默认时关闭的状态，如果不手动设置，那么bin-log只会记录直接在该库上执行的SQL语句，由replication机制的SQL线程读取relay-log而执行的SQL语句并不会记录到bin-log，那么就无法实现上述的三级级联同步（server1<—>server2<—>server3）。

mysql table derived2 还能优化吗 mysql lead lag_mysql_10

server3创建用户，创建数据库存储目录，修改所属人和组;

数据库初始化，在/data/mysql生成文件;

开启数据库

mysql table derived2 还能优化吗 mysql lead lag_数据_11

数据库安全初始化

mysql table derived2 还能优化吗 mysql lead lag_数据库_12

测试server3可以进入数据库

mysql table derived2 还能优化吗 mysql lead lag_数据库_13

server2修改mysql的配置文件 /etc/my.cnf；

mysql table derived2 还能优化吗 mysql lead lag_mysql_14

开启log-slave-updates；

从库做为其他从库的主库时 log-slave-updates参数是必须要添加的，因为从库要作为其他从库的主库。该参数就是为了让从库从主库复制数据时可以写入到binlog日志。从库还需要开启log-bin参数，如果直接往从库写数据，是可以记录log-bin日志的，但是从库通过Io线程读取主库二进制日志文件，然后通过SQL线程写入的数据，是不会记录binlog日志的。也就是说从库从主库上复制的数据，是不写入从库的binlog日志的。所以从库做为其他从库的主库时需要在配置文件中添加log-slave-updates参数。

mysql table derived2 还能优化吗 mysql lead lag_SQL_15

更改完配置文件，需要重启mysql服务

mysql table derived2 还能优化吗 mysql lead lag_数据_16

可以看到server2生成了mysql-bin.000001二进制日志文件

mysql table derived2 还能优化吗 mysql lead lag_sql_17

server2授予slave主机REPLICATION SLAVE权限，可以使用repl这个用户名和westos这个密码进行连接数据库;

mysql table derived2 还能优化吗 mysql lead lag_sql_18

测试server3可以连接server2的数据库

mysql table derived2 还能优化吗 mysql lead lag_mysql_19

查看到server2的日志记录位置为154

mysql table derived2 还能优化吗 mysql lead lag_数据库_20

server3进入数据库，进行CHANGE MASTER TO操作，以确定需要同步的主机IP，用户名，密码，binlog文件，binlog位置等信息；

启用slave

mysql table derived2 还能优化吗 mysql lead lag_sql_21

可以看到server3的master是server2

mysql table derived2 还能优化吗 mysql lead lag_sql_22

二、Gtid实现主从复制

GTID即全局事务ID (global transaction identifier), 其保证为每一个在主上提交的事务在复制集群中可以生成一个唯一的ID;

1、GTID的概述：
一个GTID在一个服务器上只执行一次，避免重复执行导致数据混乱或者主从不一致。
GTID用来代替classic的复制方法，不在使用binlog+pos开启复制。而是使用master_auto_postion=1的方式自动匹配GTID断点进行复制。
在传统的slave端，binlog是不用开启的，但是在GTID中，slave端的binlog是必须开启的，目的是记录执行过的GTID（强制）。

GTID事务：在二进制日志中每个GTID事务始终都以Gtid_log_event开头。可以使用GTID或使用文件名和位置来定位GTID事务。
GTID出现之前，在一主多从的复制拓扑中，如果主库宕机，需要从多个从库选择之一作为新主库，这个过程比较复杂。没有一种直接了当的方法找到其它从库对应的新主库二进制日志坐标。通常的做法是先要寻找每个从库复制原主库的最后语句，然后找到新主库中包含该语句的二进制日志文件，其中该语句后的第一个事件位置即为连接新主库的二进制坐标。主要难点在于不存在一个唯一标识指出“复制原主库的最后语句”，于是后来的MySQL中就出现了GTID的概念。

server1修改配置文件

mysql table derived2 还能优化吗 mysql lead lag_数据_23

启用enforce_gtid_consistency功能的时候，MySQL只允许能够保障事务安全，并且能够被日志记录的SQL语句被执行

mysql table derived2 还能优化吗 mysql lead lag_数据库_24

重启mysql

mysql table derived2 还能优化吗 mysql lead lag_SQL_25

server2修改mysql的配置文件 /etc/my.cnf；

mysql table derived2 还能优化吗 mysql lead lag_SQL_26

重启mysql

mysql table derived2 还能优化吗 mysql lead lag_数据库_27

server2进入数据库，先停止slave；

由于使用gtid，不需要指定日志文件和pos，MASTER_AUTO_POSITION = 1直接启用基于GTID的复制

mysql table derived2 还能优化吗 mysql lead lag_数据_28

开启slave后，查看状态，显示成功

mysql table derived2 还能优化吗 mysql lead lag_sql_29

server3修改配置文件

mysql table derived2 还能优化吗 mysql lead lag_SQL_30

mysql table derived2 还能优化吗 mysql lead lag_数据_31

重启mysql

mysql table derived2 还能优化吗 mysql lead lag_数据库_32

server3进入数据库，先停止slave；

由于使用gtid，直接启用基于GTID的复制

mysql table derived2 还能优化吗 mysql lead lag_数据_33

开启slave后，查看状态，显示成功

mysql table derived2 还能优化吗 mysql lead lag_sql_34

测试，server1在user_tb表里面插入数据

mysql table derived2 还能优化吗 mysql lead lag_sql_35

server2可以查看到同步过来的数据

mysql table derived2 还能优化吗 mysql lead lag_mysql_36

server3要想看到同步的server2的数据，首先要建立westos库

mysql table derived2 还能优化吗 mysql lead lag_SQL_37

备份server1的westos库的数据，由于我们做主从用了gtid，所以用mysqldump备份时就要加–set-gtid-purged=OFF，这样才会记录二进制日志

mysql table derived2 还能优化吗 mysql lead lag_SQL_38

server3将备份的文件导入westos数据库

mysql table derived2 还能优化吗 mysql lead lag_数据库_39

此时，server也可以看到同步过来的数据

mysql table derived2 还能优化吗 mysql lead lag_数据库_40

q同步的是server2的数据（二进制日志）

mysql table derived2 还能优化吗 mysql lead lag_数据库_41

server1再添加一条数据

mysql table derived2 还能优化吗 mysql lead lag_数据_42

仍然可以同步成功

mysql table derived2 还能优化吗 mysql lead lag_数据库_43

MySQL的主从复制有三种复制方式：分别是同步复制、异步复制和半同步复制。

mysql主从同步复制：数据完整性好，但是性能消耗高；
mysql默认是主从异步复制：性能消耗低，但是容易出现主从数据唯一性问题。在异步复制中，主库执行完操作，写入binlog日志后，就返回客户端，这一动作就结束了，并不会验证从库有没有收到，所以这样可能会造成数据的不一致。也就是说，复制过程中数据是否一致，主要取决于Binlog日志的安全性与完整性
在MySQL中，有sync_binlog=n这一参数，他的值表示每进行n次事务提交，MySQL就将Binlog刷新到磁盘。如果这个值为1，就代表每提交一次事务（SQL），就将Binlog往磁盘刷新一次，这样一来，就算数据库宕机了，那么最多只能损失一次事务的数据。
但是，一旦多个事务并发提交时，由于受sync_binlog的限制，MySQL只能按顺序来处理这些请求，另外，高频率的刷新binlog对IO的影响也很大，进一步影响了数据库的性能，所以，一般这个值都设为0或者其他值，在数据的安全性和高并发下的性能之间取得一个平衡。
为了更加有效的保护Binlog的安全性和完整性，MySQL5 .5之后引入了半同步复制