Mysql进阶

锁

介绍

在实际的数据库系统中，每时每刻都在发生锁定，当某个用户在修改一部分数据时，MySQL会通过锁定 防止其他用户读取同一数据。

在处理并发读或者写时，通过实现一个由两种类型的锁组成的锁系统来解决问题。两种锁通常被称为共 享锁(shared lock)和排他锁(exclusive lock)，也叫读锁(read lock)和写锁(write lock)。

读锁是共享的，是互相不阻塞的。多个客户端在同一时刻可以同时读取同一个资源，而不互相干扰。写 锁则是排他的，也就是说一个写锁会阻塞其他的写锁和读锁，这是出于安全策略的考虑，只有这样才能 确保在给定的时间里，只有一个用户能执行写入，并防止其他用户读取正在写入的同一资源。

锁分类

按锁粒度分：
全局锁：锁整Database，由MySQL的SQL layer层实现
表级锁：锁某Table，由MySQL的SQL layer层实现
行级锁：锁某Row的索引，也可锁定行索引之间的间隙，由存储引擎实现【InnoDB】

全局锁

全局锁是对整个数据库实例加锁，加锁后整个实例就处于只读状态，后续的DML的写语句，DDL语句， 已经更新操作的事务提交语句都将被阻塞。其典型的使用场景是做全库的逻辑备份，对所有的表进行锁 定，从而获取一致性视图，保证数据的完整性。

加全局锁的命令为：

flush tables with read lock;

释放全局锁的命令为：

unlock tables;

或者断开加锁session的连接，自动释放全局锁。

说到全局锁用于备份这个事情，还是很危险的。因为如果在主库上加全局锁，则整个数据库将不能写 入，备份期间影响业务运行，如果在从库上加全局锁，则会导致不能执行主库同步过来的操作，造成主 从延迟。

对于innodb这种支持事务的引擎，使用mysqldump备份时可以使用--single-transaction参数，利用 mvcc提供一致性视图，而不使用全局锁，不会影响业务的正常运行。而对于有MyISAM这种不支持事务 的表，就只能通过全局锁获得一致性视图，对应的mysqldump参数为--lock-all-tables。

表级锁

MySQL的表级锁有四种：

• 表读锁（Table Read Lock）
• 表写锁（Table Write Lock）
• 元数据锁（meta data lock，MDL)
• 自增锁(AUTO-INC Locks)

表锁相关命令

MySQL 实现的表级锁定的争用状态变量：

# 查看表锁定状态
mysql> show status like 'table%';

表锁有两种表现形式：

• 表读锁（Table Read Lock）
• 表写锁（Table Write Lock）

手动增加表锁：

lock table 表名称 read(write),表名称2 read(write)，其他;
# 举例：
lock table t read; #为表t加读锁
lock table t write; #为表t加写锁

查看表锁情况：

show open tables;

删除表锁：

unlock tables;

元数据锁

元数据锁不需要显式指定，在访问一个表的时候会被自动加上，锁的作用是保证读写的正确性。

可以想象一下：如果一个查询正在遍历一个表中的数据，而执行期间另一个线程对这个表结构做变更， 删了一列，那么查询线程拿到的结果跟表结构对不上，肯定是不行的。

因此，在 MySQL 5.5 版本中引入了元数据锁，当对一个表做增删改查操作的时候，加 元数据 读锁；当 要对表做结构变更操作的时候，加 元数据 写锁。

• 读锁是共享的，是互相不阻塞的：因此你可以有多个线程同时对一张表加读锁，保证数据在读取的 过程中不会被其他线程修改。
• 写锁则是排他的：也就是说一个写锁会阻塞其他的写锁和读锁，用来保证变更表结构操作的安全 性。因此，如果有两个线程要同时给一个表加字段，其中一个要等另一个执行完才能开始执行。

自增锁(AUTO-INC Locks)

AUTO-INC锁是一种特殊的表级锁，发生涉及AUTO_INCREMENT列的事务性插入操作时产生。

行级锁

MySQL的行级锁，是由存储引擎来实现的，这里我们主要讲解InnoDB的行级锁。InnoDB行锁是通过给 索引上的索引项加锁来实现的，因此InnoDB这种行锁实现特点：只有通过索引条件检索的数据， InnoDB才使用行级锁，否则，InnoDB将使用表锁！

如何加行级锁？

• 对于UPDATE、DELETE和INSERT语句，InnoDB会自动给涉及数据集加写锁；
• 对于普通SELECT语句，InnoDB不会加任何锁
• 事务可以通过以下语句手动给记录集加共享锁或排他锁。

CREATE TABLE `t1_simple` (
`id` int(11) NOT NULL,
`pubtime` int(11) NULL DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE,
INDEX `idx_pu`(`pubtime`) USING BTREE
) ENGINE = InnoDB;
INSERT INTO `t1_simple` VALUES (1, 10);
INSERT INTO `t1_simple` VALUES (4, 3);
INSERT INTO `t1_simple` VALUES (6, 100);
INSERT INTO `t1_simple` VALUES (8, 5);
INSERT INTO `t1_simple` VALUES (10, 1);
INSERT INTO `t1_simple` VALUES (100, 20);

添加读锁：

select * from t1_simple where id = 4 lock in share mode;

添加写锁：

select * from t1_simple where id = 4 for update; 

行锁四兄弟：记录、间隙、临键和插入意向锁

记录锁（Record Locks）仅仅锁住索引记录的一行，在单条索引记录上加锁。记录锁锁住的永远是索 引，而非记录本身，即使该表上没有任何显示索引，那么innodb会在后台创建一个隐藏的聚簇索引索 引，那么锁住的就是这个隐藏的聚簇索引索引。

-- 加记录读锁
select * from t1_simple where id = 1 lock in share mode;
-- 加记录写锁
select * from t1_simple where id = 1 for update;
-- 新增，修改，删除加记录写锁
insert into t1_simple values (1, 22);
update t1_simple set pubtime=33 where id =1;
delete from t1_simple where id =1;

间隙锁
（1）间隙锁(Gap Locks)，仅仅锁住一个索引区间（开区间，不包括双端端点）。
（2）在索引记录之间的间隙中加锁，或者是在某一条索引记录之前或者之后加锁，并不包括该索引记录 本身。
（3）间隙锁可用于防止幻读，保证索引间隙不会被插入数据。
（4）在可重复读（REPEATABLE READ）这个隔离级别下生效。

临键锁

（1）临键锁(Next-Key Locks)相当于记录锁 + 间隙锁【左开右闭区间】，例如（5,8]
（2）默认情况下，innodb使用临键锁来锁定记录，但在不同的场景中会退化
（3）当查询的索引含有唯一属性的时候，临键锁会进行优化，将其降级为记录锁，即仅锁住索引本身， 不是范围。

插入意向锁

什么是插入意向锁？
（1）插入意向锁（Insert Intention Locks）是一种在 INSERT 操作之前设置的一种特殊的间隙锁。
（2）插入意向锁表示了一种插入意图，即当多个不同的事务，同时往同一个索引的同一个间隙中插入数 据的时候，它们互相之间无需等待，即不会阻塞。
（3）插入意向锁不会阻止插入意向锁，但是插入意向锁会阻止其他间隙写锁（排他锁）、记录锁。

加锁规则

主键索引

• 等值条件，命中，加记录锁
• 等值条件，未命中，加间隙锁
• 范围条件，命中，包含where条件的临键区间，加临键锁
• 范围条件，没有命中，加间隙锁
  辅助索引
• 等值条件，命中，命中记录的辅助索引项 + 主键索引项加记录锁，辅助索引项两侧加间隙锁
• 等值条件，未命中，加间隙锁
• 范围条件，命中，包含where条件的临键区间加临键锁。命中记录的id索引项加记录锁
• 范围条件，没有命中，加间隙锁

数据库调优

为什么要进行MySQL数据库调优

什么影响数据库性能

数据库调优到底调什么

查看SQL执行计划【Explain】

MySQL 提供了一个 Explain 命令, 它可以对 SELECT 语句的执行计划进行分析，并输出 SELECT 执行的 详细信息，以供开发人员针对性优化。使用explain命令来查看该SQL语句有没有使用上了索引，有没有 做全表扫描等等。

EXPLAIN SELECT * FROM tb_seckill_goods

• id：SELECT识别符，这是SELECT查询序列号。
• select_type：表示单位查询的查询类型，比如：普通查询、联合查询(union、union all)、子查 询等复杂查询。
• table：表示查询的表
• partitions：使用的哪些分区（对于非分区表值为null）。
• type（重要）表示表的连接类型。
• possible_keys：此次查询中可能选用的索引
• key：查询真正使用到的索引
• key_len：显示MySQL决定使用的索引size
• ref：哪个字段或常数与 key 一起被使用
• rows：显示此查询一共扫描了多少行，这个是一个估计值，不是精确的值。
• filtered: 表示此查询条件所过滤的数据的百分比
• Extra：额外信息

select_type

type

显示的是单位查询的连接类型或者理解为访问类型，访问性能依次从好到差：

system
const
eq_ref
ref
fulltext
ref_or_null
unique_subquery
index_subquery
range
index_merge
index
ALL

注意事项：

• 除了all之外，其他的type都可以使用到索引
• 最少要使用到range级别

Extra

这个列包含不适合在其他列中显示的，但十分重要的额外的信息，这个列可以显示的信息非常多，有几 十种。解释几个经常遇到的

索引优化

上面mysql高级里面写了

LIMIT优化

如果预计SELECT语句的查询结果是一条，最好使用 LIMIT 1，可以停止全表扫描。

SELECT * FROM user WHERE username=’it雄’; -- username没有建立唯一索引
SELECT * FROM user WHERE username=’it雄’ LIMIT 1;

处理分页会使用到 LIMIT ，当翻页到非常靠后的页面的时候，偏移量会非常大，这时LIMIT的效率会非 常差。 LIMIT OFFSET , SIZE；
LIMIT的优化问题，其实是 OFFSET 的问题，它会导致MySQL扫描大量不需要的行然后再抛弃掉。

子查询优化

MySQL从4.1版本开始支持子查询，使用子查询进行SELECT语句嵌套查询，可以一次完成很多逻辑上需 要多个步骤才能完成的SQL操作。子查询虽然很灵活，但是执行效率并不高。

执行子查询时，MYSQL需要创建临时表，查询完毕后再删除这些临时表，所以子查询的速度会受到一定 的影响。这多了一个创建临时表和销毁表的过程。

优化方式：可以使用连接查询（JOIN）代替子查询，连接查询时不需要建立临时表，其速度比子查询 快

其他查询优化

SQL语句性能分析

Query Profiler是MySQL自带的一种Query诊断分析工具，通过它可以分析出一条SQL语句的硬件性能 瓶颈在什么地方。通常我们是使用的explain，以及slow query log都无法做到精确分析，但是Query Profiler却可以定位出一条SQL语句执行的各种资源消耗情况，比如CPU，IO等，以及该SQL执行所耗 费的时间等。

不过该工具只有在MySQL 5.0.37以及以上版本中才有实现。默认的情况下，MySQL的该功能没有打 开，需要自己手动启动。

开启Profile功能

Profile 功能由MySQL会话变量 : profiling控制，默认是OFF关闭状态。
查看是否开启了Profile功能:

select @@profiling;
-- 或者
show variables like '%profil%';

开启profile功能

-- 1是开启、0是关闭
set profiling=1;

语法

SHOW PROFILE [type [, type] ... ]
[FOR QUERY n]
[LIMIT row_count [OFFSET offset]]
type: {
ALL
| BLOCK IO
| CONTEXT SWITCHES
| CPU
| IPC
| MEMORY
| PAGE FAULTS
| SOURCE
| SWAPS
}

• show profile 和 show profiles 语句可以展示当前会话(退出session后,profiling重置为0) 中执行 语句的资源使用情况.
• show profiles :以列表形式显示最近发送到服务器上执行的语句的资源使用情况，显示的记录数由 变量:profiling_history_size 控制,默认15条
• show profile: 展示最近一条语句执行的详细资源占用信息,默认显示 Status和Duration两列
• show profile 还可根据 show profiles 列表中的 Query_ID ,选择显示某条记录的性能分析信息

-- 查看某条SQL的性能分析信息
show profile for query 1;
-- 查看某条SQL的具体某个指标的性能分析
show profile cpu for query 1;

分析案例
查看是否打开了性能分析功能

select @@profiling;

打开 profiling 功能

set profiling=1;

执行 show profiles 查看分析列表

查询第二条语句的执行情况

show profile for query 2;

慢查询日志

数据库性能问题，根据经验来说，80%以上都是由于慢SQL造成的。
数据库查询快慢是影响项目性能的一大因素，对于数据库，我们除了要优化 SQL，更重要的是得先找到 需要优化的SQL。
MySQL数据库“慢查询日志”功能，用来记录查询时间超过某个设定值的SQL语句，这将极大程度帮助我 们快速定位到症结所在，以便对症下药。至于查询时间的多少才算慢，每个项目、业务都有不同的要 求。
MySQL的慢查询日志功能默认是关闭的，需要手动开启

开启慢查询日志

# 查看是否开启慢查询日志
show variables like '%slow_query%';
show variables like 'long_query_time%';

开启慢查询功能

注意：打开慢查询日志可能会对系统性能有一点点影响，如果你的MySQL是主从结构，可以考虑打开其 中一台从服务器的慢查询日志，这样既可以监控慢查询，对系统性能影响又小。

# 开启慢查询日志
set global slow_query_log=on;
# 大于1秒钟的数据记录到慢日志中，如果设置为默认0，则会有大量的信息存储在磁盘中，磁盘很容易满
掉
# 如果设置不生效，建议配置在my.cnf配置文件中
set global long_query_time=1;
# 记录没有索引的查询。
set global log_queries_not_using_indexes=on;

慢查询日志格式

# Time: 2021-07-27T08:32:44.023309Z
# User@Host: root[root] @ [172.26.233.201] Id: 1243
# Query_time: 218.295526 Lock_time: 0.000126 Rows_sent: 10959
Rows_examined: 10929597
use hero_all;
SET timestamp=1627374764;
# 慢查询SQL语句
select tk.id,ts.* from tb_seckill_goods ts LEFT JOIN tb_sku tk ON
tk.id=ts.id where ts.id>100 order by ts.price;

分析慢查询日志工具

使用mysqldumpslow工具，mysqldumpslow是MySQL自带的慢查询日志工具。可以使用 mysqldumpslow工具搜索慢查询日志中的SQL语句。
得到按照时间排序的前10条里面含有左连接的查询语句：

[root@localhost mysql]# mysqldumpslow -s t -t 10 -g "left join"
/var/lib/mysql/slow.log

[root@localhost ~]# mysqldumpslow -s t /var/lib/mysql/localhost-slow.log
Reading mysql slow query log from /var/lib/mysql/localhost-slow.log
Count: 1 Time=77.12s (77s) Lock=0.00s (0s) Rows=0.0 (0),
root[root]@[192.168.200.1]
select tk.id,ts.* from tb_seckill_goods ts LEFT JOIN tb_sku tk ON
tk.id=ts.id where ts.id>N order by ts.price
Count: 1 Time=2.00s (2s) Lock=0.00s (0s) Rows=1.0 (1),
root[root]@[192.168.200.1]
select sleep(N)

数据库优化-结构优化

• 将字段很多的表分解成多个表（分表）
  对于字段较多的表，如果有些字段的使用频率很低，可以将这些字段分离出来形成新表。因为当一个表 的数据量很大时，会由于使用频率低的字段的存在而变慢。
  项目实战的时候会将一个完全信息的表里面的数据拆分出来 形成多个新表 每个新表负责那一块的数据查 询。

• 增加中间表
  对于需要经常联合查询的表，可以建立中间表以提高查询效率。通过建立中间表，将需要通过联合查询 的数据插入到中间表中，然后将原来的联合查询改为对中间表的查询。
  通常都是在统计当中使用，每次统计报表的时候都是离线统计，后台有有一个线程对你这统计查询结果 放入一个中间表，然后你对这个中间表查询。

• 增加冗余字段

设计数据表时应尽量遵循关系数据库范式的规约，尽可能的减少冗余字段，让数据库设计看起来精致、 优雅。但是合理的加入冗余字段可以提高查询速度。
表的规范化程度越高，表和表之间的关系越多，需要连接查询的情况也就越多，性能也就越差。

注意：冗余字段的值在一个表中修改了，就要想办法在其他表中更新，否则就会导致数据不一致的问 题。

服务器层面优化

缓冲区优化
降低磁盘写入次数

服务器硬件优化

提升硬件设备，例如选择尽量高频率的内存（频率不能高于主板的支持）、提升网络带宽、使用SSD高 速磁盘、提升CPU性能等。

集群搭建之主从复制

主从复制作用

通过搭建MySQL主从集群，可以缓解MySQL的数据存储以及访问的压力

1. 数据安全（主备）：给主服务增加一个数据备份。基于这个目的，可以搭建主从架构，或者也可以 基于主从架构搭建互主的架构。
2. 读写分离（主从）：对于大部分的Java业务系统来说，都是读多写少的，读请求远远高于写请求。 这时，当主服务的访问压力过大时，可以将数据读请求转为由从服务来分担，主服务只负责数据写 入的请求，这样大大缓解数据库的访问压力。
3. 故障转移-高可用：当MySQL主服务宕机后，可以由一台从服务切换成为主服务，继续提供数据读 写功能。
   对于高可用架构，主从数据的同步也只是实现故障转移的一个前提条件，要实现MySQL主从切换， 还需要依靠一些其他的中间件来实现。比如MMM、MHA、MGR。

在一般项目中，如果数据库的访问压力没有那么大，那读写分离不一定是必须要做的，但是，主从架构 和高可用架构则是必须要搭建的。

主从复制原理

MySQL服务的主从架构都是通过 binlog 日志文件来进行的。

具体流程如下：

1. 在主服务上打开binlog记录每一步的数据库操作
2. 然后，从服务上会有一个IO线程，负责跟主服务建立一个TCP连接，请求主服务将binlog传输过来
3. 这时，主库上会有一个IO dump线程，负责通过这个TCP连接把binlog日志传输给从库的IO线程
4. 主服务器MySQL服务将所有的写操作记录在 binlog 日志中，并生成 log dump 线程，将 binlog 日 志传给从服务器MySQL服务的 I/O 线程。
5. 接着从服务的IO线程会把读取到的binlog日志数据写入自己的relay日志文件中。
6. 然后从服务上另外一个SQL线程会读取relay日志里的内容，进行操作重演，达到还原数据的目的。

注意：

1. 主从复制是异步的逻辑的 SQL 语句级的复制
2. 复制时，主库有一个 I/O 线程，从库有两个线程，即 I/O 和 SQL 线程
3. 实现主从复制的必要条件是主库要开启记录 binlog 的功能
4. 作为复制的所有 MySQL 节点的 server-id 都不能相同
5. binlog 文件只记录对数据内容有更改的 SQL 语句，不记录任何查询语句
6. 双方MySQL必须版本一致，至少需要主服务的版本低于从服务
7. 两节点间的时间需要同步

主从复制形式：

1. 一主一从
2. 主主复制
3. 一主多从
4. 多主一从
5. 级联复制

3）一主多从

binlog和relay日志

• binlog：二进制日志，将数据改变记录到二进制（binary）日志中，可用于本机数据恢复和主从同 步。
• relaylog：中继日志，Slave节点会把中继日志中的事件信息一条一条的在本地执行一次，实现主 从同步这个过程也叫数据重放。

binlog的三种模式

开启binlog

修改my.cnf文件
在[mysqld]段下添加：

# binlog刷盘策略
sync_binlog=1
# 需要备份的数据库
binlog-do-db=hello
# 不需要备份的数据库
binlog-ignore-db=mysql
# 启动二进制文件
log-bin=mysql-bin
# 服务器ID
server-id=132
#只保留7天的二进制日志，以防磁盘被日志占满(可选)
expire-logs-days=7

类似redo日志的刷盘机制，如下图：

调整binlog日志模式

查看binlog的日志模式：

mysql> show variables like 'binlog_format';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW |
+---------------+-------+
1 row in set (0.00 sec)

调整binlog的日志模式：binlog的三种格式： STATEMENT 、 ROW 、 MIXED 。

mysql> set binlog_format=STATEMENT;
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like 'binlog_format';
+---------------+-----------+
| Variable_name | Value |
+---------------+-----------+
| binlog_format | STATEMENT |
+---------------+-----------+
1 row in set (0.00 sec)

如何查看binlog和relaylog日志

方式一：使用mysqlbinlog查看binlog日志文件

因为binlog日志文件：mysql-bin.000005是二进制文件，没法用vi等打开，这时就需要mysql的自带的 mysqlbinlog工具进行解码，执行： mysqlbinlog mysql-bin.000005 可以将二进制文件转为可阅读的 sql语句。

mysqlbinlog --base64-output=decode-rows -v -v mysql-bin.000001 > binlog.txt

方式二：在MySQL终端查看binlog

show master logs ，查看所有二进制日志列表 ，和 show binary logs 同义。

mysql> show master logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 385 |
+------------------+-----------+
1 row in set (0.00 sec)

使用 show binlog events 命令可以以列表的形式显示日志中的事件信息。

show binlog events命令的格式：

show binlog events [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count];

切换binlog文件：

mysql> flush logs;
Query OK, 0 rows affected (0.00 sec)

注意：刷新日志会生成一个新的日志文件

基于Pos主从复制

开放端口

需要将3306端口放行，如果是内网也可关闭防火墙

主服务器配置
查看binlog是否开启可以使用命令：

mysql> show variables like 'log_bin%';
+---------------------------------+-------+
| Variable_name | Value |
+---------------------------------+-------+
| log_bin | OFF |
| log_bin_basename | |
| log_bin_index | |
| log_bin_trust_function_creators | OFF |
| log_bin_use_v1_row_events | OFF |
+---------------------------------+-------+
5 rows in set (0.12 sec)

log_bin如果是 OFF 代表是未开启状态

第一步：修改my.cnf文件

在[mysqld]段下添加：

# binlog刷盘策略
sync_binlog=1
# 需要备份的数据库
binlog-do-db=hello
# 不需要备份的数据库
binlog-ignore-db=mysql
# 启动二进制文件
log-bin=mysql-bin
# 服务器ID
server-id=132

第二步：重启mysql服务

systemctl restart mysqld

第三步：主机给从机授备份权限

mysql>GRANT REPLICATION SLAVE ON *.* TO '从机MySQL用户名'@'从机IP' identified
by '从机MySQL密码';
GRANT REPLICATION SLAVE ON *.* TO 'root'@'%' identified by 'root';

注意：一般不用root帐号，“%”表示所有客户端都可能连，只要帐号，密码正确，此处可用具体客户端 IP代替，如39.99.131.178，加强安全。

第四步：刷新权限

mysql> FLUSH PRIVILEGES;

第五步：查询master的状态

mysql> show master status;
+------------------+----------+--------------+------------------+------------
-------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
Executed_Gtid_Set |
+------------------+----------+--------------+------------------+------------
-------+
| mysql-bin.000002 | 593 | hello | mysql |
|
+------------------+----------+--------------+------------------+------------
-------+
1 row in set

从服务器配置

第一步：修改my.conf文件

[mysqld]
server-id=133

第二步：重启mysqld服务

systemctl restart mysqld

第三步：重启并登录到MySQL进行配置Slave

mysql>change master to
master_host='172.17.187.78',
master_port=3306,
master_user='root',
master_password='root',
master_log_file='mysql-bin.000001',
master_log_pos=1109,
MASTER_AUTO_POSITION=0;

注意：语句中间不要断开， master_port 为MySQL服务器端口号（无引号）， master_user 为 执行同步操作的数据库账户， 593 无单引号（此处的 1109 就是 show master status 中看到的 position 的值，这里的 mysql-bin.000001 就是 file 对应的值）。

第四步：启动从服务器复制功能

mysql>start slave;

第五步：检查从服务器复制功能状态

mysql> show slave status \G;
……………………(省略部分)
Slave_IO_Running: Yes //此状态必须YES
Slave_SQL_Running: Yes //此状态必须YES
……………………(省略部分)

注：Slave_IO及Slave_SQL进程必须正常运行，即YES状态，否则都是错误的状态(如：其中一个NO均 属错误)。

搭建成功之后，往主机中插入数据，看看从机中是否有数据

基于GTID的主从复制

从 MySQL 5.6.5 开始新增了一种基于 GTID 的复制方式。GTID即全局事务ID （Global Transaction Identifier），其保证每个主节点上提交的事务，在从节点可以一致性的复制。

这种方式强化了数据库的主备一致性，故障恢复以及容错能力。GTID在一主一从情况下没有优势，对于 两主以上的结构优势异常明显，可以在数据不丢失的情况下切换新主。

GTID实际上是由UUID+TID (即transactionId)组成的，其中UUID(即server_uuid) 产生于auto.conf文 件，是一个MySQL实例的唯一标识。TID代表了该实例上已经提交的事务数量，并且随着事务提交单调 递增，所以GTID能够保证每个MySQL实例事务的执行。GTID在一组复制中，全局唯一。 通过GTID的 UUID可以知道这个事务在哪个实例上提交的。

GTID 主从复制原理

通过GTID可以很方便的进行复制结构上的故障转移，新主设置，这就很好地解决了下面这个图所展现出 来的问题。

注意：在构建主从复制之前，在一台将成为主的实例上进行一些操作（如：数据清理等），通过 GTID复制，这些在主从成立之前的操作也会被复制到从服务器上，引起复制失败。也就是说通过 GTID复制都是从最先开始的事务日志开始，即使这些操作在复制之前执行。比如在server1上执行 一些drop、delete的清理操作，接着在server2上执行change的操作，会使得server2也进行 server1的清理操作。

搭建GTID同步集群

他的搭建方式跟我们上面的主从架构整体搭建方式差不多。只是需要在my.cnf中修改一些配置。

1）主节点

gtid_mode=on
enforce_gtid_consistency=on
# 强烈建议，其他格式可能造成数据不一致
binlog_format=row

2）从节点

gtid_mode=on
enforce_gtid_consistency=on
# 做级联复制的时候，再开启。允许下端接入slave
log_slave_updates=1

3）使用GTID的方式，salve重新挂载master端：

启动以后最好不要立即执行事务，先change master上，然后在执行事务。
使用下面的sql切换slave到新的master。

# 停止从节点
stop slave;
# 切换主节点配置，比基于pos简单不少
change master to
master_host='172.17.187.78',
master_port=3306,
master_user='root',
master_password='root',
master_auto_position=1;
# 启动从节点
start slave;

4）启动GTID的两种情况

分别重启主服务和从服务，就可以开启GTID同步复制，启动方法有两种情况

情况一：如果是新搭建的服务器，直接启动就行了
情况二：如果是在已经跑的服务器，需要重启mysqld

• 启动之前要先关闭master的写入，保证所有slave端都已经和master端数据保持同步

• 所有slave需要加上skip_slave_start=1的配置参数，避免启动后还是使用老的复制协议

# 避免启动后还是使用老的复制协议
skip_slave_start=1

5）测试

show master status;

异步复制

MySQL主从集群默认采用的是一种异步复制的机制。
主服务在执行用户提交的事务后，写入binlog日志，然后就给客户端返回一个成功的响应了。而binlog 会由一个dump线程异步发送给Slave从服务。由于这个发送binlog的过程是异步的。主服务在向客户端 反馈执行结果时，是不知道binlog是否同步成功了的。这时候如果主服务宕机了，而从服务还没有备份 到新执行的binlog，那就有可能会丢数据。

这就要靠MySQL的半同步复制机制来保证数据安全

半同步复制

半同步复制机制是一种介于异步复制和全同步复制之前的机制。
主库在执行完客户端提交的事务后，并不是立即返回客户端响应，而是等待至少一个从库接收并写到 relaylog中，才会返回给客户端。
MySQL在等待确认时，默认会等 10 秒，如果超过10秒没有收到ack，就会降级成为 异步复制 。

这种半同步复制相比异步复制，能够有效的提高数据的安全性。但是这种安全性也不是绝对的，他只保 证事务提交后的binlog至少传输到了一个从库，且并不保证从库应用这个事务的binlog是成功的。另一 方面，半同步复制机制也会造成一定程度的延迟，这个延迟时间最少是一个TCP/IP请求往返的时间。整 个服务的性能是会有所下降的。而当从服务出现问题时，主服务需要等待的时间就会更长，要等到从服 务的服务恢复或者请求超时才能给用户响应。

主从同步延迟的原因及解决办法

原因

在我们搭建的这个主从集群中，有一个比较隐藏的问题，就是这样的主从复制之间会有延迟。这在做了 读写分离后，会更容易体现出来。即数据往主服务写，而读数据在从服务读。这时候这个主从复制延迟 就有可能造成刚插入了数据但是查不到。当然，这在我们目前的这个集群中是很难出现的，但是在大型 集群中会很容易出现。

解决办法

判断主从延迟

MySQL提供了从服务器状态命令，可以通过 show slave status 进行查看， 比如可以看看 Seconds_Behind_Master参数的值来判断，是否有发生主从延时。

读写分离

Mycat是一个数据库中间件，支持读写分离、分库分表、还支持水平分片与垂直分片。

• 水平分片：一个表格的数据分割到多个节点上，按照行分割
• 垂直分片：一个数据库中多个表格A，B，C，A存储到节点1上，B存储到节点2上，C存储到节点3 上。

下载安装

注意：需要jdk

下载Mycat

wget http://dl.mycat.org.cn/1.6.7.1/Mycat-server-1.6.7.1-release-
20190627191042-linux.tar.gz   

解压缩

tar -zxf Mycat-server-1.6.7.1-release-20190627191042-linux.tar.gz

进行mycat/bin目录，启动Mycat

/root/mycat/bin/mycat start
/root/mycat/bin/mycat stop
/root/mycat/bin/mycat restart
/root/mycat/bin/mycat status

访问Mycat

使用MySQL的客户端直接连接mycat服务。默认服务端口为【 8066 】。

mysql -uroot -p123456 -h127.0.0.1 -P8066

配置Mycat

1）配置端口和密码

修改server.xml，配置端口和密码：

<!--修改mycat服务端口-->
<property name="serverPort">8067</property>
<user name="root" defaultAccount="true">
<property name="password">123456</property>
<property name="schemas">mycat</property>
</user>
<user name="user">
<property name="password">user</property>
<property name="schemas">mycat</property>
<property name="readOnly">true</property>
</user>

2）配置读写分离

修改schema.xml，配置读写分离：

<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
<schema name="mycat" checkSQLschema="false" sqlMaxLimit="100">
<table name="tb_user" dataNode="dn1" />
<table name="t1" dataNode="dn1" />
<table name="t2" dataNode="dn1" />
<table name="t3" dataNode="dn1" />
</schema>
<dataNode name="dn1" dataHost="host1" database="mycat" />
<dataHost name="host1" maxCon="1000" minCon="10" balance="1"
writeType="0" dbType="mysql" dbDriver="native" switchType="1"
slaveThreshold="100">
<heartbeat>select user()</heartbeat>
<!-- can have multi write hosts -->
<writeHost host="hostM1" url="123.57.135.5:3306" user="root"
password="root">
<!-- can have multi read hosts -->
<!--<readHost host="hostS2" url="47.95.211.46:3306" user="root"
password="root" />-->
</writeHost>
<!-- <writeHost host="hostM2" url="localhost:3316" user="root"
password="123456"/> -->
<writeHost host="hostM2" url="47.95.211.46:3306" user="root"
password="root"/>
</dataHost>
</mycat:schema>

读写分离测试

1）创建表

use mycat;
CREATE TABLE tb_user (
login_name VARCHAR ( 32 ) COMMENT '登录名',
user_id BIGINT COMMENT '用户标识',
TYPE INT COMMENT '用户类型 1 商家，2买家',
passwd VARCHAR ( 128 ) COMMENT '密码',
PRIMARY KEY ( user_id )
);

2）插入数据与查询数据

# 新增
INSERT INTO `tb_user`(`login_name`,`user_id`,`TYPE`,`passwd`) VALUES ('name-
1',22,1,'passwd-A');
INSERT INTO `tb_user`(`login_name`,`user_id`,`TYPE`,`passwd`) VALUES ('name-
2',22,1,'passwd-A');
# 查
select * from tb_user;

3）测试

SELECT @@server_id;