Default storage engine (InnoDB) is not available的解决方法

自己用的MYSQL都是用MYISAM数据库,还没涉及到需要INNODB,因此打算直接不加载INNODB引擎。

在my.ini(linux下/etc/my.cnf)加上skip-innodb,就可以了。

我这样设置后,在linux下都没问题,今天在我本机winXP启动MYSQL,提示启动不起来。看下mysql目录的错误日志:

090613 10:15:27 [ERROR] Default storage engine (InnoDB) is not available

090613 10:15:27 [ERROR] Aborting

090613 10:15:27 [Note] C:wwwmysqlbinmysqld-nt: Shutdown complete

估计是上次加了参数后,一直没启动起来。。。已经很久没用本机的mysql了。

查了下,原来my.ini里有一句:default-storage-engine=INNODB,把默认的engine设为INNODB,而我又加了skip-innodb,怪不得启不来了。

将default-storage-engine改为MYISAM,MYSQL启动OK!
在skip-innodb下,data目录下的ibdata1,ib_logfile0,ib_logfile1这三个文件就不需要了,就可以删除。

测试了下,在没加skip-innodb时,删除了这三个文件,启动时会自动创建这三个文件,日志如下:

InnoDB: The first specified data file .ibdata1 did not exist:
InnoDB: a new database to be created!
090613 10:19:37 InnoDB: Setting file .ibdata1 size to 10 MB
InnoDB: Database physically writes the file full: wait...
090613 10:19:37 InnoDB: Log file .ib_logfile0 did not exist: new to be created
InnoDB: Setting log file .ib_logfile0 size to 24 MB
InnoDB: Database physically writes the file full: wait...
090613 10:19:38 InnoDB: Log file .ib_logfile1 did not exist: new to be created
InnoDB: Setting log file .ib_logfile1 size to 24 MB
InnoDB: Database physically writes the file full: wait...
InnoDB: Doublewrite buffer not found: creating new
InnoDB: Doublewrite buffer created
InnoDB: Creating foreign key constraint system tables
InnoDB: Foreign key constraint system tables created
090613 10:19:39 InnoDB: Started; log sequence number 0 0
090613 10:19:39 [Note] C:wwwmysqlbinmysqld-nt: ready for connections.

再删除这三个文件,加了skip-innodb后,启动日志:

090613 10:30:12 [Note] C:wwwmysqlbinmysqld-nt: Normal shutdown

090613 10:30:12 [Note] C:wwwmysqlbinmysqld-nt: Shutdown complete

090613 10:30:17 [Note] C:wwwmysqlbinmysqld-nt: ready for connections.

Version: '5.0.45' socket: '' port: 3306 Source distribution

那三个文件就不会自动产生了。

InnoDB的ib_logfile写入策略

ib_logfile是InnoDB的事务日志文件。本文简要说明其写入时机、写入策略及如何保证数据安全。

一、基本概念

  1. redo是物理日志,记录的是页的物理修改操作,是幂等的。
  2. ib_logfile文件个数由innodb_log_files_in_group配置决定,若为2,则在datadir目录下有两个文件,命令从0开始,分别为ib_logfile0和ib_logfile.
  3. 文件为顺序写入,当达到最后一个文件末尾时,会从第一个文件开始顺序复用。
  4. lsn: Log Sequence Number,是一个递增的整数。 Ib_logfile中的每次写入操作都包含至少1个log,每个log都带有一个lsn。在内存page修复过程中,只有大于page_lsn的log才会被使用。
  5. lsn的保存在全局内存结构log_sys中。递增数值等于每个log的实际内容长度。即如果新增的一个log长度是len,则log_sys->lsn += len.
  6. ib_logfile每次写入以512(OS_FILE_LOG_BLOCK_SIZE)字节为单位。实际写入函数 log_group_write_buf(log/log0log.c)
  7. 每次写盘后是否flush,由参数innodb_flush_log_at_trx_commit控制。

二、log_sys介绍

log_sys是一个全局内存结构。以下说明几个成员的意义。

未分类

三、相关更新

用一个简单的更新语句来说明log_sys以及ib_logfile的更新内容的过程。假设我们的更新只涉及到非索引的固定长度字段。

  1. 在bufferpool中写入undo log。 对于一个单一的语句,需要先创建一个undolog头。
  2. 在bufferpool中写入undo log的实际内容。
  3. 在log_sys->buf中写入buffer page的更新内容。此处保存了更新的完整信息。
  4. 在log_sys->buf中写入启动事务(trx_prepare)的日志。
  5. 将c、d更新的log内容写入ib_logfile中。
  6. 在log_sys->buf中写入事务结束(trx_commit)的日志。
  7. 将f步骤的log内容写入ib_logfile中。

四、说明

完成上述所有操作时,数据文件还没有更新。

  1. 每次写入log_sys->buf时同时更新lsn和buf_free。 每次写ib_logfile时同时更新written_to_all_lsn和buf_next_to_write;
  2. 每次写ib_logfile时以512字节为对齐,如需写入600字节,则实际写入1k。写到最后一个文件末尾则从第一个文件重复使用。
  3. 从上述流程看到,在a~d过程中若出现异常关闭,由于没有写入到磁盘中,因此整个事务放弃;若在e刚完成时出现异常关闭,虽然事务内容已经写盘,但没有提交。在重启恢复的时候,发现这个事务还没有提交,逻辑上整个事务放弃。 (重启日志中会有Found 1 prepared transaction(s) in InnoDB字样)。在g完成后出现异常关闭,则能够在重启恢复中正常提交。
  4. 在e和f之间会写mysql的bin-log,若bin-log写完前异常关闭,事务无效,bin-log写入成功后,则异常重启后能够根据bin-log恢复事务的修改。
  5. 若涉及到索引更新,在步骤c之后会增加索引更新的log。由于索引可能有merge过程,因此在merge过程中会另外增加写入一个log。但事务完全提交仍在步骤g中。索引的更新由于已经写盘,并不会因此丢失。

MySQL中Innodb如何计算索引的统计信息?

摘要: MySQL查询优化器的执行计划是根据统计信息中键值的分布选择合适的索引这是基于索引的选择性的。innodb通过抽样的方式来计算统计信息首先随机的读取少量的索引页面然后以此为样本计算索引的统计信息。老的innodb默认样本页面数为8新版本可以通过innodb_stats_transient_sample_pages5.6.3之前是innodb_stats_sample_pages来设置样本页的数量。

MySQL查询优化器的执行计划是根据统计信息中键值的分布选择合适的索引,这是基于索引的选择性的。innodb通过抽样的方式来计算统计信息,首先随机的读取少量的索引页面,然后以此为样本计算索引的统计信息。老的innodb默认样本页面数为8,新版本可以通过innodb_stats_transient_sample_pages(5.6.3之前是innodb_stats_sample_pages)来设置样本页的数量。样本页的数量设置的更大,理论上来说是可以得到更准确的统计信息,特别是对于超大的表。但是具体设置多大合适还是需要根据实际情况

innodb索引的统计信息存储方式有两种,一种是非持久性存储,既存储在内存中,如果服务器重启就会丢失;一种是持久性存储,即存储到磁盘上,可以永久保存。通过参数innodb_stats_persistent来控制。在MySQL5.6.6之后,默认是持久性存储。

两种存储方式:

非持久性存储,通过设置innodb_stats_persistent=OFF或者使用STATS_PERSISTENT=0创建,通过以下操作可以触发计算统计信息:

a) 执行analyze table

b) 在使用show table status、show index等命令的时候,或者在查询系统表INFORMATION_SCHEMA.TABLES 和 INFORMATION_SCHEMA.STATISTICS的时候。需要一个参数控制是否会触发更新统计信息,innodb_stats_on_metadata=on时。

这里需要注意的是,数据库中有大量的表或者索引的时候,会给数据库的IO带来更大的压力;并且如果频繁的更新统计信息,MySQL的执行计划的稳定性也会受到影响。

c) 在启动mysql客户端的时候采用–auto-rehash参数。

d) 一个表首次被打开的时候。

e) 表发生非常大的变化的时候(大小变化超过1/16或者新插入20亿行数据)。

持久性存储,设置innodb_stats_persistent=ON,或者STATS_PERSISTENT=1创建。

持久化的信息存储在MySQL的系统表mysql.innodb_table_stats 和mysql.innodb_index_stats 中。

因为是持久性存储到磁盘上,所以在表一段时间之后或者是进行大的改动的时候需要手动执行analyze table来更新统计信息。

总结:建议设置持久性存储到磁盘上,可以得到更稳定的执行计划,并且在系统重启之后可以更快速的生成统计信息。但是需要周期性的执行analyze table来手动更新统计信息,否则统计信息永远不变。

MyISAM与InnoDB的区别

MyISAM是MySQL的默认数据库引擎(5.5版之前),由早期的ISAM(Indexed Sequential Access Method:有索引的顺序访问方法)所改良。虽然性能极佳,但却有一个缺点:不支持事务处理(transaction)。不过,在这几年的发展下,MySQL也导入了InnoDB(另一种数据库引擎),以强化参考完整性与并发违规处理机制,后来就逐渐取代MyISAM。

InnoDB,是MySQL的数据库引擎之一,为MySQL AB发布binary的标准之一。InnoDB由Innobase Oy公司所开发,2006年五月时由甲骨文公司并购。与传统的ISAM与MyISAM相比,InnoDB的最大特色就是支持了ACID兼容的事务(Transaction)功能,类似于PostgreSQL。目前InnoDB采用双轨制授权,一是GPL授权,另一是专有软件授权。

MyISAM与InnoDB的区别

1、 存储结构

MyISAM:每个MyISAM在磁盘上存储成三个文件。第一个文件的名字以表的名字开始,扩展名指出文件类型。.frm文件存储表定义。数据文件的扩展名为.MYD (MYData)。索引文件的扩展名是.MYI (MYIndex)。
InnoDB:所有的表都保存在同一个数据文件中(也可能是多个文件,或者是独立的表空间文件),InnoDB表的大小只受限于操作系统文件的大小,一般为2GB。

2、 存储空间

MyISAM:可被压缩,存储空间较小。支持三种不同的存储格式:静态表(默认,但是注意数据末尾不能有空格,会被去掉)、动态表、压缩表。
InnoDB:需要更多的内存和存储,它会在主内存中建立其专用的缓冲池用于高速缓冲数据和索引。

3、 可移植性、备份及恢复

MyISAM:数据是以文件的形式存储,所以在跨平台的数据转移中会很方便。在备份和恢复时可单独针对某个表进行操作。
InnoDB:免费的方案可以是拷贝数据文件、备份 binlog,或者用 mysqldump,在数据量达到几十G的时候就相对痛苦了。

4、 事务支持

MyISAM:强调的是性能,每次查询具有原子性,其执行数度比InnoDB类型更快,但是不提供事务支持。
InnoDB:提供事务支持事务,外部键等高级数据库功能。 具有事务(commit)、回滚(rollback)和崩溃修复能力(crash recovery capabilities)的事务安全(transaction-safe (ACID compliant))型表。

5、 AUTO_INCREMENT

MyISAM:可以和其他字段一起建立联合索引。引擎的自动增长列必须是索引,如果是组合索引,自动增长可以不是第一列,他可以根据前面几列进行排序后递增。
InnoDB:InnoDB中必须包含只有该字段的索引。引擎的自动增长列必须是索引,如果是组合索引也必须是组合索引的第一列。

6、 表锁差异

MyISAM:只支持表级锁,用户在操作myisam表时,select,update,delete,insert语句都会给表自动加锁,如果加锁以后的表满足insert并发的情况下,可以在表的尾部插入新的数据。
InnoDB:支持事务和行级锁,是innodb的最大特色。行锁大幅度提高了多用户并发操作的新能。但是InnoDB的行锁,只是在WHERE的主键是有效的,非主键的WHERE都会锁全表的。

7、 全文索引

MyISAM:支持 FULLTEXT类型的全文索引
InnoDB:不支持FULLTEXT类型的全文索引,但是innodb可以使用sphinx插件支持全文索引,并且效果更好。

8、 表主键

MyISAM:允许没有任何索引和主键的表存在,索引都是保存行的地址。
InnoDB:如果没有设定主键或者非空唯一索引,就会自动生成一个6字节的主键(用户不可见),数据是主索引的一部分,附加索引保存的是主索引的值。

9、 表的具体行数

MyISAM:保存有表的总行数,如果select count() from table;会直接取出出该值。
InnoDB:没有保存表的总行数,如果使用select count(
) from table;就会遍历整个表,消耗相当大,但是在加了wehre条件后,myisam和innodb处理的方式都一样。

10、 CURD操作

MyISAM:如果执行大量的SELECT,MyISAM是更好的选择。
InnoDB:如果你的数据执行大量的INSERT或UPDATE,出于性能方面的考虑,应该使用InnoDB表。DELETE 从性能上InnoDB更优,但DELETE FROM table时,InnoDB不会重新建立表,而是一行一行的删除,在innodb上如果要清空保存有大量数据的表,最好使用truncate table这个命令。

11、 外键

MyISAM:不支持
InnoDB:支持
通过上述的分析,基本上可以考虑使用InnoDB来替代MyISAM引擎了,原因是InnoDB自身很多良好的特点,比如事务支持、存储 过程、视图、行级锁定等等,在并发很多的情况下,相信InnoDB的表现肯定要比MyISAM强很多。另外,任何一种表都不是万能的,只用恰当的针对业务类型来选择合适的表类型,才能最大的发挥MySQL的性能优势。如果不是很复杂的Web应用,非关键应用,还是可以继续考虑MyISAM的,这个具体情况可以自己斟酌。

MySQL InnoDB MVCC实现

数据多版本(MVCC)是MySQL实现高性能的一个主要的一个主要方式,通过对普通的SELECT不加锁,直接利用MVCC读取指版本的值,避免了对数据重复加锁的过程,今天我们就用最简单的方式,来分析下MVCC具体的原理,先解释几个概念:

隐藏列

在分析MVCC原理之前,先看下InnoDB中数据行的结构:

未分类

在InnoDB中,每一行都有2个隐藏列DATA_TRX_ID和DATA_ROLL_PTR(如果没有定义主键,则还有个隐藏主键列):

  • DATA_TRX_ID表示最近修改该行数据的事务ID
  • DATA_ROLL_PTR则表示指向该行回滚段的指针,该行上所有旧的版本,在undo中都通过链表的形式组织,而该值,正式指向undo中该行的历史记录链表

整个MVCC的关键就是通过DATA_TRX_ID和DATA_ROLL_PTR这两个隐藏列来实现的。

事务链表

MySQL中的事务在开始到提交这段过程中,都会被保存到一个叫trx_sys的事务链表中,这是一个基本的链表结构:

未分类

事务链表中保存的都是还未提交的事务,事务一旦被提交,则会被从事务链表中摘除。

ReadView

有了前面隐藏列和事务链表的基础,接下去就可以构造MySQL实现MVCC的关键——ReadView。

ReadView说白了就是一个数据结构,在SQL开始的时候被创建。这个数据结构中包含了3个主要的成员:ReadView{low_trx_id, up_trx_id, trx_ids},在并发情况下,一个事务在启动时,trx_sys链表中存在部分还未提交的事务,那么哪些改变对当前事务是可见的,哪些又是不可见的,这个需要通过ReadView来进行判定,首先来看下ReadView中的3个成员各自代表的意思:

  • low_trx_id表示该SQL启动时,当前事务链表中最大的事务id编号,也就是最近创建的除自身以外最大事务编号;
  • up_trx_id表示该SQL启动时,当前事务链表中最小的事务id编号,也就是当前系统中创建最早但还未提交的事务;
  • trx_ids表示所有事务链表中事务的id集合。

上述3个成员组成了ReadView中的主要部分,简单图示如下:

未分类

根据上图所示,所有数据行上DATA_TRX_ID小于up_trx_id的记录,说明修改该行的事务在当前事务开启之前都已经提交完成,所以对当前事务来说,都是可见的。而对于DATA_TRX_ID大于low_trx_id的记录,说明修改该行记录的事务在当前事务之后,所以对于当前事务来说是不可见的。

注意,ReadView是与SQL绑定的,而并不是事务,所以即使在同一个事务中,每次SQL启动时构造的ReadView的up_trx_id和low_trx_id也都是不一样的,至于DATA_TRX_ID大于low_trx_id本身出现也只有当多个SQL并发的时候,在一个SQL构造完ReadView之后,另外一个SQL修改了数据后又进行了提交,对于这种情况,数据其实是不可见的。

最后,至于位于(up_trx_id, low_trx_id)中间的事务是否可见,这个需要根据不同的事务隔离级别来确定。对于RC的事务隔离级别来说,对于事务执行过程中,已经提交的事务的数据,对当前事务是可见的,也就是说上述图中,当前事务运行过程中,trx1~4中任意一个事务提交,对当前事务来说都是可见的;而对于RR隔离级别来说,事务启动时,已经开始的事务链表中的事务的所有修改都是不可见的,所以在RR级别下,low_trx_id基本保持与up_trx_id相同的值即可。

最后用一张图来解释MySQL中的MVCC实现:

未分类

MySQL不支持InnoDB的解决方法

在OpenSUSE下装上MySQL后,发现无法选择添加事务支持数据引擎InnoDB。
G一下后,解决如下:
/var/lib/mysql目录下,删除ibdata1、ib_logfile1、 ib_logfile0,然后重启MySql让其重建以上文件:

mysqladmin -uroot -p shutdown
sudo mysqld_safe &

搞定!
下面是网络上的其它文章。大家也可以参考下。
早上起来,到PHP站点去看了下,准备测试下别人写的一个CMS系统,高兴的下载了程序,然后把程序拷贝到所在目录。由于该程序没有install.php,里面只包含了一个.sql的数据库语句,只得到mysql数据库中去执行这条语句:
进入数据库后,输入source 所在目录/
.sql
这个时候问题出现了:
QUOTE:

MySQL Server Error:
The 'InnoDB' feature is disabled; you need MySQL built with 'InnoDB' to have it working

在mysql中输入SHOW variables like “have_%”查看,显示如下:

mysql> SHOW variables like "have_%"
-> ;
+-----------------------+----------+
| Variable_name | Value |
+-----------------------+----------+
| have_archive | YES |
| have_bdb | NO |
| have_blackhole_engine | NO |
| have_compress | YES |
| have_crypt | NO |
| have_csv | NO |
| have_dynamic_loading | YES |
| have_example_engine | NO |
| have_federated_engine | NO |
| have_geometry | YES |
| have_innodb | DISABLED |
| have_isam | NO |
| have_merge_engine | YES |
| have_ndbcluster | NO |
| have_openssl | DISABLED |
| have_query_cache | YES |
| have_raid | NO |
| have_rtree_keys | YES |
| have_symlink | YES |
+-----------------------+----------+
19 rows in set (0.00 sec)

蓝色表示我的MYSQL并不支持innodb。
MySQL中InnoDB和MyISAM类型的差别

  • InnoDB和MyISAM是在使用MySQL最常用的两个表类型,各有优缺点,视具体应用而定。下面是已知的两者之间的差别,仅供参考。
    1.InnoDB不支持FULLTEXT类型的索引。
    2.InnoDB 中不保存表的具体行数,也就是说,执行select count() from table时,InnoDB要扫描一遍整个表来计算有多少行,但是MyISAM只要简单的读出保存好的行数即可。注意的是,当count()语句包含 where条件时,两种表的操作是一样的。
    3.对于AUTO_INCREMENT类型的字段,InnoDB中必须包含只有该字段的索引,但是在MyISAM表中,可以和其他字段一起建立联合索引。
    4.DELETE FROM table时,InnoDB不会重新建立表,而是一行一行的删除。
    5.LOAD TABLE FROM MASTER操作对InnoDB是不起作用的,解决方法是首先把InnoDB表改成MyISAM表,导入数据后再改成InnoDB表,但是对于使用的额外的InnoDB特性(例如外键)的表不适用。
    另外,InnoDB表的行锁也不是绝对的,如果在执行一个SQL语句时MySQL不能确定要扫描的范围,InnoDB表同样会锁全表,例如update table set num=1 where name like “%aaa%”
    任何一种表都不是万能的,只用恰当的针对业务类型来选择合适的表类型,才能最大的发挥MySQL的性能优势。
    如果你想使用外键,事务等功能,记得用innodb引擎。使用方法是create table xxx()engine=innodb;如果想所有建立的表格都用innodb引擎,可以把“default-storage-engine=INNODB”加到/etc/mysql/my.cnf(位置可能不同)。设完之后就可以用“show engines;”检查是否设置好。不过据说该设置在5.0.22下可能无效。

网上查找了,打开我的my.ini文件,找到skip-innodb,改成#skip-innodb。
之后重启mysql。。问题解决。

mysql> SHOW variables like "have_%"
-> ;
+-----------------------+----------+
| Variable_name | Value |
+-----------------------+----------+
| have_archive | YES |
| have_bdb | NO |
| have_blackhole_engine | NO |
| have_compress | YES |
| have_crypt | NO |
| have_csv | NO |
| have_dynamic_loading | YES |
| have_example_engine | NO |
| have_federated_engine | NO |
| have_geometry | YES |
| have_innodb | YES |
| have_isam | NO |
| have_merge_engine | YES |
| have_ndbcluster | NO |
| have_openssl | DISABLED |
| have_query_cache | YES |
| have_raid | NO |
| have_rtree_keys | YES |
| have_symlink | YES |
+-----------------------+----------+
19 rows in set (0.00 sec)

亲测Mysql表结构为InnoDB类型从ibd文件恢复数据

客户的机器系统异常关机,重启后mysql数据库不能正常启动,重装系统后发现数据库文件损坏,悲催的是客户数据库没有进行及时备份,只能想办法从数据库文件当中恢复,查找资料,试验各种方法,确认下面步骤可行:

一、找回表结构,如果表结构没有丢失直接到下一步

a、先创建一个数据库,这个数据库必须是没有表和任何操作的。
b、创建一个表结构,和要恢复的表名是一样的。表里的字段无所谓。一定要是innodb引擎的。CREATE TABLE `test`(  `testID` bigint(20)) ENGINE=InnoDB DEFAULT CHARSET=utf8;
c、关闭mysql, service mysqld stop;
d、用需要恢复的frm文件覆盖刚新建的frm文件;  
e、修改my.ini 里  innodb_force_recovery=1 , 如果不成修改为 2,3,4,5,6。
f、 启动mysql,service mysqld start;show create table test就能够看到表结构信息了。

二、找回数据

a、建立一个数据库,根据上面导出的创建表的sql执行创建表。
b、找到记录点。先要把当前数据库的表空间废弃掉,使当前ibd的数据文件和frm分离。  ALTER TABLE test DISCARD TABLESPACE;
c、把之前要恢复的 .ibd文件复制到新的表结构文件夹下。 使当前的ibd 和frm发生关系。ALTER TABLE test  IMPORT TABLESPACE;

d、将恢复好的数据导出就行了

InnoDB锁原理

背景

MySQL是一个支持插件式存储引擎的数据库系统,其中InnoDB是MySQL的事务安全的存储引擎,在OLTP系统中使用非常广乏。InnoDB最大的特性是支持事务,事务的特性包括原子性、一致性、隔离性、持久性,其中事务的隔离性,就是通过锁来实现的。在正式介绍锁之前,先来回顾一下MySql/InnoDB的隔离级别:

  • READ UNCOMMITTED 可以读取到未提交的数据,会产生脏读的问题
  • READ COMMITTED 读取已经提交的数据,不会有脏读,但是会有不可重复读和幻读。
  • REPEATABLE READ 在同一个事务中,可以重复读取,同时InnoDB在此隔离级别下不会有幻象读现象。
  • SERIALIZABLE 读取和写入都需要加锁,效率比较低。

锁的类型

InnoDB存储引擎有两种行级锁

  • 共享锁 S,允许事务读取一行数据。
  • 排他锁 X,允许事务修改一行数据。

他们的兼容性如下:

未分类

除此之外,InnoDB还有一种表级别锁,意向锁:

  • 意向共享锁 IS,表示事务想要获取表中某几行的共享锁。
  • 意向排他锁 IX,表示事务想要获取表中某几行的排他锁。

InnoDB的意向锁主要用户多粒度的锁并存的情况。比如事务A要在一个表上加S锁,如果表中的一行已被事务B加了X锁,那么该锁的申请也应被阻塞。如果表中的数据很多,逐行检查锁标志的开销将很大,系统的性能将会受到影响。为了解决这个问题,可以在表级上引入新的锁类型来表示其所属行的加锁情况,这就引出了“意向锁”的概念。举个例子,如果表中记录1亿,事务A把其中有几条记录上了行锁了,这时事务B需要给这个表加表级锁,如果没有意向锁的话,那就要去表中查找这一亿条记录是否上锁了。如果存在意向锁,那么假如事务A在更新一条记录之前,先加意向锁,再加X锁,事务B先检查该表上是否存在意向锁,存在的意向锁是否与自己准备加的锁冲突,如果有冲突,则等待直到事务A释放,而无须逐条记录去检测。事务B更新表时,其实无须知道到底哪一行被锁了,它只要知道反正有一行被锁了就行了。

说白了意向锁的主要作用是处理行锁和表锁之间的矛盾,能够显示“某个事务正在某一行上持有了锁,或者准备去持有锁”。

读取

innoDB中的数据读取分为锁定读取和非锁定读取。

非锁定读取

非锁定读指的是在读取的时候不需要加任何锁,读写不冲突。对于简单的查询语句select * from table where ?;在离级别READ UNCOMMITTED,READ COMMITTED和REPEATABLE READ下,是非锁定读取。在隔离级别SERIALIZABLE下,是锁定读取,需要获取行锁,此隔离级别效率极低,线上都不会采用。

在读多写少的OLTP系统当中,非锁定读取可以极大提高系统的并发处理能力。innoDB通过多版本的并发控制协议——MVCC (Multi-Version Concurrency Control)来实现非锁定读取,在读取的时候不用等待行上的锁释放,直接去读取行的一个快照数据。流程如下图所示:

未分类

对于行上的快照数据,innoDB是通过undo log来实现的,undo log可用于回滚事务,也可以用来实现MVCC功能。一个行上可能不只有一个版本的快照数据,对于事务隔离级别READ COMMITTED和REPEATABLE READ,他们所读取的快照版本是不一样的。在READ COMMITTED下,读取的快照数据总是最新的版本,在REPEATABLE READ下,总是读取事务开始时的行数据。

锁定读取

在某些情况下,为了保证数据的一致性,要先获取行锁,再进行数据读取。如下所示语句都会产生锁定读取:

  • select … lock in share mode; S
  • select … for update; X
  • insert into table values (..); 由于插入时需要唯一性检查,所以需要X锁。
  • update table set ? where ?; X
  • delete from table where ?; X

行锁的算法

  • Record Lock:单个行记录上锁。
  • Gap Lock:间隙锁,锁定一个范围,不包含记录本身。
  • Next-Key Lock:Gap Lock + Record Lock,锁定一个范围,并且锁定记录本身。

Record lock单条索引记录上加锁,Record lock锁住的永远是索引,而非记录本身。索引分为主键索引和非主键索引两种,如果一条sql语句操作了主键索引,MySQL就会锁定这条主键索引;如果一条语句操作了非主键索引,MySQL会先锁定该非主键索引,再锁定相关的主键索引。即使该表上没有任何索引,那么innodb会在后台创建一个隐藏的聚集主键索引,那么锁住的就是这个隐藏的聚集主键索引。所以说当一条sql没有走任何索引时,那么将会在每一条聚集索引后面加X锁。

Gap Lock锁定的是索引之间的间隙,并不是记录本身。例如有一个索引有3,5,6,10和20这几个值,他们之前的间隙包括(-∞,3)、(3,5)、(5,6)、(10,20)、(20,+∞),对于Gap Lock锁定就是这几个范围。

InnoDB在不同的隔离级别下使用的锁算法也不同。

READ COMMITTED

对于innoDB的READ COMMITTED隔离级别下,会存在幻象读问题。在该隔离级别下,除了外键约束和唯一性检查依然需要Gap Lock,其余情况均使用Record Lock进行锁定。

REPEATABLE READ

InnoDB在该隔离级别下,没有幻读现象。幻读是指在同一事务下,连续执行两次同样的SQL语句可能导致不同的结果,第二次执行可能返回之前不存在的行。例如对下述语句:select * from table where id > 10 for update; 在事务1查询之后,如果另一个事务2可以插入id大于10的记录,在事务1下次查询的时候也会返回事务2插入的记录,两次的读取结果不一样,这就是幻读。

InnoDB通过Next-key Lock来避免幻读的现象,除了锁住记录本身之外,还要锁住可能涉及到的间隙(Gap)。对于上述例子select * from table where id > 10 for update; 在REPEATABLE READ隔离级别下锁定是(10,+∞)这个范围。

总结

本文主要介绍了InnoDB的锁的类型及RC和RR级别下的加锁情况,希望能给大家带来帮助。

MySQL数据库INNODB表损坏修复处理过程分享

##状况描述

突然收到MySQL报警,从库的数据库挂了,一直在不停的重启,打开错误日志,发现有张表坏了。innodb表损坏不能通过repair table 等修复myisam的命令操作。现在记录下解决过程,下次遇到就不会这么手忙脚乱了。

处理过程

一遇到报警之后,直接打开错误日志,里面的信息:

InnoDB: Database page corruption on disk or a failed InnoDB: file read of page 30506. InnoDB: You may have to recover from a backup. 130509 20:33:48 InnoDB: Page dump in ascii and hex (16384 bytes): ##很多十六进制的代码 …… …… InnoDB: End of page dump 130509 20:37:34 InnoDB: Page checksum 1958578898, prior-to-4.0.14-form checksum 3765017239 InnoDB: stored checksum 3904709694, prior-to-4.0.14-form stored checksum 3765017239 InnoDB: Page lsn 5 614270220, low 4 bytes of lsn at page end 614270220 InnoDB: Page number (if stored to page already) 30506, InnoDB: space id (if created with >= MySQL-4.1.1 and stored already) 19 InnoDB: Page may be an index page where index id is 54 InnoDB: (index "PRIMARY" of table "maitem"."email_status") InnoDB: Database page corruption on disk or a failed InnoDB: file read of page 30506. InnoDB: You may have to recover from a backup. InnoDB: It is also possible that your operating InnoDB: system has corrupted its own file cache InnoDB: and rebooting your computer removes the InnoDB: error. InnoDB: If the corrupt page is an index page InnoDB: you can also try to fix the corruption InnoDB: by dumping, dropping, and reimporting InnoDB: the corrupt table. You can use CHECK InnoDB: TABLE to scan your table for corruption. InnoDB: See also http://dev.mysql.com/doc/refman/5.5/en/forcing-innodb-recovery.html InnoDB: about forcing recovery. InnoDB: A new raw disk partition was initialized or InnoDB: innodb_force_recovery is on: we do not allow InnoDB: database modifications by the user. Shut down InnoDB: mysqld and edit my.cnf so that newraw is replaced InnoDB: with raw, and innodb_force_... is removed. 130509 20:39:35 [Warning] Invalid (old?) table or database name '#sql2-19c4-5'

从错误日志里面很清楚的知道哪里出现了问题,该怎么处理。这时候数据库隔几s就重启,所以差不多可以说你是访问不了数据库的。所以马上想到要修复innodb表了。以前在Performance的blog上看过类似文章。

当时想到的是在修复之前保证数据库正常,不是这么异常的无休止的重启。

所以就修改了配置文件的一个参数:innodb_force_recovery

innodb_force_recovery 会影响整个InnoDB存储引擎的恢复状况。默认为0,表示当需要恢复时执行所有的

innodb_force_recovery可以设置为1-6,大的数字包含前面所有数字的影响。当设置参数值大于0后,可以对表进行

select,create,drop操作,但insert,update或者delete这类操作是不允许的。

  • (SRV_FORCE_IGNORE_CORRUPT):忽略检查到的corrupt页。
    (SRV_FORCE_NO_BACKGROUND):阻止主线程的运行,如主线程需要执行full purge操作,会导致crash。
  • (SRV_FORCE_NO_TRX_UNDO):不执行事务回滚操作。
  • (SRV_FORCE_NO_IBUF_MERGE):不执行插入缓冲的合并操作。
  • (SRV_FORCE_NO_UNDO_LOG_SCAN):不查看重做日志,InnoDB存储引擎会将未提交的事务视为已提交。
  • (SRV_FORCE_NO_LOG_REDO):不执行前滚的操作。

因为错误日志里面提示出现了坏页,导致数据库崩溃,所以这里把innodb_force_recovery 设置为1,忽略检查到的坏页。重启数据库之后,正常了,没有出现上面的错误信息。找到错误信息出现的表:
(index "PRIMARY" of table "maitem"."email_status")

数据页面的主键索引(clustered key index)被损坏。这种情况和数据的二级索引(secondary indexes)被损坏相比要糟很多,因为后者可以通过使用OPTIMIZE TABLE命令来修复,但这和更难以恢复的表格目录(table dictionary)被破坏的情况来说要好一些。

操作步骤

因为被破坏的地方只在索引的部分,所以当使用innodb_force_recovery = 1运行InnoDB时,操作如下:

执行check,repair table 都无效

alter table email_status engine =myisam; #也报错了,因为模式是innodb_force_recovery =1。

ERROR 1025 (HY000): Error on rename of '...' to '....' (errno: -1)

1. 建立一张表:

mysql> create table email_status_bak #和原表结构一样,只是把INNODB改成了MYISAM。

2. 把数据导进去 :

mysql>  insert into email_status_bak select * from email_status;

3. 删除掉原表:

mysql>  drop table email_status;

4. 注释掉innodb_force_recovery 之后,重启服务。

5. 重命名:

mysql> rename table edm_email_status_bak to email_status;

6. 最后该回存储引擎

mysql> alter table edm_email_status engine = innodb;

总结

这里的一个重要知识点就是 对 innodb_force_recovery 参数的理解了,要是遇到数据损坏甚至是其他的损坏。可能上面的方法不行了,需要尝试另一个方法:insert into tb select * from ta limit X;甚至是dump出去,再load回来。

Xtrabackup实现Mysql的InnoDB引擎热备份

前面Zabbix使用的数据库是mysql,数据库备份不用多说,必须滴,由于使用的是innodb引擎,既然做,那就使用第三方强大的Xtrabackup工具来热备吧,Xtrabackup的说明,参见https://my.oschina.net/u/1171265/blog/200437

数据库备份

简单粗暴,下面给出mysql备份脚本:

#!/bin/bash
#mysql热备脚本,Version:1.0
#Author:jzd
#备份策略:每周一进行完整备份,以后每一天在前一天的基础上进行增量备份
#
#备份目录
back_dir="/back"

#备份数据库信息
host="127.0.0.1"
dbuser="dbuser"
dbpasswd="dbpasswd"

#日志文件
back_log="${back_dir}/mysql_back.log"

#week day
week_day=`date +%w`
#yesterday
yesterday=`date +%F -d "1 days ago"`

#日志记录函数
function log(){

  echo "`date` $1" | tee -a ${back_log}

}

#判断当前日期,是周一进行全备份,其他时间进行增量备份
if [ ${week_day} -eq 1 ]; then
#完整备份
  log "周${week_day}开始完全备份..."
  innobackupex --host=${host} --user=${dbuser} --password=${dbpasswd} ${back_dir} &>> ${back_log}
  if [ $? -eq 0 ]; then
    log "完全备份完毕."
    else
       log "完全备份出错,请检查."
       exit 1
  fi
  else
    let dir_num=`find ${back_dir} -type d -name "${yesterday}*" | wc -l`
    if [ ${dir_num} -ne 1 ]; then
      log "昨天增量目录未找到或昨天备份目录大于等于2个,请确认后再次备份."
      exit 1
    fi
    incremental_dir=`find ${back_dir} -type d -name "${yesterday}*"`
    log "周${week_day}开始增量备份..."
    #增量备份
    innobackupex --host=${host} --user=${dbuser} --password=${dbpasswd} --incremental ${back_dir} --incremental-basedir=${incremental_dir} &>> ${back_log}
    if [ $? -eq 0 ]; then
      log "增量备份成功."
      else
        log "备份失败,请检查日志..."
        exit 1
    fi
fi

exit $?

需修改的地方,也可以写成配置文件source进去,完全可以用在生产环境。

#备份目录

back_dir="/back"



#备份数据库信息

host="127.0.0.1"

dbuser="dbuser"

dbpasswd="dbpasswd"



#日志文件

back_log="${back_dir}/mysql_back.log"

数据恢复

话说备份容易,恢复不易啊,下面也给出恢复脚本,配合上面备份脚本使用,但是恢复完毕后,需手动恢复当日的二进制文件,恢复方法见开篇Xtrabackup介绍文章;

恢复脚本的星期和日期对应的转换,搞得头都大了,缠了两天,判断太多,导致太乱,于是多使用函数,发现自己对函数的使用加强了(哈哈,相对于以前,感觉自己有面向对象的思想了,但这是shell)

#!/bin/bash
#mysql数据恢复脚本
#Author:jzd
#Version:V1.0

#back dir
back_dir='/back'
#full back day
full_back_day=1
#today
today=`date +%F`
#week day
week_day=`date +%w`
#log file
recovery_log="${back_dir}/mysql_recovery.log"

#log
function log(){
  echo "`date` $1" | tee -a ${recovery_log}
}

#find back dir
function finddir(){
  if [ `find $back_dir -type d -name "$1*" | wc -l` -ne 1 ]; then
    log "发现备份目录$1为0个或多于一个,请检查..."
    exit 1
    else
      log "发现备份目录`find $back_dir -type d -name "$1*"`"
      find_dir="`find $back_dir -type d -name "$1*"`"
  fi
}

#all recovery
function allredo(){
 log "开始完整备份恢复准备..."
 innobackupex --apply-log --redo-only $1 &>> ${recovery_log}
 if [ $? -ne 0 ]; then
    log "完整恢复准备出错,请检查..."
    exit 1
 fi
 log "完整备份恢复准备完成"
}

#incremental recovery
function incredo(){
 log "开始增量数据恢复准备..."
 innobackupex --apply-log --redo-only $1 --incremental-dir=$2 &>> ${recovery_log}
 if [ $? -ne 0 ]; then
    log "增量数据恢复准备出错,请检查..."
    exit 1
  fi
  log "增量数据$2恢复准备完成"
}
#real recovery
function recovery(){
  log "备份数据准备完成,开始恢复数据..."
  innobackupex --copy-back $1 &>> ${recovery_log}
  if [ $? -ne 0 ]; then
    log "完整恢复准备出错,请检查..."
    exit 1
  fi
  log "恢复数据完成,请检查."
  log "请手动恢复二进制文件数据."
}

#判断今日是否是周一

if [ $week_day -eq ${full_back_day} ]; then
  finddir ${today}
  all_dir="${find_dir}"
  if [ ! -z "${all_dir}" ]; then
    allredo $all_dir
    recovery $all_dir
    else
      log "未发现今日备份,恢复上周数据."
      for i in "7 6 5 4 3 2 1"
        do
          back_date=`date +%F -d "${i} days ago"`
          if [ $i -eq 7 ]; then
            finddir ${back_date}
            all_dir="${find_dir}"
            allredo ${all_dir}
            else
              finddir ${back_date}
              dir_back=${find_dir}
              incredo ${all_dir} ${dir_back}
          fi
      done
      recovery ${all_dir}
  fi
  else
    back_date=`date +%F -d "$((${week_day}-${full_back_day})) days ago"`
    finddir ${back_date}
    all_dir="${find_dir}"
    allredo ${all_dir}
    let flag=$((${week_day}-1-${full_back_day}))
    while [ ${flag} -ge 0 ]
      do
        incr_date=`date +%F -d "${flag} days ago"`
        finddir ${incr_date}
        incr_dir="${find_dir}"
        incredo ${all_dir} ${incr_dir}
        let flag=`expr $flag - 1`
    done
    recovery ${all_dir}
fi

exit 0