MySQL之xtrabackup备份恢复的实现-编程学习网

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点做一次增备

到此这篇关于MySQL之xtrabackup备份恢复的实现的文章就介绍到这了,更多相关MySQL xtrabackup备份恢复内容请搜索我们以前的文章或继续浏览下面的相关文章希望大家以后多多支持我们！

文章详情

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之xtrabackup备份恢复的实现

mysql xtrabackup 备份恢复实现分享

xtrabackup如何实现MySQL自动备份恢复

Xtrabackup实现数据的备份与恢复

xtrabackup备份恢复MySQL数据库

【MySQL】Xtrabackup备份及恢复脚本

XtraBackup MySql 数据备份和恢复

MySQL Xtrabackup备份和恢复方法

mysql中xtrabackup全量备份/增量备份及恢复

XtraBackup全量备份与恢复MySQL数据

如何进行MySQL中的xtrabackup备份恢复

mysql备份、恢复之mysqldump

【MySql】Mysql之备份与恢复

xtrabackup备份以及恢复（MySQL适用于8.0）

使用XtraBackup恢复|备份 Mysql数据库 -- 数据恢复篇

mysqldump+binlog的备份与恢复和xtrabackup备份工具

Percona XtraBackup怎样实现全备及增量备份与恢复

XtraBackup全量备份与恢复的过程

基于xtrabackup来实现InnoDB存储引擎的备份恢复

xtrabackup全量、增量备份恢复mysql数据库