MySQL table ibdata1 data switch to a single table store, default exported table structure

Free up space for MySQL ibdata1 file

Mysqldump options:

--add-drop-table: Adds a statement that deletes a database table before each database table statement is created;

--add-locks: Lock database tables when backing up database tables;

--all-databases: Back up all databases on the MySQL server;

--comments: Add comment information;

--compact: Compression mode, resulting in less output;

--complete-insert: INSERT statement for output completion;

--databases: Specifies the database to be backed up;

--default-character-set: Specifies the default character set;

--force: The backup operation continues when an error occurs;

--host: Specifies the server to back up the database;

--lock-tables: Lock all database tables before backing up;

--NO-CREATE-DB: Prohibit generation of creation of database statements;

--no-create-info: Prohibit generation of CREATE database library table statements;

--password: Password to connect to MySQL server;

The port number of the--port:mysql server;

--user: The user name to connect to the MySQL server.

Mysqldump the table structure exported by default is as follows:

Mysqldump-uroot-p-d-b TestDB >testdb.sql

[SQL] view plain copy 650) this.width=650; "Src="/e/u261/themes/default/images/ Spacer.gif "alt=" on code to see the "width=" "height=" "style=" Background:url ("/e/u261/themes/default/images/word.gif ") No-repeat center;border:1px solid #ddd;"/>650) this.width=650; "Src=" Https://code.csdn.net/assets/ico _fork.svg "alt=" Derivation to My Code slice "width=" "height=" "style=" background-color:inherit;margin-top:8px; "/>

2. DROP TABLE IF EXISTS ' test ';

4. CREATE TABLE ' test ' (

6. ' id ' int(one) Notnull auto_increment,

8. 'name' varchar(5)DEFAULT NULL,

10. PRIMARY KEY (' ID ')

12. ) Engine=innodb auto_increment=4 DEFAULT Charset=utf8;

from: http://man.linuxde.net/mysqldump

In the MySQL database, if you do not specify the innodb_file_per_table parameter, the data for each table exists separately, and the MySQL data will be stored in the Ibdata1 file.
MySQL ibdata1 store data, indexes, etc., is the most important data of MySQL.

Steps:

1, backing up the database

Enter MySQL Server 5.1/bin from the command line
Back up all databases, execute commands mysqldump-q-uusername-pyourpassword--add-drop-table--all-databases >/backup/all.sql

Deleting a database
Drop database db1;
Drop database DB2;

The application database is not deleted here, and when the database is restored in the whole library, an exception error is reported, and some *.ibd of the table will not exist.

How to resolve:

Move Db1/backup/db1_bak

Move Db2/backup/db2_bak

After you finish this step, then stop the database.

2. Modify the MySQL configuration file

Modify the My.cnf file to add the following configuration

Innodb_file_per_table

Use a separate InnoDB file for each table to modify the/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 (Mysql,test,information_schema the database itself folder do not delete)

4. Restore the database

Start the database service

Enter MySQL Server 5.1/bin from the command line
Restore all databases, execute commands Mysql-uusername-pyourpassword </backup/*.sql

Big announcement into.

After these steps, you can see that the new Ibdata1 file is only dozens of M, and the data and indexes become small IBD files for a single table, which are under the folder of the corresponding database.

MySQL table ibdata1 data switch to a single table store, default exported table structure