How to narrow down the MySqlibdata1 file if it is too large

MySqlibdata1 file is too large how to narrow down MySqlinnodb if it is a shared tablespace, ibdata1 file is getting bigger and bigger, reaching more than 30 GB, some useless tables are cleared: truncatetablexxx; then optimizetablexxx; no effect because it does not work for the shared tablespace. Mysqlibdata1 stores data, indexes, and so on. It is MYSQL.

MySql ibdata1 file is too large how to reduce MySql innodb if it is a shared tablespace, ibdata1 file is getting bigger and bigger, reaching more than 30 GB, some useless tables are cleared: truncate table xxx; then optimize table xxx; no effect because it does not work for the shared tablespace. Mysql ibdata1 stores data, indexes, and so on. It is MYSQL

How to narrow down a MySql ibdata1 file that is too large

If the MySql innodb table space is shared, the ibdata1 file will become larger and larger, reaching more than 30 GB, and some useless tables will be cleared:
Truncate table xxx;
Then optimize table xxx; no effect
Because it does not work for the shared tablespace.
Mysql ibdata1 stores data and indexes, and is the primary data of MYSQL.

If you do not store the data separately, the size of the file will easily be GB, or even dozens of GB. It is not suitable for some applications. Therefore, we need to narrow down this file.
The data cannot be automatically shrunk. You must export the data, delete ibdata1, and import the data. Therefore, you need to change it to a separate file for each table.

Solution: store data files separately (how to change the shared tablespace to an independent tablespace file for each table ).
The procedure is as follows:

?

1) back up the database

Enter MySQL Server 5.0 \ bin from the command line
Back up all databases and execute commands
D: \> mysqldump-q-umysql-ppassword -- add-drop-table -- all-databases> c:/all. SQL

After this step, stop the database service.

?

2) Find the my. ini or my. cnf file.
Run./mysqld -- verbose -- help | grep-A 1 'default options' in linux'
There will be a similar display:
Default options are read from the following files in the given order:
/Etc/my. cnf ~ /. My. cnf/usr/local/service/mysql3306/etc/my. cnf

In windows, you can:
Mysqld -- verbose -- help> mysqlhelp.txt
Notepad mysqlhelp.txt
Find Default options in the directory, and you can see the order of my. ini to find the real directory.

?

3) modify the mysql configuration file
Open the my. ini or my. cnf File
Add the following configuration under [mysqld]
Innodb_file_per_table = 1

Verify that the configuration takes effect. You can restart mysql and run
Show variables like '% per_table %'
Check whether the innodb_file_per_table variable is ON.

?

4) Delete the original data file

Delete the original ibdata1 file and Log File ib_logfile *, and delete the application database folder under the data Directory (do not delete the mysql folder)

?

5) restore the database

Start Database Service

Enter MySQL Server 5.0 \ bin from the command line
Restore all databases and run mysql-uusername-pyourpassword <c:/all. SQL

After the preceding steps, we can see that the new ibdata1 file is only dozens of MB, and the data and indexes are converted into small ibd files for a single table, they are in the folder of the corresponding database.

?

?