文章詳情頁

MySQL之xtrabackup備份恢復的實現

瀏覽：171日期：2023-02-18 16:43:11

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/full2022-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 4drwxr-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 -pEnter password: Welcome to the MySQL monitor.  Commands end with ; or \g.Your MySQL connection id is 13Server version: 8.0.28 MySQL Community Server - GPLCopyright (c) 2000, 2022, Oracle and/or its affiliates.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.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 changedmysql> 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: 0mysql> exitBye

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 8drwxr-x---. 5 root root 4096 Nov  2 09:53 fulldrwxr-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 -pEnter password: Welcome to the MySQL monitor.  Commands end with ; or \g.Your MySQL connection id is 16Server version: 8.0.28 MySQL Community Server - GPLCopyright (c) 2000, 2022, Oracle and/or its affiliates.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.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> exitBye

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: 12022-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.index2022-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-only2022-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 182021442022-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 stopShutting 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]# lltotal 12drwxr-xr-x. 6 mysql mysql 4096 Nov  2 10:00 data.BAKdrwxr-x---. 7 root  root  4096 Nov  2 10:05 fulldrwxrwxr-x. 9 mysql mysql  129 Nov  1 11:25 mysql8srwxrwxrwx. 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]# lltotal 12drwxr-x---. 7 mysql mysql 4096 Nov  2 10:05 datadrwxr-xr-x. 6 mysql mysql 4096 Nov  2 10:00 data.BAKdrwxrwxr-x. 9 mysql mysql  129 Nov  1 11:25 mysql8srwxrwxrwx. 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 startStarting MySQL. SUCCESS! [root@myoracle bin]# ./mysql -u root -pEnter password: Welcome to the MySQL monitor.  Commands end with ; or \g.Your MySQL connection id is 8Server version: 8.0.28 MySQL Community Server - GPLCopyright (c) 2000, 2022, Oracle and/or its affiliates.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.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/bashdate=`date +%F`  backupDir=/data/backup/ target_dir=/data/backup/$datextrDir=/usr/local/xtrabackup/bin/xtrabackup     mysqlDir=/home/mysql/mysql8/bin/mysql  backupLog=$backupDir/backuplogmysql_host=192.168.x.x       mysql_port=3306mysql_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/bashdate=`date +%F`datenum=`date -d "1 days ago" +%F`cur_dateTime=$(date "+%Y-%m-%d %H:%M:%S")backupDir=/data/backuptarget_dir=/data/backup/$datextrDir=/usr/local/xtrabackup/bin/xtrabackupmysqlDir=/home/mysql/mysql8/bin/mysqlbackupLog=$backupDir/backuplogmysql_host=192.168.x.xmysql_port=3306mysql_root=rootmysql_password=xxxmysql_socket=/home/mysql/mysql.sockmysql_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 -l0 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

上一條：MySQL數據庫的多種連接方式以及實用工具下一條：MySql深分頁問題解決

排行榜

					
					MySQL刪除數據，表文件大小依然沒變的原因
MySQL中 concat函數的使用
傳甲骨文將增加對MySQL投資與微軟競爭
用三個方法設置Oracle數據庫穿越防火墻
Oracle 11g透明數據加密安全特性解析
MySQL CHAR和VARCHAR該如何選擇
MySQL全文索引、聯合索引、like查詢、json查詢速度哪個快
MySQL 性能、監控與災難恢復
認識那些影響Oracle系統性能初始化參數
MYSQL(電話號碼,身份證)數據脫敏的實現
MySql導出后再導入數據時出錯問題
				