MySQL database migration, multi-port operation, Innobackupex backup

1.Requirements: mysql migrated from server to server

on server:

Nc-l 12345|tar ZXVF

to one server:

Ldd/usr/local/mysql/bin/mysqld|xargs tar zcvf so.tar.gz // use ldd to view dependent library files and package

SCP so.tar.gz 192.168.1.12:/root // Remote Transfer library file

Tar zcvf-mysql |nc 192.168.1.12 12345

on server:// above I use NC command to transmit, the method is arbitrary, can pass on the line

Iptables-f

Setenforce 0 // pay attention to shutting down the security mechanism to guarantee file transfer

Tar XF so.tar.gz // put lib64 in place, I will not write in detail here

MV mysql//usr/local/ // transfer OK move to usr directory

cd/usr/local/ Mysql/su Pport-files /

CP my-medium.cnf/etc/my.cnf // Copy medium configuration file

CP Mysql.server/etc/init.d/mysqld // copy boot file

ln-s/usr/local/mysql/bin/*/usr/local/bin // copy mysql command, of course, add it with Paht.

CD /usr/local/mysql/ scripts/

useradd-m-s/sbin/nologin MySQL // create mysql program user in order to initialize data

./mysql_install_db--basedir=/usr/local/mysql/--datadir=/usr/local/mysql/data--user=mysql // data initialization

Service mysqld Start // start mysqld service

PS Aux|grep MySQL

mysql-uroot-p // login, user name password and Server-like, data is the same

2. Requirement: A mysql server starts MySQL on multiple ports

[[Email protected] ~]# service mysqld stop

[[email protected] ~]# for i in {3306..3308};d o Cp/etc/my.cnf/etc/my${i}.cnf;done

Copy the configuration file with the name /etc/my3307.cnf

[[email protected] ~]# mkdir-p/www/mysqldata{3306..3308}

Make Data Catalog

[[email protected]/]# for i in {3306..3308};d o/usr/local/mysql/scripts/mysql_install_db--datadir=/www/mysqldata$i-- User=mysql--basedir=/usr/local/mysql;done

initialization of Data Catalog for production

[[email protected] local]# vi/etc/my3307.cnf// Change two: Here take 3307 as an example

[Mysqld]

port=3307 // This item has been changed.

Socket=/tmp/mysql_3307.sock// specify socket directory

datadir=/www/mysqldata3307/// Specify Data directory, if not specified, you need to add--datadir=/www/when starting 3307 port below mysqldata3307--user=mysql

[Client]

Port = 3307

Socket =/tmp/mysql_3307.sock

start the 3307 port: (Other similar)

Mysqld_safe--DEFAULTS-EXTRA-FILE=/ETC/MY3307.CNF &

go to MySQL:

[[email protected] local]# mysql-uroot-s/tmp/mysql_3307.sock (in fact, You can log in without-root)

close the corresponding mysqld service: (Many methods, can also use kill-9)

[Email protected] local]# mysqladmin-uroot-s/tmp/mysql_3307.sock shutdown

3307 data can be copied directly to the 3306:

[email protected] test]# CP aa.frm. /.. /mysqldata3306/test/

currently under the mysqldata3307/test

3. Requirements: backing up data with the Innobackupex tool

Open Multi-port mode, for 3306 port, configuration file is /etc/my3306.cnf data file is /www/mysqldata3306

[[Email protected] ~] #yum-y install perl perl-devel libaio libaio-devel perl-time-hires perl-dbd-mysql// Installation Dependency Package

[[Email protected] ~] #rpm-IVH percona-xtrabackup-2.0.2-461.rhel6.x86_64.rpm--nodeps

[[email protected] tmp]# MKDIR/HOME/MYSQLBAK/FULLFILE/AAA// make backup file storage path

[email protected] tmp]# Innobackupex--user=root--slave-info--socket=/tmp/mysql3306.sock--defaults-file=/etc/ My3306.cnf/home/mysqlbak/fullfile/aaa >/dev/null// start Backup

Close the database before backing up, and delete the data and log files (rename it is possible)

Mysqladmin-uroot-s/tmp/mysql33036.sock shutdown// Close Database

[email protected] www]# mv mysqldata3306 Mysqldata3306.bak // Delete the original data file

[email protected] www]# mkdir mysqldata3306 // make empty directory data file

[email protected] tmp]# Innobackupex--defaults-file=/etc/my3306.cnf--user=root--apply-log--rsync--use-memory /home/mysqlbak/fullfile/aaa/2017-03-06_13-08-44// Application log,--use-memory Option to speed up recovery

[email protected] tmp]# Innobackupex--defaults-file=/etc/my3306.cnf--user=root--copy-back/home/mysqlbak/ fullfile/aaa/2017-03-06_13-08-44 // Copy to the original data file

[email protected] www]# chown-r mysql:mysql mysqldata3306 // give permission to allow MySQL to read

from what can be seen, recovery is divided into two steps, Step 1 is apply-log, in order to speed up, the general recommended setting --use-memory , after this step is complete, The backup file under directory/backup/mysql/data/2017-03-06_13-08-44 is ready.

Step 2 is copy-back, which copies the backup files to the original data directory.

When the recovery is complete, be sure to check the data Catalog for the correct owner and permissions.

Common options:

--parallel=4--throttle=400 Parallel number, according to the host configuration selection appropriate, the default is 1 , multiple can speed up the backup speed.

--stream=tar compression type, here choose the tar format, you can add, can not add. It's a little bit smaller, and it's packed in a backup.

This article is from the "12384524" blog, please be sure to keep this source http://12394524.blog.51cto.com/12384524/1903707

MySQL database migration, multi-port operation, Innobackupex backup