本机和云端服务器安装xtrabackup

#为了防止每次yum操作都会自动更新，卸载这个软件
yum -y remove mysql57-community-release-el7-10.noarch#下载XtraBackup
yum install -y https://repo.percona.com/yum/percona-release-latest.noarch.rpm#激活该yum仓库
percona-release setup ps80#直接通过yum命令本地安装XtraBackup
yum install -y percona-xtrabackup-24 #输出版本信息验证是否可用
xtrabackup --version

本机进行数据备份 

innobackupex --user=root --password=123456 --host=20.0.0.81 --incremental /opt/backup
#备份在/opt/backup目录下

xftp迁移数据

云端安装Mysql

#yum安装mysql 5.7
yum remove mariadb* -ywget -i -c http://dev.mysql.com/get/mysql57-community-release-el7-10.noarch.rpm
yum -y install mysql57-community-release-el7-10.noarch.rpm
cd /etc/yum.repos.d/
sed -i 's/gpgcheck=1/gpgcheck=0/' mysql-community.repo
yum -y install mysql-community-serversystemctl start mysqld.service
systemctl enable mysqld.service#在日志文件中找出root用户的初始密码
grep "password" /var/log/mysqld.log	#使用初始密码进入数据库之后修改密码
alter user 'root'@'localhost' identified by '新密码';#给与权限
grant all privileges on *.* to root@"%" identified by "密码" with grant option;
flush privileges;

云端进行数据恢复

systemctl stop firewalld#将/var/lib/mysql/下的数据备份到opt下的var_lib_mysql
cd /opt/
mkdir var_lib_mysql
cd /var/lib/mysql/
cp -a * /opt/var_lib_mysql#在/usr/local下创建mysql目录,在mysql目录下创建data目录
cd /usr/local/
mkdir mysql
cd mysql/
mkdir data
mkdir data.bak#将/var/lib/mysql/中的数据全部转移到data目录中并备份
cd /var/lib/mysql/
mv * /usr/local/mysql/data
cp -r /usr/local/mysql/data /usr/local/mysql/data.bak/#修改配置文件
vim /etc/my.cnf
--修改成和本机一致--

#将/usr/local/mysql以及此目录下的文件的所有组修改成mysql
chown -R root.root /usr/local/mysql/data.bak

#重启服务
systemctl restart mysqld

#关闭服务
systemctl stop mysqld#合并数据
innobackupex --apply-log /opt/backup/2023-11-08_14-29-25/#删除data目录
rm -rf data#恢复数据
innobackupex --copy-back /opt/backup/2023-11-08_14-29-25/#修改/usr/local/mysql/data目录所属
chown -R mysql.mysql data

#重启服务
systemctl restart mysqld#进入数据库
mysql -u root -p#此时云端的数据库已经和本机数据库一致