允许转载, 转载时请以超链接形式标明文章原始出处和网站信息

InnoDB: Error: Table “mysql”.”innodb_table_stats” not found.

对于MySQL的安装我们熟悉的不能在陌生了,但是呢,当随着MySQL版本不断更新升级,从之前的5.1,到中间过渡的5.5,再到现如今让人夸赞的5.6.10,其实不同版本的mysql_install_db的初始化db的操作也在悄然发生的变化,正因为这个微小的变化,在我们原来的安装步骤下,你就会不小心遇到标题上的这个Error提示,那么究其原因就往下看吧。

Shell> wget http://cdn.mysql.com/Downloads/MySQL-5.6/mysql-5.6.10.tar.gz

Shell> yum install -y cmake make gcc perl libaio-devel

Note: cmake 2.6.3 or later, make 3.75 or later, gcc 4.2.1 or later,这里gcc版本测试中使用gcc 4.1.2也能正常编译安装。

Shell> cd /usr/local/; tar -xzf  mysql-5.6.10.tar.gz; chown -R root.root mysql-5.6.10

Shell> mkdir mybuild; cd mybuild

Tip: 为了不污染下载的源码包,通常可以建立一个我们自己的build目录与当前的源码包在相同的目录下,每次编译前,先将之前的build目录清空。

Shell> cmake -DBUILD_CONFIG=mysql_release \

-DCMAKE_INSTALL_PREFIX=/usr/local/mysql5610source \

../mysql-5.6.10

Note: -DBUILD_CONFIG=mysql_release 该选项表示,我们使用与Oracle官方相同的参数进行编译;-DCMAKE_INSTALL_PREFIX选项表示basedir的位置,即编译好的MySQL安装到该目录下。

Shell> make && make install

Shell> cd /usr/local/mysql5610source

Shell> scripts/mysql_install_db –defaults-file=/etc/my.cnf –datadir=/data/mysql –user=mysql

Update 0927: 今天安装5.6.14的时候,发现mysql_innodb_db多了个新选项–ldata,不过这个选项和–datadir作用相同,如下:

Shell> scripts/mysql_install_db –defaults-file=/etc/my.cnf –ldata=/data/mysql –user=mysql

Note: 从5.5开始,应该是始于5.6,初始化数据库的时候,会临时启动mysqld,如果不指定–defaults-file这个选项,mysqld会用自己默认的参数启动,并初始化一个12M的ibdata1文件以及两个48M的iblogfile文件,当你用定制好的my.cnf配置文件正式启动mysqld的时候,这时就会遇到两个问题:

1>. 当my.cnf里面设置的innodb_log_file_size的大小大于48M时,5.6 GA版本会自动帮我们扩容到指定大小,这个问题被自动解决,而其他版本则需要关闭mysqld,删除原来的iblogfile启动时重新生成;

以下是5.6GA版的处理过程:

2013-03-26 16:11:18 14084 [Warning] InnoDB: Resizing redo log from 2*3072 to 2*32768 pages, LSN=1625987

2013-03-26 16:11:18 14084 [Warning] InnoDB: Starting to delete and rewrite log files.

2013-03-26 16:11:18 14084 [Note] InnoDB: Setting log file ./ib_logfile101 size to 512 MB

InnoDB: Progress in MB: 100 200 300 400 500

2013-03-26 16:11:22 14084 [Note] InnoDB: Setting log file ./ib_logfile1 size to 512 MB

InnoDB: Progress in MB: 100 200 300 400 500

2013-03-26 16:11:27 14084 [Note] InnoDB: Renaming log file ./ib_logfile101 to ./ib_logfile0

2013-03-26 16:11:27 14084 [Warning] InnoDB: New log files created, LSN=1625987

2>. 当ibdata1的大小如果和my.cnf指定的不同,那么这时mysqld就无法启动,以下是5.6GA版启动失败的错误提示:

2013-03-26 16:16:41 14936 [ERROR] InnoDB: auto-extending data file /data/mysql5610source/ibdata1 is of a different size 768 pages (rounded down to MB) than specified in the .cnf file: initial 16384 pages, max 0 (relevant if non-zero) pages!

2013-03-26 16:16:41 14936 [ERROR] InnoDB: Could not open or create the system tablespace. If you tried to add new data files to the system tablespace, and it failed here, you should now edit innodb_data_file_path in my.cnf back to what it was, and remove the new ibdata files InnoDB created in this failed attempt. InnoDB only wrote those files full of zeros, but did not yet use them in any way. But be careful: do not remove old data files which contain your precious data!

2013-03-26 16:16:41 14936 [ERROR] Plugin ‘InnoDB’ init function returned error.

2013-03-26 16:16:41 14936 [ERROR] Plugin ‘InnoDB’ registration as a STORAGE ENGINE failed.

2013-03-26 16:16:41 14936 [ERROR] Unknown/unsupported storage engine: InnoDB

2013-03-26 16:16:41 14936 [ERROR] Aborting

要想正常启动,要么注释掉innodb_data_file_path参数,不改变默认的大小;要么,当你的版本是5.5时,你可以直接删除初始化生成的ibdata1文件,然后重新生成指定大小的,当你的版本是5.6 GA以后的版本时,一定要在做mysql_install_db的时候指定–defaults-file选项,按照你的my.cnf里面的参数去初始化,而不能采用和5.5一样的方法删除之前的ibdata1文件,重新生成新的,因为5.6GA版在mysql系统库下引入了6个新的innodb表(innodb_index_stats、innodb_table_stats、slave_master_info、slave_relay_log_info、slave_worker_info),innodb的表结构除了在.frm中有记录,同时在ibdata1中也会记录,这就是为什么当我们删除后,启动mysqld,虽然也能启动成功,伴随着如下Warnings:

 [Warning] InnoDB: Cannot open table mysql/slave_master_info

[Warning] InnoDB: Cannot open table mysql/slave_worker_info

[Warning] InnoDB: Cannot open table mysql/slave_relay_log_info

但是每当我们有写操作的时候,就会涌现大量的Errors,尽管数据仍能正常的写入,以后每一次的更新都会有下面相同的Error出现:

41a38940 InnoDB: Error: Table “mysql”.”innodb_table_stats” not found.

41a38940 InnoDB: Error: Fetch of persistent statistics requested for table “test”.”test” but the required system tables mysql.innodb_table_stats and mysql.innodb_index_stats are not present or have unexpected structure. Using transient stats instead.

 

6 comments to InnoDB: Error: Table “mysql”.”innodb_table_stats” not found.

路过踩一脚

 

 

 

能使用 these HTML 标签

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong> <pre lang="" line="" escaped="" highlight="">