Lixora's DB Home

前面介绍数据字典是 MySQL 维护元数据的模块，MySQL 真正操作表数据是基于表定义对象 TABLE，和存储引擎和数据打交道，每个 TABLE 内有一个handler，表示使用的引擎对象，Innodb 引擎对应的表定义信息是 dict_table_t，TABLE 对表保留有操作数据的必备的信息，如表及字段的特征信息，元数据锁，record 查找结果等。每次执行 SQL 时，都会将涉及的表列表（TABLE_LIST，如 Join 多个表）逐个 open，

1234567891011121314151617181920212223242526272829。

In this DocumentConsidering a Virtualized environment with a SSD based SAN, what are the configuration parameters to be modified and their suggested values. Usually the SAN is not exposed to the VM directly. Instead VMs use the file system exposed by t

In this DocumentGuidance for configuring the server when seeing these messages frequently:[Note] InnoDB: page_cleaner: 1000ms intended loop took 4120ms. The settings might not be optimal. (flushed=0 and evicted=85273, during the time.) [Note] InnoDB: pa

可以通过dbdeplyoer defaults export导出并修改配置或者直接通过dbdeployer defaults update来更新默认文件，默认配置文件为当前用户的$HOME/.dbdeployer/config.json作为配置文件，##安装单实例Percona Server。#部署一套1M1S的gtid复制集群。配置文件包含MySQL初始信息。##部署mysql 单实例环境。##下载在线tarball。

mysql 8.0 由原来的一个大buffer pool mutex被拆分成多个为free_list, LRU_list, zip_free, 和zip_hash单独使用mutex:

当在3之前崩溃 重启恢复：虽没有commit，但满足prepare和binlog完整，所以重启后会自动commit。备份：有binlog. 一致。binlog和redolog 有一个共同的数据字段,叫XID。2.如果碰到只有prepare、而没有commit的redo log,就拿着XID去binlog找对应的事务。当在2之前崩溃时 重启恢复：后发现没有commit，回滚。备份恢复：没有binlog。1.如果碰到既有prepare、又有commit的redo log,就直接提交。

2.RR 隔离级别下，只有select 操作在整个事务期间使用一个 readview ，而dml 操作（insert update，delete）都是read commit（每个操作单独一个readview） 的；1.RR级别在MySQL里意味着在一个事务活跃期间，快照会一直打开，这可能会导致Undo/垃圾数据不能及时清理，Undo空间膨胀。没啥业务上必须的理由，尽量用RC。| 20593 |自己事务里的操作是对自己可见的，直接剪掉affected rows 的计数 20594 -1=20593。..

mysql 勒索病毒loki 加密数据恢复

##为什么mysql 建议innodb 表使用自增主键？1.使数据插入变成顺序IO，减少随机IO，避免索引页（branch page）/leaf block 重组，分裂；2.节约ibd文件物理存储空间，节省物理IO（随机IO）浪费

##leaf blocks 为什么b+tree索引叶子块 叶块上有上双向链表？为了避免由于范围查询扫描（ 类似 < 10 ,>10 等谓词条件查询）导致的重复branch blocks 扫描浪费IO，增加查询延时，可以直接扫描leaf blocks 定位（leaf block 是逻辑上有序的，leaf block 中有pre block 块位置偏移，next block 块位置偏移 ）...

eg：Select * from abc.lixora a where a.id=100;根据sql 解析完成后知道表，根据元数据知道对应的ibd 物理文件，读取ibd inode 相关信息（leaf segment，non-leaf segment），获取索引组织表的root index page ，读取 root index page到内存里，（根据key 分布找到下一级non-leaf block），根据key 值找到最终的leaf page（在非叶子节点中利用索引页内的页目录...

mysql 8025 故障日志：2022-02-23T13:59:27.477020Z 0 [Warning] [MY-010909] [Server] /root/opt/mysql/8.0.25/bin/mysqld: Forcing close of thread 8 user: 'root'.2022-02-23T13:59:27.926166Z 0 [System] [MY-010910] [Server] /root/opt/mysql/8.0.25/bin/mysqld: Shutd.

C:\Users\Administrator\page_stream>page_stream.exe!!! Please input tablespace file(path)##--------------------------------------------------------#### Copy right 2021-2099 powered by HuangLinJie 177-6715-1782## Usage: ./page_stream -f ./lixora/bi..

##故障日志InnoDB: End of page dumpInnoDB: Page may be an insert buffer bitmap page2021-12-13T11:17:43.257770Z 0 [ERROR] [MY-011899] [InnoDB] [FATAL] Unable to read page [page id: space=2, page number=1] into the buffer pool after 100 attempts. The most pro

File page types (values of FIL_PAGE_TYPE) 十六进制 十进制 解释 备注 FIL_PAGE_INDEX 45bf 17855 /** B-tree node */B+树（leaf，non-leaf page） 　 ，每个B+ tree使用两个segment来管理page，分别是leaf node segment（叶子节点segment）和non-leaf node...

报错信息：在使用 root 用户直接运行 #mysqld_safemysql无法启动报错如下2018-03-02 16:29:06 27644 [Note]180302 16:29:06 mysqld_safe mysqld from pid file /monitor/mysql/data/service2.pid ended2018-03-02 16:29:03 27644 [ERROR]...

##问题描述 研发兄弟反馈说，他们的数据库中有一些表不能正常查询。开发同学在线cats 操作复制测试表，导致linux 服务器目录空间满，然后他们自己强制重启了mysql。。。。。。mysql-lixora > select * from test_part;ERROR 2013 (HY000): Lost connection to MySQL server during query##故障日志：2021-12-01T01:28:14.280281Z 8 [E...

### my.cnf for 8.0版本## author: yejr(yejinrong@zhishutang.com, http://imysql.com, QQ: 4700963)#### 叶金荣(yejr)## 国内知名MySQL专家，MySQL布道师，Oracle MySQL ACE Director，腾讯云TVP成员。## 微信公众：老叶茶馆(imysql_wx), 博客：https://imysql.com## QQ群: 125572178## 注意：个别建议可能需要根据实际情

Mysql容灾环境规划 主机名 IP/Port ROLE OS Version MySQL Version GTID Mode Binlog Format MTS Lixora 192.168.1.99/3306 master CentOS 7.1 ..

IMPLEMENTATION OF THE BUFFER POOL =================================Performance improvement:------------------------Thread scheduling in NT may be so slow that the OS wait mechanism shouldnot be used even in waiting for disk reads to co...

在线上truncate 了一张2000w 的表：mysql [localhost:5724] {root} (test) > truncate table t1;Query OK, 0 rows affected (1 min 37.19 sec)发现全局系统都慢了一下，processlist 中发现truncate操作处于system lockmysql [localh...

mysql [localhost:5642] {msandbox} (test) >mysql [localhost:5642] {msandbox} (test) > show keys from geek;+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+-----

Which Locks Does OPTIMIZE TABLE Take? (文档 ID 1910515.1) 转到底部 APPLIES TO: MySQL Server - Version 4.0 and later Information in this document applies to any platform. GOAL Learn what the impact of executingOPTIMIZE TABLEis ...

2020-09-26T02:08:47.027083Z 0 [Note] InnoDB: Starting crash recovery.2020-09-26T02:08:47.027083Z 0 [Note] InnoDB: Reading tablespace information from the .ibd files...2020-09-26T02:08:47.068115Z 0 [Note] InnoDB: Restoring possible half-written data page.

How To Skip a Transaction When GTIDs Are Enabled? (Doc ID 2043205.1) To Bottom Goal Solution References APPLIES TO: MySQL Server - Version 5.6 and later Information in this document applies ...

Clone a Replication Slave From a Live Master using Global Transaction IDs (GTID) and mysqldump (Doc ID 1589291.1) To Bottom In this Document Goal Solution References APPLIES TO: MySQL Serve...

Change Replication Topology A->B,C to A->B->C (Doc ID 1444892.1) To Bottom In this Document APPLIES TO: MySQL Server - Version 5.5 and later Information in this document applies to any platform. GOAL Change from mas

Mysql容灾环境规划 主机名 IP/Port ROLE OS Version MySQL Version GTID Mode Binlog Format Lixora 192.168.1.99/3306 master CentOS 7.1 5.7.24

###问题日志：2020-07-16T01:26:34.626656Z 0 [Note] InnoDB: Highest supported file format is Barracuda.2020-07-16T01:26:34.671803Z 0 [ERROR] InnoDB: Operating system error number 13 in a file operation.2020-07-16T01:26:34.671866Z 0 [ERROR] InnoDB: The error m

花了一点时间做了一个window版的 undrop for innodb ，用于windows 平台误操作（delete，drop，truncate）数据恢复，同时支持window 平台文件系统损坏时，在底层文件系统上上进行mysql ibd 挖掘，尽可能的恢复mysql 数据；mysql innodb dul for windows 平台undrop-for-innodb-windowsmysql 紧急技术恢复服务热线：1565865544 7...

mysql ibd 数据文件恢复极速恢复最近不少朋友遇到mysql 丢失ibddata 数据文件或者文件系统损坏只有部分ibd 文件的故障找我来帮忙处理，有些朋友的环境有几百上千个ibd 文件，手动恢复着实麻烦，写了一个工具，批量恢复mysql innodb，（myasim） ibd 的工具，目前可以在windows 和linux运行；mysql 的文件解析还是有点累的，搞得有点想吐ibd...

WhenALTER TABLE ... IMPORT TABLESPACEis run on the destination instance, the import algorithm performs the following operations for the tablespace being imported: Each tablespace page is checked ...

最近花了点时间做了一个mysql 和 postgresql 的数据库健康巡检报告；

"Error: 2013, Lost connection to MySQL server at 'reading authorization packet'" in MySQL Server Error Log (Doc ID 1024157.1) To Bottom APPLIES TO: MySQL Server - Version 4.0 and lat...

###语句兼容mysql 5.6，5.7，8.0###top 10 sql:SELECT SCHEMA_NAME, DIGEST_TEXT, COUNT_STAR, sys.format_time ( SUM_TIMER_WAIT ) AS sum_time, sys.format_time ( MIN_TIMER_WAIT ) AS min_t...

mysql 5.6 vs 5.7 vs 8.0 版本优化器参数对比：mysql 5.6.42@@optimizer_switch: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condit...

1）file-per-table tablespace 、system tablespace -----》 generaltablespaceALTER TABLE tbl_name TABLESPACE [=] tablespace_name;eg：alter table t1 tablespace ts1;move 完table后表原来的ibd 文件会被删除；2）...

The innodb_data_home_dir default value is the MySQL data directory (datadir). innodb_data_home_dir ：The common part of the directory path for InnoDB system tablespace data files. innodb_data_file_path...

mysql>use lixora;mysql> CREATE TABLE t5(id int(11)) ENGINE=InnoDBdddddddd;ERROR 1286 (42000): Unknown storage engine 'InnoDBdddddddd'mysql> SELECT @@sql_mode;+-----------------------------