MySQL之xtrabackup备份恢复的实现

mysql版本：8.0.28
xtrabackup版本：8.0.28

1、安装xtrabackup

下载地址：Download Percona XtraBackup 8.0

?

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