MySQL之xtrabackup备份恢复的实现
作者：God_sign 发布时间：2024-01-15 06:59:08
标签：MySQL,xtrabackup,备份恢复
mysql版本：8.0.28
xtrabackup版本：8.0.28
1、安装xtrabackup
下载地址：Download Percona XtraBackup 8.0
[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
0
投稿猜你喜欢
- 在提交添加或修改内容时，需要对关键数据进行判空处理，如何在js中判断checkboxlist是否有选择项呢？ 具体操作如下： var Che
- 一般事件事件浏览器支持描述onClickHTML: 2 | 3 | 3.2 | 4 Browser: IE3 | N2 | O3 鼠标点击事
- 一·逻辑运算符优先级：not>and>or符号名称描述and逻辑与运算当and的两边同时满足条件时，结果为
- Django 作为后端Web开发框架，有时候我们需要用到定时任务来或者固定频次的任务来执行某段代码，这时我们就要用到Celery了。Djan
亲身实践安装mysql，用时居然花费了三个小时，在有那么多教程的情况下，依然在不该花费时间的路上浪费了太多时间。希望这篇文章能够帮助大家少走- 如下所示：import osimport reimport stringfile = open("data2.txt")
- 周末在家，儿子闹着要玩游戏，让玩吧，不利于健康，不让玩吧，扛不住他折腾，于是想，不如一起搞个小游戏玩玩！之前给他编过猜数字 和 掷骰子 游戏
- Python操作MySQL主要使用两种方式：原生模块 pymsqlORM框架 SQLAchemypymqlpymsql是Python中操作M
- 一、pycharm配置1、部署配置工具==》部署==》配置2、python解释器文件==》设置==》项目：xx==》python解释器3、运
- 修改python plot折线图的坐标轴刻度，这里修改为整数：代码如下：from matplotlib import pyplot as p
- 1.选择数据库 USE刚链接到MySQL时，没有数据库打开供你使用，而我们需要选择一个数据库，才能进行以下的操作。方法：USE语句USE M
- 最近要做一个微信平台的投票活动，需要在关注公众号之后才能参与投票，那么，如何判断用户是否关注了公众号呢？第一想法是，通过获取公众号的关注列表
- ubuntu 系统自带的 python 有多个版本，使用时难免会遇到环境变量出错，特别是当自动化运行脚本的时候。特别是近一个月来，实验室的小
- 没什么实际用途，纯属消遣Quick Click<html><head><title>Quick_Clic
- 很多序列的方法字符串同样适用，但是，字符串是不可变的，所以一些试图改变字符串的方法是不可用的1 字符串格式化1）用元组或者字典格式化字符串f
- 阅读目录什么是前端代码异常 window.onerror写一个js报错的上报库注意点：缺点：在平时的工作，js报错是比较常见的一个
- 有时候为了自动化测试网页,我们往往希望能够使用一些脚本语言控制浏览器. 通过脚本模拟一些浏览器动作,然后测试得到的结果.这里, 我们讲解一下
- 情景一：不需要原有的数据库数据删除数据库所有的表删除项目的migration模块中的所有 文件，除了__init__.py 文件执行脚本py
- 1. 简介在 Go 语言中，new 和 make 是用于创建对象的两个内建函数，它们的使用方式和作用有所不同。正确理解 new 和 make
- 首先创建数据库hncu，建立stud表格。添加数据:create table stud(sno varchar(30) not null p
