InnoDB官方文档--14.6.4 InnoDB数据字典|14.6.5 双写缓冲区

发表于 2019-03-30 更新于 2019-12-18 分类于文档翻译阅读次数：

14.6.4 InnoDB数据字典(InnoDB Data Dictionary)

InnoDB数据字典由内部系统表构成,包含用于跟踪对象如表,索引和表的列的元数据.元数据实际位于InnoDB系统表空间内.由于历史原因,数据字典元数据在某种程度上与InnoDB表元数据文件(.frm文件)中存储的信息重叠.

阅读全文 »

InnoDB官方文档--14.6.3 表空间(Tablespaces)

发表于 2019-03-25 更新于 2019-12-18 分类于文档翻译阅读次数：

14.6.3 表空间(Tablespaces)

14.6.3.1 系统表空间(The System Tablespace)

InnoDB系统表空间包含了InnoDB数据文件(InnoDB相关的对象元数据)并且存储区域是双写缓存,变更缓存和undo日志的存储区域.系统表空间也包含了用户创建在系统表空间中的表和索引数据.

系统表空间有一到多个数据文件.默认情况下名为 ibdata1的系统表空间数据文件被创建在数据目录下.系统表空间的大小和数量由innodb_data_file_path启动选项控制.

调整系统表空间(Resizing the System Tablespace)

增加InnoDB系统表空间的大小

增加InnoDB系统表空间大小的最简单方法是从一开始就将其配置为自动扩展.在表空间定义中指定最后一个数据文件为autoextend属性.然后InnoDB在空间不足时以64MB为增量自动增加该文件的大小.可以通过设置innodb_autoextend_increment系统变量的值来更改增量大小,以MB为单位.

你可以添加另一个数据文件指定数量来扩展系统表空间:

关闭MySQL服务器.
如果之前的最后一个数据文件定义了关键字autoextend,根据基于实际增长的大小改变其定义为固定的大小.检查数据文件的大小,将其向下舍入为最接近的1024×1024字节的倍数(1MB),然后在innodb_data_file_path指定这个大小.
在innodb_data_file_path配置后添加一个新的数据文件,配置其自动扩展.只有innodb_data_file_path中的最后一个文件可以被指定为自动扩展.
启动MySQL服务器.
阅读全文 »

InnoDB官方文档--14.6.2 索引(Indexes)

发表于 2019-03-10 更新于 2019-12-18 分类于文档翻译阅读次数：

14.6.2 索引(Indexes)

14.6.2.1 聚簇和二级索引(Clustered and Secondary Indexes)

每个InnoDB表都有一个称作聚簇索引的特殊索引,存储了行的数据.通常情况下,聚簇索引是主键的代名词.为了从查询,插入和其他数据库操作中获得最好的性能,你必须了解InnoDB如何使用聚簇索引来优化每个表最常见的查找和DML操作.

当你在表上定义了主键,InnoDB用其作为聚簇索引.给你创建的每个表定义一个主键.如果没有逻辑上唯一和非空的列或列的集合,添加一个新的自增列(作为主键),它的值会自动填充.
如果你没有为表定义主键,MySQL会找到第一个唯一索引,其中所有的列键都是非空的并且InnoDB会将其作为聚簇索引.
如果表没有主键或合适的唯一索引,InnoDB会在包含行ID值的合成列内部生成一个名为GEN_CLUST_INDEX的隐藏的聚簇索引.行数据按照InnoDB给这类表分配的ID来排序.行ID是一个6位的字段,随着新行的插入单调递增.因此,行通过ID的排序顺序和物理插入顺序一致.
阅读全文 »

阿里云RDS逻辑备份恢复主从

发表于 2019-03-02 更新于 2019-12-18 分类于问题解决阅读次数：

问题产生

在主从备份恢复没多久之后,从库又出现了新的错误:

The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains
information that should help you find out what is causing the crash.
190220 00:00:01 mysqld_safe Number of processes running now: 0
190220 00:00:01 mysqld_safe mysqld restarted
2019-02-20 00:00:02 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2019-02-20 00:00:02 0 [Note] /usr/sbin/mysqld (mysqld 5.6.39-log) starting as process 31815 ...
2019-02-20 00:00:02 31815 [Note] Plugin 'FEDERATED' is disabled.
2019-02-20 00:00:02 31815 [Note] InnoDB: Using atomics to ref count buffer pool pages
2019-02-20 00:00:02 31815 [Note] InnoDB: The InnoDB memory heap is disabled
2019-02-20 00:00:02 31815 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2019-02-20 00:00:02 31815 [Note] InnoDB: Memory barrier is not used
2019-02-20 00:00:02 31815 [Note] InnoDB: Compressed tables use zlib 1.2.3
2019-02-20 00:00:02 31815 [Note] InnoDB: Using Linux native AIO
2019-02-20 00:00:02 31815 [Note] InnoDB: Using CPU crc32 instructions
2019-02-20 00:00:02 31815 [Note] InnoDB: Initializing buffer pool, size = 128.0M
2019-02-20 00:00:02 31815 [Note] InnoDB: Completed initialization of buffer pool
2019-02-20 00:00:02 31815 [Note] InnoDB: Highest supported file format is Barracuda.
2019-02-20 00:00:02 31815 [Note] InnoDB: Log scan progressed past the checkpoint lsn 17886034157
2019-02-20 00:00:02 31815 [Note] InnoDB: Database was not shutdown normally!
2019-02-20 00:00:02 31815 [Note] InnoDB: Starting crash recovery.
2019-02-20 00:00:02 31815 [Note] InnoDB: Reading tablespace information from the .ibd files...
2019-02-20 00:00:02 31815 [Note] InnoDB: Restoring possible half-written data pages 
2019-02-20 00:00:02 31815 [Note] InnoDB: from the doublewrite buffer...
InnoDB: Doing recovery: scanned up to log sequence number 17886040149
InnoDB: Last MySQL binlog file position 0 5052574, file name mysql-bin.000002
2019-02-20 00:00:02 31815 [Note] InnoDB: 128 rollback segment(s) are active.
2019-02-20 00:00:02 31815 [Note] InnoDB: Waiting for purge to start
2019-02-20 00:00:02 31815 [Note] InnoDB: 5.6.39 started; log sequence number 17886040149
2019-02-20 00:00:02 31815 [Note] Recovering after a crash using /data/log/mysql/mysql-bin
2019-02-20 00:00:02 31815 [Note] Starting crash recovery...
2019-02-20 00:00:02 31815 [Note] Crash recovery finished.
2019-02-20 00:00:02 31815 [Note] Server hostname (bind-address): '*'; port: 3306
2019-02-20 00:00:02 31815 [Note] IPv6 is available.
2019-02-20 00:00:02 31815 [Note]   - '::' resolves to '::';
2019-02-20 00:00:02 31815 [Note] Server socket created on IP: '::'.
2019-02-20 00:00:02 31815 [Warning] 'user' entry 'root@izbp127nd1c1ngjebt90o3z' ignored in --skip-name-resolve mode.
2019-02-20 00:00:02 31815 [Warning] 'user' entry '@izbp127nd1c1ngjebt90o3z' ignored in --skip-name-resolve mode.
2019-02-20 00:00:02 31815 [Warning] 'proxies_priv' entry '@ root@izbp127nd1c1ngjebt90o3z' ignored in --skip-name-resolve mode.
2019-02-20 00:00:02 31815 [Warning] Neither --relay-log nor --relay-log-index were used; so replication may break when this MySQL server acts as a slave and has his hostname changed!! Please use '--relay-log=mysqld-relay-bin' to avoid this problem.
2019-02-20 00:00:02 31815 [Warning] Storing MySQL user name or password information in the master info repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START SLAVE; see the 'START SLAVE Syntax'
 in the MySQL Manual for more information.
2019-02-20 00:00:02 31815 [Note] Slave I/O thread: connected to master 'slave-001-read@rm-bp16vmecn3tzzl51u.mysql.rds.aliyuncs.com:3306',replication started in log 'mysql-bin.001423' at position 4492905
2019-02-20 00:00:02 31815 [Note] Event Scheduler: Loaded 0 events
2019-02-20 00:00:02 31815 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.6.39-log'  socket: '/data/mysql/mysql.sock'  port: 3306  MySQL Community Server (GPL)
2019-02-20 00:00:02 31815 [Warning] Slave SQL: If a crash happens this configuration does not guarantee that the relay log info will be consistent, Error_code: 0
2019-02-20 00:00:02 31815 [Note] Slave SQL thread initialized, starting replication in log 'mysql-bin.001422' at position 1778562, relay log './mysqld-relay-bin.000006' position: 1778732
2019-02-20 00:00:02 7f3b7850e700  InnoDB: Assertion failure in thread 139893398365952 in file pars0pars.cc line 865
InnoDB: Failing assertion: sym_node->table != NULL
InnoDB: We intentionally generate a memory trap.
InnoDB: Submit a detailed bug report to http://bugs.mysql.com.
InnoDB: If you get repeated assertion failures or crashes, even
InnoDB: immediately after the mysqld startup, there may be
InnoDB: corruption in the InnoDB tablespace. Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.6/en/forcing-innodb-recovery.html
InnoDB: about forcing recovery.
16:00:02 UTC - mysqld got signal 6 ;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
We will try our best to scrape up some info that will hopefully help
diagnose the problem, but since we have already crashed, 
something is definitely wrong and this may fail.

key_buffer_size=8388608
read_buffer_size=131072
max_used_connections=0
max_threads=2000
thread_count=2
connection_count=0
It is possible that mysqld could use up to 
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 801785 K  bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

Thread pointer: 0x7f3b48000990
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
stack_bottom = 7f3b7850d9d0 thread_stack 0x40000
/usr/sbin/mysqld(my_print_stacktrace+0x3b)[0x8e986b]
/usr/sbin/mysqld(handle_fatal_signal+0x491)[0x675b01]
/lib64/libpthread.so.0(+0xf5e0)[0x7f3bb5b895e0]
/lib64/libc.so.6(gsignal+0x37)[0x7f3bb47831f7]
/lib64/libc.so.6(abort+0x148)[0x7f3bb47848e8]
/usr/sbin/mysqld[0x988ce7]
/usr/sbin/mysqld(_Z7yyparsev+0x1e7f)[0xabecdf]
/usr/sbin/mysqld[0x98af81]
/usr/sbin/mysqld[0xaba9f8]
/usr/sbin/mysqld[0xaa0786]
/usr/sbin/mysqld[0xaad730]
/usr/sbin/mysqld[0xaad985]
/usr/sbin/mysqld[0xaadf10]
/usr/sbin/mysqld[0x9b1d78]
/usr/sbin/mysqld[0x921478]
/usr/sbin/mysqld(_ZN7handler12ha_write_rowEPh+0xaa)[0x5bdbfa]
/usr/sbin/mysqld(_ZN20Write_rows_log_event9write_rowEPK14Relay_log_infob+0x135)[0x8804d5]
/usr/sbin/mysqld(_ZN20Write_rows_log_event11do_exec_rowEPK14Relay_log_info+0x19)[0x8807f9]
/usr/sbin/mysqld(_ZN14Rows_log_event12do_apply_rowEPK14Relay_log_info+0x26)[0x870586]
/usr/sbin/mysqld(_ZN14Rows_log_event14do_apply_eventEPK14Relay_log_info+0x566)[0x87e616]
/usr/sbin/mysqld(_ZN9Log_event11apply_eventEP14Relay_log_info+0x6a)[0x877dfa]
/usr/sbin/mysqld(_Z26apply_event_and_update_posPP9Log_eventP3THDP14Relay_log_info+0x258)[0x8affa8]
/usr/sbin/mysqld(handle_slave_sql+0x17ce)[0x8b3dde]
/usr/sbin/mysqld(pfs_spawn_thread+0x146)[0xb417a6]
/lib64/libpthread.so.0(+0x7e25)[0x7f3bb5b81e25]
/lib64/libc.so.6(clone+0x6d)[0x7f3bb484634d]

Trying to get some variables.
Some pointers may be invalid and cause the dump to abort.
Query (0): Connection ID (thread ID): 2
Status: NOT_KILLED

阅读全文 »

InnoDB官方文档--14.6 InnoDB在磁盘上的结构--14.6.1 表(Tables)

发表于 2019-03-01 更新于 2019-12-18 分类于文档翻译阅读次数：

14.6 InnoDB在磁盘上的结构(InnoDB On-Disk Structures)

14.6.1 表(Tables)

14.6.1.1 创建InnoDB表

使用CREATE TABLE语句来创建一个InnoDB表

1	CREATE TABLE t1 (a INT, b CHAR (20), PRIMARY KEY (a)) ENGINE=InnoDB;

如果InnoDB是默认的存储引擎的话你不需要特定指定ENGINE=InnoDB,可以使用下面语句检查默认的存储引擎

mysql> SELECT @@default_storage_engine;
+--------------------------+
| @@default_storage_engine |
+--------------------------+
| InnoDB                   |
+--------------------------+

阅读全文 »

InnoDB官方文档--14.4 InnoDB 架构|14.5 InnoDB 在内存中的结构

发表于 2019-02-18 更新于 2019-12-18 分类于文档翻译阅读次数：

14.4 InnoDB 架构

阅读全文 »

记一次从阿里云rds备份单库到服务器

发表于 2019-02-16 更新于 2019-12-18 分类于问题解决阅读次数：

问题产生

在线上主从同步的slave数据库中产生了如下的错误：

1
2

ERROR 1787 (HY000): When @@GLOBAL.ENFORCE_GTID_CONSISTENCY = 1, the statements CREATE TEMPORARY TABLE and 
DROP TEMPORARY TABLE can be executed in a non-transactional context only, and require that AUTOCOMMIT = 1.

使用show slave status可以查看从库当前的同步状态,发现I/O状态已经变为NO,尝试使用:

1	set global sql_slave_skip_counter = 1;

之后产生如下的问题:

ERROR 1858 (HY000): sql_slave_skip_counter can not be set when the server is running with @@GLOBAL.GTID_MODE = ON. Instead, for each transaction that you want to skip, generate an empty transaction with the same GTID as the transaction

根据错误说明使用一个空事务来跳过异常,代码如下:

STOP SLAVE;
SET GTID_NEXT=# binlog中事务的下一个位置;
BEGIN; COMMIT; # 产生一个空事务并提交
SET GTID_NEXT="AUTOMATIC";
START SLAVE;

然而这样会导致主从的数据可能产生不一致的情况,这是我们想要避免的,并且希望主库可以不锁表导出数据即可进行备份.

阅读全文 »

InnoDB官方文档--14.3 InnoDB的多版本

发表于 2019-02-16 更新于 2019-12-18 分类于文档翻译阅读次数：

14.3 InnoDB的多版本

InnoDB是一个多版本存储引擎:它会保留关于行变动的旧版本信息用来支持事务特性,例如并发和回滚(rollback).这些信息被称为回滚段(rollback segment),存储在表空间中.InnoDB使用回滚段中的信息来执行事务回滚中所需要的撤销操作.还使用该信息构建早期版本以进行一致的行读取.

在内部,InnoDB为存储在数据库的每一行添加三个字段.一个6位的DB_TRX_ID字段用来标识插入或更新行的最后一个事务.此外,删除在内部被视为更新,行的一个特殊位用来标记是否删除.每一行还包含一个7位的DB_ROLL_PTR字段叫做滚动指针(roll pointer).滚动指针指向写入回滚段的撤消日志记录.如果行被更新了,那么撤消日志记录了用来重建被更新之前的行内容所必要的信息.一个6位的DB_ROW_ID字段包含了一个行ID在插入的时候递增.如果InnoDB自动生成聚簇索引,这个索引会包含行ID的值.否则,DB_ROW_ID列不会出现在任何索引中.

回滚段中的撤销日志分为插入和更新的撤销日志(undo logs).插入的回滚日志只在事务回滚的时候需要并且在事务提交之后可以立即丢弃.更新的撤销日志也被用于一致性读,但是只有在InnoDB没有分配快照的事务之后才能丢弃它们,在一致读取中可能需要更新撤消日志中的信息来构建数据库行的早期版本.

定期提交你的事务,包括那些只有一致性读的事务.否则,InnoDB将不能从更新的撤销日志里丢弃数据使得回滚段变得过大而填满表空间.

回滚段中撤消日志记录的物理大小通常小于相应的插入或更新行.你可以使用此信息计算回滚段所需的空间.

在InnoDB多版本方案中,当你使用sql语句删除行的时候不会马上从数据库中物理删除.InnoDB只在丢弃为删除而写的更新撤销日志的时候才会物理的删除相应的行.这个删除操作叫做清除(purge),它很快,通常采用和sql语句删除时相同的顺序.

如果你在表中以相同的速率执行小批量的插入和删除操作,清除线程可能开始落后,因为那些”死掉”的行,表会变得越来越大,使得一切都受磁盘限制变得很慢.在这种情况下,通过调整innodb_max_purge_lag系统变量来限制新行操作,并为清除线程分配更多资源.

阅读全文 »

InnoDB官方文档--14.2 InnoDB 和 ACID 模型

发表于 2019-02-16 更新于 2019-12-18 分类于文档翻译阅读次数：

14.2 InnoDB 和 ACID 模型

ACID模型是一系列的数据库设计准则,强调对业务数据和关键应用非常重要的可靠性方面.MySQL包括了像InnoDB存储引擎这样遵守ACID模型的组件所以数据不会因为例如软件崩溃和硬件故障这样的意外情况而遭到破坏或者丢失.当你依赖ACID相关特性的时候,你不需要重新发明一致性检查和崩溃恢复机制.如果你有其他附加的软件保障,高可用的硬件或者一个可以容忍小部分数据的丢失或者不一致的话,你可以调整MySQL设置用ACID可靠性来交换更好的性能和吞吐量.

下面讨论MySQL的特性尤其是InnoDB存储引擎是如何与ACID模型关联的.

A: 原子性(atomicity).
C: 一致性(consistency).
I: 隔离性(isolation).
D: 持久性(durability).
阅读全文 »

InnoDB官方文档--14.1 InnoDB简介

发表于 2019-02-16 更新于 2019-12-18 分类于文档翻译阅读次数：

14.1 InnoDB简介

InnoDB是一个平衡了高可用和高性能的通用存储引擎.在Mysql 5.7中已经作为默认的存储引擎使用.

关键优势:

DML操作遵循ACID原则,具有事务的提交,回滚和崩溃恢复能力
行级锁和Oracle风格的一致性读保证了多用户并发和性能
InnoDB在硬盘上基于主键来管理数据用来优化查询.每一个InnoDB表都有一个主键索引被称作聚簇索引来组织数据,在主键查找的时候可以最小化I/O.
为了保持数据完整性,InnoDB支持外键约束.在外键作用下,inserts,updates和deletes操作会被检查以确保在不同的表间不会出现数据不一致.
阅读全文 »