MySQL备份恢复之Xtrabackup

时间:2021-06-21 05:27:25
Preface
 
    Today,I'm gonna use the Xtrabackup tool to demonstrate the procedure of backing up MySQL db server.The lastest version of Xtrabackup is 2.4.11 nowadays,but i'd rather choose the 2.4.4 version which is more popular now.Here we go.
 
Procedure
 
 ###Download the Xtrabackup tool.###
#wget https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.4.4/\
> binary/redhat//x86_64/percona-xtrabackup--2.4.-.el7.x86_64.rpm
---- ::-- https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.4.4/binary/redhat/7/x86_64/percona-xtrabackup-24-2.4.4-1.el7.x86_64.rpm
Resolving www.percona.com (www.percona.com)... 74.121.199.234
Connecting to www.percona.com (www.percona.com)|74.121.199.234|:... connected.
HTTP request sent, awaiting response... OK
Length: (7.5M) [application/x-redhat-package-manager]
Saving to: ‘percona-xtrabackup--2.4.-.el7.x86_64.rpm’ %[===========================================================================================================>] ,, .4KB/s in 5m 14s -- :: (24.4 KB/s) - ‘percona-xtrabackup--2.4.-.el7.x86_64.rpm’ saved [/] ###Install the Xtrabackup tool.###
[root@zlm1 :: ~]
#yum localinstall percona-xtrabackup--2.4.-.el7.x86_64.rpm
Loaded plugins: fastestmirror
Repodata is over weeks old. Install yum-cron? Or run: yum makecache fast
Examining percona-xtrabackup--2.4.-.el7.x86_64.rpm: percona-xtrabackup--2.4.-.el7.x86_64
Marking percona-xtrabackup--2.4.-.el7.x86_64.rpm to be installed
Resolving Dependencies
--> Running transaction check
---> Package percona-xtrabackup-.x86_64 :2.4.-.el7 will be installed
--> Processing Dependency: rsync for package: percona-xtrabackup--2.4.-.el7.x86_64
base | 3.6 kB ::
epel/x86_64/metalink | 6.8 kB ::
epel | 3.2 kB ::
extras | 3.4 kB ::
updates | 3.4 kB ::
(/): base//x86_64/group_gz | kB ::
(/): extras//x86_64/primary_db | kB ::
(/): epel/x86_64/group_gz | kB ::
(/): base//x86_64/primary_db | 5.9 MB ::
(/): updates//x86_64/primary_db | 2.7 MB ::
(/): epel/x86_64/updateinfo | kB ::
(/): epel/x86_64/primary | 3.5 MB ::
Determining fastest mirrors
* base: mirrors.cn99.com
* epel: mirrors.tongji.edu.cn
* extras: ftp.sjtu.edu.cn
* updates: ftp.sjtu.edu.cn
epel /
--> Processing Dependency: libev.so.()(64bit) for package: percona-xtrabackup--2.4.-.el7.x86_64
--> Running transaction check
---> Package libev.x86_64 :4.15-.el7 will be installed
---> Package rsync.x86_64 :3.1.-.el7 will be installed
--> Finished Dependency Resolution Dependencies Resolved =====================================================================================================================================================
Package Arch Version Repository Size
=====================================================================================================================================================
Installing:
percona-xtrabackup- x86_64 2.4.-.el7 /percona-xtrabackup--2.4.-.el7.x86_64 M
Installing for dependencies:
libev x86_64 4.15-.el7 extras k
rsync x86_64 3.1.-.el7 base k Transaction Summary
=====================================================================================================================================================
Install Package (+ Dependent packages) Total size: M
Total download size: k
Installed size: M
Is this ok [y/d/N]: y
Downloading packages:
(/): libev-4.15-.el7.x86_64.rpm | kB ::
(/): rsync-3.1.-.el7.x86_64.rpm | kB ::
-----------------------------------------------------------------------------------------------------------------------------------------------------
Total 1.0 MB/s | kB ::
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
Installing : rsync-3.1.-.el7.x86_64 /
Installing : libev-4.15-.el7.x86_64 /
Installing : percona-xtrabackup--2.4.-.el7.x86_64 /
Verifying : percona-xtrabackup--2.4.-.el7.x86_64 /
Verifying : libev-4.15-.el7.x86_64 /
Verifying : rsync-3.1.-.el7.x86_64 / Installed:
percona-xtrabackup-.x86_64 :2.4.-.el7 Dependency Installed:
libev.x86_64 :4.15-.el7 rsync.x86_64 :3.1.-.el7 Complete! ###Create a backup user with mixium privileges.###
root@localhost:mysql3306.sock [(none)]::>create user 'bkuser'@'localhost' identified by 'bkuser';
Query OK, rows affected (0.00 sec) root@localhost:mysql3306.sock [(none)]::>grant reload,lock tables,process,replication client on *.* to 'bkuser'@'localhost';
Query OK, rows affected (0.00 sec) root@localhost:mysql3306.sock [(none)]::>flush privileges;
Query OK, rows affected (0.00 sec) root@localhost:mysql3306.sock [(none)]::>select user,host from mysql.user;
+---------------+--------------+
| user | host |
+---------------+--------------+
| repl | 192.168..% |
| bkuser | localhost |
| mysql.session | localhost |
| mysql.sys | localhost |
| root | localhost |
+---------------+--------------+
rows in set (0.00 sec)

    After install the Xtrabackup,you'll get two programs:xtrabackup & innobackupex.Acturally,both of them can be used to backup or restore the database independently.The innobackupex is a symlink to the xtrabackup C program but in the old version of Xtrabackup it is Perl script.

 ###Generate a backup by innobackex.###
[root@zlm1 :: ~]
#innobackupex --user=bkuser --password=bkuser --defaults-file=/data/mysql/mysql3306/my3306.cnf /data/backup
xtrabackup: Error: --defaults-file must be specified first on the command line -- Error shows that it should be put at first place. [root@zlm1 :: ~]
#innobackupex --defaults-file=/data/mysql/mysql3306/my3306.cnf --user=bkuser --password=bkuser /data/backup
:: innobackupex: Starting the backup operation IMPORTANT: Please check that the backup run completes successfully.
At the end of a successful backup run innobackupex
prints "completed OK!". Can't locate Digest/MD5.pm in @INC (@INC contains: /usr/local/lib64/perl5 /usr/local/share/perl5 /usr/lib64/perl5/vendor_perl /usr/share/perl5/vendor_perl /usr/lib64/perl5 /usr/share/perl5 .) at - line 693.
BEGIN failed--compilation aborted at - line .
:: Connecting to MySQL server host: localhost, user: bkuser, password: set, port: , socket: /tmp/mysql3306.sock
Using server version 5.7.-log
innobackupex version 2.4. based on MySQL server 5.7. Linux (x86_64) (revision id: df58cf2)
xtrabackup: uses posix_fadvise().
xtrabackup: cd to /data/mysql/mysql3306/data
xtrabackup: open files limit requested , set to
xtrabackup: using the following InnoDB configuration:
xtrabackup: innodb_data_home_dir = .
xtrabackup: innodb_data_file_path = ibdata1:100M:autoextend
xtrabackup: innodb_log_group_home_dir = ./
xtrabackup: innodb_log_files_in_group =
xtrabackup: innodb_log_file_size =
xtrabackup: using O_DIRECT
InnoDB: Number of pools:
:: >> log scanned up to ()
xtrabackup: Generating a list of tablespaces
InnoDB: Allocated tablespace ID for mysql/plugin, old maximum was
:: >> log scanned up to ()
:: [] Copying ./ibdata1 to /data/backup/--16_11--/ibdata1
:: >> log scanned up to ()
:: >> log scanned up to ()
:: >> log scanned up to ()
:: [] ...done
:: >> log scanned up to ()
:: [] Copying ./mysql/plugin.ibd to /data/backup/--16_11--/mysql/plugin.ibd
:: [] ...done
:: [] Copying ./mysql/servers.ibd to /data/backup/--16_11--/mysql/servers.ibd
:: [] ...done
-- To simplify the output,I omitt the other innodb *.ibd file copy contents.
:: [] Copying ./zlm/t1.ibd to /data/backup/--16_11--/zlm/t1.ibd
:: [] ...done
:: [] Copying ./zlm/t2.ibd to /data/backup/--16_11--/zlm/t2.ibd
:: [] ...done
:: [] Copying ./zlm/t3.ibd to /data/backup/--16_11--/zlm/t3.ibd
:: [] ...done
:: [] Copying ./zlm/test_flashbk.ibd to /data/backup/--16_11--/zlm/test_flashbk.ibd
:: >> log scanned up to ()
:: [] ...done
:: >> log scanned up to ()
:: [] Copying ./zlm/test.ibd to /data/backup/--16_11--/zlm/test.ibd
:: [] ...done
:: >> log scanned up to ()
:: [] Copying ./zlm/semi_sync_test.ibd to /data/backup/--16_11--/zlm/semi_sync_test.ibd
:: [] ...done
:: >> log scanned up to ()
:: Executing FLUSH NO_WRITE_TO_BINLOG TABLES...
:: Executing FLUSH TABLES WITH READ LOCK... -- Add whole instance read-only lock on tables(but not table locks.)
:: Starting to backup non-InnoDB tables and files
:: [] Copying ./mysql/db.opt to /data/backup/--16_11--/mysql/db.opt
:: [] ...done
:: [] Copying ./mysql/db.frm to /data/backup/--16_11--/mysql/db.frm
:: [] ...done
:: [] Copying ./mysql/db.MYI to /data/backup/--16_11--/mysql/db.MYI
:: [] ...done
:: [] Copying ./mysql/db.MYD to /data/backup/--16_11--/mysql/db.MYD
-- Omitt portion of the contents.
:: [] Copying ./performance_schema/file_summary_by_event_name.frm to /data/backup/--16_11--/performance_schema/file_summary_by_event_name.frm
:: >> log scanned up to ()
:: [] ...done
:: [] Copying ./performance_schema/file_summary_by_instance.frm to /data/backup/--16_11--/performance_schema/file_summary_by_instance.frm
:: [] ...done
-- Omitt portion of the contents.
:: [] Copying ./sys/waits_by_user_by_latency.frm to /data/backup/--16_11--/sys/waits_by_user_by_latency.frm
:: [] ...done
:: >> log scanned up to ()
:: [] Copying ./sys/x@0024innodb_lock_waits.frm to /data/backup/--16_11--/sys/x@0024innodb_lock_waits.frm
:: [] ...done
-- Omitt portion of the contents.
:: [] Copying ./zlm/db.opt to /data/backup/--16_11--/zlm/db.opt
:: [] ...done
:: [] Copying ./zlm/t1.frm to /data/backup/--16_11--/zlm/t1.frm
:: [] ...done
:: [] Copying ./zlm/t2.frm to /data/backup/--16_11--/zlm/t2.frm
:: [] ...done
:: [] Copying ./zlm/t3.frm to /data/backup/--16_11--/zlm/t3.frm
:: [] ...done
:: [] Copying ./zlm/test_flashbk.frm to /data/backup/--16_11--/zlm/test_flashbk.frm
:: [] ...done
:: [] Copying ./zlm/test.frm to /data/backup/--16_11--/zlm/test.frm
:: [] ...done
:: [] Copying ./zlm/semi_sync_test.frm to /data/backup/--16_11--/zlm/semi_sync_test.frm
:: [] ...done
:: [] Writing -help/db.opt
:: [] ...done
:: Finished backing up non-InnoDB tables and files
:: >> log scanned up to ()
:: [] Writing xtrabackup_binlog_info
:: [] ...done
:: Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS...
xtrabackup: The latest check point (for incremental): ''
xtrabackup: Stopping log copying thread.
. :: >> log scanned up to () :: Executing UNLOCK TABLES -- Release the locks.
:: All tables unlocked
:: [] Copying ib_buffer_pool to /data/backup/--16_11--/ib_buffer_pool
:: [] ...done
:: Backup created in directory '/data/backup/2018-06-16_11-28-59'
MySQL binlog position: filename 'mysql-bin.000046', position '', GTID of the last change '2a4b3562-2ab6-11e8-be7a-080027de0e0e:1-2700058'
:: [] Writing backup-my.cnf
:: [] ...done
:: [] Writing xtrabackup_info
:: [] ...done
xtrabackup: Transaction log of lsn () to () was copied. -- Point the incremental lsn.
:: completed OK! There's an error at the beginning of the backup output below: Can't locate Digest/MD5.pm in @INC (@INC contains: /usr/local/lib64/perl5 /usr/local/share/perl5 /usr/lib64/perl5/vendor_perl /usr/share/perl5/vendor_perl /usr/lib64/perl5 /usr/share/perl5 .) at - line 693.
BEGIN failed--compilation aborted at - line . This is caused by lack of package of perl-Digest-MD5,as the innobackex is coded by perl,but I'm afraid it can be ignored,because I've got "completed OK!" message in the end of screen output. The backup is normally generated,too.see details below. [root@zlm1 :: ~]
#ls -l /data/backup
total
drwxr-x--- root root Jun : --16_11-- -- All the backup data and informations stored in this directory. [root@zlm1 :: /data/backup]
#cd /data/backup/--16_11--/ [root@zlm1 :: /data/backup/--16_11--]
#ls -l
total
-rw-r----- root root Jun : backup-my.cnf
drwxr-x--- root root Jun : -help
-rw-r----- root root Jun : ib_buffer_pool
-rw-r----- root root Jun : ibdata1
drwxr-x--- root root Jun : mysql
drwxr-x--- root root Jun : performance_schema
drwxr-x--- root root Jun : sys
-rw-r----- root root Jun : xtrabackup_binlog_info -- Contains the binlog position info.
-rw-r----- root root Jun : xtrabackup_checkpoints -- Contains teh checkpoint info.
-rw-r----- root root Jun : xtrabackup_info -- Contains the Whole info.
-rw-r----- root root Jun : xtrabackup_logfile -- It's a binary file which contains logfile info.
drwxr-x--- root root Jun : zlm [root@zlm1 :: /data/backup/--16_11--]
#cat xtrabackup_binlog_info
mysql-bin. 2a4b3562-2ab6-11e8-be7a-080027de0e0e:- [root@zlm1 :: /data/backup/--16_11--]
#cat xtrabackup_checkpoints
backup_type = full-backuped
from_lsn =
to_lsn =
last_lsn =
compact =
recover_binlog_info = [root@zlm1 :: /data/backup/--16_11--]
#cat xtrabackup_info
uuid = bad60a4a--11e8-978f-080027de0e0e
name =
tool_name = innobackupex
tool_command = --defaults-file=/data/mysql/mysql3306/my3306.cnf --user=bkuser --password=... /data/backup
tool_version = 2.4.
ibbackup_version = 2.4.
server_version = 5.7.-log
start_time = -- ::
end_time = -- ::
lock_time =
binlog_pos = filename 'mysql-bin.000046', position '', GTID of the last change '2a4b3562-2ab6-11e8-be7a-080027de0e0e:1-2700058'
innodb_from_lsn =
innodb_to_lsn =
partial = N
incremental = N
format = file
compact = N
compressed = N
encrypted = N [root@zlm1 :: /data/backup/--16_11--]
#strings xtrabackup_logfile
xtrabkup ::
:/mysql/gtid_executed.ibd
F^<.
$2a4b3562-2ab6-11e8-be7a-080027de0e0e
./mysql/gtid_executed.ibd
./mysql/gtid_executed.ibd
F^=K8
F^=s8
F^=|
size

    Let's see the detail in the general.log file below.It seems very short and simple.Obviously,xtrabckup will also hold lock when backing up like mysqldump does.The time period depents on the amounts of  non-innodb tables.

 [root@zlm1 :: /data/mysql/mysql3306/data]
#cat zlm1.log --16T09::.361317Z Connect bkuser@localhost on using Socket
--16T09::.361444Z Query SET SESSION wait_timeout=
--16T09::.361590Z Query SHOW VARIABLES
--16T09::.364391Z Query SHOW ENGINE INNODB STATUS
--16T09::.442559Z Query SET SESSION lock_wait_timeout=
--16T09::.442693Z Query FLUSH NO_WRITE_TO_BINLOG TABLES -- Finish *.idb file copy operation.
--16T09::.444503Z Query FLUSH TABLES WITH READ LOCK -- There is also FTWRL lock here.
--16T09::.361073Z Query SHOW MASTER STATUS
--16T09::.361330Z Query SHOW VARIABLES
--16T09::.384043Z Query FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS -- Finish redo copy operation.
--16T09::.587336Z Query UNLOCK TABLES -- Release the locks on table.It has spent three seconds in all to hold FTWRL.
--16T09::.600570Z Query SELECT UUID()
--16T09::.600794Z Query SELECT VERSION()
--16T09::.804252Z Quit
Summary
  • Xtrabackup is more like a physical backup tool compared with mysqldump,which is usually used to backup whole database.
  • FTWRL is inevitable when backing up by Xtrabackup.The holding time depends on the amount of non-innodb tables.
  • Furthermore,Xtrabackup supports parallel and incremental backup which mysqldump doesn't support.