mysql共享表空间转独立表空间

使用innodb_export_import.py脚本：

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

 

安装MySQL-python模块:

shell > yum install MySQL-python -y 

步骤一：修改my.cnf文件

增加innodb_force_recovery = 4参数，重启mysql，阻塞所有读写操作，

步骤二：执行脚本导出innodb表的数据（脚本没有用户和密码选项，需要写在my.cnf文件中，即user=xxx和password=xxx参数）：

shell > python innodb_export_import.py --export --dir=/data/mysql.bak --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

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

 

此时可以查看export日志文件：

shell > cat /data/mysql.bak/201510140221/innodb_export.log

 

查看备份文件：

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

总用量 12

-rw-r--r-- 1 root root  211 10月 14 02:21 innodb_export.log

drwxr-xr-x 2 root root 4096 10月 14 02:21 sbtest

drwxr-xr-x 2 root root 4096 10月 14 02:21 xiaoboluo

 

步骤三：停止mysql，在my.cnf中添加独立表空间参数：innodb_file_per_table=1，删掉my.cnf中的innodb_force_recovery=4参数，备份原来的数据目录,并删掉ibdata1和ib_logfile*文件，启动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

 

步骤四：并执行恢复innodb表操作：

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

此时可以查看import日志：

shell > cat /data/mysql.bak/201510140221/innodb_import.log

 

步骤五：进行检测：

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

此时可以查看check日志：

shell > cat /data/mysql.bak/201510140221/201510140259/innodb_check.log

 

参考链接：http://thecpaneladmin.com/how-to-convert-innodb-to-innodb_file_per_table-and-shrink-ibdata1/

除了这个方法，还可以使用mysqldump来备份数据