Solve the large space occupied by ibdata1 files in the MySQL database directory.

When MySQL is often used and the InnoDB engine is enabled, you will find that the ibdata1 file is growing in the corresponding directory of the database, and even if you delete the table data, it cannot reduce the space usage. Therefore, the next configuration is to solve this problem.

1. Stop all database access services first;
2. Export data files;

Mysqldump-u root-p dbname | gzip> dbname. SQL .gz
This command compresses and exports the database and slows down. If your database is not large, you do not need to compress it:

Mysqldump-u root-p dbname> dbname. SQL
3. Modify the MySQL configuration;

Vi/etc/mysql/my. cnf
Add a row under [mysqld:

Innodb_file_per_table
Delete the ibdata1 and two log files in the database path.
Then restart the database:

Service mysql restart
Next, log on to the database and run the following command to check whether the configuration takes effect:

Show variables like '% per_table % ';
If the output result is ON, the configuration has been modified successfully;
4. Import the original data
After logging on to the database, run:

Source dbname. SQL
Or do not log on to mysql for execution:

Mysqldump-uroot-p dbname <dbname. SQL;
After the import is complete, you can find that the ibdata1 file has not grown much, and the data in all data tables will be stored in the database file with the same name in its own directory. After cleaning the table data, the space usage is also reduced.

Modify the mysql data file path in Linux

1. Set a new storage path

Mkdir-p/data/mysql
2. Copy the original data

Cp-R/var/lib/mysql/*/data/mysql
3. Modify permissions

Chown-R mysql: mysql/data/mysql
4. Modify the configuration file

Vi/etc/mysql/my. cnf
Datadir =/data/mysql
5. Modify the startup file

Vi/etc/apparmor. d/usr. sbin. mysqld
# Set

/Var/lib/mysql r,
/Var/lib/mysql/** rwk,
# Change

/Data/mysql r,
/Data/mysql/** rwk,
6. Restart the service.
Restart apparmor

/Etc/init. d/apparmor restart
/Etc/init. d/mysql restart
So far, the modification is complete.

Appendix:

Export and compress the database:

Mysqldump-u root-p mysql | gzip> mysql. SQL .gz
Decompress:

Gunzip mysql. SQL .gz
Import:

Source mysql. SQL
Or

Mysqldump-uroot-p dbname <dbname. SQL;
If it cannot be started, view the LOG:

Tail-n 40/var/log/syslog