Tag:mysql 数据库迁移
Article From:https://www.cnblogs.com/yimingwang/p/9121884.html


Reprinted from: http://sofar.blog.51cto.com/353572/1598364



First, background introduction


1、Problem description

Because of the relocation of the computer room, the backstage DB server needs to be migrated, and in order to ensure that in the process of data migration, there is no impact on the online business and can be switched on second level. If we use normal logical backup, such as mysqldump, there will be a lock table, which is obviously not desirable.Therefore, we adopt physical backup scheme, and the backup tool is xtrabackup.


2、System environment


System version



SUSE Linux Enterprise Server 10 (x86_64)


Old environment (temporary owner)

CentOS 6.3 (x86_64)


New environment (main)

CentOS 6.3 (x86_64)


New environment (from)

(1)、Since the current MySQL version is too old, the backup tool recommends the version of xtrabackup-2.0.8.

(2)、Because the current system environment is too old, the official xtrabackup binary version can not run, so it needs to compile the source code.


3、Compilation requirements

(1)、The cmake dependency library needs to be installed;

(2)、You need to download a mysql-5.1.59 source package and put it in the percona-xtrabackup-2.0.8 directory.

(3)、libtoolThe version can’t be more than 2.4.



Two.xtrabackupCompile and install


1、Compile and installcmake


# 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、Compile and installxtrabackup


# 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 without decompression.



# ./utils/build.sh innodb50


Be careful:

The above annotation part needs to correspond to the current main version of MySQL. If you do not understand it, you 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



Three.DBData backup


1、Some common usage

## If it’s running at the end of it

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


## If you run it at the main end

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


## If you run and compress it to the remote server at the main end

innobackupex –user=root –defaults-file=/etc/my.cnf –no-timestamp –stream=tar /data/mysql_backup | gzip | ssh root@″ cat – > /data/mysql_backup.tgz”


Be careful:

Our backups are done directly on the main DB (, because xtrabackup does not lock the table, does not affect the business, and will also facilitate the establishment of subsequent master and slave relations.


2、Backup step

Since there is not much disk space left by the local server, the backup is compressed to remote servers. However, in order to better describe the problems encountered in this backup, we first use the local mode.


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


Be careful:

If the wrong information is reported, it is mainly because the basedir and dataDir parameter settings are not specified in the “/etc/my.cnf” configuration file, resulting in xtrabackup not being found.


# ps aux | grep mysql[d]


Be careful:

Based on the above information, we need to add the following parameters to the configuration file (/etc/my.cnf):




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


Be careful:

This error message occurs because the backup directory “/data/mysql_backup” already exists, mainly caused by the previous operation. Because xtrabackup requires backup directory not to exist beforehand, and it will need to be created automatically at execution time.


If we backup the data to local (if the server has enough disk space), execute the following commands:

# rm -rf /data/mysql_backup

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


At the time of recovery, synchronize the local backup data Rsync to the remote server, then restore it with xtrabackup.


But in view of the lack of local disk space, this data migration, we use compressed transmission to remote server backup method (this method is slower), execute the following command:

innobackupex –user=root –defaults-file=/etc/my.cnf –no-timestamp –stream=tar /data/mysql_backup | gzip | ssh root@ “cat – > /data/mysql_backup.tgz”


## Backup information output on a local server



## File information on a remote server



Be careful:

The above data backup time may be relatively long, recommended late at night, second days to recover.



Four.DBdata recovery


1、installmysql-5.0.27Environmental Science


# /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 \



# 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



# /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



Be careful:

When we start to deploy the MySQL environment, we have less table space files, so that we can make MySQL start faster and make a difference for the next recovery operation. We should also pay attention to the following parameters in Appendix “bind-address” and “Se”.Rver-id “.


# cd /usr/local/mysql

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

# service mysqld start


2、Compile and installxtrabackup

In this ellipsis, you can refer to the previous steps, or copy directly the previously compiled binary files.


3DBdata recovery

# mkdir -p /data/mysql_backup

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


Be careful:

When doing the tar decompression operation, you must add the “-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



Be careful:

The above parameters should correspond to the old environment, otherwise it can not be started. If the parameter “innodb_log_files_in_group” is not set in the old environment, it can be ignored and the default value is 2. We also need to pay attention to the following parameters in Appendix “bind-addrEss “,” server-id “.


# 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/



Be careful:

The above steps must be carried out, otherwise it will be reported wrong, resulting in the failure of startup.


# chown -R mysql:mysql /data/dbdata/data

# service mysqld start


## As for the DB data recovery of another server (, you can synchronize the unzip data on “” and restore it in the same way.

# rsync -arvPz -e “ssh -lroot -p36000” /data/mysql_backup/


4、The establishment of the principal and subordinate relationship

(1)DBAccount recovery

Export the “MySQL” database of “” and then import it to “”.

Export the “” from the “MySQL” database of DB, and then import it to “”.


(2)、“”And “”The establishment of the principal and subordinate relationship

Operation on “”:

# mysql -uroot -e “grant replication slave on *.* to ‘repl’@’’ identified by ‘repl123456’;”

# mysql -uroot -e “flush privileges;”

# mysql -uroot -e “show master status\G”


Operation on “”:

# mysql -uroot -e “change master to master_host=’′,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”


Be careful:

The above annotation part needs to be filled in according to the actual situation.


(3)、“”And “”The establishment of the principal and subordinate relationship

Operation on “”:

# mysql -uroot -e “grant replication slave on *.* to ‘repl’@’’ identified by ‘repl123456’;”

# mysql -uroot -e “flush privileges;”

# mysql -uroot -e “show master status\G”

Operation on “”:

# cat /data/mysql_backup/xtrabackup_binlog_info



# mysql -uroot -e “change master to master_host=’′,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 principal and subordinate relationship is as follows: à  à

Mater             Master Slave        Slave


Be careful:

The “server-id” parameter in the configuration file “/etc/my.cnf” of the above 3 DB must remain different, otherwise you will know how to read it.


When the business side switches, only the new main DB “” can be pointed out. After the confirmation is unmistakable, the “” and “” are established before the shutdown, and “10.217.121.”The relationship between 196 and should remain unchanged.



Five. Summary of some problems


1、Since there are more BUG in the version of MySQL-5.0.26, official downloading is not available, so the new environment is MySQL-5.0.27.


2、The previous attempt to migrate to the 5.1, 5.5 series version, but when the master from the setting, always wrong, try the MySQL-5.0.37 version is also not, which also verifies that the possibility of the existence of the BUG; MySQL-5.0.26;


3、At present, we can build a master-slave relationship between MySQL-5.0.26 and MySQL-5.0.27, but individuals still recommend 5.5 series versions, and then we can try to build a master-slave between MySQL-5.0.27 and MySQL-5.5.X;


4、This migration is also time-consuming, mainly involved in too many table space files, in fact, the logical data is not so much, I think the old environment of the previous “innodb_data_file_path” parameter setting is not too reasonable, can consider the ibdata to lean and match.Readjustment;


5、During the migration, it was found in the previous 64 environment that the 32 – bit version of MySQL was running, which would lead to a problem, even if you had more memory in the 64 – bit environment, and the maximum size of your buffer pool could only be set to 4G.

Label: database, migration
Link of this Article: Mysql database migration

Leave a Reply

Your email address will not be published. Required fields are marked *