MySQL shared table space to stand-alone table space

Source: Internet
Author: User

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.