MySQL之xtrabackup备份恢复的实现

作者:God_sign 时间:2024-01-15 06:59:08 

mysql版本:8.0.28
xtrabackup版本:8.0.28

1、安装xtrabackup

下载地址:Download Percona XtraBackup 8.0

MySQL之xtrabackup备份恢复的实现

[root@myoracle ~]# tar -zxvf percona-xtrabackup-8.0.28-20-Linux-x86_64.glibc2.17.tar.gz
[root@myoracle ~]# mv percona-xtrabackup-8.0.28-20-Linux-x86_64.glibc2.17 /usr/local/xtrabackup

2、备份

xtrabackup备份时报错:
[Xtrabackup] failed to execute query ‘LOCK INSTANCE FOR BACKUP' : 1227 (42000) Access denied; you need (at least one of) the BACKUP_ADMIN privilege(s) for this operation
解决方案:
进入mysql
赋予权限
grant BACKUP_ADMIN on *.* to ‘root'@‘%';
flush privileges;

2.1、做一次全备

[root@myoracle bin]# ./xtrabackup -u root -p -S /home/mysql/mysql.sock --backup --target-dir=/data/backup/full
2022-11-02T09:53:02.321003+08:00 0 [Note] [MY-011825] [Xtrabackup] recognized server arguments: --datadir=/home/mysql/data --innodb_buffer_pool_size=512M
2022-11-02T09:53:02.321193+08:00 0 [Note] [MY-011825] [Xtrabackup] recognized client arguments: --port=3306 --socket=/home/mysql/mysql.sock --user=root --password --socket=/home/mysql/mysql.sock --backup=1 --target-dir=/data/backup/full
Enter password:
...
...
2022-11-02T09:53:09.742194+08:00 0 [Note] [MY-011825] [Xtrabackup] Transaction log of lsn (18182291) to (18182301) was copied.
2022-11-02T09:53:09.962188+08:00 0 [Note] [MY-011825] [Xtrabackup] completed OK!
[root@myoracle bin]# ll /data/backup/
total 4
drwxr-x---. 5 root root 4096 Nov  2 09:53 full

2.2、进入mysql添加数据

[root@myoracle bin]# cd /home/mysql/mysql8/bin/
[root@myoracle bin]# ./mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 13
Server version: 8.0.28 MySQL Community Server - GPL

Copyright (c) 2000, 2022, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

mysql> create database dey;
Query OK, 1 row affected (0.07 sec)

mysql> use dey;
Database changed
mysql> create table tb(id int);
Query OK, 0 rows affected (0.07 sec)

mysql> insert into tb values(1),(2);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> exit
Bye

2.3、做一次增备

[root@myoracle bin]# cd /usr/local/xtrabackup/bin/
[root@myoracle bin]# ./xtrabackup -u root -p -S /home/mysql/mysql.sock --backup --target-dir=/data/backup/inc1 --incremental-basedir=/data/backup/full/
2022-11-02T09:58:37.492129+08:00 0 [Note] [MY-011825] [Xtrabackup] recognized server arguments: --datadir=/home/mysql/data --innodb_buffer_pool_size=512M
2022-11-02T09:58:37.492345+08:00 0 [Note] [MY-011825] [Xtrabackup] recognized client arguments: --port=3306 --socket=/home/mysql/mysql.sock --user=root --password --socket=/home/mysql/mysql.sock --backup=1 --target-dir=/data/backup/inc1 --incremental-basedir=/data/backup/full/
Enter password:
...
...
2022-11-02T09:58:44.745723+08:00 0 [Note] [MY-011825] [Xtrabackup] Transaction log of lsn (18201617) to (18201627) was copied.
2022-11-02T09:58:44.954584+08:00 0 [Note] [MY-011825] [Xtrabackup] completed OK!
[root@myoracle bin]# ll /data/backup/
total 8
drwxr-x---. 5 root root 4096 Nov  2 09:53 full
drwxr-x---. 6 root root 4096 Nov  2 09:58 inc1

2.4、删除数据库(千万不要在生产库执行!!!)

[root@myoracle bin]# cd -
/home/mysql/mysql8/bin
[root@myoracle bin]# ./mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 16
Server version: 8.0.28 MySQL Community Server - GPL

Copyright (c) 2000, 2022, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| dey                |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.01 sec)

mysql> drop database dey;
Query OK, 1 row affected (0.12 sec)

mysql> exit
Bye

3、恢复

3.1、准备阶段

[root@myoracle bin]# ./xtrabackup --prepare --apply-log-only --target-dir=/data/backup/full/
2022-11-02T10:03:16.369911+08:00 0 [Note] [MY-011825] [Xtrabackup] recognized server arguments: --innodb_checksum_algorithm=crc32 --innodb_log_checksums=1 --innodb_data_file_path=ibdata1:12M:autoextend --innodb_log_files_in_group=2 --innodb_log_file_size=50331648 --innodb_page_size=16384 --innodb_undo_directory=./ --innodb_undo_tablespaces=2 --server-id=0 --innodb_log_checksums=ON --innodb_redo_log_encrypt=0 --innodb_undo_log_encrypt=0
2022-11-02T10:03:16.370082+08:00 0 [Note] [MY-011825] [Xtrabackup] recognized client arguments: --prepare=1 --apply-log-only=1 --target-dir=/data/backup/full/
./xtrabackup version 8.0.28-20 based on MySQL server 8.0.28 Linux (x86_64) (revision id: 4cc3081873d)
...
...
2022-11-02T10:03:18.052072+08:00 0 [Note] [MY-013251] [InnoDB] Number of pools: 1
2022-11-02T10:03:18.054302+08:00 0 [Note] [MY-011825] [Xtrabackup] completed OK!

[root@myoracle bin]# ./xtrabackup --prepare --apply-log-only --target-dir=/data/backup/full/ --incremental-dir=/data/backup/inc1/
2022-11-02T10:04:02.786618+08:00 0 [Note] [MY-011825] [Xtrabackup] recognized server arguments: --innodb_checksum_algorithm=crc32 --innodb_log_checksums=1 --innodb_data_file_path=ibdata1:12M:autoextend --innodb_log_files_in_group=2 --innodb_log_file_size=50331648 --innodb_page_size=16384 --innodb_undo_directory=./ --innodb_undo_tablespaces=2 --server-id=0 --innodb_log_checksums=ON --innodb_redo_log_encrypt=0 --innodb_undo_log_encrypt=0
2022-11-02T10:04:02.786786+08:00 0 [Note] [MY-011825] [Xtrabackup] recognized client arguments: --prepare=1 --apply-log-only=1 --target-dir=/data/backup/full/ --incremental-dir=/data/backup/inc1/
./xtrabackup version 8.0.28-20 based on MySQL server 8.0.28 Linux (x86_64) (revision id: 4cc3081873d)
...
...
2022-11-02T10:04:05.260675+08:00 0 [Note] [MY-011825] [Xtrabackup] Done: Copying /data/backup/inc1/binlog.index to ./binlog.index
2022-11-02T10:04:05.262194+08:00 0 [Note] [MY-011825] [Xtrabackup] completed OK!

[root@myoracle bin]# ./xtrabackup --prepare --target-dir=/data/backup/full/     //最后一次执行不需要--apply-log-only
2022-11-02T10:05:08.225156+08:00 0 [Note] [MY-011825] [Xtrabackup] recognized server arguments: --innodb_checksum_algorithm=crc32 --innodb_log_checksums=1 --innodb_data_file_path=ibdata1:12M:autoextend --innodb_log_files_in_group=2 --innodb_log_file_size=50331648 --innodb_page_size=16384 --innodb_undo_directory=./ --innodb_undo_tablespaces=2 --server-id=0 --innodb_log_checksums=ON --innodb_redo_log_encrypt=0 --innodb_undo_log_encrypt=0
2022-11-02T10:05:08.225323+08:00 0 [Note] [MY-011825] [Xtrabackup] recognized client arguments: --prepare=1 --target-dir=/data/backup/full/
./xtrabackup version 8.0.28-20 based on MySQL server 8.0.28 Linux (x86_64) (revision id: 4cc3081873d)
...
...
2022-11-02T10:05:10.760137+08:00 0 [Note] [MY-012980] [InnoDB] Shutdown completed; log sequence number 18202144
2022-11-02T10:05:10.760601+08:00 0 [Note] [MY-011825] [Xtrabackup] completed OK!

3.2、模仿数据库丢失,停掉服务

[root@myoracle bin]# mv /home/mysql/data/ /home/mysql/data.BAK
[root@myoracle bin]# service mysql stop
Shutting down MySQL.. SUCCESS!

3.3、恢复阶段

[root@myoracle bin]# ./xtrabackup --copy-back --target-dir=/data/backup/full/
2022-11-02T10:05:44.060196+08:00 0 [Note] [MY-011825] [Xtrabackup] recognized server arguments: --datadir=/home/mysql/data --innodb_buffer_pool_size=512M
2022-11-02T10:05:44.060373+08:00 0 [Note] [MY-011825] [Xtrabackup] recognized client arguments: --port=3306 --socket=/home/mysql/mysql.sock --copy-back=1 --target-dir=/data/backup/full/
./xtrabackup version 8.0.28-20 based on MySQL server 8.0.28 Linux (x86_64) (revision id: 4cc3081873d)
2022-11-02T10:05:44.060416+08:00 0 [Note] [MY-011825] [Xtrabackup] cd to /data/backup/full/
2022-11-02T10:05:44.060680+08:00 0 [Note] [MY-011825] [Xtrabackup] Original data directory /home/mysql/data is not empty!
[root@myoracle bin]# cp -a /data/backup/full/ /home/mysql/
[root@myoracle bin]# cd /home/mysql
[root@myoracle mysql]# ll
total 12
drwxr-xr-x. 6 mysql mysql 4096 Nov  2 10:00 data.BAK
drwxr-x---. 7 root  root  4096 Nov  2 10:05 full
drwxrwxr-x. 9 mysql mysql  129 Nov  1 11:25 mysql8
srwxrwxrwx. 1 mysql mysql    0 Nov  2 09:19 mysql.sock
-rw-------. 1 mysql mysql    5 Nov  2 09:19 mysql.sock.lock
[root@myoracle mysql]# chown -R mysql.mysql full/
[root@myoracle mysql]# mv full/ data
[root@myoracle mysql]# ll
total 12
drwxr-x---. 7 mysql mysql 4096 Nov  2 10:05 data
drwxr-xr-x. 6 mysql mysql 4096 Nov  2 10:00 data.BAK
drwxrwxr-x. 9 mysql mysql  129 Nov  1 11:25 mysql8
srwxrwxrwx. 1 mysql mysql    0 Nov  2 09:19 mysql.sock
-rw-------. 1 mysql mysql    5 Nov  2 09:19 mysql.sock.lock

3.4、启动服务

[root@myoracle bin]# service mysql start
Starting MySQL. SUCCESS!
[root@myoracle bin]# ./mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.28 MySQL Community Server - GPL

Copyright (c) 2000, 2022, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| dey                |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.01 sec)

mysql>
数据恢复成功

4、定时备份

4.1、全备脚本

#!/bin/bash

date=`date +%F`                                                      
backupDir=/data/backup/                                                    
target_dir=/data/backup/$date                                                
xtrDir=/usr/local/xtrabackup/bin/xtrabackup                                    
mysqlDir=/home/mysql/mysql8/bin/mysql                                          
backupLog=$backupDir/backuplog                                                
mysql_host=192.168.x.x                                              
mysql_port=3306
mysql_root=root                                                                              
mysql_password=xxx                                                  
mysql_socket=/home/mysql/mysql.sock                                          
mysql_cnf=/etc/my.cnf                                        

$xtrDir --defaults-file=$mysql_cnf --user=$mysql_user --password=$mysql_password --socket=$mysql_socket --compress --compress-threads=2 --backup --target-dir=$target_dir >>$backupLog/backup_full_success.log 2>&1    ##备份时--compress将备份出来的文件压缩成.qp结尾的文件,需要先解压--decompress,不然在prepare时报错找不到xtrabackup_logfile,或者在备份时去掉--compress和--compress-threads,我这里是去掉了。

$xtrDir --prepare --apply-log-only --target-dir=$target_dir >>$backupLog/prepare_full_success.log 2>&1

#find $backupDir -maxdepth 1 -type d -mtime +14 -exec rm -Rf {} \; >>$backupLog/deleted_record.log  2>&1          ##删除14天前的备份文件

4.2、增备脚本

#!/bin/bash

date=`date +%F`
datenum=`date -d "1 days ago" +%F`        
cur_dateTime=$(date "+%Y-%m-%d %H:%M:%S")
backupDir=/data/backup
target_dir=/data/backup/$date
xtrDir=/usr/local/xtrabackup/bin/xtrabackup
mysqlDir=/home/mysql/mysql8/bin/mysql
backupLog=$backupDir/backuplog
mysql_host=192.168.x.x
mysql_port=3306
mysql_root=root
mysql_password=xxx
mysql_socket=/home/mysql/mysql.sock
mysql_cnf=/etc/my.cnf

$xtrDir --defaults-file=$mysql_cnf --port=$mysql_port --user=$mysql_user --password=$mysql_password --socket=$mysql_socket --compress --compress-threads=2 --backup --target-dir=$target_dir --incremental-basedir=$backupDir/$datenum >> $backupLog/backup_incr_success.log 2>&1          ##这里和全备脚本一样,需要先解压或者去掉--compress和--compress-threads

$xtrDir --prepare --apply-log-only --target-dir=$backupDir/$datenum --incremental-dir=$target_dir >> $backupLog/prepare_incr_success.log 2>&1

#find $backupDir -maxdepth 1 -type d -mtime +14 -exec rm -Rf {} \; >>$backupLog/deleted_record.log  2>&1            ##删除14天前的备份文件

4.3、添加定时任务

[root@myoracle data]# crontab -l
0 10 * * 7 sh /data/mysql_full_backup.sh        ##周日10点做一次全备
0 10 * * 1-6 sh /data/mysql_incr_backup.sh      ##周一到周六10点做一次增备

来源:https://blog.csdn.net/God_sign/article/details/127861989

标签:MySQL,xtrabackup,备份恢复
0
投稿

猜你喜欢

  • 用Meta标签控制360浏览器默认极速模式打开自己的网站

    2022-12-22 07:58:40
  • 实例代码讲解Python 线程池

    2023-07-19 03:53:04
  • OpenCV特征提取与检测之Harris角点检测

    2021-06-05 10:45:51
  • python如何查看系统网络流量的信息

    2022-11-03 13:54:08
  • pytorch dataloader 取batch_size时候出现bug的解决方式

    2023-08-12 01:27:45
  • 怎么让别人看不到网页源代码

    2008-03-21 13:06:00
  • python接口测试对修改密码接口进行压测

    2022-05-16 04:26:15
  • Django Auth用户认证组件实现代码

    2022-05-02 18:06:01
  • python import 上级目录的导入

    2021-09-13 00:54:29
  • MySQL数据库事务隔离级别介绍(Transaction Isolation Level)

    2024-01-18 05:54:11
  • python数据化运营的重要意义

    2021-05-05 21:02:27
  • python爬虫实战之爬取京东商城实例教程

    2022-05-11 05:33:23
  • Python完整实现俄罗斯方块游戏全解

    2022-10-23 16:52:52
  • python实现sm2和sm4国密(国家商用密码)算法的示例

    2021-11-17 08:02:13
  • Python函数式编程

    2023-12-29 10:58:46
  • 关于数据库优化问题收集汇总

    2024-01-28 19:05:28
  • 浅析MySQL数据库授权原则

    2009-12-15 09:21:00
  • 使用python画个小猪佩奇的示例代码

    2023-08-25 17:27:52
  • Python可视化神器pyecharts之绘制地理图表练习

    2022-08-12 10:20:00
  • mysql 5.5 安装配置方法图文教程

    2024-01-20 04:28:35
  • asp之家 网络编程 m.aspxhome.com