MySQL shared table space to stand-alone table space

Using the innodb_export_import.py script:

Https://github.com/thecpaneladmin/innodb-tools

To install the Mysql-python module :

Shell > Yum Install mysql-python-y

Step One: Modify the my.cnf file

Add innodb_force_recovery = 4 parameter, restart MySQL, block all read and write operations,

Step Two: Execute the script to export the data from the InnoDB table (the script has no user and password options and needs to be written in the my.cnf file, i.e. user=xxx and password=xxx parameters):

Shell > Python innodb_export_import.py--export--dir=/data/mysql.bak--config=/usr/local/services/mysql/my.cnf

Backup process:

Getting a list of databases ...

Checking for InnoDB tables ...

Database Performance_schema ... No InnoDB Tables

Database sbtest ... Detected 1 InnoDB Tables

Database test ... No InnoDB Tables

Database Xiaoboluo ... Detected 2 InnoDB Tables

Dumping tables ...

Dumped table Xiaoboluo.t1 (1/3)

Dumped table Xiaoboluo.t2 (2/3)

Dumped table Sbtest.sbtest (3/3)

SUMMARY:

Tables Exported:3

Tables failed:0

Databases Total:2

Tables Total:3

Log File:/data/mysql.bak/201510140221/innodb_export.log

Tables were dumped to:/data/mysql.bak/201510140221

You can view the export log file at this time:

Shell > Cat/data/mysql.bak/201510140221/innodb_export.log

To view the backup file:

Shell > ll/data/mysql.bak/201510140221/

Total dosage

-rw-r--r--1 root root 211 October 02:21 innodb_export.log

Drwxr-xr-x 2 root root 4096 October 02:21 sbtest

Drwxr-xr-x 2 root root 4096 October 02:21 Xiaoboluo

Step three: Stop MySQL, add the independent tablespace parameter in MY.CNF:innodb_file_per_table=1, delete the innodb_force_recovery=4 parameter in my.cnf, Back up the original data directory and delete the ibdata1 and ib_logfile* files to start mysql

Shell > Cp-ar/data/mysql/data/data/mysql/data.bak

Shell > rm-rf/data/mysql/data/ibdata1/data/mysql/data/ib_logfile*

Shell > Service mysqld Start

Step four: and perform the Restore innodb table operation:

Shell > Python innodb_export_import.py--import--dir=/data/mysql.bak/201510140221/--config=/usr/local/services/ Mysql/my.cnf

Checking sbtest ...

/var/lib/mysql/sbtest/sbtest.ibd

Imported Table Sbtest.sbtest

MySQL Error 1049:unknown database ' 201510140246 '

MySQL Error 1049:unknown database ' 201510140253 '

Checking Xiaoboluo ...

/var/lib/mysql/xiaoboluo/t1.ibd

Imported Table Xiaoboluo.t1

/var/lib/mysql/xiaoboluo/t2.ibd

Imported Table Xiaoboluo.t2

SUMMARY:

Tables failed:0

Tables Imported:3

Databases Total:4

Tables Total:3

Tables skipped:0

Log File:/data/mysql.bak/201510140221/innodb_import.log

You can view the import log at this time:

Shell > Cat/data/mysql.bak/201510140221/innodb_import.log

Step five: Perform the test:

Shell > Python innodb_export_import.py--verify--dir=/data/mysql.bak/201510140221/--config=/usr/local/services/ Mysql/my.cnf

Getting a list of databases ...

Checking for InnoDB tables ...

Database Performance_schema ... No InnoDB Tables

Database sbtest ... Detected 1 InnoDB Tables

Database test ... No InnoDB Tables

Database Xiaoboluo ... Detected 2 InnoDB Tables

Checking tables ...

Checking xiaoboluo.t1 ...

Table Xiaoboluo.t1 is OK

Checking xiaoboluo.t2 ...

Table Xiaoboluo.t2 is OK

Checking sbtest.sbtest ...

Table Sbtest.sbtest is OK

SUMMARY:

Tables Ok:3

Databases Total:2

Tables Checked:3

Tables Total:3

Tables bad:0

Log File:/data/mysql.bak/201510140221/201510140259/innodb_check.log

You can view the check log at this time:

Shell > Cat /data/mysql.bak/201510140221/201510140259/innodb_check.log

Reference link:http://thecpaneladmin.com/how-to-convert-innodb-to-innodb_file_per_table-and-shrink-ibdata1/

In addition to this method, you can also use mysqldump to back up your data

MySQL shared table space to stand-alone table space