云RDS MySQL迁移至本地MySQL

本地准备工作
1.安装:percona-xtrabackup
上传percona-xtrabackup-2.3.9-Linux-x86_64.tar.gz包到/usr/local
tar -zxvf percona-xtrabackup-2.3.9-Linux-x86_64.tar.gz
mv percona-xtrabackup-2.3.9-Linux-x86_64 percona-xtrabackup

2.创建数据目录
cd /data/
mkdir rds-mysql
chown mysql:mysql rds-mysql
chmod 775 rds-mysql

3.将RDS MySQL备份解压至创建的目录
cd /soft
tar -izxvf hins820_data_20210625103209.tar.gz -C /data/rds-mysql/

解压完8.7G

cd /data/
chown -R mysql:mysql rds-mysql
chmod -R 775 rds-mysql

4.进行恢复

cd /usr/local/percona-xtrabackup/bin

./innobackupex --defaults-file=/data/rds-mysql/backup-my.cnf --apply-log /data/rds-mysql

提示完成后，进行下一步
恢复完11G

[root@mysql rds-mysql]# cd /usr/local/percona-xtrabackup/bin
[root@mysql bin]# ./innobackupex --defaults-file=/data/rds-mysql/backup-my.cnf --apply-log /data/rds-mysql
210629 17:33:06 innobackupex: Starting the apply-log operation

IMPORTANT: Please check that the apply-log run completes successfully.
           At the end of a successful apply-log run innobackupex
           prints "completed OK!".

./innobackupex version 2.3.9 based on MySQL server 5.6.24 Linux (x86_64) (revision id: fde0e3e)
xtrabackup: cd to /data/rds-mysql/
xtrabackup: This target seems to be not prepared yet.
xtrabackup: xtrabackup_logfile detected: size=2097152, start_lsn=(293078720261)
xtrabackup: using the following InnoDB configuration for recovery:
xtrabackup:   innodb_data_home_dir = ./
xtrabackup:   innodb_data_file_path = ibdata1:200M:autoextend
xtrabackup:   innodb_log_group_home_dir = ./
xtrabackup:   innodb_log_files_in_group = 1
xtrabackup:   innodb_log_file_size = 2097152
xtrabackup: using the following InnoDB configuration for recovery:
xtrabackup:   innodb_data_home_dir = ./
xtrabackup:   innodb_data_file_path = ibdata1:200M:autoextend
xtrabackup:   innodb_log_group_home_dir = ./
xtrabackup:   innodb_log_files_in_group = 1
xtrabackup:   innodb_log_file_size = 2097152
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: Highest supported file format is Barracuda.
InnoDB: Log scan progressed past the checkpoint lsn 293078720261
InnoDB: Database was not shutdown normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages 
InnoDB: from the doublewrite buffer...
InnoDB: Doing recovery: scanned up to log sequence number 293079851297 (60%)
InnoDB: Starting an apply batch of log records to the database...
InnoDB: Progress in percent: 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 
InnoDB: Apply batch completed
InnoDB: 128 rollback segment(s) are active.
InnoDB: Waiting for purge to start
InnoDB: 5.6.24 started; log sequence number 293079851297
xtrabackup: Last MySQL binlog file position 24206254, file name mysql-bin.004613

xtrabackup: starting shutdown with innodb_fast_shutdown = 1
InnoDB: FTS optimize thread exiting.
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 293079851893
xtrabackup: using the following InnoDB configuration for recovery:
xtrabackup:   innodb_data_home_dir = ./
xtrabackup:   innodb_data_file_path = ibdata1:200M:autoextend
xtrabackup:   innodb_log_group_home_dir = ./
xtrabackup:   innodb_log_files_in_group = 2
xtrabackup:   innodb_log_file_size = 1048576000
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=293079851893
InnoDB: Highest supported file format is Barracuda.
InnoDB: 128 rollback segment(s) are active.
InnoDB: Waiting for purge to start
InnoDB: 5.6.24 started; log sequence number 293079852044
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
InnoDB: FTS optimize thread exiting.
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 293079855264
210629 17:33:15 completed OK!
[root@mysql bin]# cd /data
[root@mysql data]# ll
total 4
drwxrwxr-x. 5 mysql mysql  167 Jun 29 14:59 mysql
drwxrwxr-x. 8 mysql mysql 4096 Jun 29 17:33 rds-mysql
[root@mysql data]# cd rds-mysql/
[root@mysql rds-mysql]# ll
total 3956016
-rwxrwxr-x. 1 mysql mysql        450 Jun 25 10:38 backup-my.cnf
-rwxrwxrwx. 1 mysql mysql 1887436800 Jun 29 17:33 ibdata1
-rw-r--r--. 1 root  root  1048576000 Jun 29 17:33 ib_logfile0
-rw-r--r--. 1 root  root  1048576000 Jun 29 17:33 ib_logfile1
-rwxrwxr-x. 1 mysql mysql   62874130 Jun 25 10:38 log000000000005.tokulog29
drwxrwxr-x. 2 mysql mysql       4096 Jun 29 17:31 mysql
drwxrwxr-x. 2 mysql mysql       4096 Jun 29 17:31 performance_schema
drwxrwxr-x. 2 mysql mysql         20 Jun 29 17:31 test
-rwxrwxr-x. 1 mysql mysql      16384 Feb 11  2018 tokudb.directory
-rwxrwxr-x. 1 mysql mysql      32768 May 28  2018 tokudb.environment
-rwxrwxr-x. 1 mysql mysql      16384 May 28  2018 tokudb.rollback
-rwxrwxr-x. 1 mysql mysql        123 Jun 25 10:38 xtrabackup_binlog_info
-rw-r--r--. 1 root  root          26 Jun 29 17:33 xtrabackup_binlog_pos_innodb
-rwxrwxr-x. 1 mysql mysql        123 Jun 29 17:33 xtrabackup_checkpoints
-rwxrwxr-x. 1 mysql mysql        826 Jun 25 10:38 xtrabackup_info
-rwxrwxr-x. 1 mysql mysql    2097152 Jun 29 17:33 xtrabackup_logfile
-rwxrwxr-x. 1 mysql mysql         80 Jun 25 10:38 xtrabackup_slave_filename_info
-rwxrwxr-x. 1 mysql mysql        164 Jun 25 10:38 xtrabackup_slave_info
drwxrwxr-x. 2 mysql mysql      36864 Jun 29 17:33 yhd_kf
drwxrwxr-x. 2 mysql mysql       4096 Jun 29 17:31 yhd_qzk
drwxrwxr-x. 2 mysql mysql      90112 Jun 29 17:31 yhd_test
[root@mysql rds-mysql]# 

5.恢复完成后修改权限

cd /data
chown -R mysql:mysql rds-mysql
chmod -R 775 rds-mysql

chmod 777 /data/rds-mysql/ib*

6.更新参数文件及参数
cd /data/rds-mysql

cp backup-my.cnf /etc/
cd /etc/
mv my.cnf my.cnf.bak
mv backup-my.cnf my.cnf

vi my.cnf
注释掉其他，[mysqld]下加入下面两个参数：
[mysqld]
lower_case_table_names=1
skip-grant-tables

vi /etc/init.d/mysqld
修改data_dir路径

7.启动mysqld服务
[root@mysql etc]# service mysqld restart
 ERROR! MySQL server PID file could not be found!
Starting MySQL.Logging to '/data/rds-mysql/mysql.err'.
. SUCCESS! 
[root@mysql etc]# service mysqld status
 SUCCESS! MySQL running (23168)
[root@mysql etc]# 

8.迁移完修改root密码
免密码登陆：

mysql -uroot -p

use mysql;
update mysql.user set password=PASSWORD('root') where User='root';
flush privileges;
exit

9.注释掉skip-grant-tables并重启数据库服务

[root@mysql rds-mysql]# vi /etc/my.cnf
# This MySQL options file was generated by innobackupex.

# The MySQL server
[mysqld]

lower_case_table_names=1
#skip-grant-tables
~

service mysqld restart

10.连接测试
mysql -h 127.0.0.1 -uroot -p

GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'root' WITH GRANT OPTION;