如何为innodb添加数据文件

  mysql安装完成后,针对innodb引擎默认只有一个数据文件ibdata1,要想添加数据文件,需要在my.cnf修改参数innodb_data_file_path,指向多个文件,如下是添加数据文件的步骤.

1.查看数据文件
mysql> SHOW VARIABLES LIKE '%innodb_data_file_path%';
+-----------------------+------------------------+
| Variable_name         | Value                  |
+-----------------------+------------------------+
| innodb_data_file_path | ibdata1:12M:autoextend |
+-----------------------+------------------------+
1 row in set (0.00 sec)

2.进入到mysql数据库目录查看ibdata1实际大小
[mysql@node2 mysqldata]$ ll -h
总计 237M
-rw-r----- 1 mysql mysql   56 08-15 10:39 auto.cnf
drwxrwxr-x 2 mysql mysql 4.0K 09-17 08:41 bin_log
drwx------ 2 mysql mysql 4.0K 08-15 10:39 cr_debug
drwx------ 2 mysql mysql 4.0K 08-15 10:39 database
drwx------ 2 mysql mysql 4.0K 08-15 10:39 databasename
drwx------ 2 mysql mysql 4.0K 09-17 15:25 hxl
-rw-r----- 1 mysql mysql 140M 09-17 15:27 ibdata1
-rw-r----- 1 mysql mysql  48M 09-17 15:27 ib_logfile0
-rw-r----- 1 mysql mysql  48M 09-17 15:27 ib_logfile1
drwx--x--x 2 mysql mysql 4.0K 08-15 10:39 mysql
-rw-r----- 1 mysql mysql 399K 09-17 15:31 node2.err
-rw-rw---- 1 mysql mysql    5 09-17 08:41 node2.pid
drwx------ 2 mysql mysql 4.0K 08-15 10:39 performance_schema
-rw-r--r-- 1 mysql mysql  113 08-15 10:39 RPM_UPGRADE_HISTORY
-rw-r--r-- 1 mysql mysql  113 08-15 10:39 RPM_UPGRADE_MARKER-LAST
drwxr-xr-x 2 mysql mysql 4.0K 08-15 10:39 test

3.停止mysql
[mysql@node2 mysqldata]$ mysqladmin -h node2 -uroot -p shutdown

4.修改/etc/my.cnf文件
innodb_data_file_path=ibdata1:1G;ibdata2:1G:autoextend

这里ibdata2没有指定具体路径的情况下,默认是在datadir参照指定的目录，我这里是:/var/lib/mysqldata

ibdata文件存在不同路径的话(不同路径的情况下，将innodb_data_home_dir参数设置为空),需要指定路径名,如下:
innodb_data_home_dir=
innodb_data_file_path=/var/lib/mysqldata/ibdata1:140M;/var/lib/mysqldata/ibdata2:2G;/home/mysql/ibdata/ibdata3:100M:autoextend

5.启动mysql服务
[root@node2 soft]# service mysql start
Starting MySQL...............................The server quit without updating PID file (/var/lib/mysqldata/node2.pid).[FAILED]
[root@node2 soft]#

查看错误信息如下
2014-09-17 15:53:11 23572 [Note] InnoDB: Completed initialization of buffer pool
2014-09-17 15:53:11 23572 [ERROR] InnoDB: Data file ./ibdata1 is of a different size 8960 pages (rounded down to MB) than specified in the .cnf file 65536 pages!
2014-09-17 15:53:11 23572 [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!
2014-09-17 15:53:11 23572 [ERROR] Plugin 'InnoDB' init function returned error.
2014-09-17 15:53:11 23572 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
2014-09-17 15:53:11 23572 [ERROR] Unknown/unsupported storage engine: InnoDB
2014-09-17 15:53:11 23572 [ERROR] Aborting

计算公式 8960/64=140,64pages相当于1M,从步骤1查看ibdata1文件的大小也可以获取到该值.
修改配置如下:
innodb_data_file_path=ibdata1:140M;ibdata2:1G:autoextend

重新启动成功
[root@node2 soft]# service mysql start
Starting MySQL...........................................[  OK  ]

6.查看数据文件目录生成了ibdata2
[mysql@node2 mysqldata]$ ls -al
总计 1291992
drwxr-xr-x 10 mysql mysql       4096 09-17 16:00 .
drwxr-xr-x 30 root  root        4096 08-15 10:37 ..
-rw-r-----  1 mysql mysql         56 08-15 10:39 auto.cnf
drwxrwxr-x  2 mysql mysql       4096 09-17 16:00 bin_log
drwx------  2 mysql mysql       4096 08-15 10:39 cr_debug
drwx------  2 mysql mysql       4096 08-15 10:39 database
drwx------  2 mysql mysql       4096 08-15 10:39 databasename
drwx------  2 mysql mysql       4096 09-17 15:25 hxl
-rw-r-----  1 mysql mysql  146800640 09-17 16:00 ibdata1
-rw-rw----  1 mysql mysql 1073741824 09-17 16:00 ibdata2
-rw-r-----  1 mysql mysql   50331648 09-17 16:00 ib_logfile0
-rw-r-----  1 mysql mysql   50331648 09-17 15:27 ib_logfile1
drwx--x--x  2 mysql mysql       4096 08-15 10:39 mysql
-rw-r-----  1 mysql mysql     419140 09-17 16:01 node2.err
-rw-rw----  1 mysql mysql          6 09-17 16:00 node2.pid
drwx------  2 mysql mysql       4096 08-15 10:39 performance_schema
-rw-r--r--  1 mysql mysql        113 08-15 10:39 RPM_UPGRADE_HISTORY
-rw-r--r--  1 mysql mysql        113 08-15 10:39 RPM_UPGRADE_MARKER-LAST
drwxr-xr-x  2 mysql mysql       4096 08-15 10:39 test


-- The End --