本文目录一览:
- 1、常用的DDL、DML操作属于Mysql逻辑体系里的哪一层
- 2、数据库主从DDL是什么
- 3、关于mysql 56 哪些操作可以进行online ddl 详解
- 4、让ddl sql到mysql数据库中执行很久时间,有没有这样的办法的
- 5、mysql 存储过程 DDL 参数
- 6、mysql 对一个大表做在线ddl,怎么进行实施的才能尽可能降低影响
常用的DDL、DML操作属于Mysql逻辑体系里的哪一层
属于数据库层。
MySQL支持DML(数据操作语言)、DDL(数据定义语言)、存储过程、视图、触发器、自定义函数等多种SQL语言接口。
数据库主从DDL是什么
谈到MySQL数据库主从同步延迟原理,得从mysql的数据库主从复制原理说起,mysql的主从复制都是单线程的操作,主库对所有DDL和DML产生binlog,binlog是顺序写,所以效率很高,slave的Slave_IO_Running线程到主库取日志,效率很比较高,下一步,问题来了,slave的Slave_SQL_Running线程将主库的DDL和DML操作在slave实施。DML和DDL的IO操作是随即的,不是顺序的,成本高很多,还可能可slave上的其他查询产生lock争用,由于Slave_SQL_Running也是单线程的,所以一个DDL卡主了,需要执行10分钟,那么所有之后的DDL会等待这个DDL执行完才会继续执行,这就导致了延时。有朋友会问:“主库上那个相同的DDL也需要执行10分,为什么slave会延时?”,答案是master可以并发,Slave_SQL_Running线程却不可以。
关于mysql 56 哪些操作可以进行online ddl 详解
实际测试中add primary key 和修改字段类型是需要copy tmp table的并且阻塞dml操作,另外在5617版本之前时候用alter table table_name engine=innodb 是需要 copy table的并且也阻塞dml。 本来打算翻译成中文,后来发现这些英文实在是太简单了关于mysql 56 哪些操作可以进行online ddl 详解
让ddl sql到mysql数据库中执行很久时间,有没有这样的办法的
具体操作如下:
先看看看event 事件是否开启
show variables like '%sche%';
如没开启,则开启。需要数据库超级权限
set global event_scheduler =1;
创建存储过程 update_a (注:就是你要执行的sql语句)
mysql create procedure update_a() update a set a.y_avg=(select avg(b.youhao) from b where a.a_id=b.a_id);
创建一个定时任务:event e_updateA
mysql create event if not exists e_updateA
- on schedule every 60 second ---设置60秒执行一次
- on schedule at date_add(now(),interval 1 minute) ---在一分钟后执行
- on completion preserve
- do call update_a(); ---执行update_a()存储过程
创建Event之后,sql语句就定时执行一次。
关闭事件任务
mysql alter event e_updateA ON
- COMPLETION PRESERVE DISABLE;
开启事件任务
mysql alter event e_updateA ON
- COMPLETION PRESERVE ENABLE;
mysql 存储过程 DDL 参数
MySQL8.0 开始支持原⼦ DDL(atomic DDL),数据字典的更新,存储引擎操作,写⼆进制日志结合成了一个事务。在没有原⼦DDL之前,DROP TABLE test1,test2;如遇到server crash,可能会有test1被drop了,test2没有被drop掉。下面来看下在MySQL8.0之前和MySQL8.0 数据字典的区别
在MySQL8.0 之前,Data Dictionary除了存在与.FRM, .TRG, .OPT ⽂件外,还存在于系统表中(MyISAM ⾮事务引擎表中),在MySQL8.0 ,Data Dictionary 全部存在于Data Dictionary Storage Engine(即 InnoDB表中),这使crash recovery 维持原⼦性成为了可能
存储引擎⽀持
目前,只有InnoDB存储引擎⽀持原子DDL,为了实现原子DDL,Innodb要写DDL logs 到 mysql.innodb_ddl_log 表,这是⼀个隐藏在mysql.ibd 数据字典表空间⾥的数据字典表。要看mysql.innodb_ddl_log 中的内容,需要
SET GLOBAL LOG_ERROR_VERBOSITY=3;(MySQL 8.0 默认为2,error log 记录Errors and
warnings,不不记录notes)
SET GLOBAL innodb_print_ddl_logs=1;
CREATE TABLE t1 (c1 INT) ENGINE = InnoDB;
查看error log
[Note] [MY-011066] InnoDB: DDL log insert : [DDL record: DELETE SPACE, id=30,
thread_id=25, space_id=9, old_file_path=./test/t1.ibd]
[Note] [MY-011066] InnoDB: DDL log delete : by id 30
[Note] [MY-011066] InnoDB: DDL log insert : [DDL record: REMOVE CACHE, id=31,
thread_id=25, table_id=1066, new_file_path=test/t1]
[Note] [MY-011066] InnoDB: DDL log delete : by id 31
[Note] [MY-011066] InnoDB: DDL log insert : [DDL record: FREE, id=32, thread_
id=25, space_id=9, index_id=143, page_no=4]
[Note] [MY-011066] InnoDB: DDL log delete : by id 32
[Note] [MY-011066] InnoDB: DDL log post ddl : begin for thread id : 25
[Note] [MY-011066] InnoDB: DDL log post ddl : end for thread id : 25
原子DDL 操作步骤
准备:创建所需的对象并将DDL⽇志写入 mysql.innodb_ddl_log表中。DDL日志定义了如何前滚和回滚DDL操作。
执行:执⾏DDL操作。例如,为CREATE TABLE操作执⾏创建。
提交:更新数据字典并提交数据字典事务。
Post-DDL:重播并从mysql.innodb_ddl_log表格中删除DDL⽇志。为确保回滚可以安全执⾏⽽不引⼊不⼀致性,在此最后阶段执⾏⽂件操作(如重命名或删除数据文件)。这一阶段还从 mysql.innodb_dynamic_metadata的数据字典表删除的动态元数据为了DROP TABLE,TRUNCATE和其它重建表的DDL操作。
⽆论事务是提交还是回滚,DDL日志都会mysql.innodb_ddl_log在Post-DDL阶段重播并从表中删除 。mysql.innodb_ddl_log如果服务器在DDL操作期间暂停,DDL⽇志应该只保留在表中。在这种情况下,DDL⽇志会在恢复后重播并删除。
在恢复情况下,当服务器重新启动时,可能会提交或回退DDL事务。如果在重做⽇志和⼆进制日志中存在DDL操作的提交阶段期间执⾏的数据字典事务,则该操作被认为是成功的并且被前滚。否则,在InnoDB重放数据字典重做日志时回滚不完整的数据字典事务 ,并且回滚DDL事务。
原⼦DDL ⽀持类型
• DROP TABLES , all tables dropped or none
• DROP SCHEMA, all entities in the schema are dropped, or none
• Note that atomic DDL statements will be rolled back or committed even in case of crash, e.g. RENAME TABLES
• CREATE TABLE would be successfully committed or rolled back (no orphan ibd left)
• TRUNCATE TABLE (including InnoDB tables with FTS AUX tables) would be successfully committed or rolled back
• RENAME TABLES, all or none
• ALTER TABLE successful or not done
示例
结论
在MySQL8.0之前,alter table 操作在server crash的情况下,会遗留.frm,.ibd文件。MySQL8.0 能实现原⼦DDL(包括 DROP TABLE, DROP SCHEMA, CREATE TABLE, TRUNCATE TABLE, ALTER TABLE),alter table 操作,在server crash的情况下,不会遗留.frm,.ibd临时文件。让我们⼀起期待MySQL8.0 GA的到来吧!
mysql 对一个大表做在线ddl,怎么进行实施的才能尽可能降低影响
可以采用中间表。假设你原始表名是“test”,那么步骤如下
建立一个和“test”一样表结构的新表,表名为test_new。create table test_new like test;
将test表中数据拷贝到test_new中。insert into test_new select * from test;
在test_new上执行ddl操作
最后将执行过ddl更新的test_new表改名为test,原test表改名为test_old。Rename table test to test_old, test_new to test;
确认检查无误后drop掉test_old表
如果test表很大,在第二步会消耗很长时间,那么第二步可以以主键ID为准,采用分段导入,一次导入比如5000条数据,多次导入,这样不会对生产环境造成太大影响,假设test表上有自增主键“form_id",那么上面第二步命令变为:
insert into test_new select * from test where form_id between '1' and '5000';
insert into test_new select * from test where form_id between '5001' and '10000';
.
.
.