How to narrow down the mysql ibdata1 File

The reduction of the mysql ibdata1 file has recently found that MYSQL fragments are very large, occupying tens of millions of shards in a single tablespace, but alert table table_name engine = innodb alone is basically unable to be executed, because the table is too large, the ALERT method is suitable for some small and medium databases. Therefore, the final method is to import and export the step www.2cto.com mysql ibdata1 to store data and indexes, which is the primary data of MYSQL. If you do not store the data separately, the size of the file will easily reach G, or even 10 + G. It is not suitable for some applications. Therefore, we need to narrow down this file. Method: Separate data files. Step: 1. Back up the database www.2cto.com from the command line to MySQL Server 5.0 \ bin to back up all the databases, run mysqldump-q-uusername-pyourpassword -- add-drop-table-all-databases>/all. after SQL completes this step, stop the database service. 2. Modify the mysql configuration file to modify my. INI file, add the following innodb_file_per_table configuration to use a separate innoDB file for each table, modify/etc/my. cnf file 3, 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) 4, restore database start Database Service from command line enter MySQL Server 5.0 \ bin restore all databases, Execute Command mysql-uusername-pyourpassword </all. after completing the preceding steps, you 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. For those who have used MySQL on innodb_file_per_table at www.2cto.com, The MyISAM Table engine is the most frequently used at the beginning. The database of this engine creates three files respectively: Table Structure, table index, and table data space. You can migrate a database directory to another database. However, when you use InnoDB, everything changes. By default, InnoDB stores all the table data of the InnoDB engine in a shared space: ibdata1, which makes it uncomfortable. When adding or deleting databases, the ibdata1 file does not automatically contract, the backup of a single database will also become a problem. Data can only be exported using mysqldump and then imported to solve this problem. Add the innodb_file_per_table parameter to the MySQL configuration file [mysqld. You can change InnoDB to the independent tablespace mode. Each table in each database generates a data space. Independent tablespace: advantages: 1. Each table has its own independent tablespace. 2. Data and indexes of each table are stored in its own tablespace. 3. A single table can be moved in different databases. 4. space can be recycled (except for the drop table operation, the table cannot be recycled by itself) a) the Drop table operation automatically recycles the table space. For a statistical analysis or daily value table, after deleting a large amount of data, you can use: alter table TableName engine = innodb; Reduce Unnecessary space. B) using turncate table for innodb-plugin will also shrink the space. C) For tables that use independent tablespaces, no matter how they are deleted, the tablespace fragments will not seriously affect performance, and there is a chance to process them. Disadvantage: the increase in a single table at www.2cto.com is too large, for example, over 100 GB. Conclusion: Shared tablespace has few advantages in Insert operations. Other independent tablespaces do a good job. When the independent tablespace is enabled, properly adjust innodb_open_files. The tablespace cp of InnoDB Hot Backup (cold Backup) won't face a lot of useless copies. In addition, innodb hot backup and tablespace management commands can be used to achieve single-current movement. 1. innodb_file_per_table settings. enable: In my. in cnf, set innodb_file_per_table = 1 2 under [mysqld. check whether mysql> show variables like '% per_table %' is enabled; 3. disable the exclusive tablespace innodb_file_per_table = 0 close the independent tablespace mysql> show variables like '% per_table % ';