Ecs重装系统后恢复mysql_阿里云rds mysql数据库数据恢复到ecs中

背景：

aliyun上的rds数据库快满了，于是删除了某个备份的表

后面大boss说是有用的表，需要恢复回来，阿里云有7天内的物理全量备份(通过percona-xtrabackup备份的)

第一时间应该延长备份时间(默认保留7天，可以配置15天或更长避免在恢复过程中被阿里云的rds删除)

准备：

数据库大概有700G左右，物理备份文件100G左右，恢复回来需要占用大概1多T的空间，于是在现有同样区域的ecs上挂载一块2T的磁盘用来做恢复

整体的过程是：

在相同地域的ecs安装好mysql，恢复到上面，然后拷贝到生产环境中

其实有更简单的方法，直接可以通过恢复克隆实例恢复数据(需要新购买实例，数据恢复到这个新的实例中)

主要的时间用在了如下几块，建议在screen下进行，否则中途shell连接断开就会浪费不少时间：

1.备份文件的下载基本10M/s，100G用了几个小时

2.通过阿里的sh脚本解压(100G左右用了差不多至少6个小时)

3.通过perconna-xtrabackup恢复估计在6个小时左右

具体步骤：

1.找到阿里云提供的备份内网下载地址:

# -c是支持断点续传

wget -c 'http://rdsbak-hzi-v2.oss-cn-hangzhou-i-internal.aliyuncs.com/custins758461/hins3305619_data_20171109075610.tar.gz?OSSAccessKeyId=xxx' -O hins3305619_data_20171109075610.tar.gz

2.在目标ecs上安装mysql5.6

①添加用户

groupadd mysql

useradd -r -g mysql mysql

②编译安装mysql5.6.16(保持和阿里云的rds一致，避免出错)

wget http://dev.mysql.com/get/Downloads/MySQL-5.6/mysql-5.6.16.tar.gz

tar -zxvf mysql-5.6.16.tar.gz

cd mysql-5.6.16

mkdir -p /app/data/mydata

cmake . -DCMAKE_INSTALL_PREFIX=/usr/local/mysql -DMYSQL_DATADIR=/app/data/mydata -DSYSCONFDIR=/etc -DWITH_INNOBASE_STORAGE_ENGINE=1 -DWITH_MYISAM_STORAGE_ENGINE=1 -DWITH_ARCHIVE_STORAGE_ENGINE=1 -DWITH_PARTITION_STORAGE_ENGINE=1 -DWITH_BLACKHOLE_STORAGE_ENGINE=1 -DWITH_READLINE=1 -DWITH_SSL=system -DWITH_ZLIB=system -DMYSQL_UNIX_ADDR=/tmp/mysql.sock -DMYSQL_TCP_PORT=3306 -DENABLED_LOCAL_INFILE=1 -DEXTRA_CHARSETS=all -DDEFAULT_CHARSET=utf8 -DWITH_LIBWRAP=0 -DDEFAULT_COLLATION=utf8_general_ci

make && make install

chown -R mysql.mysql /usr/local/mysql

chown -R mysql.mysql /app/data/mydata

cd /usr/local/mysql

scripts/mysql_install_db --user=mysql --basedir=/usr/local/mysql --datadir=/app/data/mydata

cp support-files/mysql.server /etc/rc.d/init.d/mysqld

cp support-files/my-default.cnf /etc/my.cnf

mkdir /data/binlogs

chown -R mysql.mysql /app/data/

chmod +x /etc/init.d/mysqld

# 开机启动

chkconfig --add mysqld

chkconfig mysqld on

# vim /etc/my.cnf

[client]

port = 3306

socket = /tmp/mysql.sock

default-character-set = utf8

[mysqld]

port = 3306

innodb_file_per_table = 1

init-connect = 'SET NAMES utf8mb4'

character-set-server = utf8mb4

default_storage_engine = InnoDB

skip-name-resolve

skip-external-locking

datadir = /app/data/mydata

log-bin=/app/data/binlogs/master-bin

binlog_format=row

socket=/tmp/mysql.sock

interactive_timeout = 28800

wait_timeout = 28800

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

[mysqldump]

quick

max_allowed_packet = 16M

[myisamchk]

key_buffer_size = 8M

sort_buffer_size = 8M

read_buffer = 4M

write_buffer = 4M

3.将下载的文件解压(100G的时间用了差不多6个小时)

# bash rds_backup_extract.sh -f hins3305619_data_20171109075610.tar.gz -C /app/data/scriptdb

通过上图，可以很明显的看出，RDS是通过percona-Xtrabackup进行全量备份的

4.将解压的文件恢复到本地

# 经过测试percona-xtrabackup-24-2.4.4-1.el6.x86_64这个比较新的版本不适用mysql5.6的恢复

# yum localinstall -y percona-xtrabackup-2.2.9-5067.el6.x86_64_rds_5.6_ok.rpm

[root@iZ23bqedx3fZ scriptdb]# /etc/init.d/mysqld restart

Shutting down MySQL. [ OK ]

Starting MySQL... [ OK ]

[root@iZ23bqedx3fZ scriptdb]# ps -ef|grep mysql

root 17459 1 0 08:51 pts/13 00:00:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/app/data/mydata --pid-file=/app/data/mydata/iZ23bqedx3fZ.pid

mysql 17760 17459 33 08:51 pts/13 00:00:01 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/app/data/mydata --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=/app/data/mydata/iZ23bqedx3fZ.err --pid-file=/app/data/mydata/iZ23bqedx3fZ.pid --socket=/tmp/mysql.sock --port=3306

root 17801 19726 0 08:51 pts/13 00:00:00 grep mysql

chown -R mysql:mysql /app/data/scriptdb

# innobackupex --defaults-file=/app/data/scriptdb/backup-my.cnf --apply-log /app/data/scriptdb

xtrabackup: innodb_log_file_size = 1048576000

xtrabackup: Starting InnoDB instance for recovery.

xtrabackup: Using 104857600 bytes for buffer pool (set by --use-memory parameter)

InnoDB: Using atomics to ref count buffer pool pages

InnoDB: The InnoDB memory heap is disabled

InnoDB: Mutexes and rw_locks use GCC atomic builtins

InnoDB: Memory barrier is not used

InnoDB: Compressed tables use zlib 1.2.3

InnoDB: Using CPU crc32 instructions

InnoDB: Initializing buffer pool, size = 100.0M

InnoDB: Completed initialization of buffer pool

InnoDB: Setting log file ./ib_logfile101 size to 1000 MB

InnoDB: Progress in MB: 100 200 300 400 500 600 700 800 900 1000

InnoDB: Setting log file ./ib_logfile1 size to 1000 MB

InnoDB: Progress in MB: 100 200 300 400 500 600 700 800 900 1000

InnoDB: Renaming log file ./ib_logfile101 to ./ib_logfile0

InnoDB: New log files created, LSN=1574126930421

InnoDB: Highest supported file format is Barracuda.

InnoDB: 128 rollback segment(s) are active.

InnoDB: Waiting for purge to start

InnoDB: 5.6.22 started; log sequence number 1574126930444

[notice (again)]

If you use binary log and don't use any hack of group commit,

the binary log position seems to be:

InnoDB: Last MySQL binlog file position 0 183911920, file name mysql-bin.000324

# 若系统返回如下类似结果，则说明备份文件已成功恢复到本地数据库

xtrabackup: starting shutdown with innodb_fast_shutdown = 1

InnoDB: FTS optimize thread exiting.

InnoDB: Starting shutdown...

InnoDB: Shutdown completed; log sequence number 1574126933060

171117 12:55:58 innobackupex: completed OK!

注意：

一定要严格按照阿里云的帮助文档进行，期间参考了一篇非官方的博文，没有用percona-xtrabackup恢复直接使用mysql启动，发现无法启动再次使用percona-xtrabackup的时候发现报错，是因为之前启动的时候产生了一些没用的日志，检测不通过删除即可

如下：

InnoDB: Log file ./ib_logfile1 is of different size 50331648 bytes than other log files 3447439360 b

移除掉 ib_logfile1文件即可

为避免版本问题，需修改backup-my.cnf参数，具体操作步骤如下。

执行如下命令，以文本方式编辑backup-my.cnf文件。

vim /app/data/scriptdb/backup-my.cnf

执行如下命令，注释掉如下参数。

#innodb_fast_checksum

#innodb_page_size

#innodb_log_block_size

[root@iZ23bqedx3fZ scriptdb]# cat /app/data/scriptdb/backup-my.cnf

# This MySQL options file was generated by innobackupex.

# The MySQL server

[mysqld]

innodb_checksum_algorithm=innodb

#innodb_log_checksum_algorithm=innodb

innodb_data_file_path=ibdata1:200M:autoextend

innodb_log_files_in_group=2

innodb_log_file_size=1048576000

#innodb_fast_checksum=false

#innodb_page_size=16384

#innodb_log_block_size=512

innodb_undo_directory=.

innodb_undo_tablespaces=0

explicit_defaults_for_timestamp=true

#rds_encrypt_data=false

#innodb_encrypt_algorithm=aes_128_ecb

执行如下命令，修改文件属主，并确定文件所属为MySQL用户。

chown -R mysql:mysql /app/data/scriptdb

执行如下命令，启动MySQL进程。

/usr/local/mysql/bin/mysqld_safe --defaults-file=/app/data/scriptdb/backup-my.cnf --user=mysql --datadir=/app/data/scriptdb --socket=/tmp/mysql.sock --port=3306 &

执行如下命令，登录MySQL数据库以验证进程启动成功。

[root@iZ23bqedx3fZ scriptdb]# ps -ef|grep mysql

root 17459 1 0 08:51 pts/13 00:00:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/app/data/mydata --pid-file=/app/data/mydata/iZ23bqedx3fZ.pid

mysql 17760 17459 0 08:51 pts/13 00:00:05 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/app/data/mydata --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=/app/data/mydata/iZ23bqedx3fZ.err --pid-file=/app/data/mydata/iZ23bqedx3fZ.pid --socket=/tmp/mysql.sock --port=3306

root 23806 19726 0 13:34 pts/13 00:00:00 grep mysql

启动mysql显示警告：

171117 13:40:51 mysqld_safe Starting mysqld daemon with databases from /app/data/scriptdb

2017-11-17 13:40:51 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).

解决：

[mysqld]

explicit_defaults_for_timestamp=true

报错类似的：

2017-11-17 13:43:17 26048 [ERROR] /usr/local/mysql/bin/mysqld: unknown variable 'innodb_encrypt_algorithm=aes_128_ecb'

2017-11-17 13:43:17 26048 [ERROR] Aborting

注释掉一些rds的参数即可

[root@iZ23bqedx3fZ scriptdb]# cat backup-my.cnf

# This MySQL options file was generated by innobackupex.

# The MySQL server

[mysqld]

innodb_checksum_algorithm=innodb

#innodb_log_checksum_algorithm=innodb

innodb_data_file_path=ibdata1:200M:autoextend

innodb_log_files_in_group=2

innodb_log_file_size=1048576000

#innodb_fast_checksum=false

#innodb_page_size=16384

#innodb_log_block_size=512

innodb_undo_directory=.

innodb_undo_tablespaces=0

explicit_defaults_for_timestamp=true

#rds_encrypt_data=false

#innodb_encrypt_algorithm=aes_128_ecb

启动成功后的进程：

[root@iZ23bqedx3fZ scriptdb]# ps -ef|grep mysql

root 26205 23288 0 13:44 pts/6 00:00:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --defaults-file=/app/data/scriptdb/backup-my.cnf --user=mysql --datadir=/app/data/scriptdb --socket=/tmp/mysql.sock --port=3306

mysql 26409 26205 8 13:44 pts/6 00:00:01 /usr/local/mysql/bin/mysqld --defaults-file=/app/data/scriptdb/backup-my.cnf --basedir=/usr/local/mysql --datadir=/app/data/scriptdb --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=/app/data/scriptdb/iZ23bqedx3fZ.err --pid-file=/app/data/scriptdb/iZ23bqedx3fZ.pid --socket=/tmp/mysql.sock --port=3306

root 26435 23288 0 13:44 pts/6 00:00:00 grep mysql

至此数据恢复就告一段落，我们就可以像操作普通的数据库一样操作了

参考操作文档：

https://help.aliyun.com/knowledge_detail/41817.html