数据库学习案例20240218- MySQL备库延迟较高分析处理

最新推荐文章于 2024-07-28 21:26:53 发布

执笔画情ora

最新推荐文章于 2024-07-28 21:26:53 发布

阅读量55

点赞数

分类专栏： mysql数据库高可用管理文章标签：数据库学习 mysql

原文链接：https://blog.csdn.net/wangkun_j/article/details/118669480

版权

mysql数据库高可用管理专栏收录该内容

16 篇文章 0 订阅

订阅专栏

1 双11配置

show variables like '%sync%'; 0

sync_binlog=0

show variables like '%commit%'; 0

innodb_flush_log_at_trx_commit=0

2 主键检查

在 MySQL 中，建表时一般都会要求有主键。若要求不规范难免会出现几张无主键的表，本篇文章让我们一起揪出那个无主键的表。

1.无主键表的危害
以 InnoDB 表为例，我们都知道，在 InnoDB 中，表都是根据主键顺序以索引的形式存放的，这种存储方式的表称为索引组织表。一张 InnoDB 表必须有一个聚簇索引，当有主键时，会以主键作为聚簇索引；如果没有显式定义主键，InnoDB 会选择一个唯一的非空索引代替。如果没有这样的索引，则 MySQL 自动为 InnoDB 表生成一个隐含字段作为主键。

也就是说，最好我们可以显式定义主键，那么无主键表可能会产生哪些危害呢？首先没有主键就意味着无法用到主键索引，可能影响查询效率。其次是对维护不友好，比如想升级为 MGR 集群或使用某些开源工具时，都会要求表要有主键。还有一点，对于无主键的表批量更新或删除，极易引起很长时间的主从延迟。

这里也顺便提下，当主库对于无主键表（特别是既无主键又无索引的表）大量更新或删除时，从库会发生极大的主从延迟，甚至会一直卡着执行不下去，别问我怎么知道的，前段时间遇到过。发生这种情况的现象是从库延迟不断增大，且正在执行的主库 binlog pos 位点一直不变，这个时候需要去主库解析下从库卡着的 binlog pos 位点，发现是对某个无主键表的操作，这时若想从库尽快赶上，可以手动设置下忽略该表的同步，处理 SQL 如下：

# 假设检查发现是 testtb 表导致了主从延迟可以再从库忽略该表的同步
mysql> STOP SLAVE SQL_THREAD;
Query OK, 0 rows affected (0.00 sec)

mysql> CHANGE REPLICATION FILTER REPLICATE_IGNORE_TABLE = (db.testtb);
Query OK, 0 rows affected (0.00 sec)

mysql> START SLAVE SQL_THREAD;
Query OK, 0 rows affected (0.01 sec)

忽略掉该表的同步后，从库很快就会追上主库了。后续可以为该表增加主键，然后再手动同步下并解除忽略即可。

2.找到无主键的表
言归正传，当我们的数据库实例中有好多好多张表时，又应该如何查找是否有无主键的表呢？总不能一个个找吧，聪明的你可能想到了，可以从 MySQL 自带的系统表中查找，因为我们的所有建表信息都存储在系统库 information_schema 中。下面 SQL 可以查找出无主键的表：

# 查找某个库中无主键的表（有唯一键无主键的表也会被查出）
SELECT
   t1.table_schema,
   t1.table_name
FROM
   information_schema.TABLES t1
LEFT OUTER JOIN information_schema.TABLE_CONSTRAINTS t2 ON t1.table_schema = t2.TABLE_SCHEMA
AND t1.table_name = t2.TABLE_NAME
AND t2.CONSTRAINT_NAME IN ('PRIMARY')
WHERE
   t2.table_name IS NULL
AND t1.table_type = 'BASE TABLE'
AND t1.TABLE_SCHEMA = 'testdb';

# 查找整个实例中无主键的表
SELECT
   t1.table_schema,
   t1.table_name
FROM
   information_schema.TABLES t1
LEFT OUTER JOIN information_schema.TABLE_CONSTRAINTS t2 ON t1.table_schema = t2.TABLE_SCHEMA
AND t1.table_name = t2.TABLE_NAME
AND t2.CONSTRAINT_NAME IN ('PRIMARY')
WHERE
   t2.table_name IS NULL
AND t1.table_type = 'BASE TABLE'
AND t1.TABLE_SCHEMA NOT IN (
   'information_schema',
   'performance_schema',
   'mysql',
   'sys'
);

找到了无主键的表，下一步就是为表新增主键了，无论你使用自增 id ，uuid ，或其他算法生成的主键字段，都建议为表新增主键。以自增 id 为例，我们可以为无主键的表这样新增主键：

# 为表 tb1 新增自增ID字段作为主键
ALTER TABLE tb1 ADD COLUMN inc_id INT UNSIGNED NOT NULL auto_increment COMMENT '自增主键' PRIMARY KEY FIRST;

# 查找到的无主键表拼接出新增主键的SQL
SELECT
CONCAT('ALTER TABLE ',t1.table_schema,'.',t1.table_name,' ADD COLUMN inc_id INT UNSIGNED NOT NULL auto_increment COMMENT \'自增主键\' PRIMARY KEY FIRST;')
FROM
   information_schema.TABLES t1
LEFT OUTER JOIN information_schema.TABLE_CONSTRAINTS t2 ON t1.table_schema = t2.TABLE_SCHEMA
AND t1.table_name = t2.TABLE_NAME
AND t2.CONSTRAINT_NAME IN ('PRIMARY')
WHERE
   t2.table_name IS NULL
AND t1.table_type = 'BASE TABLE'
AND t1.TABLE_SCHEMA NOT IN (
   'information_schema',
   'performance_schema',
   'mysql',
   'sys'
) ;

3 大事务排除

1 查看binlogs正在执行的大部分信息对应的表内容

show binlog events in 'binlog.00000001';

然后将表进行白名单配置

数据库层面白名单复制db111 db222

mysql> stop slave;
mysql> CHANGE REPLICATION FILTER REPLICATE_DO_DB = (db111, db222);

mysql> stop slave; #启动slave即可恢复主从同步

取消白名单

mysql> CHANGE REPLICATION FILTER REPLICATE_DO_DB = ();

2、db111、db222加入复制黑名单

mysql> stop slave;
mysql> CHANGE REPLICATION FILTER Replicate_Ignore_DB=(db111,db222);
mysql> start slave;

取消复制过滤

mysql> stop slave;
mysql> CHANGE REPLICATION FILTER Replicate_Ignore_DB=();
mysql> start slave;

3、db1.t1111%、db2.t2222%,加白db1中t1开头的表，拉黑db2中t2开头的表

mysql> stop slave;
mysql>CHANGE REPLICATION FILTER REPLICATE_WILD_DO_TABLE = ('db1.t1%'),REPLICATE_WILD_IGNORE_TABLE = ('db1111.t2222%');
mysql> start slave;