undo表空间

undo表空间

利用undo表空间主要用来完成撤销工作（回滚操作）；

在数据库5.7版本中，默认存储在共享表空间中（ibdata）；在数据库8.0版本后，默认就是独立存储了(undo_001-undo_002)；

在实际生产环境中，建议在5.7版本之后，都将undo表空间进行独立文件存储；

undo表空间管理：

• 表空间配置参数信息查看

mysql> select @@innodb_undo_tablespaces;
+-------------------------------------------+
| @@innodb_undo_tablespaces       |
+-------------------------------------------+
|                                                         2 |
+-------------------------------------------+
1 row in set (0.00 sec)
-- 确认是否打开独立undo模式，并设置undo表空间文件个数（3-5个）

mysql> select @@innodb_max_undo_log_size;
+-------------------------------------------+
| @@innodb_max_undo_log_size     |
+-------------------------------------------+
|                                       1073741824 |
+-------------------------------------------+
1 row in set (0.00 sec) 1G
-- 表示undo日志信息的大小，默认1G

mysql> select @@innodb_undo_log_truncate;
+-------------------------------------------+
| @@innodb_undo_log_truncate      |
+-------------------------------------------+
|                                                          1 |
+-------------------------------------------+
1 row in set (0.00 sec)
-- 表示开启undo自动回收的机制（undo purge）

mysql> select @@innodb_purge_rseg_truncate_frequency;
+----------------------------------------------------------+
| @@innodb_purge_rseg_truncate_frequency   |
+----------------------------------------------------------+
|                                                                         128 |
+----------------------------------------------------------+
1 row in set (0.00 sec)
-- 触发自动回收的条件，单位是检测次数 

官方参数使用说明（important）：

The number of undo tablespaces can only be configured when initializing a MySQL instence and is fixed for the

life of the instance；

undo表空间的数量只能在初始化MySQL实例时配置，并且在实例生命周期内是固定的

表空间配置参数修改调整

修改数据库5.7版本服务的undo表空间，实现undo表空间的独立存储；

查看获取数据库5.7版本的默认undo配置情况：

mysql> select @@innodb_undo_tablespaces;
+--------------------------------------+
| @@innodb_undo_tablespaces |
+--------------------------------------+
|                                                  0 |
+--------------------------------------+
1 row in set (0.00 sec)
-- 在数据库5.7版本中，undo表空间默认并未实现独立存储；

关闭数据库服务程序，对undo表空间进行独立存储配置：

# 关闭数据库服务程序，清理数据库服务数据目录
[root@baimeidashu-01 ~]# systemctl stop mysqld3357
[root@baimeidashu-01 ~]# systemctl is-active mysqld3357
unknown
-- 关闭数据库服务程序
[root@baimeidashu-01 ~]# rm -rf /data/3357/data/*
-- 删除清空数据库数据目录

# 编写修改数据库服务配置文件
[root@baimeidashu-01 ~]# vim /data/3357/my.cnf
[mysqld]
innodb_undo_tablespaces=3
innodb_max_undo_log_size=128M
innodb_undo_log_truncate=ON
innodb_purge_rseg_truncate_frequency=32
-- 在数据库服务端添加以上参数信息

重新初始化数据库服务程序：

[root@baimeidashu-01 ~]#  /usr/local/mysql57/bin/mysqld --defaults-file=/data/3357/my.cnf --initialize-insecure  --basedir=/usr/local/mysql57 --datadir=/data/3357/data --user=mysql

[root@baimeidashu-01 ~]# ll /data/3357/data/undo*
-rw-r----- 1 mysql mysql 10485760 11月 15 10:19 /data/3357/data/undo001
-rw-r----- 1 mysql mysql 10485760 11月 15 10:19 /data/3357/data/undo002
-rw-r----- 1 mysql mysql 10485760 11月 15 10:19 /data/3357/data/undo003

重新启动数据库服务程序：

[root@baimeidashu-01 ~]# systemctl start mysqld3357
[root@baimeidashu-01 ~]# systemctl is-active mysqld3357
active

实现undo表空间文件指定目录存储

# 将数据库服务进行关闭
[root@baimeidashu-01 ~]# systemctl stop mysqld3357

# 编写数据库服务配置文件
[root@baimeidashu-01 ~]# vim /data/3357/my.cnf
[mysqld]
innodb_undo_directory=/data/3357/undologs

# 创建存储undo表空间文件目录
[root@baimeidashu-01 ~]# mkdir -p /data/3357/undologs
[root@baimeidashu-01 ~]# chown -R mysql.mysql /data/*
[root@baimeidashu-01 ~]# cp -a /data/3357/data/undo* /data/3357/undologs/

# 将数据库服务进行启动
[root@baimeidashu-01 ~]# mysql -S /tmp/mysql3357.sock
mysql > select @@innodb_undo_directory;
+-------------------------------------+
| @@innodb_undo_directory    |
+-------------------------------------+
| /data/3357/undologs               |
+-------------------------------------+
1 row in set (0.00 sec)

说明：对于数据库8.0版本，在进行undo表空间配置信息调整的时候，可以进行在线调整；

数据库8.0 undo表空间与数据库5.7undo表空间区别资料：

https://dev.mysql.com/doc/refman/8.0/en/innodb-undo-tablespaces.html

===============================================================================================

数据库8.0独立表空间设置扩展：Adding Undo Tablespaces

Because undo logs can become large during long-running transactions, creating additional undo tablespaces

can help prevent individual undo tablespaces from becoming too large.

As of MySQL 8.0.14, additional undo tablespaces can be created at runtime using CREATE UNDO TABLESPACE syntax.

# 创建新的独立的undo表空间文件
CREATE UNDO TABLESPACE tablespace_name ADD DATAFILE 'file_name.ibu';

# 查看已经创建的独立的undo表空间文件
mysql> select tablespace_name,file_name from information_schema.files where file_type like 'undo log';
+---------------------------+----------------+
| TABLESPACE_NAME    | FILE_NAME    |
+---------------------------+----------------+
| innodb_undo_001      | ./undo_001   |
| innodb_undo_002      | ./undo_002   |
| tablespace_name       | ./baimeidashu1.ibu |
+---------------------------+----------------+
3 rows in set (0.00 sec)

# 删除已有的独立的undo表空间文件
ALTER UNDO TABLESPACE tablespace_name SET INACTIVE;
-- 将指定的undo表空间信息设置为失效
DROP UNDO TABLESPACE tablespace_name;
-- 删除指定的undo表空间信息

# 查看已有的独立的undo表空间状态
SELECT NAME, STATE FROM INFORMATION_SCHEMA.INNODB_TABLESPACES
  WHERE NAME LIKE 'tablespace_name';

欢迎来撩 ： 汇总all