MySQL之xtrabackup备份恢复的实现_F11 - 专业站长和开发者的学习网站

分享到

MySQL之xtrabackup备份恢复的实现

Mysql 来源：互联网作者：佚名发布时间：2023-02-06 21:25:42 人浏览

摘要

mysql版本：8.0.28 xtrabackup版本：8.0.28 1、安装xtrabackup 下载地址：Download Percona XtraBackup 8.0https://www.percona.com/downloads 1 2 [root@myoracle ~]# tar -zxvf percona-xtrabackup-8.0.28-20-Linux-x86_64.glibc2.17.tar.gz

mysql版本：8.0.28
xtrabackup版本：8.0.28

1、安装xtrabackup

下载地址：Download Percona XtraBackup 8.0 https://www.percona.com/downloads

在这里插入图片描述

1 2	[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

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

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

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

Tag : mysql(211)恢复(6)备份(7)

MySQL字符串前缀索引使用

1. 前缀索引与全部索引概念怎么给字符串字段加索引？现在，几乎所有的系统都支持邮箱登录，如何在邮箱这样的字段上建立合理的索引，
MySql深分页问题解决

1. 问题描述日常开发中经常会涉及到数据查询分页的问题，一般情况下都是根据前端传入页数与页码通过mysql的limit方式实现分页，对于数据
MySQL之xtrabackup备份恢复的实现

mysql版本：8.0.28 xtrabackup版本：8.0.28 1、安装xtrabackup 下载地址：Download Percona XtraBackup 8.0https://www.percona.com/downloads 1 2 [root@myoracle ~]# tar -zxvf
Mysql的SELECT语句与显示表结构介绍

SELECT... 1 2 SELECT 1+1, 2+2;# 直接这样写相当于下面这句 SELECT 1+1, 2+2 FROM DUAL; # 这里DUAL：伪表 SELECT ... FROM 语法： SELECT 标识选择哪些字段(列)
MySql如何不插入重复数据

向数据库的插入值时，需要判断插入是否重复，然后插入。这种操作怎么提高效率下面说说一些解决方案 1、insert ignore into 当插入数据时，
MYSQL统计逗号分隔字段元素的个数

写SQL的时候会遇到如下的问题，统计如下表中project_id字段中id的个数。 company_id project_id 77 94882,214880,94881,154882,94871,94879 140 2890,2872,3178,4314,
MySQL事务的SavePoint简介及操作

什么是SavePoint SavePoint是数据库事务中的一个概念, 可以将整个事务切割为不同的小事务, 可以选择将状态回滚到某个小事务发生时的样子，
mysql查看表大小的方法介绍

1.查看所有数据库容量大小 1 2 3 4 5 6 7 8 select table_schema as 数据库, sum(table_rows) as 记录数, sum(truncate(data_length/1024/1024, 2)) as 数据容量(MB), sum(
MySQL不用like+%实现模糊查询

我们都知道 InnoDB 在模糊查询数据时使用 %xx 会导致索引失效，但有时需求就是如此，类似这样的需求还有很多，例如，搜索引擎需要根基用
SQL通用语法以及分类图文介绍

MySQL的启动和连接数据模型 MySQL的启动和停止 1.services.msc 2.命令行启停启动：net start mysql80 停止：net stop mysql80 (命令行窗口以管理员权限打

MySQL之xtrabackup备份恢复的实现

1、安装xtrabackup

2、备份

2.1、做一次全备

2.2、进入mysql添加数据

2.3、做一次增备

2.4、删除数据库（千万不要在生产库执行！！！）

3、恢复

3.1、准备阶段

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

3.3、恢复阶段

3.4、启动服务

4、定时备份

4.1、全备脚本

4.2、增备脚本

4.3、添加定时任务

您可能感兴趣的文章 :

MySQL字符串前缀索引使用

MySql深分页问题解决

MySQL之xtrabackup备份恢复的实现

Mysql的SELECT语句与显示表结构介绍

MySql如何不插入重复数据

MYSQL统计逗号分隔字段元素的个数

MySQL事务的SavePoint简介及操作

mysql查看表大小的方法介绍

MySQL不用like+%实现模糊查询

SQL通用语法以及分类图文介绍

zabbix监控mysql的实例方法

MySQL时间类型的选择

MySQL数据库基本SQL语句教程之高级操作

达梦数据库获取SQL实际执行计划的方

分享Mysql大型SQL文件快速恢复方案