MySQL Database migration detailed steps (RPM)

==========================================================================================

First, Background introduction

==========================================================================================

1 , problem description

Due to the relocation of the machine room, the background DB server needs to be migrated, and in order to ensure that the data migration process, the online business does not affect, and can achieve the second level switch. If we use ordinary logical backup, such as: mysqldump, there will be a lock table situation, obviously not desirable. So we take a physical backup-based approach, the Backup tool is chosen as: Xtrabackup.

2 , System Environment

IP Address	System version	MySQL version	Role
172.17.138.239	SUSE Linux Enterprise Server ten (x86_64)	5.0.26	Legacy environment (Temporary master)
10.217.121.196	CentOS 6.3 (x86_64)	5.0.27	New Environment (master)
10.217.121.216	CentOS 6.3 (x86_64)	5.0.27	New Environment (from)

(1), because the current MySQL version is too old, so the backup tool recommended the choice of xtrabackup-2.0.8 version;

(2), because the current system environment is too old, the official Xtrabackup binary version can not run, it is necessary to compile the source code.

3 , compilation Requirements

(1), need to install cmake dependent library;

(2), need to download a copy of mysql-5.1.59 source package, placed in the percona-xtrabackup-2.0.8 directory;

(3), the Libtool version can not be higher than 2.4.

==========================================================================================

Second, Xtrabackup Compiling the installation

==========================================================================================

1 , compiling and installing CMake

Http://wwwNaNake.org/files/v2.8/cmake-2.8.10.tar.gz

# TAR-XVZF Cmake-2.8.10.tar.gz

# CD cmake-2.8.10

#./bootstrap--prefix=/usr/local

# gmake--jobs= ' grep processor/proc/cpuinfo | Wc-l '

# gmake Install

2 , compiling and installing Xtrabackup

Http://www.percona.com/downloads/XtraBackup/XtraBackup-2.0.8/binary/Linux/x86_64/percona-xtrabackup-2.0.8-587.tar.gz

# tar Xvzf percona-xtrabackup-2.0.8.tar.gz

# CD percona-xtrabackup-2.0.8

# # Download the mysql-5.1.59 source package to the percona-xtrabackup-2.0.8 directory, no need to unzip

Http://downloads.mysql.com/archives/get/file/mysql-5.1.59.tar.gz

#./utils/build.sh INNODB50

Attention:

The above callout section, need to correspond with the current MySQL major version, if have not understood, can refer to the following information.

# Cat BUILD.txt

# vim./utils/build.sh

# CP innobackupex/usr/local/bin/

# CD SRC

# CP Xbstream xtrabackup_51/usr/local/bin/

# cd/usr/local/bin/

# ln-s Innobackupex innobackupex-1.5.1

# ln-s xtrabackup_51 Xtrabackup

==========================================================================================

Third, DB Data Backup

==========================================================================================

1 , some common usage

# # If you run from the end

Innobackupex--user=root--defaults-file=/etc/my.cnf--slave-info--no-timestamp/data/mysql_backup

# # If running on the main side

Innobackupex--user=root--defaults-file=/etc/my.cnf--no-timestamp/data/mysql_backup

# # If running on the main side and compressing the transfer to the remote server

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

Attention:

Our current backup, directly on the main db (172.17.138.239), because the xtrabackup will not lock the table, the business will not affect, but also for the follow-up master-slave relationship to facilitate the establishment.

2 , backup steps

Because there is not much disk space left on the local server, this backup takes the form of compressed transfer to a remote server. But in order to better describe the problems encountered in this backup, we first use local mode.

# Innobackupex--user=root--defaults-file=/etc/my.cnf--no-timestamp/data/mysql_backup

Attention:

If the wrong information is reported, mainly because the "/etc/my.cnf" configuration file does not specify the Basedir, datadir parameter settings, resulting in xtrabackup cannot be found.

# PS aux | grep Mysql[d]

Attention:

Based on the above information, we need to add the following parameters to the configuration file (/ETC/MY.CNF):

basedir=/usr/local/services/mysql-5.0.26

Datadir=/usr/local/services/mysql-5.0.26/var

# Innobackupex--user=root--defaults-file=/etc/my.cnf--no-timestamp/data/mysql_backup

Attention:

This error message occurs because the backup directory "/data/mysql_backup" already exists, mainly caused by the previous operation. Because Xtrabackup requires that the backup directory not exist beforehand, it needs to be created automatically at execution time.

If we back up the data locally (provided the server has enough disk space), execute the following command:

# Rm-rf/data/mysql_backup

# Innobackupex--user=root--defaults-file=/etc/my.cnf--no-timestamp/data/mysql_backup

When recovering, synchronize the local backup data with rsync to the remote server and then restore it with Xtrabackup.

However, given the lack of local disk space, so this data migration, we use compressed transmission to the remote server backup method (this method is relatively slow ^_^), execute the following command:

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

# # Backup information output on the local server

# # File information on the remote server

Attention:

The above data backup time may be longer, recommended late at night, the next day to recover ^_^

==========================================================================================

Four, DB Data Recovery

==========================================================================================

1 , installation mysql-5.0.27 Environment

Http://cdn.mysql.com/archives/mysql-5.0/mysql-5.0.27.tar.gz

#/usr/sbin/groupadd MySQL

#/usr/sbin/useradd Mysql-g mysql-s/sbin/nologin

# mkdir-p/data/dbdata/{data,binlog,relaylog,otherlog}/var/run/mysql

# Chown-r Mysql:mysql/data/dbdata/var/run/mysql

# CD/USR/LOCAL/SRC

# tar Xvzf mysql-5.0.27.tar.gz

# CD mysql-5.0.27

#./configure--prefix=/usr/local/mysql \

--datadir=/data/dbdata/data \

--enable-thread-safe-client \

--enable-assembler \

--enable-local-infile \

--with-charset=utf8 \

--WITH-COLLATION=UTF8_GENERAL_CI \

--with-extra-charsets=all \

--with-unix-socket-path=/var/run/mysql/mysql.sock \

--without-debug \

--with-embedded-server \

--with-big-tables

# make--jobs= ' grep processor/proc/cpuinfo | Wc-l '

# make Install

# CP./support-files/mysql.server/etc/init.d/mysqld

# chmod +x/etc/init.d/mysqld

# chkconfig--add mysqld

# Chkconfig Mysqld on

# cd/usr/local

# mv MySQL mysql-5.0.27

# ln-s mysql-5.0.27 MySQL

# chmod +w/usr/local/mysql

# Chown-r mysql:mysql/usr/local/mysql/

# vim/etc/ld.so.conf

/usr/local/mysql/lib/mysql

#/sbin/ldconfig

# CD/ETC

# rm-f my.cnf && ln-s my_old.cnf my.cnf

# Vim MY.CNF

......

Innodb_data_file_path = Ibdata1:1024m:autoextend

Innodb_log_file_size = 512M

Innodb_log_files_in_group = 3

......

Attention:

Above we deliberately start deploying the MySQL environment, the table space files are allocated less, so that MySQL can start faster, but also for the next recovery operation to make a difference. Also note the parameters "Bind-address", "Server-id" in the following attachments.

# Cd/usr/local/mysql

#./bin/mysql_install_db--datadir=/data/dbdata/data--user=mysql

# service Mysqld Start

2 , compiling and installing Xtrabackup

In this omission, you can refer to the previous steps, or simply copy the previously compiled binaries.

3 , DB Data Recovery

# mkdir-p/data/mysql_backup

# tar-ixvzf/data/mysql_backup.tgz-c/data/mysql_backup

Attention:

When doing the TAR decompression operation, be sure to add "-i" parameter.

# Service Mysqld Stop

# Cd/data/dbdata

# MV Data data.old && mkdir data

# RM-RF binlog/*

# CD/ETC

# rm my.cnf && ln-s my_new.cnf my.cnf

......

Innodb_data_file_path =ibdata1:2000m;ibdata2:2000m;ibdata3:2000m;ibdata4:2000m;ibdata5:2000m;ibdata6:2000m; Ibdata7:2000m;ibdata8:2000m;ibdata9:2000m;ibdata10:2000m;ibdata11:2000m;ibdata12:2000m;ibdata13:2000m;ibdata14 : 2000m;ibdata15:2000m;ibdata16:2000m;ibdata17:2000m;ibdata18:2000m;ibdata19:2000m;ibdata20:2000m;ibdata21 : 2000m;ibdata22:2000m;ibdata23:2000m;ibdata24:2000m;ibdata25:2000m;ibdata26:2000m;ibdata27:2000m;ibdata28 : 2000m;ibdata29:2000m;ibdata30:2000m;ibdata31:2000m;ibdata32:2000m;ibdata33:2000m;ibdata34:2000m;ibdata35 : 2000m;ibdata36:2000m;ibdata37:2000m;ibdata38:2000m;ibdata39:2000m;ibdata40:2000m;ibdata41:2000m;ibdata42 : 2000m;ibdata43:2000m;ibdata44:2000m;ibdata45:2000m;ibdata46:2000m;ibdata47:2000m;ibdata48:2000m;ibdata49 : 2000m;ibdata50:2000m;ibdata51:2000m;ibdata52:2000m;ibdata53:2000m;ibdata54:2000m;ibdata55:2000m;ibdata56 : 2000m;ibdata57:2000m;ibdata58:2000m;ibdata59:2000m;ibdata60:2000m;ibdata61:2000m;ibdata62:2000m;ibdata63 : 2000m;ibdata64:2000m;ibdata65:2000m;ibdata66:2000m;ibdata67:2000m;ibdata68:2000m;ibdata69:2000m;ibdata70:2000m;ibdata71:2000m;ibdata72:2000m;ibdata73:2000m; ibdata74:2000m;ibdata75:2000m;ibdata76:2000m;ibdata77:2000m;ibdata78:2000m;ibdata79:2000m;ibdata80:2000m; ibdata81:2000m;ibdata82:2000m;ibdata83:2000m;ibdata84:2000m;ibdata85:2000m;ibdata86:2000m;ibdata87:2000m; ibdata88:2000m;ibdata89:2000m;ibdata90:2000m;ibdata91:2000m;ibdata92:2000m;ibdata93:2000m;ibdata94:2000m; ibdata95:2000m;ibdata96:2000m;ibdata97:2000m;ibdata98:2000m;ibdata99:2000m;ibdata100:2000m;ibdata101:2000m; ibdata102:2000m;ibdata103:2000m;ibdata104:2000m;ibdata105:2000m;ibdata106:2000m;ibdata107:2000m;ibdata108 : 2000m;ibdata109:2000m;ibdata110:2000m;ibdata111:2000m;ibdata112:2000m;ibdata113:2000m;ibdata114:2000m; ibdata115:2000m;ibdata116:2000m;ibdata117:2000m;ibdata118:2000m;ibdata119:2000m;ibdata120:2000m;ibdata121 : 2000m:autoextend

Innodb_log_file_size = 256M

Innodb_log_files_in_group = 2

......

Attention:

The above parameters should correspond to the old environment, otherwise it will not start, if the parameter "Innodb_log_files_in_group" is not set in the old environment, it can be ignored here, the default value is 2. Also note the parameters "Bind-address", "Server-id" in the following attachments.

# Innobackupex--apply-log/data/mysql_backup

# Innobackupex--copy-back/data/mysql_backup

# cp-a/usr/local/src/mysql-5.0.27/sql/share/*/data/dbdata/data/mysql/

Attention:

The above steps must be performed, or an error will be made, causing the startup to fail.

# Chown-r Mysql:mysql/data/dbdata/data

# service Mysqld Start

# # for the DB data recovery of another server (10.217.121.216), you can sync the extracted data from "10.217.121.196" and restore it in the same way

# rsync-arvpz-e "ssh-lroot-p36000"/data/mysql_backup/10.217.121.216:/data/mysql_backup/

4 , the establishment of master-slave relationship

(1) , DB Account Recovery

Export the "MySQL" Database of "172.17.138.239" and import it to "10.217.121.196"

Export "172.17.138.239" from DB's "MySQL" Database and import to "10.217.121.216"

(2) , " 10.217.121.196 "and" 10.217.121.216 "Master-Slave relations established

Actions on "10.217.121.196":

# mysql-uroot-e "Grant replication Slave on * * to ' repl ' @ ' 10.217.121.216 ' identified by ' repl123456 ';"

# MYSQL-UROOT-E "flush privileges;"

# mysql-uroot-e "Show Master Status\g"

Actions on "10.217.121.216":

# mysql-uroot-e "Change master to master_host= ' 10.217.121.196 ', master_user= ' repl ', master_password= ' repl123456 ', Master_log_file= ' XXX ', master_log_pos=xxx;

# mysql-uroot-e "Start slave;"

# mysql-uroot-e "Show slave Status\g"

Attention:

The above labeling part, need to fill in according to the actual situation.

(3) , " 172.17.138.239 "and" 10.217.121.196 "Master-Slave relations established

Actions on "172.17.138.239":

# mysql-uroot-e "Grant replication Slave on * * to ' repl ' @ ' 10.217.121.196 ' identified by ' repl123456 ';"

# MYSQL-UROOT-E "flush privileges;"

# mysql-uroot-e "Show Master Status\g"

Actions on "10.217.121.196":

# Cat/data/mysql_backup/xtrabackup_binlog_info

# mysql-uroot-e "Change master to master_host= ' 172.17.138.239 ', master_user= ' repl ', master_password= ' repl123456 ', Master_log_file= ' taesupport.001635 ', master_log_pos=436953160;

# mysql-uroot-e "Start slave;"

# mysql-uroot-e "Show slave Status\g"

The final master-slave relationship is as follows:

172.17.138.239 à 10.217.121.196 à 10.217.121.216

Mater Master Slave Slave

Attention:

The "Server-id" parameter in the configuration file "/etc/my.cnf" of the 3 db above must be kept different, otherwise you know ^_^

Business-side switching, just point to the new primary db "10.217.121.196" can be confirmed, after the establishment of the "172.17.138.239" and "10.217.121.196" master-slave relationship, and "10.217.121.196" and " 10.217.121.216 "The master-slave relationship needs to be retained.

==========================================================================================

V. Summary of some issues

==========================================================================================

1, due to the MySQL-5.0.26 version there are more bugs, the official has not provided the download, so this time the new environment using MySQL-5.0.27;

2, before the attempt to migrate to 5.1, 5.5 series version, but in the master from the set, always error, try the MySQL-5.0.37 version also not, this also verified the possibility of MySQL-5.0.26 there is a bug;

3, at present, we in MySQL-5.0.26 and MySQL-5.0.27 between the establishment of the master-slave relationship is possible, but the individual is more recommended 5.5 series version, the next can try to MySQL-5.0.27 and mysql-5.5.x between the establishment of master-slave;

4, this migration work is also relatively time-consuming, mainly involves too many table space files, in fact, the logical data is not so much, the personal feel before the old environment of "Innodb_data_file_path" parameter setting is not too reasonable, you can consider the ibdata to thin body and configuration adjustment;

5, in the migration process, but found that the previous 64 for the environment, the operation is the 32-bit version of MySQL, which will cause a problem, even if you in 64-bit environment, more memory, and your buffer pool size can only be set to 4G.

This article from "The ideal of life is Perseverance" blog, please be sure to keep this source http://sofar.blog.51cto.com/353572/1598364

MySQL Database migration detailed steps (RPM)