一段三次分拆的蚂蚁搬家式MySQL迁移经历

[root@web57 ~]# more   /usr/local/nginx/conf/nginx.conf

user  www   www;

worker_processes    6;

worker_rlimit_nofile 51200;

events {

 use epoll;

 #use   kqueue;   #FreeBSD system

 worker_connections 51200;

}

http {

 include         mime.types;

 default_type    application/octet-stream;

 #charset    gb2312;

 server_names_hash_bucket_size 256;

 client_header_buffer_size 256k;

 large_client_header_buffers 4 256k;

 client_max_body_size 500m;

…………………………………………省略若干…………………………………

include vhosts/faxian.quanzhen.com.conf;

include vhosts/www.quanzhen.com.conf;

include vhosts/news.quanzhen.com.conf;

include vhosts/s.quanzhen.com.conf;

include vhosts/down.quanzhen.com.conf;

include vhosts/static.quanzhen.com.conf;

include vhosts/image.quanzhen.com.conf;

include vhosts/3g.quanzhen.com.conf;

include vhosts/mini.quanzhen.com.conf;

include vhosts/xml.quanzhen.com.conf;

include vhosts/mayiapi.quanzhen.com.conf;

include vhosts/www.android77.com.conf;

include vhosts/fahongbao.android77.com.conf;

include vhosts/update.android77.com.conf;

include vhosts/dev.quanzhen.com.conf;

include vhosts/qr.110.cc.conf;

include vhosts/110.cc.conf;

include vhosts/eggserver.quanzhen.com.conf;

include vhosts/apkegg.quanzhen.com.conf;

include vhosts/eggserver.yidong7.cn.conf;

include vhosts/www.yidong7.cn.conf;

include vhosts/down.yidong7.cn.conf;

include vhosts/wan.quanzhen.com.conf;

include vhosts/open.quanzhen.com.conf;

include vhosts/bakdown.yidong7.cn.conf ;

include vhosts/hanhua.quanzhen.com.conf;

include vhosts/mpk.quanzhen.com.conf;

include vhosts/android.quanzhen.com.conf;

include vhosts/pay.quanzhen.com.conf;

include vhosts/cmstop.quanzhen.cn.conf;

include vhosts/news.quanzhen.cn.conf;

include vhosts/pingce.quanzhen.cn.conf;

include vhosts/gonglue.quanzhen.cn.conf;

include vhosts/hao.quanzhen.cn.conf;

include vhosts/all.quanzhen.cn.conf;

include vhosts/s.quanzhen.cn.conf;

include vhosts/apkz.quanzhen.com.conf;

include vhosts/ajax.quanzhen.com.conf;

include vhosts/union.quanzhen.com.conf;

include vhosts/mai.quanzhen.com.conf;

include vhosts/blog.quanzhen.com.conf;

include vhosts/guazi.quanzhen.com.conf;

include vhosts/lockscreen.yidong7.cn.conf;

include vhosts/dsp.pujia8.com.conf;

include vhosts/3svx4haii9.quanzhen.com.conf;

include vhosts/u.quanzhen.com.conf;

include vhosts/bianji.quanzhen.com.conf;

include vhosts/default.conf;

}

[root@db-209   ~]# innobackupex    --user=root  --passwor='i%=KGb76'   \

--defaults-file=/etc/my.cnf  \

--databases=“quanzhen_mobile7lockscreen   quanzhen_equipment” /data/bakmysql/

InnoDB Backup Utility v1.5.1-xtrabackup; Copyright   2003, 2009 Innobase Oy

and Percona Ireland Ltd 2009-2012.  All Rights Reserved.

This software is published under

the GNU GENERAL PUBLIC LICENSE Version 2, June   1991.

180618 00:30:31    innobackupex: Starting mysql with options:  --defaults-file='/etc/my.cnf'   --password=xxxxxxxx --user='root' --unbuffered --

180618 00:30:31    innobackupex: Connected to database with mysql child process   (pid=20090)

180618 00:30:37    innobackupex: Connection to database server closed

IMPORTANT: Please check that the backup run   completes successfully.

             At the end of a successful backup run innobackupex

             prints "completed OK!".

innobackupex: Using mysql  Ver 14.12 Distrib 5.0.95, for   redhat-linux-gnu (x86_64) using readline 5.1

innobackupex: Using mysql server version Copyright   (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

innobackupex: Created backup directory   /data/bakmysql/2018-06-18_00-30-37

180618 00:30:37    innobackupex: Starting mysql with options:  --defaults-file='/etc/my.cnf'   --password=xxxxxxxx --user='root' --unbuffered --

180618 00:30:37    innobackupex: Connected to database with mysql child process   (pid=20123)

180618 00:30:39    innobackupex: Connection to database server closed

180618 00:30:39    innobackupex: Starting ibbackup with command: xtrabackup_55  --defaults-file="/etc/my.cnf"  --defaults-group="mysqld"   --backup --suspend-at-end --target-dir=/data/bakmysql/2018-06-18_00-30-37   --tmpdir=/tmp

innobackupex: Waiting for ibbackup (pid=20132) to   suspend

innobackupex: Suspend file   '/data/bakmysql/2018-06-18_00-30-37/xtrabackup_suspended'

xtrabackup_55 version 2.0.7 for Percona Server   5.5.16 Linux (x86_64) (revision id: 552)

xtrabackup: uses posix_fadvise().

xtrabackup: cd to /data/mysql_db

xtrabackup: Target instance is assumed as   followings.

xtrabackup:     innodb_data_home_dir = ./

xtrabackup:     innodb_data_file_path = ibdata1:10M:autoextend

xtrabackup:     innodb_log_group_home_dir = ./

xtrabackup:     innodb_log_files_in_group = 2

xtrabackup:     innodb_log_file_size = 5242880

>> log scanned up to (601191481892)

[01] Copying ./ibdata1 to /data/bakmysql/2018-06-18_00-30-37/ibdata1

>> log scanned up to (601191481892)

>> log scanned up to (601191481892)

>> log scanned up to (601191481892)

>> log scanned up to (601191481892)

>> log scanned up to (601191481892)

>> log scanned up to (601191481892)

>> log scanned up to (601191481892)

…………………………………省略……………………………………………

[root@db-209 ~]#innobackupex  --user=root    --passwor='i%=KGb76'  \

--defaults-file=/etc/my.cnf --databases=“quanzhen_equipment”   /data/bakmysql/

[root@msyql mysql_db]# innobackupex    --defaults-file=/etc/my.cnf  \

--copy-back /data/db_bk/2018-06-18_00-30-37

[root@msyql mysql_db]#cd /usr/local/mysql/

[root@msyql ~]#scripts/mysql_install_db  --user=mysql --datadir=/data/mysql_db

[root@msyql ~]#mysql

mysql>grant all on quanzhen_equipment.*   to ……

[root@msyql db_bk]# pwd

/data/db_bk

[root@msyql db_bk]#innobackupex  --defaults-file=/etc/my.cnf --copy-back \

--force-non-empty-directories 2018-06-22_00-24-52

180623 23:31:57 innobackupex: Starting the copy-back   operation

IMPORTANT: Please check that the copy-back run   completes successfully.

             At the end of a successful copy-back run innobackupex

             prints "completed OK!".

innobackupex version 2.4.11 based on MySQL server   5.7.19 Linux (x86_64) (revision id: b4e0db5)

innobackupex:   Can't create/write to file '/data/mysql_db/ib_logfile0' (Errcode: 17 - File   exists)

[01] error: cannot open the destination stream for   ib_logfile0

[01] Error: copy_file() failed.