Shulou(Shulou.com)06/01 Report--

If MySql innodb is a shared tablespace, the ibdata1 file is getting larger and larger, reaching more than 30 gigabytes. Empty some useless tables:

Truncate table xxx

Then optimize table xxx; had no effect.

Because it doesn't work on shared tablespaces.

Mysql ibdata1 stores data, indexes, etc., which is the most important data of MYSQL.

If the data is not stored separately, the size of the file can easily be gigabytes, or even tens of gigabytes. For some applications, it is not very appropriate. So we need to shrink this file.

Cannot shrink automatically, you must export data, delete ibdata1, and then import data, which is troublesome, so you need to change to a separate file for each table.

Solution: data files are stored separately (how to change the shared tablespace to a separate tablespace file for each table).

The steps are as follows:

1) back up the database

Enter MySQL Server 5.0\ bin from the command line

Back up all databases and execute the command

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

Execute. / mysqld-- verbose-- help under linux | grep-A 1 'Default options'

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 environment, you can:

Mysqld-- verbose-- help > mysqlhelp.txt

Notepad mysqlhelp.txt

Look for Default options inside, and you can see the order in which you look for my.ini to find the real directory.

3) modify mysql configuration file

Open a my.ini or my.cnf file

Add the following configuration under [mysqld]

Innodb_file_per_table=1

Verify that the configuration is in effect. After restarting mysql, execute

Show variables like'% per_table%'

See if the innodb_file_per_table variable is ON

4) Delete the original data file

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

5) restore the database

Start the database service

Enter MySQL Server 5.0\ bin from the command line

Restore all databases, execute the command mysql-uusername-pyourpassword < c:/all.sql

After the above steps, you can see that the new ibdata1 file is only a few dozen M, and the data and index have become small ibd files for a single table, which are under the folder of the corresponding database.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.