mysql在线编辑器

雨点打透心脏的1/2处 2023-09-25 11:00 48阅读 0赞

### mysql 能否设置DDL语句 可以回滚？ ###

MySQL8.0开始支持原⼦DDL(atomicDDL)，数据字典的更新，存储引擎操作，写⼆进制日志结合成了一个事务。在没有原⼦DDL之前，DROPTABLEtest1,test2；如遇到servercrash，可能会有test1被drop了，test2没有被drop掉。下面来看下在MySQL8.0之前和MySQL8.0数据字典的区别

在MySQL8.0之前，DataDictionary除了存在与.FRM,.TRG,.OPT⽂件外，还存在于系统表中(MyISAM⾮事务引擎表中)，在MySQL8.0，DataDictionary全部存在于DataDictionaryStorageEngine(即InnoDB表中)，这使crashrecovery维持原⼦性成为了可能

存储引擎⽀持

目前，只有InnoDB存储引擎⽀持原子DDL，为了实现原子DDL，Innodb要写DDLlogs到mysql.innodb\_ddl\_log表，这是⼀个隐藏在mysql.ibd数据字典表空间⾥的数据字典表。要看mysql.innodb\_ddl\_log中的内容，需要

SETGLOBALLOG\_ERROR\_VERBOSITY=3;(MySQL8.0默认为2，errorlog记录Errorsand

warnings，不不记录notes)

SETGLOBALinnodb\_print\_ddl\_logs=1;

CREATETABLEt1(c1INT)ENGINE=InnoDB;

查看errorlog

\[Note\]\[MY-011066\]InnoDB:DDLloginsert:\[DDLrecord:DELETESPACE,id=30,

thread\_id=25,space\_id=9,old\_file\_path=./test/t1.ibd\]

\[Note\]\[MY-011066\]InnoDB:DDLlogdelete:byid30

\[Note\]\[MY-011066\]InnoDB:DDLloginsert:\[DDLrecord:REMOVECACHE,id=31,

thread\_id=25,table\_id=1066,new\_file\_path=test/t1\]

\[Note\]\[MY-011066\]InnoDB:DDLlogdelete:byid31

\[Note\]\[MY-011066\]InnoDB:DDLloginsert:\[DDLrecord:FREE,id=32,thread\_

id=25,space\_id=9,index\_id=143,page\_no=4\]

\[Note\]\[MY-011066\]InnoDB:DDLlogdelete:byid32

\[Note\]\[MY-011066\]InnoDB:DDLlogpostddl:beginforthreadid:25

\[Note\]\[MY-011066\]InnoDB:DDLlogpostddl:endforthreadid:25

原子DDL操作步骤

准备：创建所需的对象并将DDL⽇志写入mysql.innodb\_ddl\_log表中。DDL日志定义了如何前滚和回滚DDL操作。

执行：执⾏DDL操作。例如，为CREATETABLE操作执⾏创建。

提交：更新数据字典并提交数据字典事务。

Post-DDL：重播并从mysql.innodb\_ddl\_log表格中删除DDL⽇志。为确保回滚可以安全执⾏⽽不引⼊不⼀致性，在此最后阶段执⾏⽂件操作(如重命名或删除数据文件)。这一阶段还从mysql.innodb\_dynamic\_metadata的数据字典表删除的动态元数据为了DROPTABLE，TRUNCATE和其它重建表的DDL操作。

⽆论事务是提交还是回滚，DDL日志都会mysql.innodb\_ddl\_log在Post-DDL阶段重播并从表中删除。mysql.innodb\_ddl\_log如果服务器在DDL操作期间暂停，DDL⽇志应该只保留在表中。在这种情况下，DDL⽇志会在恢复后重播并删除。

在恢复情况下，当服务器重新启动时，可能会提交或回退DDL事务。如果在重做⽇志和⼆进制日志中存在DDL操作的提交阶段期间执⾏的数据字典事务，则该操作被认为是成功的并且被前滚。否则，在InnoDB重放数据字典重做日志时回滚不完整的数据字典事务，并且回滚DDL事务。

原⼦DDL⽀持类型

•DROPTABLES,alltablesdroppedornone

•DROPSCHEMA,allentitiesintheschemaaredropped,ornone

•NotethatatomicDDLstatementswillberolledbackorcommittedevenincaseofcrash,e.g.RENAMETABLES

•CREATETABLEwouldbesuccessfullycommittedorrolledback(noorphanibdleft)

•TRUNCATETABLE(includingInnoDBtableswithFTSAUXtables)wouldbesuccessfullycommittedorrolledback

•RENAMETABLES,allornone

•ALTERTABLEsuccessfulornotdone

请点击输入图片描述

在MySQL8.0之前，altertable操作在servercrash的情况下，会遗留.frm,.ibd文件。MySQL8.0能实现原⼦DDL(包括DROPTABLE,DROPSCHEMA,CREATETABLE,TRUNCATETABLE,ALTERTABLE)，altertable操作，在servercrash的情况下，不会遗留.frm，.ibd临时文件。让我们⼀起期待MySQL8.0GA的到来吧！

![mysql在线编辑器\_mysql在线ddl][mysql_mysql_ddl]

### 让ddl sql到mysql数据库中执行很久时间，有没有这样的办法的 ###

具体操作如下：

先看看看event事件是否开启

showvariableslike'%sche%';

如没开启，则开启。需要数据库超级权限

setglobalevent\_scheduler=1;

创建存储过程update\_a(注：就是你要执行的sql语句)

mysql>createprocedureupdate\_a()updateaseta.y\_avg=(selectavg(b.youhao)frombwherea.a\_id=b.a\_id);

创建一个定时任务：evente\_updateA

mysql>createeventifnotexistse\_updateA

\->onscheduleevery60second---设置60秒执行一次

\->onscheduleatdate\_add(now(),interval1minute)---在一分钟后执行

\->oncompletionpreserve

\->docallupdate\_a();---执行update\_a()存储过程

创建Event之后，sql语句就定时执行一次。

关闭事件任务

mysql>alterevente\_updateAON

\->COMPLETIONPRESERVEDISABLE;

开启事件任务

mysql>alterevente\_updateAON

\->COMPLETIONPRESERVEENABLE;

### mysqlbinlog语法错误 ###

MySQL复制是异步的，也就是说是非同步的过程，它不会校验数据库中数据的一致性，只要SQL语法正确并且没有错误就能成功执行

\[plain\] view plain copy print?

MASTER@root@test 12:20:40>create table tab01

\-> (id int(10) primary key ,

\-> name varchar(20));

Query OK, 0 rows affected (0.03 sec)

MASTER@root@test 12:21:32>

MASTER@root@test 12:21:49>show master status;

\+----------------------+----------+--------------+------------------+

\+----------------------+----------+--------------+------------------+

| binlog-master.000004 |338 |||

\+----------------------+----------+--------------+------------------+

1 row in set (0.00 sec)

MASTER@root@test 12:21:52>

\[plain\] view plain copy print?

\[root@mynode1 mysql\]\# /service/mysql/bin/mysqlbinlog binlog-master.000004|tail -13

\# at 213

\#140130 12:21:32 server id 1 end\_log\_pos 338Querythread\_id=3exec\_time=0error\_code=0

use \`test\`/\*!\*/;

SET TIMESTAMP=1391055692/\*!\*/;

create table tab01

(id int(10) primary key ,

name varchar(20))

/\*!\*/;

DELIMITER ;

\# End of log file

ROLLBACK /\* added by mysqlbinlog \*/;

/\*!50003 SET COMPLETION\_TYPE=@OLD\_COMPLETION\_TYPE\*/;

/\*!50530 SET @@SESSION.PSEUDO\_SLAVE\_MODE=0\*/;

可以看到binlog里记录了这条语句，可以通过sql\_log\_bin参数来控制是否捕获binlog中的操作

\[plain\] view plain copy print?

MASTER@root@test 12:25:32>set sql\_log\_bin=0;

Query OK, 0 rows affected (0.00 sec)

MASTER@root@test 12:25:37>alter table tab01 add index(name);

Query OK, 0 rows affected (0.05 sec)

Records: 0 Duplicates: 0 Warnings: 0

MASTER@root@test 12:26:04>set sql\_log\_bin=1;

Query OK, 0 rows affected (0.00 sec)

MASTER@root@test 12:26:07>

MASTER@root@test 12:26:08>show create table tab01\\G

\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 1. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*

Table: tab01

Create Table: CREATE TABLE \`tab01\` (

\`id\` int(10) NOT NULL,

\`name\` varchar(20) DEFAULT NULL,

PRIMARY KEY (\`id\`),

KEY \`name\` (\`name\`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8

1 row in set (0.00 sec)

\[plain\] view plain copy print?

\[root@mynode1 mysql\]\# /service/mysql/bin/mysqlbinlog binlog-master.000004

/\*!50530 SET @@SESSION.PSEUDO\_SLAVE\_MODE=1\*/;

/\*!40019 SET @@session.max\_insert\_delayed\_threads=0\*/;

/\*!50003 SET @OLD\_COMPLETION\_TYPE=@@COMPLETION\_TYPE,COMPLETION\_TYPE=0\*/;

DELIMITER /\*!\*/;

\# at 4

\#140130 12:08:05 server id 1 end\_log\_pos 107Start: binlog v 4, server v 5.5.34-log created 140130 12:08:05 at startup

\# Warning: this binlog is either in use or was not closed properly.

ROLLBACK/\*!\*/;

BINLOG '

JdDpUg8BAAAAZwAAAGsAAAABAAQANS41LjM0LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA

AAAAAAAAAAAAAAAAAAAl0OlSEzgNAAgAEgAEBAQEEgAAVAAEGggAAAAICAgCAA==

'/\*!\*/;

\# at 107

\#140130 12:13:45 server id 1 end\_log\_pos 213Querythread\_id=3exec\_time=0error\_code=0

use \`tmp\`/\*!\*/;

SET TIMESTAMP=1391055225/\*!\*/;

SET @@session.pseudo\_thread\_id=3/\*!\*/;

SET @@session.foreign\_key\_checks=1, @@session.sql\_auto\_is\_null=0, @@session.unique\_checks=1, @@session.autocommit=1/\*!\*/;

SET @@session.sql\_mode=0/\*!\*/;

SET @@session.auto\_increment\_increment=1, @@session.auto\_increment\_offset=1/\*!\*/;

/\*!\\C utf8 \*//\*!\*/;

SET @@session.character\_set\_client=33,@@session.collation\_connection=33,@@session.collation\_server=33/\*!\*/;

SET @@session.lc\_time\_names=0/\*!\*/;

SET @@session.collation\_database=DEFAULT/\*!\*/;

create table tab\_02 as select \* from tab\_tmp

/\*!\*/;

\# at 213

\#140130 12:21:32 server id 1 end\_log\_pos 338Querythread\_id=3exec\_time=0error\_code=0

use \`test\`/\*!\*/;

SET TIMESTAMP=1391055692/\*!\*/;

create table tab01

(id int(10) primary key ,

name varchar(20))

/\*!\*/;

DELIMITER ;

\# End of log file

ROLLBACK /\* added by mysqlbinlog \*/;

/\*!50003 SET COMPLETION\_TYPE=@OLD\_COMPLETION\_TYPE\*/;

/\*!50530 SET @@SESSION.PSEUDO\_SLAVE\_MODE=0\*/;

\[root@mynode1 mysql\]\#

可以将此DDL语句在slave库执行

\[plain\] view plain copy print?

SLAVE@root@test 12:26:39>alter table tab01 add index(name);

Query OK, 0 rows affected (0.04 sec)

Records: 0 Duplicates: 0 Warnings: 0

SLAVE@root@test 12:27:57>show create table tab01\\G

\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 1. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*

Table: tab01

Create Table: CREATE TABLE \`tab01\` (

\`id\` int(10) NOT NULL,

\`name\` varchar(20) DEFAULT NULL,

PRIMARY KEY (\`id\`),

KEY \`name\` (\`name\`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8

1 row in set (0.00 sec)

SLAVE@root@test 12:28:10>

在大型master-slave环境下执行DDL语句时，在每一台slave上手动去执行DDL命令能绕过MySQL数据复制单线程对某些命令的限制mysqlbinlog mysql-bin.00005 --startdatetime='时间' --stopdatetime='时间' --database=db\_name | grep -i -a 6 'id=111' 用grep..

![mysql在线编辑器\_mysql在线ddl\_02][mysql_mysql_ddl_02]

### mysql语句dml全称是什么意思 ###

数据库操纵语言 DML；

数据库查询语言 DQL;

数据库模式定义语言 DDL;

数据库控制语言 DCL;没看懂什么意思？

[mysql_mysql_ddl]: https://img0.baidu.com/it/u=2236303754,2277723339&fm=26&fmt=auto
[mysql_mysql_ddl_02]: https://img0.baidu.com/it/u=2993008346,3262872984&fm=26&fmt=auto