Xtrabackup backup restore single library / table
It is convenient to restore a single database / table using mysqldump logical backups. However, when the amount of data is large, it takes too long to restore the logical backup. Choosing a physical backup at this time can greatly shorten the time involved.
Using xtrabackup for full library physical backup and restore is very simple and does not require re-import of its tablespace (tablespace).
Xtrabackup full library backup and restore steps:
Create a backup
$xtrabackup-backup-target-dir=/data/backups/
Prepare for backup
$xtrabackup-prepare-target-dir=/data/backups/
Restore backup
$xtrabackup-copy-back-target-dir=/data/backups/
$chown-R mysql:mysql / var/lib/mysql
Single database / table backup and restore steps:
Create a backup
Xtrabackup-backup-datadir=/var/lib/mysql-target-dir=/data/backups/-tables= "^ test [.]. *"
Prepare for backup
$xtrabackup-prepare-target-dir=/data/backups/
Export table structure
$mysqldump-d ^ test > test.sql
Import table structure
$mysql test
< test.sql 释放表空间 # mysql -e "set foreign_key_checks=0;alter table test.* discard tablespace;" 复制文件到数据目录 # cp -f /data/backups/*.cfg /data/backups/*.ibd /data/backups/*.frm /var/lib/mysql/test 修改属主 # chown -R mysql:mysql /var/lib/mysql/test 导入表空间 # mysql -e "set foreign_key_checks=0;alter table test.* import tablespace;analyze table test.*;" 如果两个实例都具有 GA（通用可用性）状态并且它们的版本在同一个系列中，则从另一个 MySQL 服务器实例导入表空间文件将起作用。 否则，该文件必须已在与其导入的服务器实例相同的服务器实例上创建。 以下是一个通过 xtrabackup 对单库进行备份还原的脚本 #!/bin/bash# by geamover on 2017-12-26# last updated on 2018-05-08# This is for backup one DB to the orther DB on the same server by xtrabackup.BK_DIR=/data/backup/`date +%F`/$1 # 备份路径MT=$BK_DIR/$1.sql # 元数据DT=/data/mysql/data/ # 数据目录SK=/tmp/mysql.sock # mysql.sockPW=123456 # mysql 密码if [ $# -ne 2 ]; then echo -e "\e[1;31mUsage:\e[0m $0 source_DB destination_DB" exit 1fimysql -u root -p"$PW" -e "use $1;" &>/ dev/nullif [$?! = 0]; then echo "There is no database $1, please check!" Exit 1fiecho "It will drop databae $2, are you sure continue?" echo-e "\ e [1control 31my\ e [0m for continue, any orther for exit:" read inputif [$input = = "y"]; then echo "Go on!" else echo "Cancled!" Exitfiif [- d $BK_DIR]; then rm-rf $BK_DIR/*else mkdir-p $BK_DIRfiecho "From $1 to $2" | tee-a xtra.logecho `date "+% F% T" `"Backing up databae $1 to $BK_DIR" | tee-a xtra.logxtrabackup-- backup-u root-p "$PW"-socket=$SK-databases= "$1"-parallel=8-target-dir=$BK_DIR & > xtra.logJD= `tail-n 1 xtra.log | awk'{print $3 $4} '`if [$JD= = "completedOK!"] Then echo `date "+% F% T" `"Back up databae $1 to $BK_DIR completed OK!" else echo `date "+% F% T"` "Back up databae $1 to $BK_DIR failed!" Exit 1fiecho `date "+% F% T" `"Prepare the backup." | tee-a xtra.logxtrabackup-- prepare-- export-- parallel=8-- target-dir=$BK_DIR & > > xtra.logJD= `tail-n 1 xtra.log | awk'{print $3 $4} '`if [$JD= = "completedOK!"]; then echo `date "+% F% T"` "Prepare up databae $1 to $BK_DIR completedOK!" else echo `date "+% F% T" `"Prepare up databae $1 to $BK_DIR failed! " Exit 1fiecho `date "+% F% T" `"Dump the metadata of $1 to $MT." #-- if GTID is enabled, specify this option to export metadata mysqldump-u root-p "$PW"-d $1 > $MTecho "Drop database $2 if exists.Then create it and import metadata." mysql-u root-p "$PW"-e "Drop database if exists $2 create database $2 Singapore use $2 source $MT "& > / dev/nullfor I in $(grep" CREATE TABLE "$MT | awk'{print $3}'| sed" s #\ `# # g ") do mysql-u root-p" $PW "- e" set foreign_key_checks=0;alter table $2.pragi discard tablespace " "& > / dev/null cp-f $BK_DIR/$1/$i.cfg $BK_DIR/$1/$i.ibd $BK_DIR/$1/$i.frm $DT$2/ chown-R mysql:mysql $DT$2/ mysql-u root-p" $PW "- e" set foreign_key_checks=0;alter table $2.Secreti import tablespace;analyze table $2.Secreti "& > / dev/null doneecho `date" +% F% T "`" From $1 to $2 done. "| tee-a xtra.log# delete the backuprm-rf $BK_DIR
