note: InnoDB

新增 innodb 資料檔方式：


先算出目前的 /usr/local/mysql/var/ibdata1 檔案大小 (MB數)
144703488/1024/1024=138MB (剛好整數)
若算出來是 138.1MB，則要填入 139MB，如下：

my.cnf
innodb_data_file_path=/usr/local/mysql/var/ibdata1:138M;/usr/local/mysql/var/ibdata2:200M:autoextend:max:1024M
(改完當然就要 restart mysqld)

填錯 ibdata1 資料檔的大小，會出現的 mysql err log如下 (mysqld 仍會繼續執行)

InnoDB: Error: data file /usr/local/mysql/var/ibdata1 is of a different size
InnoDB: 8832 pages (rounded down to MB)
InnoDB: than specified in the .cnf file 10240 pages!
InnoDB: Could not open or create data files.
InnoDB: If you tried to add new data files, and it failed here,
InnoDB: you should now edit innodb_data_file_path in my.cnf back
InnoDB: to what it was, and remove the new ibdata files InnoDB created
InnoDB: in this failed attempt. InnoDB only wrote those files full of
InnoDB: zeros, but did not yet use them in any way. But be careful: do not
InnoDB: remove old data files which contain your precious data!
 

若新存到 innodb 的資料超過 1024MB 時，就會出現類似：
mysql> alter table TABLENAME type=innodb;
ERROR 1114 (HY000): The table '#sql-7c23_9' is full

---

有關 innodb_data_file_path 的設定

mysql官網有中文說明

---

另：
一个高级的my.cnf例子。假设你有一台Linux计算机，有2GB内存和三个60GB硬盘（在目录路径/, /dr2和/dr3)。下列例子显示了在my.cnf里对InnoDB可能的配置参数。

[mysqld]

# You can write your other MySQL server options here

# ...

innodb_data_home_dir =

#
# Data files must be able to hold your data and indexes
innodb_data_file_path = /ibdata/ibdata1:2000M;/dr2/ibdata/ibdata2:2000M:autoextend

#
# Set buffer pool size to 50-80% of your computer's memory,
# but make sure on Linux x86 total memory usage is < 2GB

innodb_buffer_pool_size=1G
innodb_additional_mem_pool_size=20M
innodb_log_group_home_dir = /dr3/iblogs

#
innodb_log_files_in_group = 2
#
# Set the log file size to about 25% of the buffer pool size

innodb_log_file_size=250M
innodb_log_buffer_size=8M

#
innodb_flush_log_at_trx_commit=1
innodb_lock_wait_timeout=50

#
# Uncomment the next lines if you want to use them
#innodb_thread_concurrency=5


警告：在32位GNU/Linux x86上,你必须要小心不要设置过高的内存用量。glibc可能允许进程堆积在线程堆栈上发展，它会造成你的服务器崩溃。如果下列表达式的值接近或者超过2GB，系统会面临危机：

innodb_buffer_pool_size
+ key_buffer_size
+ max_connections*(sort_buffer_size+read_buffer_size+binlog_cache_size)
+ max_connections*2MB

每个线程使用一个堆栈（通常是2MB，但在MySQL AB二进制分发版里只有256KB）并且在最坏的情况下也使用sort_buffer_size + read_buffer_size附加内存。