mysql 全量数据恢复

1、查看mysql版本、mysql状态
[root@k8s-129 mysql]# mysql -V
mysql  Ver 8.0.33 for Linux on x86_64 (MySQL Community Server - GPL)
[root@k8s-129 mysql]# systemctl status mysql
● mysqld.service - LSB: start and stop MySQL
   Loaded: loaded (/etc/rc.d/init.d/mysqld; bad; vendor preset: disabled)
   Active: active (running) since Mon 2024-04-01 17:18:07 CST; 10min ago
     Docs: man:systemd-sysv-generator(8)
  Process: 57880 ExecStop=/etc/rc.d/init.d/mysqld stop (code=exited, status=0/SUCCESS)
  Process: 57915 ExecStart=/etc/rc.d/init.d/mysqld start (code=exited, status=0/SUCCESS)
    Tasks: 41
   Memory: 569.0M
   CGroup: /system.slice/mysqld.service
           ├─57928 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/usr/local/mysql/data --pid-file=/usr/local/mysql/mysqld.pid
           └─58167 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-...

Apr 01 17:18:04 k8s-129 systemd[1]: Starting LSB: start and stop MySQL...
Apr 01 17:18:07 k8s-129 mysqld[57915]: Starting MySQL... SUCCESS!
Apr 01 17:18:07 k8s-129 systemd[1]: Started LSB: start and stop MySQL.

 

1、全量备份到/backup/full

xtrabackup --backup --target-dir=/backup/full --user=back --password=123456 --host=192.168.25.129 --port=3306 

 

备份过后

把刚才id为1的数据修改保存

我后悔了要修改恢复回来 

3、删除data mysql数据库目录   
mv /usr/local/mysql/data /usr/local/mysql/data5 

# 创建新的数据目录
mkdir /usr/local/mysql/data/  # 创建新的数据目录以准备恢复
chown -R mysql:mysql /usr/local/mysql/data  # 设置目录所有者为MySQL用户

 #准备恢复操作

xtrabackup --prepare --apply-log-only --target-dir=/backup/full/
xtrabackup --prepare --target-dir=/backup/full/

# 复制备份数据到新的数据目录
xtrabackup --copy-back --target-dir=/backup/full/

这三步最后都出现即可

[Xtrabackup] completed OK!

# 设置新的数据目录的权限
chown -R mysql:mysql /usr/local/mysql/data/*

重新启动服务器

[root@k8s-129 mysql]# systemctl restart mysql

再看数据 恢复了