【mysql案例】mysql5.6.14配置my.cnf多实例,mysql_install_db初_MySQL

【环境描述】

在多实例配置的/etc/my.cnf环境中，执行mysql_install_db后，启动Mysql报错。

【操作步骤】

/etc/my.cnf配置文件：

[mysqld3307]

innodb_data_file_path =ibdata1:1G:autoextend

初始化数据库：

[root@test home]# mysql_install_db--datadir=/home/mysql_3307 --user=mysql --defaults-file=/etc/my.cnf

Installing MySQL system tables...2014-05-1311:07:33 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated.Please use --explicit_defaults_for_timestamp server option (see documentationfor more details).

2014-05-13 11:07:33 8897 [Note] InnoDB: TheInnoDB memory heap is disabled

2014-05-13 11:07:33 8897 [Note] InnoDB:Mutexes and rw_locks use GCC atomic builtins

2014-05-13 11:07:33 8897 [Note] InnoDB:Compressed tables use zlib 1.2.3

2014-05-13 11:07:33 8897 [Note] InnoDB:Using Linux native AIO

2014-05-13 11:07:33 8897 [Note] InnoDB:Using CPU crc32 instructions

2014-05-13 11:07:34 8897 [Note] InnoDB:Initializing buffer pool, size = 128.0M

2014-05-13 11:07:34 8897 [Note] InnoDB:Completed initialization of buffer pool

2014-05-13 11:07:34 8897 [Note] InnoDB: Thefirst specified data file ./ibdata1 did not exist: a new database to becreated!

2014-05-13 11:07:34 8897 [Note] InnoDB:Setting file ./ibdata1 size to 12 MB

2014-05-13 11:07:34 8897 [Note] InnoDB:Database physically writes the file full: wait...

2014-05-13 11:07:34 8897 [Note] InnoDB:Setting log file ./ib_logfile101 size to 48 MB

2014-05-13 11:07:34 8897 [Note] InnoDB:Setting log file ./ib_logfile1 size to 48 MB

2014-05-13 11:07:35 8897 [Note] InnoDB:Renaming log file ./ib_logfile101 to ./ib_logfile0

2014-05-13 11:07:35 8897 [Warning] InnoDB:New log files created, LSN=45781

2014-05-13 11:07:35 8897 [Note] InnoDB:Doublewrite buffer not found: creating new

2014-05-13 11:07:35 8897 [Note] InnoDB:Doublewrite buffer created

启动mysql实例时，error.log报错：

2014-05-13 11:11:15 8989 [Note] InnoDB:Completed initialization of buffer pool

2014-05-13 11:11:15 8989 [ERROR]InnoDB: auto-extending data file ./ibdata1 is of a differentsize 768 pages (rounded down to MB) than specified in the .cnf file: initial65536 pages, max 0 (relevant if non-zero) pages!

2014-05-13 11:11:15 8989 [ERROR] InnoDB:Could not open or create the system tablespace. If you tried to add new datafiles to the system tablespace, and it failed he

re, you should now editinnodb_data_file_path in my.cnf back to what it was, and remove the new ibdatafiles InnoDB created in this failed attempt. InnoDB only wrote

those files full of zeros, but did not yetuse them in any way. But be careful: do not remove old data files which containyour precious data!

2014-05-13 11:11:15 8989 [ERROR] Plugin'InnoDB' init function returned error.

2014-05-13 11:11:15 8989 [ERROR] Plugin'InnoDB' registration as a STORAGE ENGINE failed.

2014-05-13 11:11:15 8989 [ERROR]Unknown/unsupported storage engine: InnoDB

2014-05-13 11:11:15 8989 [ERROR] Aborting

2014-05-13 11:11:15 8989 [Note] Binlog end

2014-05-13 11:11:15 8989 [Note] Shuttingdown plugin 'partition'

2014-05-13 11:11:15 8989 [Note] Shuttingdown plugin 'ARCHIVE'

虽然执行mysql_install_db命令的时候指定了读取/etc/my.cnf配置文件，但是进行初始化创建数据库的时候，实际上没有读取：

初始化时的输出日志：

2014-05-13 11:07:34 8897 [Note] InnoDB:Setting file ./ibdata1 size to 12 MB

查看/home/mysql_3307/ibdata1的大小：

[root@ test mysql_3307]# ls -ltrh |grep ibdata1

-rw-rw---- 1 mysql mysql 12M May 13 11:07 ibdata1

可以发现实际创建的ibdata1文件大小也是mysql默认的12M，而非配置文件中配置的1G大小，在启动实例时遇到的报错，也是说ibdata1文件大小跟配置文件中的不匹配。

【报错原因】

在mysql5.6.14版本中，mysql_install_db命令读取my.cnf配置文件时，只会读取[mysqld]配置模块，它不会读取多实例中配置的[mysqldNNN]，所以在本案例中，虽然my.cnf配置了ibdata大小为1G，但是mysql仍然使用默认的12M。

【解决方法】

修改my.cnf，把[mysqldNNN]依次修改为[mysqld]，执行完初始化之后，在把它修改回去。

网上有由于datadir路径空间不足导致的报错，但是除非my.cnf中配置的ibdata非常大，一般在线上情况不会发生的。