mysql8使用日志--auto_increment自增值

最近做项目用到mysql8,有个业务场景是创建完资料后跳到资料详情。按常理直接返回个insert id了事。问题就此发生了,每次输出的自增值居然是一样的。然后就进行手动调试

SELECT AUTO_INCREMENT FROM information_schema.`TABLES` WHERE Table_Schema='dbname' AND table_name = 'tablename';

发现查询出来的AUTO_INCREMENT确实是一样的,这就很奇怪了,于是去翻了下mysql的资料。

在5.7及以前,alter table修改的auto_increment值确实是记录到tables表的auto_increment列的,但是有一个缺点是,tables表的引擎是memory,也就是说,如果MySQL重启了,这个表记录的auto_increment值就丢失了。例如tab表当前的auto_increment值是30,使用alter table将其修改为50,那么tables表的auto_increment列确实是记录显示了50,但如果tab没有插入任何数据,并重启了MySQL(不管是正常还是异常重启),下一次运行时,tab表的auto_increment属性就会回退到30,同时tables表的auto_increment列显示的也是30。
那么既然tables是memory引擎表,重启之后理应没有数据才对,为什么auto_increment列还显示30呢?原因是MySQL重启后,通过select max(col) from tab for update的方式,来选出当前的最大值并赋值给auto_increment。
那么到了8.0以后,有什么变化呢?auto_increment的值会记录到redo log里了。什么概念,redo log大哥出手,就意味着下次重启还能把值要回来啊,不再随着重启打水漂。
除了redo log,在系统表中也会记录auto_increment的信息,例如tables表。那么,在插入新行时,递增列应该参考redo log还是系统表呢?而且从上面的例子可以看到,tables记录根本就不准确。其实答案就一句话,哪个是最新的,就用哪个。
那么回到系统表的问题上,既然要在重启后提供参考,总得靠谱一点吧?以前那么老实,到新版就变调皮了啊。看一下8.0以前的tables表定义:

mysql> show create table tables\G
*************************** 1. row ***************************
       Table: TABLES
Create Table: CREATE TEMPORARY TABLE `TABLES` (
  `TABLE_CATALOG` varchar(512) NOT NULL DEFAULT '',
  `TABLE_SCHEMA` varchar(64) NOT NULL DEFAULT '',
  `TABLE_NAME` varchar(64) NOT NULL DEFAULT '',
  `TABLE_TYPE` varchar(64) NOT NULL DEFAULT '',
  `ENGINE` varchar(64) DEFAULT NULL,
  `VERSION` bigint(21) unsigned DEFAULT NULL,
  `ROW_FORMAT` varchar(10) DEFAULT NULL,
  `TABLE_ROWS` bigint(21) unsigned DEFAULT NULL,
  `AVG_ROW_LENGTH` bigint(21) unsigned DEFAULT NULL,
  `DATA_LENGTH` bigint(21) unsigned DEFAULT NULL,
  `MAX_DATA_LENGTH` bigint(21) unsigned DEFAULT NULL,
  `INDEX_LENGTH` bigint(21) unsigned DEFAULT NULL,
  `DATA_FREE` bigint(21) unsigned DEFAULT NULL,
  `AUTO_INCREMENT` bigint(21) unsigned DEFAULT NULL,
  `CREATE_TIME` datetime DEFAULT NULL,
  `UPDATE_TIME` datetime DEFAULT NULL,
  `CHECK_TIME` datetime DEFAULT NULL,
  `TABLE_COLLATION` varchar(32) DEFAULT NULL,
  `CHECKSUM` bigint(21) unsigned DEFAULT NULL,
  `CREATE_OPTIONS` varchar(255) DEFAULT NULL,
  `TABLE_COMMENT` varchar(2048) NOT NULL DEFAULT ''
) ENGINE=MEMORY DEFAULT CHARSET=utf8

mysql8之后


mysql> show create table tables\G
*************************** 1. row ***************************
                View: TABLES
         Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`mysql.infoschema`@`localhost` SQL SECURITY DEFINER VIEW `TABLES` AS select `cat`.`name` AS `TABLE_CATALOG`,`sch`.`name` AS `TABLE_SCHEMA`,`tbl`.`name` AS `TABLE_NAME`,`tbl`.`type` AS `TABLE_TYPE`,if((`tbl`.`type` = 'BASE TABLE'),`tbl`.`engine`,NULL) AS `ENGINE`,if((`tbl`.`type` = 'VIEW'),NULL,10) AS `VERSION`,`tbl`.`row_format` AS `ROW_FORMAT`,internal_table_rows(`sch`.`name`,`tbl`.`name`,if(isnull(`tbl`.`partition_type`),`tbl`.`engine`,''),`tbl`.`se_private_id`,(`tbl`.`hidden` <> 'Visible'),`ts`.`se_private_data`,coalesce(`stat`.`table_rows`,0),coalesce(cast(`stat`.`cached_time` as unsigned),0)) AS `TABLE_ROWS`,internal_avg_row_length(`sch`.`name`,`tbl`.`name`,if(isnull(`tbl`.`partition_type`),`tbl`.`engine`,''),`tbl`.`se_private_id`,(`tbl`.`hidden` <> 'Visible'),`ts`.`se_private_data`,coalesce(`stat`.`avg_row_length`,0),coalesce(cast(`stat`.`cached_time` as unsigned),0)) AS `AVG_ROW_LENGTH`,internal_data_length(`sch`.`name`,`tbl`.`name`,if(isnull(`tbl`.`partition_type`),`tbl`.`engine`,''),`tbl`.`se_private_id`,(`tbl`.`hidden` <> 'Visible'),`ts`.`se_private_data`,coalesce(`stat`.`data_length`,0),coalesce(cast(`stat`.`cached_time` as unsigned),0)) AS `DATA_LENGTH`,internal_max_data_length(`sch`.`name`,`tbl`.`name`,if(isnull(`tbl`.`partition_type`),`tbl`.`engine`,''),`tbl`.`se_private_id`,(`tbl`.`hidden` <> 'Visible'),`ts`.`se_private_data`,coalesce(`stat`.`max_data_length`,0),coalesce(cast(`stat`.`cached_time` as unsigned),0)) AS `MAX_DATA_LENGTH`,internal_index_length(`sch`.`name`,`tbl`.`name`,if(isnull(`tbl`.`partition_type`),`tbl`.`engine`,''),`tbl`.`se_private_id`,(`tbl`.`hidden` <> 'Visible'),`ts`.`se_private_data`,coalesce(`stat`.`index_length`,0),coalesce(cast(`stat`.`cached_time` as unsigned),0)) AS `INDEX_LENGTH`,internal_data_free(`sch`.`name`,`tbl`.`name`,if(isnull(`tbl`.`partition_type`),`tbl`.`engine`,''),`tbl`.`se_private_id`,(`tbl`.`hidden` <> 'Visible'),`ts`.`se_private_data`,coalesce(`stat`.`data_free`,0),coalesce(cast(`stat`.`cached_time` as unsigned),0)) AS `DATA_FREE`,internal_auto_increment(`sch`.`name`,`tbl`.`name`,if(isnull(`tbl`.`partition_type`),`tbl`.`engine`,''),`tbl`.`se_private_id`,(`tbl`.`hidden` <> 'Visible'),`ts`.`se_private_data`,coalesce(`stat`.`auto_increment`,0),coalesce(cast(`stat`.`cached_time` as unsigned),0),`tbl`.`se_private_data`) AS `AUTO_INCREMENT`,`tbl`.`created` AS `CREATE_TIME`,internal_update_time(`sch`.`name`,`tbl`.`name`,if(isnull(`tbl`.`partition_type`),`tbl`.`engine`,''),`tbl`.`se_private_id`,(`tbl`.`hidden` <> 'Visible'),`ts`.`se_private_data`,coalesce(cast(`stat`.`update_time` as unsigned),0),coalesce(cast(`stat`.`cached_time` as unsigned),0)) AS `UPDATE_TIME`,internal_check_time(`sch`.`name`,`tbl`.`name`,if(isnull(`tbl`.`partition_type`),`tbl`.`engine`,''),`tbl`.`se_private_id`,(`tbl`.`hidden` <> 'Visible'),`ts`.`se_private_data`,coalesce(cast(`stat`.`check_time` as unsigned),0),coalesce(cast(`stat`.`cached_time` as unsigned),0)) AS `CHECK_TIME`,`col`.`name` AS `TABLE_COLLATION`,internal_checksum(`sch`.`name`,`tbl`.`name`,if(isnull(`tbl`.`partition_type`),`tbl`.`engine`,''),`tbl`.`se_private_id`,(`tbl`.`hidden` <> 'Visible'),`ts`.`se_private_data`,coalesce(`stat`.`checksum`,0),coalesce(cast(`stat`.`cached_time` as unsigned),0)) AS `CHECKSUM`,if((`tbl`.`type` = 'VIEW'),NULL,get_dd_create_options(`tbl`.`options`,if((ifnull(`tbl`.`partition_expression`,'NOT_PART_TBL') = 'NOT_PART_TBL'),0,1))) AS `CREATE_OPTIONS`,internal_get_comment_or_error(`sch`.`name`,`tbl`.`name`,`tbl`.`type`,`tbl`.`options`,`tbl`.`comment`) AS `TABLE_COMMENT` from (((((`mysql`.`tables` `tbl` join `mysql`.`schemata` `sch` on((`tbl`.`schema_id` = `sch`.`id`))) join `mysql`.`catalogs` `cat` on((`cat`.`id` = `sch`.`catalog_id`))) left join `mysql`.`collations` `col` on((`tbl`.`collation_id` = `col`.`id`))) left join `mysql`.`tablespaces` `ts` on((`tbl`.`tablespace_id` = `ts`.`id`))) left join `mysql`.`table_stats` `stat` on(((`tbl`.`name` = `stat`.`table_name`) and (`sch`.`name` = `stat`.`schema_name`)))) where (can_access_table(`sch`.`name`,`tbl`.`name`) and is_visible_dd_object(`tbl`.`hidden`))
character_set_client: utf8
collation_connection: utf8_general_ci

可以发现tables从原来的memory表变成视图了,auto_increment列引用自mysql.table_stats表的auto_increment列。于是,执行一下analyze,自增值就更新到最新了

analyze table tablename

 

<think>好的,我现在要解决用户的问题:如何防止或修复MySQL中数据被自动删除的问题。首先,我需要理解用户的具体情况。用户提到MySQL自动删除了数据,可能的原因有多种,比如误操作、配置问题、触发器或事件、主键不一致等。根据用户提供的引用内容,有几个关键点需要注意: 首先,引用[1]提到了sql_safe_updates=on时,如果没有WHERE条件或者使用索引字段,删除操作会被阻止。这可能和用户的情况有关,如果用户的数据被自动删除,可能是因为某些操作绕过了安全模式,或者有事件触发了删除。 引用[3]提到主键被占用后即使数据被删除,主键依然不可用,导致主键不连续。这可能和用户的问题关联不大,但需要确认是否有主键问题导致数据异常。 接下来,我需要考虑可能的解决方案。根据常见的MySQL问题,数据被自动删除的可能原因包括: 1. 误操作:用户或管理员不小心执行了DELETE语句。 2. 自动清理机制:如设置了定时任务或事件自动删除数据。 3. 触发器:某个表上的触发器在特定条件下自动删除数据。 4. 配置问题:比如某些配置导致数据被意外清理,比如binlog过期时间过短。 5. 外键约束:ON DELETE CASCADE导致级联删除。 6. 主从同步问题:从库执行了误操作。 针对这些可能性,我需要逐步排查。 首先,检查是否有自动删除的事件或任务。查看MySQL的事件调度器是否启用,并检查相关事件。用户可能需要检查information_schema.events表,查找是否有定时执行的DELETE语句。 其次,检查触发器。查看是否有触发器在特定操作后删除数据,可以使用SHOW TRIGGERS命令。 第三,检查外键约束。如果表之间有外键关系,并且设置了ON DELETE CASCADE,删除父表数据可能导致子表数据被删除。需要检查表结构中的外键设置。 第四,检查binlog和日志,确定删除操作的时间点和具体SQL语句。通过分析binlog或general log,可以找到执行删除的源头。 第五,确认是否启用了安全更新模式。如果sql_safe_updates=on,可能阻止无WHERE的删除,但如果关闭了,可能导致误操作。 根据引用[2]提到的数据恢复方法,如果数据已经被删除,可能需要从备份或binlog中恢复。但用户的问题更多是防止再次发生,所以预防措施更重要。 用户需要采取的步骤可能包括:启用安全模式、检查事件和触发器、审查外键约束、定期备份、监控日志等。同时,设置适当的权限,避免不必要的删除权限,也是重要的。 现在需要将这些思路整理成结构化的回答,分步骤说明原因和解决方法,并引用提供的资料中的相关信息。例如,引用[1]的安全模式设置,引用[3]的主键问题可能不直接相关,但需注意。 最后,生成相关问题,帮助用户进一步理解可能涉及的方面,比如如何恢复数据、如何设置安全模式等。</think>### 解决MySQL数据被自动删除问题的方法 #### 1. **确认数据删除原因** - **检查自动删除事件** 通过查询`information_schema.events`表,检查是否有定时任务自动执行`DELETE`操作: ```sql SELECT EVENT_NAME, EVENT_DEFINITION, INTERVAL_VALUE, INTERVAL_FIELD FROM information_schema.EVENTS WHERE EVENT_DEFINITION LIKE '%DELETE%'; ``` 如果存在相关事件,可通过`DROP EVENT`命令删除。 - **排查触发器** 使用`SHOW TRIGGERS`查看所有触发器,确认是否有触发器在特定操作(如`INSERT`、`UPDATE`)后触发删除: ```sql SHOW TRIGGERS FROM your_database_name; ``` 若存在可疑触发器,使用`DROP TRIGGER`删除。 - **外键约束导致的级联删除** 检查表结构中的外键是否包含`ON DELETE CASCADE`: ```sql SELECT TABLE_NAME, COLUMN_NAME, CONSTRAINT_NAME, REFERENCED_TABLE_NAME, REFERENCED_COLUMN_NAME, DELETE_RULE FROM information_schema.KEY_COLUMN_USAGE WHERE REFERENCED_TABLE_NAME IS NOT NULL AND DELETE_RULE = 'CASCADE'; ``` 若需禁用级联删除,需修改外键约束为`ON DELETE NO ACTION`。 --- #### 2. **启用安全防护机制** - **开启安全更新模式** 设置`sql_safe_updates=ON`,禁止无`WHERE`条件或未使用索引的`DELETE`/`UPDATE`操作[^1]: ```sql SET sql_safe_updates = 1; ``` 在配置文件`my.cnf`中添加以下配置以永久生效: ```ini [mysqld] sql_safe_updates = ON ``` - **限制用户权限** 为应用程序账户分配最小权限,避免授予全局`DELETE`权限: ```sql GRANT SELECT, INSERT, UPDATE ON your_database.* TO 'app_user'@'%'; ``` --- #### 3. **数据恢复与备份** - **从备份恢复数据** 若有定期备份(如通过`mysqldump`或物理备份工具),直接还原备份文件: ```bash mysql -u root -p your_database < backup.sql ``` - **通过Binlog恢复** 若未备份但开启了二进制日志,可通过`mysqlbinlog`工具解析并重放日志: ```bash mysqlbinlog --start-datetime="2024-01-01 00:00:00" --stop-datetime="2024-01-02 00:00:00" mysql-bin.000001 | mysql -u root -p ``` 需根据删除操作发生时间定位具体日志文件[^2]。 --- #### 4. **监控与日志分析** - **启用General Log** 临时开启通用查询日志,记录所有SQL语句: ```sql SET GLOBAL general_log = 'ON'; SET GLOBAL log_output = 'TABLE'; ``` 分析`mysql.general_log`表,查找可疑的`DELETE`语句。 - **审计插件(Enterprise版)** 若使用MySQL企业版,可通过审计插件记录高危操作。 --- #### 5. **避免主键冲突导致的数据异常** 若因主键不连续导致数据插入异常(如自增主键跳跃),可重置自增值: ```sql ALTER TABLE your_table AUTO_INCREMENT = 1; ``` 但需注意这可能与现有数据冲突[^3]。 ---
评论 1
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值