mysql在线表设计

转载

lemon 2024-11-30 12:12:00

文章标签 mysql在线表设计数据库 mysql dba 触发器 文章分类 MySQL 数据库

前言

1.变更前查看表的量级

select table_schema, table_name, table_rows, round(data_length / 1024 / 1024) DATA_MB, round(index_length / 1024 / 1024) INDEX_MB, round(data_free / 1024 / 1024) FREE_MB, round(data_length / 1024 / 1024)+round(index_length /
1024 / 1024)+round(data_free / 1024 / 1024) TOTAL_MB from information_schema.tables where table_name = 'sbtest1' and table_schema = 'sbsmall';

2.查询长事务是否存在

SELECT 
    trx.trx_id, 
    CONCAT(trx.trx_started, ' ', @@time_zone, '/', @@system_time_zone) AS trx_started, 
    TIMEDIFF(NOW(), trx.trx_started) AS trx_length, 
    IF(trx.trx_state='LOCK WAIT', CONCAT(trx.trx_state, ' This statement has been waiting for a lock for ', TIMEDIFF(NOW(), trx.trx_wait_started)), trx.trx_state) AS trx_state, 
    trx.trx_isolation_level, 
    trx.trx_operation_state, 
    IF(trx.trx_is_read_only=1, 'This transaction was started as READ ONLY.', '0') AS trx_is_read_only, 
    CONCAT('This transaction has modified and/or inserted ', trx.trx_rows_modified, ' row(s).') AS trx_rows_modified, 
    CONCAT('This transaction has altered and/or locked ~', trx.trx_weight, ' row(s).') AS trx_weight,
    trx.trx_mysql_thread_id AS connection_id, 
    CONCAT(pl.user, '@', pl.host) AS user, 
    pl.command, 
    pl.time, 
    CONCAT('This statement has locked ', trx.trx_rows_locked, ' row(s) in ', trx.trx_tables_locked, ' table(s).') AS stmt_current_locks,
    trx.trx_query 
FROM 
    information_schema.innodb_trx trx 
    JOIN information_schema.processlist pl ON trx.trx_mysql_thread_id = pl.id 
ORDER BY 3 DESC LIMIT 5G

### Example output
*************************** 1. row ***************************
             trx_id: 6798
        trx_started: 2023-08-16 16:00:00 SYSTEM/UTC
         trx_length: 00:06:30
          trx_state: RUNNING
trx_isolation_level: REPEATABLE READ
trx_operation_state: NULL
   trx_is_read_only: 0
  trx_rows_modified: This transaction has modified and/or inserted 1 row(s).
         trx_weight: This transaction has altered and/or locked ~3 row(s).
      connection_id: 14
               user: root@localhost
            command: Sleep
               time: 390
 stmt_current_locks: This statement has locked 1 row(s) in 1 table(s).
          trx_query: NULL
1 row in set (0.00 sec)

3.timeout设置

mysql> SET SESSION lock_wait_timeout=5;
Query OK, 0 rows affected (0.00 sec)

mysql> alter table sbsmall.sbtest1 add column r int null;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

1.pt-osc

1.1原理与优缺点

创建新表，表结构与原表相同
alter新表在原表上创建insert、delete、update触发器
拷贝旧表数据到新表，同时通过触发器将增量数据同步到新表
如果原表有外键约束，处理外键
原表命名为old表新表命名为原表删除old表

CREATE TRIGGER `pt_osc_sbtest_sbtest2_del` 
AFTER DELETE ON `sbtest`.`sbtest2` 
FOR EACH ROW DELETE IGNORE FROM `sbtest`.`_sbtest2_new`
 WHERE `sbtest`.`_sbtest2_new`.`id` <=> OLD.`id`;
 
CREATE TRIGGER `pt_osc_sbtest_sbtest2_upd` 
AFTER UPDATE ON `sbtest`.`sbtest2` 
FOR EACH ROW REPLACE INTO `sbtest`.`_sbtest2_new` (`id`, `k`, `c`, `pad`, `ptosc`) 
VALUES (NEW.`id`, NEW.`k`, NEW.`c`, NEW.`pad`, NEW.`ptosc`);

CREATE TRIGGER `pt_osc_sbtest_sbtest2_ins`
AFTER INSERT ON `sbtest`.`sbtest2` 
FOR EACH ROW REPLACE INTO `sbtest`.`_sbtest2_new` (`id`, `k`, `c`, `pad`, `ptosc`) 
VALUES (NEW.`id`, NEW.`k`, NEW.`c`, NEW.`pad`, NEW.`ptosc`);

pt-osc同时处理全量和增量数据，即一边拷表一边回放增量DML，其所用拷表语句如下：

INSERT LOW_PRIORITY IGNORE INTO `sbtest`.`_sbtest2_new` (`id`, `k`, `c`, `pad`, `ptosc`) 
SELECT `id`, `k`, `c`, `pad`, `ptosc` FROM `sbtest`.`sbtest2` FORCE INDEX(`PRIMARY`) WHERE ((`id` >= ?)) AND ((`id` <= ?)) LOCK IN SHARE MODE /*pt-online-schema-change 115039 copy nibble*/
SELECT /*!40001 SQL_NO_CACHE */ `id` FROM `sbtest`.`sbtest2` FORCE INDEX(`PRIMARY`) WHERE ((`id` >= ?)) ORDER BY `id` LIMIT ?, 2 /*next chunk boundary*/

由于采用触发器方式，需要解决增量回放与全量拷贝乱序问题。pt-osc通过如下方式解决：

在拷表select时需要进行当前读（lock in shared mode）并与insert组成一个事务，避免快照读导致增量的delete操作丢失。场景如下：

如果是快照读，且在读id=x之前，业务执行了delete操作，则触发器先执行了空操作，导致新表中仍存在需删除掉的id=x记录;
如果与insert不是一个事务，存在相似问题；
触发器将增量update和insert均转换为replace into用于防止增量回放时因数据不存在而报错。

拷表insert时采用‘LOW_PRIORITY IGNORE’也是类似的考虑，防止全量旧数据覆盖增量新数据。

根据pt-osc上述实现，可以发现其存在的使用约束至少包括：

需要该表存在主键：因为进行每个select+insert事务拆分时用到了‘FORCE INDEX(PRIMARY)’；
需要确保表上没有触发器，否则会导致触发器冲突；
在使用过程中，会出现因存在触发器导致与业务事务冲突死锁的问题。

原表上要有 primary key 或 unique index，因为当执行该工具时会创建一个 DELETE 触发器来更新新表；

注意：一个例外的情况是 –alter 指定的子句中是在原表中的列上创建 primary key 或 unique index，这种情况下将使用这些列用于 DELETE 触发器。
2. 不能使用 rename 子句来重命名表；

列不能通过删除 + 添加的方式来重命名，这样将不会 copy 原有列的数据到新列；
如果要添加的列是 not null，则必须指定默认值，否则会执行失败；
删除外键约束（DROP FOREIGN KEY constraint_name），外键约束名前面必须添加一个下划线 ‘_’，即需要指定名称 _constraint_name，而不是原始的 constraint_name；

1.2 使用

-- 安装 yum 仓库
yum install https://repo.percona.com/yum/percona-release-latest.noarch.rpm
-- 安装 percona toolkit
yum install percona-toolkit -y

#创建用户
GRANT SELECT, INSERT, UPDATE, DELETE, \
    CREATE, DROP, PROCESS, REFERENCES, \ 
    INDEX, ALTER, SUPER, LOCK TABLES, \
    REPLICATION SLAVE, TRIGGER 
ON *.* TO 'ptosc'@'%'

#检查要变更的表上是否有主键或非空唯一键
#检查是否有其他表外键引用该表
select * from information_schema.key_column_usage where referenced_table_schema='testdb' and referenced_table_name='sbtest1'\G
#检查是否有触发器
select * from information_schema.triggers where event_object_schema='testdb' and event_object_table='sbtest1'\G
#若有，则需指定 –preserve-triggers 选项，且在 percona tool 3.0.4 起，对于 MySQL 5.7.2 以上，支持原表上有触发器，建议使用前在测试环境进行测试。
#执行 dry run
pt-online-schema-change --print --statistics \
    --progress time,30 --preserve-triggers --user=ptosc \
    --password=ptosc --alter 'modify c varchar(200) not null default ""' \
    h=127.0.1.1,P=3306,D=testdb,t=sbtest1 \
    --pause-file=/tmp/aa.txt --max-load=threads_running=100,threads_connected=200 \
    --critical-load=threads_running=1000  --chunk-size=1000 \
    --alter-foreign-keys-method auto --dry-run

–print：打印工具执行的 SQL 语句。

–statistics：打印统计信息。

–pause-file：当指定的文件存在时，终止执行。

–max-load：超过指定负载时，暂定执行。

–critical-load：超过指定负载时，终止执行。

–chunck-size：指定每次复制的行数。

–alter-foreign-keys-method：指定外键更新方式。

–progress：copy 进度打印的频率。
-[no]check-unique-key-change 控制添加唯一索引时是否会清理重复数据

将 –dry-run 修改为 –execute

pt-osc

2.gh-ost

先连接到主库上，根据alter语句创建所需的新表；
作为一个“备库”连接到其中一个真正的备库上，一边在主库上拷贝已有的数据到新表，一边从备库上拉取增量数据的binlog；
然后不断的把 binlog 应用回主库；
cut-over是最后一步，锁住主库的源表，等待binlog 应用完毕，然后替换gh-ost表为源表。
由于使用单线程回放binlog来替换触发器，所以增量DML回放效率不如触发器，因为pt-osc的增量回放并发度是与业务DML并发度相同的，是多线程的。

2.1优缺点

实现层面，gh-ost对业务负载敏感度会远高于pt-osc。
在使用gh-ost工具增加唯一索引时，如没检查数据唯一性，变更后会遇到数据丢失问题
gh-ost迁移旧表数据时使用insert ignore into xxx
存在null值的字段添加not null约束，任何模式下，如果之前存在空值，则会把空值改为0（隐形默认值），存在业务涵义上的风险

mysql在线表设计_dba

从上图可以看到，在某些场景下，可能发生 gh-ost 开始捕获 DML 操作后的二进制日志，但是之前的二进制事务并没有提交！

在上图的案例中，步骤1 addDMLEventsListener 将会捕获记录5以后发生的日志。

然而，在步骤2 ReadMigrationRangeValues 中，获取 min、max的值将会是1、4。

这是因为由于 after_sync 半同步模式，记录5对应的事务还未提交（如网络原因，或从机宕机等场景），记录5对于 gh-ost 中的函数 ReadMigrationRangeValues 是不可见的。

因此，步骤3、4只会插入记录1-4，以及回放记录5之后的所有日志，但会丢失记录5。

既然知道了原因，那么修复就变得非常简单了。只需要在获取 min、max的边界值的时候通过一致性读取即可

SELECT MIN(UK),MAX(UK) FROM xxxLOCK IN SHARE MODE;

通过 LOCK IN SHARE MODE，即便发生上述 after_sync 半同步等待问题，则在函数 ReadMigrationRangeValues 执行过程中，需要等待上述事务提交才能完成边界值的获取。

这时，边界值就会变为1、5，从而不会导致数据的丢失。

对于DDL操作的灵活度掌控，可暂停，可动态修改参数；DBA可以根据执行情况来快速调整预设的参数，可快可慢，实现DDL操作性能和对业务影响的平衡；
更为稳健的切表控制：将-cut-over-lock-timeout-seconds和-default-retries 配合使用，可以对切表进行灵活的控制。避免pt-osc切表异常导致对业务造成严重影响；

3. MySQL8.0 online ddl

Online DDL有复制阻塞问题：在MySQL多线程并行复制框架下，从库回放DDL操作时排他性的，也就是说DDL操作独立为一个group，只有该DDL操作执行完才能回放后续的DML，如果DDL操作需花费2小时，那么复制延迟至少为2小时；
相关参数：
调整参数加快索引创建速度
innodb_ddl_threads

innodb_ddl_threads #创建二级索引时的并行线程数量
innodb_ddl_buffer_size #指定进行并行 DDL 操作时能够使用的 buffer 大小
innodb_parallel_read_threads #扫描聚簇索引的并行线程

mysql在线表设计_dba_02

4.级联复制 ddl

新建一个S1的从库，构建M-S1-S2级联复制
使用OnlineDDL在S2上进行字段扩容 (优点是期间M-S1的主从不受影响)
扩容完成后，等待延迟同步M-S1-S2 (降低S2与M的数据差异,并进行数据验证)
移除S1,建立M-S2的主从关系(使S2继续同步M的数据)
备份S2恢复S1，建立M-S2-S1级联复制
应用停服，等待主从数据一致(优点是差异数据量的同步时间很短) 最终S2成为主库，S1为从库(应用需要修改前端连接信息)
应用进行回归验证

环境装备:开启Gtid,注意M,S1 binlog保存时长,磁盘剩余空间大于待变更表的2倍
show global variables like 'binlog_expire_logs_seconds'; # 默认604800
set global binlog_expire_logs_seconds=1209600; # 主库和级联主库都需要设置
1.搭建 1主2从的级联复制，M -> S1 -> S2 ，安装MySQL注意本次环境lower_case_table_names = 0 
2.在S2 上做字段扩容。 预估 10个小时
`参数设置:`
set global slave_type_conversions='ALL_NON_LOSSY'; # 防止复制报错SQL_Errno: 13146，属于字段类型长度不一致无法回放
set global interactive_timeout=144000;set global wait_timeout =144000;
`磁盘IO参数设置:`
set global innodb_buffer_pool_size=32*1024*1024*1024;# 增加buffer_pool 防止Error1206The total number of locks exceeds the lock table size 资源不足
set global sync_binlog=20000;set global innodb_flush_log_at_trx_commit=2;
set global innodb_io_capacity=600000;set global innodb_io_capacity_max=1200000; # innodb_io_capacity需要设置两次
show variables like '%innodb_io%'; # 验证以上设置
screen 下执行:
time mysql -S /data/mysql/3306/data/mysqld.sock -p'' dbname -NBe "ALTER TABLE tablename MODIFY COLUMN open_id VARCHAR(500) NULL DEFAULT NULL COMMENT 'Id' COLLATE 'utf8mb4_bin';"
查看DDL进度:
SELECT EVENT_NAME, WORK_COMPLETED, WORK_ESTIMATED  FROM performance_schema.events_stages_current;
3.扩容完成后，等待延迟同步M-S1-S2 
数据同步至主从一致,对比主从Gtid
4.移除S1,建立M-S2的主从关系
S1 (可选)
stop slave;
reset slave all;
systemctl stop mysql_3306
S2
stop slave;
reset slave all;
# MASTER_HOST='M主机IP'  
CHANGE MASTER TO
  MASTER_HOST='',
  MASTER_USER='',
  MASTER_PASSWORD=',
  MASTER_PORT=3306,
  MASTER_AUTO_POSITION=1,
  MASTER_CONNECT_RETRY=10;
start slave; (flush privileges;# 验证数据可正常同步)

5.备份S2恢复S1，建立M-S2-S1级联复制
物理备份S2,重做S2->S1 级联主从
rm -rf binlog/*
rm -rf redolog/*
xtrabackup --defaults-file=/data/mysql/3306/my.cnf.3306 --move-back --target-dir=/data/actionsky/xtrabackup_recovery/data
chown -R mysql. data/
chown -R mysql. binlog/*
chown -R mysql. redolog/*
systemctl start mysql_3306
set global gtid_purged='';
reset slave all;
# MASTER_HOST='S2主机IP'  ,已扩容变更完的主机
CHANGE MASTER TO
  MASTER_HOST='',
  MASTER_USER='',
  MASTER_PASSWORD='',
  MASTER_PORT=3306,
  MASTER_AUTO_POSITION=1,
  MASTER_CONNECT_RETRY=10;
`MySQL8.0版本需要在上面语句中添加 GET_MASTER_PUBLIC_KEY=1; #防止 Last_IO_Errno: 2061 message: Authentication plugin 'caching_sha2_password' reported error: Authentication requires secure connection.`
start slave;
6.应用停服，等待主从数据一致
主库停服+可设置read_only+flush privileges，对比主从Gtid
7.最终S2成为主库，S1为从库
应用更改配置连接新主库。
S2上:
stop slave;reset slave all;
set global read_only=0;set global super_read_only=0;
`show master status\G 观察是否有新事务写入`
收尾：还原第2步的参数设置。
set global interactive_timeout=28800;set global wait_timeout =28800;
set global innodb_buffer_pool_size=8*1024*1024*1024;
set global slave_type_conversions='';
set global sync_binlog=1;set global innodb_flush_log_at_trx_commit=1;
set global innodb_io_capacity=2000;set global innodb_io_capacity_max=4000;

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。