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

In the MySQL database, if you do not specify the innodb_file_per_table=1 parameter to save the data of each table separately, the data of MySQL will be stored in the ibdata1 file, which will become very large over time.

The following is the method of saving the data in each database subdirectory according to some information on the network, so that the newly generated ibdata1 file will not be too large. The following is the slimming and operation process of ibdata1 on Ubuntu 14.04.12 x64 MySQL 5.5.

1. Backup the data in the database.

Mysqldump-u root-p-- extended-insert-- all-databases-- add-drop-database-- disable-keys-- flush-privileges-- quick-- routines-- triggers > / home/all-database.sql

/ / some documents use the following parameters: mysqldump-Q-uroot-p-add-drop-table-- all-databases-- events > / home/all-database.sql

If the ibdata1 is hundreds of GB, the backup process may take several hours.

2. Stop MySQL service.

Service mysql stop

3. Delete ibdata1, ib_logfile* and other files

Mv ibdata1 ibdata1.20160419

Mv ib_logfile0 ib_logfile0.20160420

Mv ib_logfile1 ib_logfile1.20160420

4. Modify my.cnf configuration file

Vi / etc/mysql/my.cnf

Add the following configuration under the "mysqld" sub-item:

Innodb_file_per_table = 1

5. Delete all databases except MySQL and performance_schema

Cd / var/lib/mysql

Rm-rf zabbix

There is only the Zabbix database on the server.

6. Restart the MySQL service

Service mysql start

Ps-ef | grep mysql

7. Restore data (re-import data)

Mysql-u root-p

Enter password:

> source / home/all-databaes.sql

> exit

/ / some documents are in the following command format: mysql-uroot-p < / home/all-database.sql

Enter password:

The / home/all-database.sql is the file generated by the first step backup. If there is more data in the original database, the restore process may take a long time. Please wait patiently for the restore to complete. If you haven't finished for more than 30 hours, you may want to consider aborting the data recovery operation, clearing a portion of the data in the table and starting over from step 1. If there are too many records in table and all of them can be deleted, it is faster to use truncate.

After the above steps, you can see that the new ibdata1 file is only tens of megabytes, and the data and index are small for a single table.

Ibd files, which are under the folder of the corresponding database.

Baidu library has a document "solving the problem of too large ibdata1 file operation manual" written in more detail. The link is: http://wenku.baidu.com/link?url=2qj73zDqwLwNIAPg6tB60c20wAE6a_2IIcJ8fGb2EUk0rmRdxqq2Wc0yAgnx7hoJ-dICC4rzhC9I0rWdgLVV-avatmeuCh74JxRE22NE4N_

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.