MySQL增加、缩减InnoDB 系统的表空间大小-摩杜云开发者社区

mysql 8.0 ，建议谨慎操作，在测试环境模拟成功之后生产操作

概述

数据目录中的ibdata1文件是默认的系统表空间。
你可以使用innodb_data_file_path和innodb_data_home_dir配置项来配置ibdata1。
innodb_data_file_path选项用于配置InnoDB系统表空间数据文件。它的值应该是一个或多个数据文件规范的列表。如果指定了两个或多个数据文件，则用分号(；)分隔。

如果你想要表空间中包含一个名为ibdata1的、固定大小为50MB的数据文件，
并且在数据目录中有一个名为ibdata2的50MB的自动扩展文件，则可以这样配置：

shell>sudo vi /etc/my.cnf
[mysqld]
innodb_data_file_path=ibdata1:50M;ibdata2:50M:autoextend

如果ibdata文件变得很大，特别是当innodbfile_per_table没有被启用且磁盘已满时，你可能需要在另一个磁盘上再添加一个数据文件。

增加InnoDB系统表空间

假设innodb_data_file_path是libdata1:50m:autoextend，其大小已达到76MB，而你的磁盘仅为100MB，那么可以再加一个磁盘，并在新磁盘上配置，添加另-个表空间。

1.停止MySQL服务器的运行：

shell>sudo systemctl stop mysqld

2.检查已存在的ibdata1文件的大小：

shell>sudols -lhtr /var/lib/mysql/ibdata1
-rw-r-1 mysql mysql 76M 0ct 613:33 /var/lib/mysql/ibdata1

3.挂载新磁盘，假设它挂载在/var/lib/mysql_extend上，将所有权归属更改为mysql，确保此文件尚未创建。
如果你使用的是App Armour或SELinux，请确保正确设置了别名或环境：

shell>sudo chown mysql:mysql /var/lib/mysql_extend
shell>sudo chmod 750 /var/lib/mysql_extend
shell>sudo ls -lhtr /var/lib/mysql_extend

4.打开my.cnf并添加以下内容：

shell>sudo vi /etc/my.cnf
[mysqld]
innodb_data_home_dir=
innodb_data_file_path=ibdata1:76M;/var/lib/mysql_extend/ibdata2:50M:autoextend

由于现有ibdata1的大小为76MB，所以你必须选择最大值至少为76MB。
下一个ibdata文件将在挂载在/var/lib/mysql_extend/上的新磁盘中创建。应该指定innodb_data_home_dir选项；否则， mysqld就会查看另一条路径，并出现错误。

5.重启MySQL服务器：

shell>sudo systemctl start mysql

6.验证新文件。既然已经指定它的大小为50MB，那么这个文件的初始大小将是50MB：

shell>sudo ls-lhtr /var/lib/mysql_extend/
mysql>SHOW VARIABLES LIKE ‘innodb_data_file_path’;

缩小InnoDB系统表空间

如果你使用的不是innodb_file_per_table，那么所有的表数据都存储在系统表空间中。
如果你删除(drop) 了一个表，那么该空间不会被回收。你可以缩小系统表空间并回收磁盘空间。
这需要较长的停机时间，因此建议的做法是在一个切换后没有负载的从库上操作，然后提升它为主库。

可以通过查询INFORMATION SCHEMA表来查看可用空间

mysql>SELECT SUM(data_free)/1024/1024 FROM INFORMATION_SCHEMA.TABLES;

1.停止写入数据库。
如果它是主库，mysql>SET @@GLOBAL.READ_ONLY=1;
如果它是从库，则停止复制并保存二进制日志。

mysql>STOP SLAVE;
mysql>SHOW SLAVE STATUS\G;

2.使用mysqldump或mydumper进行完整备份,不包括sys数据库。

shell>mydumper -uroot --password=<password> --trx-consistency-only --kill-long-queries --long-query-guard 500
–regex ‘^(?!sys)’ --output_dir /backups

3.停止MySQL服务器的运行。

shell>sudo systemctl stop mysql

4.删除所有的*.ibd、.ib_log和ib_data文件。
如果只使用了InnoDB表，则可以删除数据目录和存储系统表空间的所有位置(innodb_data_file_path)

shell>sudo rm-rf /var/lib/mysql/ib /var/lib/mysql/ <database directories>
shell>sudo rm-rf /var/lib/mysql_extend/*

5.初始化数据目录。

shell>sudo mysqld --initialize --datadir=/var/lib/mysql
shell>chown -R mysql:mysql /var/lib/mysql/
shell>chown -R mysql:mysql /var/lib/mysql_extend/

6.获取临时密码。

shell>sudo grep “temporary password is generated” /var/log/mysql/error.log|tail -1

7.启动MySQL并更改密码。

shell>sudo systemctl start mysqld
shell>mysql -uroot -p1I-qerr5agpa
mysql>ALTER USER ‘root’@‘localhost’ iDENTIFIED BY ‘xxxx’;
Query OK 0 rows affected(0.01sec)

8.恢复备份。使用临时密码连接到MySQL。

shell>/opt/my dumper/myloader --directory=/backups/ --queries-per-transaction = 50000 --threads= 6 --user=root --password=xxxx --overwrite-tables

9.如果它是一个主库，则通过语句mysql>SET@@GLOBAL.READ_ONLY=0;来启用写。
如果它是一个从库，则通过执行CHANGE MASTER TO COMMAND和START SLAVE来恢复复制。

除此之外有那些方式：

innodb_data_file_path 可以指定多个表空间
General tablespaces创建，迁移数据等方式