MySQL修改Undo表空间存储目录

1、查询源undo表空间目录

mysql[(none)]>show variables like 'innodb_undo%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| innodb_undo_directory    | ./    |
| innodb_undo_log_encrypt  | OFF   |
| innodb_undo_log_truncate | ON    |
| innodb_undo_tablespaces  | 2     |
+--------------------------+-------+

2、停止MySQL

systemctl stop mysqld

3、创建新undo表空间目录

[root@ct76mysql80 data]# pwd
/usr/local/mysql/data
[root@ct76mysql80 data]# mkdir undo_file
[root@ct76mysql80 data]# chown -R mysql.mysql undo_file
[root@ct76mysql80 data]# chmod -R 755 undo_file
[root@ct76mysql80 data]# ls -ld undo_file
drwxr-xr-x. 2 mysql mysql 4096 May 22 21:13 undo_file

4、移动undo表空间到新目录下

-rw-r-----. 1 mysql mysql 11534336 May 22 21:14 undo_001
-rw-r-----. 1 mysql mysql 11534336 May 22 21:14 undo_002
drwxr-xr-x. 2 mysql mysql     4096 May 22 21:13 undo_file
[root@ct76mysql80 data]# mv undo_00* undo_file
[root@ct76mysql80 data]# ls -l undo_file/
total 22528
-rw-r-----. 1 mysql mysql 11534336 May 22 21:14 undo_001
-rw-r-----. 1 mysql mysql 11534336 May 22 21:14 undo_002

5、修改undo表空间存储位置参数

innodb_undo_directory = ./undo_file

6、启动MySQL再次查询undo表空间位置

systemctl restart mysqld

[root@ct76mysql80 data]# mysql -uroot -proot
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.20 MySQL Community Server - GPL

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql[(none)]>show variables like 'innodb_undo%';
+--------------------------+-------------+
| Variable_name            | Value       |
+--------------------------+-------------+
| innodb_undo_directory    | ./undo_file |
| innodb_undo_log_encrypt  | OFF         |
| innodb_undo_log_truncate | ON          |
| innodb_undo_tablespaces  | 2           |
+--------------------------+-------------+
4 rows in set (0.01 sec)