西安网站建设推荐q479185700上墙,什么装修网站做的好的,免费网页上传网站,电商的网站有几个通过阅读源码 更加深入了解原理#xff0c;以及如何进行全量数据同步#xff0c;如何使用触发器来同步变更期间的原表的数据更改。(#xff3e;#xff0d;#xff3e;)V 目录
源码分析 Get configuration information.
Connect to MySQL.
Create --plugin.
Setup la… 通过阅读源码 更加深入了解原理以及如何进行全量数据同步如何使用触发器来同步变更期间的原表的数据更改。()V 目录
源码分析 Get configuration information.
Connect to MySQL.
Create --plugin.
Setup lag and load monitors.
Check for replication filters.
Print --tries.
Get child tables of the original table, if necessary.
Check the --alter statement.
check and create PID file if user specified --pid.
Init the --plugin.
Step 1: Create the new table.
Step 2: Alter the new, empty table. This should be very quick, or die if the user specified a bad alter statement.
Step 3: Create the triggers to capture changes on the original table and apply them to the new table.
详细解读一下这三个触发器 DELETE TRIGGER 删除触发器
UPDATE TRIGGER 更新触发器
INSERT TRIGGER 插入触发器
Step 4: Copy rows.
对于空表 对于大表的该阶段步骤
Step 5: Update foreign key constraints if there are child tables.
Step 6: Swap tables
Step 7: Drop the old table.
测试步骤
开启全量日志
创建一张空表
进行变更
输出的日志
输出日志分析 源码分析
这个文件非常“臃肿肥胖“因为依赖的模块都在这一个文件中这个文件有13000多行的代码。
找到函数的入口 main()该脚本的主题流程如下 Get configuration information.
获取并 检查设置的参数感觉对外键的处理让工具变的复杂了很多。
主要是根据设置的命令行参数进行检查设置我根据源码举几个例子
如果设置了参数 null-to-not-null 检查参数 如果设置了--alter-foreign-keys-methoddrop_swap则 --no-swap-tables 和 --no-drop-old-table 需要被设置不能交互表名 和 删除原表 。如果显示设置了 chunk-size的 值则将 chunk-time 设置为0 不会在动态调整每次数据拷贝的块大小如果--no-swap-tables 和 --no-drop-triggers 被设置则--no-drop-new-table 也许被设置参数 --no-drop-triggers 和 --preserve-triggers 不能一起使用必须设置 数据库 和 表名
Connect to MySQL.
连接到MySQL
check-foreign-keys 如果没有设置 则 SET foreign_key_checks0
检查MySQL版本 是否大于 5.0.10虽然5.0.2 支持了触发器但是到 5.0.10 之前触发器不能包含按名称对表的直接引用
检查参数 analyze-before-swap 是否必要
Create --plugin.
Setup lag and load monitors.
设置检查的从库延迟 与负载监控
Check for replication filters.
检查复制的过滤规则
Print --tries.
一些操作的重试次数 和 间隔。
Operation, tries, wait:analyze_table, 10, 1copy_rows, 10, 0.25create_triggers, 10, 1drop_triggers, 10, 1swap_tables, 10, 1update_foreign_keys, 10, 1
Get child tables of the original table, if necessary. Check the --alter statement.
检查变更语句
check and create PID file if user specified --pid.
Init the --plugin.
变更步骤
Step 1: Create the new table.
创建中间表
中间表的表名 如果设置了参数 new-table-name则新表名为 new-table-name。如果没有设置该参数则为 _%T_new,%T为原表名。
如何创建中间表
不能使用 CRATE TABLE LIKE ,因为他不会保留 外键约束。这里我们也需要重命名外键约束。这是因为外键约束内部存储的形式.外键约束名字不能重复。如果不重命名外键约束这个innodb 会抛出121错误
这段代码并不完美。 如果我们将约束重命名为 foo 到 _foo 并且 该表或另一个表中已经存在该名称的约束我们仍然可能发生冲突。 但如果有该表上有多个 FK很难知道是哪一个导致的错误。 我们应该生成随机/UUID FK 名称还是其他名称
Creating new table...
CREATE TABLE osc_test._my_table_new (id int(11) NOT NULL AUTO_INCREMENT,first_name varchar(50) DEFAULT NULL,last_name varchar(50) DEFAULT NULL,job_title varchar(100) DEFAULT NULL,hire_date date DEFAULT NULL,PRIMARY KEY (id)
) ENGINEInnoDB DEFAULT CHARSETutf8mb4
Created new table osc_test._my_table_new OK.
Step 2: Alter the new, empty table. This should be very quick, or die if the user specified a bad alter statement.
对中间表进行变更
Altering new table...
ALTER TABLE osc_test._my_table_new add index idx_first_name(first_name);
Altered osc_test._my_table_new OK.
Step 3: Create the triggers to capture changes on the original table and apply them to the new table.
创建触发器 捕获变化 应用到中间表上
2023-12-22T15:33:19 Creating triggers...
2023-12-22T15:33:19 Created triggers OK.
-- DELETE TRIGGER
CREATE TRIGGER pt_osc_osc_test_my_table_del AFTER DELETE ON osc_test.my_table FOR EACH ROW DELETE IGNORE FROM osc_test._my_table_new WHERE osc_test._my_table_new.id OLD.id--UPDATE TRIGGER
CREATE TRIGGER pt_osc_osc_test_my_table_upd AFTER UPDATE ON osc_test.my_table FOR EACH ROW BEGIN DELETE IGNORE FROM osc_test._my_table_new WHERE !(OLD.id NEW.id) AND osc_test._my_table_new.id OLD.id;REPLACE INTO osc_test._my_table_new (id, first_name, last_name, job_title, hire_date) VALUES (NEW.id, NEW.first_name, NEW.last_name, NEW.job_title, NEW.hire_date);END--INSERT TRIGGER
CREATE TRIGGER pt_osc_osc_test_my_table_ins AFTER INSERT ON osc_test.my_table FOR EACH ROW REPLACE INTO osc_test._my_table_new (id, first_name, last_name, job_title, hire_date) VALUES (NEW.id, NEW.first_name, NEW.last_name, NEW.job_title, NEW.hire_date)
详细解读一下这三个触发器
id是主键每个id 唯一 DELETE TRIGGER 删除触发器
在原表 osc_test.my_table 某个id的数据被删除后 会删除中间表osc_test._my_table_new 中上id 与原表表相匹配的数据因为使用DELETE IGNORE的语法如果此时copy-data全量数据拷贝阶段还没有把该id的数据从原表拷贝到中间表在中间表上执行DELETE IGNORE也不会报错。 中间表已经有该数据即全量数据拷贝阶段已经把该数据拷贝到中间表在原表删除后中间表的数据也会被删除。
中间表还没有该数据 也不用担心全量数据阶段会再把该数据拷贝到中间表因为原表上已经被删除了呀。 UPDATE TRIGGER 更新触发器
当在osc_test.my_table表上执行UPDATE操作时触发器会在每一行被更新之后执行。 如果原表上更新导致 主键ID发生了变化则中间表上 先删除该ID的数据然后在插入REPLACE INTO
如果原表上更新导致 主键ID没有发生了变化则在中间表直接 REPLACE INTO 忽略唯一性约束错误会覆盖该ID的数据 数据还没有同步到中间表则会在中间表插入等到全量数据同步的时候覆盖一次。
数据已经同步到中间表则走上面的逻辑。 INSERT TRIGGER 插入触发器
当在osc_test.my_table表上执行INSERT操作时触发器会在每一行被插入之后执行。
触发器的作用是将新插入的数据REPLACE INTO 插入或替换到osc_test._my_table_new表中确保id唯一。 中间表已经有该数据即全量数据拷贝阶段已经把该数据拷贝到中间表在原表插入新数据后替换中间表的数据。
中间表还没有该数据 即全量数据拷贝阶段还没有把该数据拷贝到中间表但是由于触发器存在该中间表中已经存在该ID的数据在全量复制数据期间会再次覆盖插入一次INSERT LOW_PRIORITY IGNORE INTO 。 Step 4: Copy rows.
拷贝全表数据
分为两种情况 第一中情况为 一个chunk_size大于该表的行数就不用对表进行分块第二种需要进行分块chunking the table。
如果表的数据量只有一个chunk需要确保从库的数据量也只有个chunk。 对于空表
INSERT LOW_PRIORITY IGNORE INTO
LOCK IN SHARE MODE加共享锁 查询一个chunk数据的时候不允许写允许读所以需要保证这个查询数据很短。
插入操作是对中间表进行所以不会触发老表上的触发器不用担心。
2023-12-22T15:33:19 Copying approximately 1 rows...
INSERT LOW_PRIORITY IGNORE INTO osc_test._my_table_new (id, first_name, last_name, job_title, hire_date) SELECT id, first_name, last_name, job_title, hire_date FROM osc_test.my_table LOCK IN SHARE MODE /*pt-online-schema-change 140230 copy table*/
2023-12-22T15:33:19 Copied rows OK. 对于大表的该阶段步骤
原文的注释和代码中用了 nibble 原意是蚕食感觉对大表来说比较形象将大表分成小块同步到中间表 “蚕食”。
需要确保使用的是同一个分块索引。 获取每个chunk 的起始边界ID , SELECT /*!40001 SQL_NO_CACHE */ id FROM osc_test.mytable2 FORCE INDEX(PRIMARY) ORDER BY id LIMIT 1 /*first lower boundary*/ 获取第一次chunk 的截止边界 第一次chunk 的大小和 chunk-size的值相同。因为chunk-size的默认值是1000所以WHERE ((id 1)) ORDER BY id LIMIT 999, 2; 获取到的ID是 1000
SELECT /*!40001 SQL_NO_CACHE */ id FROM osc_test.mytable2 FORCE INDEX(PRIMARY) WHERE ((id 1)) ORDER BY id LIMIT 999, 2 /*next chunk boundary*/ 查看 查询原表数据SQL的 执行计划
EXPLAIN SELECT id, emp_id, ldap, name FROM osc_test.mytable2 FORCE INDEX(PRIMARY) WHERE ((id 1)) AND ((id 1000)) LOCK IN SHARE MODE /*explain pt-online-schema-change 127495 copy nibble*/ 然后进行实际的插入
INSERT LOW_PRIORITY IGNORE INTO osc_test._mytable2_new (id, emp_id, ldap, name) SELECT id, emp_id, ldap, name FROM osc_test.mytable2 FORCE INDEX(PRIMARY) WHERE ((id 1)) AND ((id 1000)) LOCK IN SHARE MODE /*pt-online-schema-change 127495 copy nibble*/ 查看SQL执行的警告 结束的时候可以作为参数--statistics 统计信息输出
SHOW WARNINGS 查看活跃会话数这个注释是 作为 --max_load 和 --load的默认参数如果服务器超过
SHOW GLOBAL STATUS LIKE Threads_running 继续取下一个chunk的边界 chunk-size的值是根据 rows/s 每秒处理的行数自动调节的这次就变成了每个chunk 7442。 SELECT /*!40001 SQL_NO_CACHE */ id FROM osc_test.mytable2 FORCE INDEX(PRIMARY) WHERE ((id 1001)) ORDER BY id LIMIT 7442, 2 /*next chunk boundary*/ 一直循环该步骤 知道全表数据拷贝完成。 Step 5: Update foreign key constraints if there are child tables.
Step 6: Swap tables
该交换是原子性的 即两张表的rename语句是在同一个语句中该语句执行很快所以元数据锁的时间也会很短
2023-12-22T15:33:19 Analyzing new table...
2023-12-22T15:33:19 Swapping tables...
RENAME TABLE osc_test.my_table TO osc_test._my_table_old, osc_test._my_table_new TO osc_test.my_table
2023-12-22T15:33:19 Swapped original and new tables OK.
Step 7: Drop the old table.
删除原表
删除触发器
2023-12-22T15:33:19 Dropping old table...
DROP TABLE IF EXISTS osc_test._my_table_old
2023-12-22T15:33:19 Dropped old table osc_test._my_table_old OK.
2023-12-22T15:33:19 Dropping triggers...
DROP TRIGGER IF EXISTS osc_test.pt_osc_osc_test_my_table_del
DROP TRIGGER IF EXISTS osc_test.pt_osc_osc_test_my_table_upd
DROP TRIGGER IF EXISTS osc_test.pt_osc_osc_test_my_table_ins
2023-12-22T15:33:19 Dropped triggers OK.
变更完成 测试步骤 开启全量日志
开启日志后所有SQL都会被记录到 便于我们结合源码理解原理。
set global general_logon; 创建一张空表
这样变更过程中全量日志输出的内容会很少。
CREATE TABLE my_table (id INT AUTO_INCREMENT PRIMARY KEY,first_name VARCHAR(50),last_name VARCHAR(50),job_title VARCHAR(100),hire_date DATE
) ENGINEInnoDB; 进行变更
pt-online-schema-change --userroot \
--socket/home/storage/mysql/mysql_5306/run/mysql.sock \
--port5306 \Dosc_test,tmy_table \
--alteradd index idx_first_name(first_name); \
--execute \
--charsetutf8 \
--statistics --print --progresstime,10
输出的日志
No slaves found. See --recursion-method if host ehr-db-stage02.ys has slaves.
Not checking slave lag because no slaves were found and --check-slave-lag was not specified.
Operation, tries, wait:analyze_table, 10, 1copy_rows, 10, 0.25create_triggers, 10, 1drop_triggers, 10, 1swap_tables, 10, 1update_foreign_keys, 10, 1
Altering osc_test.my_table...
Creating new table...
CREATE TABLE osc_test._my_table_new (id int(11) NOT NULL AUTO_INCREMENT,first_name varchar(50) DEFAULT NULL,last_name varchar(50) DEFAULT NULL,job_title varchar(100) DEFAULT NULL,hire_date date DEFAULT NULL,PRIMARY KEY (id)
) ENGINEInnoDB DEFAULT CHARSETutf8mb4
Created new table osc_test._my_table_new OK.
Altering new table...
ALTER TABLE osc_test._my_table_new add index idx_first_name(first_name);
Altered osc_test._my_table_new OK.
2023-12-22T15:33:19 Creating triggers...
2023-12-22T15:33:19 Created triggers OK.
2023-12-22T15:33:19 Copying approximately 1 rows...
INSERT LOW_PRIORITY IGNORE INTO osc_test._my_table_new (id, first_name, last_name, job_title, hire_date) SELECT id, first_name, last_name, job_title, hire_date FROM osc_test.my_table LOCK IN SHARE MODE /*pt-online-schema-change 140230 copy table*/
2023-12-22T15:33:19 Copied rows OK.
2023-12-22T15:33:19 Analyzing new table...
2023-12-22T15:33:19 Swapping tables...
RENAME TABLE osc_test.my_table TO osc_test._my_table_old, osc_test._my_table_new TO osc_test.my_table
2023-12-22T15:33:19 Swapped original and new tables OK.
2023-12-22T15:33:19 Dropping old table...
DROP TABLE IF EXISTS osc_test._my_table_old
2023-12-22T15:33:19 Dropped old table osc_test._my_table_old OK.
2023-12-22T15:33:19 Dropping triggers...
DROP TRIGGER IF EXISTS osc_test.pt_osc_osc_test_my_table_del
DROP TRIGGER IF EXISTS osc_test.pt_osc_osc_test_my_table_upd
DROP TRIGGER IF EXISTS osc_test.pt_osc_osc_test_my_table_ins
2023-12-22T15:33:19 Dropped triggers OK.
# Event Count
#
# INSERT 1
Successfully altered osc_test.my_table. 输出日志分析 官方文档
pt-online-schema-change — Percona Toolkit Documentation 源码分析 重庆八怪
https://www.jianshu.com/p/ecec3d307ec0/
