从0到1实现RDS物理备份恢复到自建数据库虚拟机CentOS7

背景

由于线上数据库用的是阿里云RDS，MySQL5.7高可用版本，并做了数据库备份（物理备份），就想着把备份文件下载下来在自己的本地电脑上做数据恢复；因为这样可以大胆的使用各种SQL语句，不用担心会影响到线上的性能。并且我前段时间有些了如何在Windows 恢复数据，但本次要做的是在CentOS下恢复数据文件；因我Windows恢复的数据库存在一定的问题，如：

1、不能修改数据；

2、不能修改表结构；

3、数据量多的表复杂的查询会mysql服务奔溃；

4、有一定就局限性，会影响正常的数据库操作（新建数据库，新建表等等）；

5、切换麻烦等等等；

但Windows能实现简单的查询是没什么问题的，就看自己的需求了；

所以就有了在恢复到ContOS的想法，废话不多说，开干。

步骤

首先在电脑安装虚拟机VMware ，在虚拟机安装CentOS7，具体教程可以参照下面这个地址：

https://zhuanlan.zhihu.com/p/145102034

第二，安装mysql5.7，具体可以参照下面这个地址：

https://www.cnblogs.com/wpnr/p/14072634.html

值得注意的是

如果有出现【chown: 无法访问"/usr/local/mysql": 没有那个文件或目录】的问题

可以将刚刚修改的mysql文件夹拷贝到相应的路径即可（即：/usr/local/mysql）

如果遇到-bash: mysqld: 未找到命令解决方案 问题，

可以参照下面这个地址：

https://www.cnblogs.com/wangzhihang/p/11829290.html

vi /etc/profile 

source /etc/profile

mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data

增加 export PATH=$PATH:/usr/local/mysql/bin

然后执行他的命令即可；

按照他的步骤执行下去，没有多大的问题；需要注意的是远程连接的问题（即“本机电脑远程访问ContOS的MySQL数据库”）

-------------------------------补充点begin--------------------------------------------------------------------------------------------------------------------------------

然后还有个防火墙，要么就关闭防火墙，要么就给防火墙添加个端口；

防火墙设置方式一：

开启对外端口命令：

vi /etc/sysconfig/iptables

-A INPUT -m state –state NEW -m tcp -p tcp –dport 3306 -j ACCEPT（允许3306端口通过防火墙，粘贴完安esc键，然后:wq 保存）

/etc/init.d/iptables restart（重启防火墙使配置生效）

关闭防火墙命令：

//临时关闭

service iptables stop

//禁止开机启动

chkconfig iptables off

防火墙方式二：

如果上面开启端口无效或遇到错误提示，可以尝试这种方式

//开启防火墙3306端口

 firewall-cmd --zone=public --add-port=3306/tcp --permanent    （--permanent永久生效，没有此参数重启后失效）

 重新载入  

firewall-cmd --reload

查看下是否已经开启

firewall-cmd --query-port=3306/tcp

 开启防火墙

systemctl start firewalld

关闭防火墙

systemctl stop firewalld

---------------------------------补充点end--------------------------------------------------------------------------------------------

搞完重启虚拟机CentOS，即可完成远程连接；

备注：在下载MySQL的时候会很慢，晚上凌晨1点到3点的下载速度会快点；

第三，肯定要按照阿里云给的教程来，地址如下：

https://help.aliyun.com/document_detail/41817.htm?spm=a2c4g.11186623.2.7.57944bd6fe47zw#concept-41817-zh

注意，找到适合你系统（centos7）版本的Percona XtraBackup 2.4，centOS的链接是下面如下：

https://www.percona.com/doc/percona-xtrabackup/2.4/installation/yum_repo.html

在文档14点的时候，需要注释的这些，文件上有些有，有些没有，没有的注释我也添加进去了，为了避免出问题；

---------------------------------------------补充点begin----------------------------------------------------------------------------------------------------------------

在此建议增加以下参数，以防后面执行SQL语句时出现语法错误提示

sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

相关错误提示如下：

[SQL]
UPDATE front_backlist SET phone = REPLACE(phone,SUBSTR(phone,4,4),LPAD(FLOOR( RAND() * 10000),4,0)) where phone is not null ;
受影响的行: 109
时间: 0.005s

[Err] 1055 - Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column 'information_schema.PROFILING.SEQ' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

解决办法有两个，一个是上面的永久性解决，一个是临时解决，下面是临时解决的方式如下（缺点是每天都要这么去执行，不然都会报异常）：

SELECT @@sql_mode;
-- 修改SQL_MODE
SET sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));

------------------------------------------补充点 end---------------------------------------------------------------------------------------------------------------------------------------

然后在执行16点的命令时候一定要注意了，一定要杀死mysqld的进程，命令如下：

killall mysqld

kill -9 mysqld

最后成功的界面如下：

输入RDS的数据库的账号密码

mysql -u RDS数据库账号 -p RDS数据库密码

执行后即可进入数据库了

show databases;

即可查看已经还原的数据库了

补充：

在执行16的过程中可能会出现异常，如下：

2021-05-25T12:05:18.804382Z 0 [ERROR] Failed to open the relay log '/home/mysql/data3119/mysql/slave-relay.000254' (relay_log_pos 121986973).
2021-05-25T12:05:18.804401Z 0 [ERROR] Could not find target log file mentioned in relay log info in the index file './localhost-relay-bin.index' during relay log initialization.
2021-05-25T12:05:18.804890Z 0 [ERROR] Slave: Failed to initialize the master info structure for channel ''; its record may still be present in 'mysql.slave_master_info' table, consider deleting it.
2021-05-25T12:05:18.804905Z 0 [ERROR] Failed to create or recover replication info repositories.

原因如下：

relaylog文件的路径进行了指定，而默认的文件也存在，或者说默认生成的relay.index 没有删除

解决办法：

killall mysqld
cd /home/mysql/data
rm -rf localhost-relay-bin.*
rm master.info
mysqld --defaults-file=/home/mysql/data/backup-my.cnf --user=mysql --datadir=/home/mysql/data &

这样就正常了

如果想修改登录的用户名，可能会出现错误

mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> update user set user="root" where user="testman";
ERROR 1064 (42000): Unknown trigger has an error in its body: 'Unknown system variable 'maintain_user_list''

解决办法如下

[root@localhost ~]# find /home/mysql/data/ -iname *.trg
/home/mysql/data/mysql/proxies_priv.TRG
/home/mysql/data/mysql/user.TRG

[root@localhost ~]# mv /home/mysql/data/mysql/user.TRG /home/mysql/data/mysql/user.TRG.back
[root@localhost ~]# mv /home/mysql/data/mysql/proxies_priv.TRG /home/mysql/data/mysql/proxies_priv.TRG.back

退出mysql,重新修改用户名

mysql -utestman -p123456
use mysql;
update user set user="root" where user="testman";
flush privileges;
exit;
mysql -uroot -p123456