Maintenance method of table space in innodb system

Environmental description:

There is a running mysql environment, because the previous configuration file settings are too simple (innodb_data_file_path changes are not configured); As a result, the system tablespace is now full

If innodb_data_file_path is not set before, it will take the default value: innodb_data_file_path=ibdata1:12M; The problem is that it is not set here

autoextend

Solutions:

1. Shut down mysql that is already running

mysqladmin -h127.0.0.1 -uroot -pxxxx shutdown

2. Add the second system tablespace file to mysql, set its size and add autoextend attribute

[mysqld]
innodb_data_file_path      = ibdata1:12M;ibdata2:12M:autoextend

3. Restart mysql

service mysqld start

By the way, if you want to reduce the system tablespace, it is also possible to write here. Generally speaking, you should do one;

The general steps are as follows

1. Back up your database with mysqldump

2. Close your mysql database and delete the existing files under datadir

3. Edit the configuration file to the configuration you want and re-initialize a new mysql database

4. Restore the database from the dump file in Step 1