MySQL Data Migration combat

First, background:

Because of the tight machine resources, the company needs to make several machines for testing, and it needs to migrate all the applications on the existing machines to other machines.

After discussion, we use Xtrabackup tool to make data backup to InnoDB.

Ii. preparatory work

1. Installing Xtrabackup Tools

1) Install dependent packages

Yum install Libaio libaio-devel perl-time-hires Curl curl-devel zlib-devel openssl-devel perl cpio expat-devel Gettext-dev El Perl-extutils-makemaker perl-dbd-mysql.*

An installation error was encountered while installing PERL-DBD-MYSQL:

#rpm-IVH perl-dbd-mysql-4.006-1.el5.rf.i386.rpm

Warning:perl-dbd-mysql-4.006-1.el5.rf.i386.rpm:header V3 DSA Signature:nokey, key ID 6b8d79e6

error:failed dependencies:

Libmysqlclient.so.16 is needed by perl-dbd-mysql-4.006-1.el5.rf.i386

libmysqlclient.so.16 (LIBMYSQLCLIENT_15) is needed by perl-dbd-mysql-4.006-1.el5.rf.i386

Cause analysis: Because there is no mysql-shared installed, MySQL does not have Lib library, resulting in libmysqlclient.so.16 not found

Workaround: Download and install mysql-shared-compat-5.5.43-1.el7.x86_64.rpm Note the mysql-shared version should not be too much, more than 5.6 is libmysqlclient.so.18

2) Download and install Xtrabackup

can download RPM package can also download source package, if no special requirements recommended RPM package installation

The following describes the source package installation method:

1) Unzip

Cd/usr/local

Tar zxvf percona-xtrabackup-2.1.5-680-linux-x86_64.tar.gz

2) Copy Innobackupex, Xtrabackup, xtrabackup_51 tools to/usr/bin

Cp/usr/local/percona-xtrabackup-2.1.5-680-linux-x86_64/bin/innobackupex/usr/bin/innobackupex

Cp/usr/local/percona-xtrabackup-2.1.5-680-linux-x86_64/bin/xtrabackup/usr/bin/xtrabackup

Cp/usr/local/percona-xtrabackup-2.1.5-680-linux-x86_64/bin/xtrabackup_55/usr/bin/xtrabackup_55

If you do not copy to the/usr/bin directory, you can make a soft connection. When the command line is able to tab out the Innobackupex command, the installation succeeds

Third, data migration

1, back up the database, because the database is relatively large, you can first backup the database and then do other operations

Usr/bin/innobackupex--user=user--password=pass--defaults-file=/etc/my.cnf--socket=/app/mydata/mysql.sock-- Stream=tar/app/bak/2>/app/bak/mydata.log | Gzip 1>/app/bak/mydata.tar.gz

Backing up compressed data

Because of the fear of the two machines see the transmission network problems, so, do not choose to compress the transmission to the remote server, if the network is stable or insufficient disk space can choose to compress the transfer to remote server operations

/usr/bin/innobackupex--user=root--password=pass--defaults-file=/etc/my.cnf--no-timestamp--stream=tar/data/ Mysql_backup | gzip | SSH [email protected] "cat->/data/mysql_backup.tgz"

2, the realization of data single-machine multi-instance:

Because there is already a MySQL database running on the host to be migrated, it is necessary to implement multiple instances on this host machine.

(Single-machine multi-instance operation can see my other Weibo: http://yylinux.blog.51cto.com/8831641/1677678)

1) Create a new directory to hold the data

#mkdir/app/mydata3308

2) Configure the MY.CNF file, configure two my.cnf different MySQL instances to point to the different my.cnf

#cp-P/ETC/MY.CNF/ETC/MY3308.CNF

#vim/etc/my3308.cnf

Modify port to 3308 and the associated path to the corresponding/app/mydata3308 path

3. Remotely copy compressed files to the target host

#scp [Email protected]:/app/bak/mydata.tar.gz/apiapp/

4. Data recovery

1) Stop the database first: mysqladmin-uroot-p-s/app/mydata/mysql.sock Shotdown

2) Decompression TAR-IZXVF mydata.tar.gz-c/apiapp/db/(no db, need to mkdir/data/back_data/db/, note that the decompression must add the parameter-I, otherwise can not extract)

3) Restore/usr/bin/innobackupex--defaults-file=/apiapp/db/backup-my.cnf--apply-log/apiapp/db/( Note to point out the location of the BACKUP-MY.CNF, this step is to have the extracted database backup files, and the backup process generated by the redo do a consolidation, to maintain the consistency of the data, if not do this step the database does not start up)

/usr/bin/innobackupex--defaults-file=/etc/my3308.cnf--copy-back/apiapp/db/(--copy-back option command copies data from the backup directory, index, Log to the initial location specified in the My.cnf file. Note the basedir in the my.cnf file must be an empty folder. If it is not an empty folder, it cannot be executed)

4) Empowering Chown-r mysql:mysql/app/mydata3308

5) Restart database/usr/bin/mysqld_safe--defaults-file=/etc/my3308.cnf

Go to the database to see everything ok~

MySQL Data Migration combat