服务器之家：专注于VPS、云服务器配置技术及软件下载分享
分类导航

Mysql|Sql Server|Oracle|Redis|MongoDB|PostgreSQL|Sqlite|DB2|mariadb|Access|数据库技术|

服务器之家 - 数据库 - Mysql - MySQL之xtrabackup备份恢复的实现

MySQL之xtrabackup备份恢复的实现

2023-02-06 14:20God_sign Mysql

本文主要介绍了MySQL之xtrabackup备份恢复的实现，文中通过示例代码介绍的非常详细，对大家的学习或者工作具有一定的参考学习价值，需要的朋友们下面随着小编来一起学习学习吧

mysql版本：8.0.28
xtrabackup版本：8.0.28

1、安装xtrabackup

下载地址：Download Percona XtraBackup 8.0

MySQL之xtrabackup备份恢复的实现

?
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、备份

?
1
2
3
4
5
6
7
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、做一次全备

?
1
2
3
4
5
6
7
8
9
10
11
[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添加数据

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
[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、做一次增备

?
1
2
3
4
5
6
7
8
9
10
11
12
13
[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、删除数据库（千万不要在生产库执行！！！）

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
[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、准备阶段

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
[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、模仿数据库丢失，停掉服务

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

3.3、恢复阶段

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
[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、启动服务

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
[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、全备脚本

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
#!/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、增备脚本

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
#!/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、添加定时任务

?
1
2
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备份恢复内容请搜索服务器之家以前的文章或继续浏览下面的相关文章希望大家以后多多支持服务器之家！

原文链接：https://blog.csdn.net/God_sign/article/details/127861989

  • 备份恢复
  • Xtrabackup
  • mysql

    • 延伸 · 阅读

    精彩推荐
    • MysqlMySQL中的流式查询及游标查询方式

      MySQL中的流式查询及游标查询方式

      这篇文章主要介绍了MySQL中的流式查询及游标查询方式，具有很好的参考价值，希望对大家有所帮助。如有错误或未考虑完全的地方，望不吝赐教...

      Viknix7832022-08-17
    • Mysqlmysql 5.7.5 m15 winx64.zip安装教程

      mysql 5.7.5 m15 winx64.zip安装教程

      这篇文章主要为大家详细介绍了mysql 5.7.5 m15 winx64.zip安装教程，具有一定的参考价值，感兴趣的小伙伴们可以参考一下...

      wenthink2432020-07-31
    • MysqlMySQL解决SQL注入的另类方法详解

      MySQL解决SQL注入的另类方法详解

      这篇文章主要介绍了MySQL解决SQL注入的另类方法,结合实例形式列举分析了几种防止SQL注入的技巧,具有一定参考借鉴价值,需要的朋友可以参考下 ...

      OurMySQL2312020-06-06
    • MysqlMySQL排序检索数据操作方法梳理

      MySQL排序检索数据操作方法梳理

      SELECT语句是SQL中最常用的语句。它的用途是从一个或多个表中检索信息。为了使用SELECT检索表数据，必须至少给出两条信息：想选择什么、从什么地方选择...

      刘婉晴10272022-11-27
    • MysqlMySQL创建用户与授权及撤销用户权限方法

      MySQL创建用户与授权及撤销用户权限方法

      这篇文章主要介绍了MySQL创建用户并授权及撤销用户权限、设置与更改用户密码、删除用户等等,需要的朋友可以参考下 ...

      whsnow3782020-04-18
    • MysqlMySQL在grant时报错ERROR 1064 (42000)的原因及解决方法

      MySQL在grant时报错ERROR 1064 (42000)的原因及解决方法

      网上查到的grant方式大多会报错，主要原因是MySQL版本8.0后不能再使用原来的方式，这篇文章主要介绍了MySQL在grant时报错ERROR 1064 (42000)，需要的朋友可以参...

      umichan8242022-08-26
    • MysqlMySQL kill指令使用指南

      MySQL kill指令使用指南

      这篇文章主要介绍了MySQL kill指令的使用方法，帮助大家更好的理解和使用MySQL，感兴趣的朋友可以了解下...

      掘金小勇士12492021-03-25
    • MysqlMac 安装和卸载 Mysql5.7.11 的方法

      Mac 安装和卸载 Mysql5.7.11 的方法

      本文给大家介绍Mac 安装和卸载 Mysql5.7.11 的方法，本文介绍的非常详细，具有参考借鉴价值，感兴趣的朋友一起学习吧 ...

      xbamboo4512020-06-02
    最近更新
    编辑推荐
    阅读排行
    热门标签
    150 　 1044 　 1062 　 1053 　 1449 　 1045 　 1290 　 1819 　 2013 　 1406 　 1005 　 1864 　 1862 　 1146 　 1054 　 1615 　 2006 　 1215 　 1175 　 10048 　 2003 　 1366 　 28000 　 1577 　 1135 　 1093 　 1786 　 1067 　 1698 　 1251 　
    © 2019-2023 服务器之家(www.tuohang.net) 版权所有关于我们联系我们版权申明网站地图