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来备份数据

posted @ 2016-02-19 18:07  xiaoboluo768  阅读(658)  评论(0编辑  收藏  举报