MySQL设置undo为独立表空间

博客文章除注明转载外，均为原创。转载请注明出处。
本文链接地址：http://blog.chinaunix.net/uid-31396856-id-5753413.html


MySQL5.6中开始支持把undo log分离到独立的表空间，并放到单独的文件目录下；采用独立undo表空间，再也不用担心undo会把 ibdata1 文件搞大；
也给我们部署不同IO类型的文件位置带来便利，对于并发写入型负载，我们可以把undo文件部署到单独的高速存储设备上.

在数据库初始化的时候就使用如下三个参数
innodb_undo_tablespaces=3 #设置为3个
innodb_undo_logs=128 #默认128个
innodb_undo_directory =/dbfiles/mysql_home/undologs

就可以分离出单独的undo表空间
root@localhost [(none)]> show variables like '%undo%';
+-------------------------+--------------------------------+
| Variable_name           | Value                          |
+-------------------------+--------------------------------+
| innodb_undo_directory   | /data/mysql/mysql3306/undologs |
| innodb_undo_logs        | 2                              |
| innodb_undo_tablespaces | 1                              |
+-------------------------+--------------------------------+

show status like 'Innodb_available_undo_logs';
无法动态修改：
root@localhost [(none)]> set global innodb_undo_tablespaces=3;
ERROR 1238 (HY000): Variable 'innodb_undo_tablespaces' is a read only variable

已安装数据库不能修改 innodb_undo_tablespaces
2016-03-24 16:27:18 7164 [Note] InnoDB: Using Linux native AIO
2016-03-24 16:27:18 7164 [Note] InnoDB: Using CPU crc32 instructions
2016-03-24 16:27:18 7164 [Note] InnoDB: Initializing buffer pool, size = 100.0M
2016-03-24 16:27:18 7164 [Note] InnoDB: Completed initialization of buffer pool
2016-03-24 16:27:18 7fb591068720 InnoDB: Expected to open 3 undo tablespaces but was able
2016-03-24 16:27:18 7fb591068720 InnoDB: to find only 0 undo tablespaces.
2016-03-24 16:27:18 7fb591068720 InnoDB: Set the innodb_undo_tablespaces parameter to the
2016-03-24 16:27:18 7fb591068720 InnoDB: correct value and retry. Suggested value is 0
2016-03-24 16:27:18 7164 [ERROR] Plugin 'InnoDB' init function returned error.
2016-03-24 16:27:18 7164 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
2016-03-24 16:27:18 7164 [ERROR] Unknown/unsupported storage engine: InnoDB
2016-03-24 16:27:18 7164 [ERROR] Aborting

---the end