欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

MySQL备份恢复之Xtrabackup

程序员文章站 2022-08-12 15:25:59
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 ......

 

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
 
  1 ###Download the Xtrabackup tool.###
  2 #wget https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.4.4/\
  3 > binary/redhat/7/x86_64/percona-xtrabackup-24-2.4.4-1.el7.x86_64.rpm
  4 --2018-06-16 09:33:21--  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
  5 Resolving www.percona.com (www.percona.com)... 74.121.199.234
  6 Connecting to www.percona.com (www.percona.com)|74.121.199.234|:443... connected.
  7 HTTP request sent, awaiting response... 200 OK
  8 Length: 7839980 (7.5M) [application/x-redhat-package-manager]
  9 Saving to: ‘percona-xtrabackup-24-2.4.4-1.el7.x86_64.rpm’
 10 
 11 100%[===========================================================================================================>] 7,839,980   48.4KB/s   in 5m 14s 
 12 
 13 2018-06-16 09:38:36 (24.4 KB/s) - ‘percona-xtrabackup-24-2.4.4-1.el7.x86_64.rpm’ saved [7839980/7839980]
 14 
 15 
 16 ###Install the Xtrabackup tool.###
 17 [root@zlm1 09:39:18 ~]
 18 #yum localinstall percona-xtrabackup-24-2.4.4-1.el7.x86_64.rpm
 19 Loaded plugins: fastestmirror
 20 Repodata is over 2 weeks old. Install yum-cron? Or run: yum makecache fast
 21 Examining percona-xtrabackup-24-2.4.4-1.el7.x86_64.rpm: percona-xtrabackup-24-2.4.4-1.el7.x86_64
 22 Marking percona-xtrabackup-24-2.4.4-1.el7.x86_64.rpm to be installed
 23 Resolving Dependencies
 24 --> Running transaction check
 25 ---> Package percona-xtrabackup-24.x86_64 0:2.4.4-1.el7 will be installed
 26 --> Processing Dependency: rsync for package: percona-xtrabackup-24-2.4.4-1.el7.x86_64
 27 base                                                                                                                          | 3.6 kB  00:00:00     
 28 epel/x86_64/metalink                                                                                                          | 6.8 kB  00:00:00     
 29 epel                                                                                                                          | 3.2 kB  00:00:00     
 30 extras                                                                                                                        | 3.4 kB  00:00:00     
 31 updates                                                                                                                       | 3.4 kB  00:00:00     
 32 (1/7): base/7/x86_64/group_gz                                                                                                 | 166 kB  00:00:00     
 33 (2/7): extras/7/x86_64/primary_db                                                                                             | 149 kB  00:00:00     
 34 (3/7): epel/x86_64/group_gz                                                                                                   |  88 kB  00:00:00     
 35 (4/7): base/7/x86_64/primary_db                                                                                               | 5.9 MB  00:00:01     
 36 (5/7): updates/7/x86_64/primary_db                                                                                            | 2.7 MB  00:00:01     
 37 (6/7): epel/x86_64/updateinfo                                                                                                 | 928 kB  00:00:02     
 38 (7/7): epel/x86_64/primary                                                                                                    | 3.5 MB  00:00:17     
 39 Determining fastest mirrors
 40  * base: mirrors.cn99.com
 41  * epel: mirrors.tongji.edu.cn
 42  * extras: ftp.sjtu.edu.cn
 43  * updates: ftp.sjtu.edu.cn
 44 epel                                                                                                                                     12584/12584
 45 --> Processing Dependency: libev.so.4()(64bit) for package: percona-xtrabackup-24-2.4.4-1.el7.x86_64
 46 --> Running transaction check
 47 ---> Package libev.x86_64 0:4.15-7.el7 will be installed
 48 ---> Package rsync.x86_64 0:3.1.2-4.el7 will be installed
 49 --> Finished Dependency Resolution
 50 
 51 Dependencies Resolved
 52 
 53 =====================================================================================================================================================
 54  Package                             Arch                 Version                      Repository                                               Size
 55 =====================================================================================================================================================
 56 Installing:
 57  percona-xtrabackup-24               x86_64               2.4.4-1.el7                  /percona-xtrabackup-24-2.4.4-1.el7.x86_64                31 M
 58 Installing for dependencies:
 59  libev                               x86_64               4.15-7.el7                   extras                                                   44 k
 60  rsync                               x86_64               3.1.2-4.el7                  base                                                    403 k
 61 
 62 Transaction Summary
 63 =====================================================================================================================================================
 64 Install  1 Package (+2 Dependent packages)
 65 
 66 Total size: 32 M
 67 Total download size: 447 k
 68 Installed size: 32 M
 69 Is this ok [y/d/N]: y
 70 Downloading packages:
 71 (1/2): libev-4.15-7.el7.x86_64.rpm                                                                                            |  44 kB  00:00:00     
 72 (2/2): rsync-3.1.2-4.el7.x86_64.rpm                                                                                           | 403 kB  00:00:00     
 73 -----------------------------------------------------------------------------------------------------------------------------------------------------
 74 Total                                                                                                                1.0 MB/s | 447 kB  00:00:00     
 75 Running transaction check
 76 Running transaction test
 77 Transaction test succeeded
 78 Running transaction
 79   Installing : rsync-3.1.2-4.el7.x86_64                                                                                                          1/3 
 80   Installing : libev-4.15-7.el7.x86_64                                                                                                           2/3 
 81   Installing : percona-xtrabackup-24-2.4.4-1.el7.x86_64                                                                                          3/3 
 82   Verifying  : percona-xtrabackup-24-2.4.4-1.el7.x86_64                                                                                          1/3 
 83   Verifying  : libev-4.15-7.el7.x86_64                                                                                                           2/3 
 84   Verifying  : rsync-3.1.2-4.el7.x86_64                                                                                                          3/3 
 85 
 86 Installed:
 87   percona-xtrabackup-24.x86_64 0:2.4.4-1.el7                                                                                                         
 88 
 89 Dependency Installed:
 90   libev.x86_64 0:4.15-7.el7                                                rsync.x86_64 0:3.1.2-4.el7                                               
 91 
 92 Complete!
 93 
 94 ###Create a backup user with mixium privileges.###
 95 root@localhost:mysql3306.sock [(none)]09:47:08>create user 'bkuser'@'localhost' identified by 'bkuser';
 96 Query OK, 0 rows affected (0.00 sec)
 97 
 98 root@localhost:mysql3306.sock [(none)]09:47:12>grant reload,lock tables,process,replication client on *.* to 'bkuser'@'localhost';
 99 Query OK, 0 rows affected (0.00 sec)
100 
101 root@localhost:mysql3306.sock [(none)]09:47:19>flush privileges;
102 Query OK, 0 rows affected (0.00 sec)
103 
104 root@localhost:mysql3306.sock [(none)]09:47:30>select user,host from mysql.user;
105 +---------------+--------------+
106 | user          | host         |
107 +---------------+--------------+
108 | repl          | 192.168.56.% |
109 | bkuser        | localhost    |
110 | mysql.session | localhost    |
111 | mysql.sys     | localhost    |
112 | root          | localhost    |
113 +---------------+--------------+
114 5 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.

 

  1 ###Generate a backup by innobackex.###
  2 [root@zlm1 11:27:36 ~]
  3 #innobackupex --user=bkuser --password=bkuser --defaults-file=/data/mysql/mysql3306/my3306.cnf /data/backup
  4 xtrabackup: Error: --defaults-file must be specified first on the command line -- Error shows that it should be put at first place.
  5 
  6 [root@zlm1 11:28:25 ~]
  7 #innobackupex --defaults-file=/data/mysql/mysql3306/my3306.cnf --user=bkuser --password=bkuser /data/backup
  8 180616 11:28:59 innobackupex: Starting the backup operation
  9 
 10 IMPORTANT: Please check that the backup run completes successfully.
 11            At the end of a successful backup run innobackupex
 12            prints "completed OK!".
 13 
 14 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.
 15 BEGIN failed--compilation aborted at - line 693.
 16 180616 11:28:59 Connecting to MySQL server host: localhost, user: bkuser, password: set, port: 3306, socket: /tmp/mysql3306.sock
 17 Using server version 5.7.21-log
 18 innobackupex version 2.4.4 based on MySQL server 5.7.13 Linux (x86_64) (revision id: df58cf2)
 19 xtrabackup: uses posix_fadvise().
 20 xtrabackup: cd to /data/mysql/mysql3306/data
 21 xtrabackup: open files limit requested 65535, set to 65535
 22 xtrabackup: using the following InnoDB configuration:
 23 xtrabackup:   innodb_data_home_dir = .
 24 xtrabackup:   innodb_data_file_path = ibdata1:100M:autoextend
 25 xtrabackup:   innodb_log_group_home_dir = ./
 26 xtrabackup:   innodb_log_files_in_group = 3
 27 xtrabackup:   innodb_log_file_size = 104857600
 28 xtrabackup: using O_DIRECT
 29 InnoDB: Number of pools: 1
 30 180616 11:28:59 >> log scanned up to (1180581356)
 31 xtrabackup: Generating a list of tablespaces
 32 InnoDB: Allocated tablespace ID 2 for mysql/plugin, old maximum was 0
 33 180616 11:29:00 >> log scanned up to (1180581356)
 34 180616 11:29:00 [01] Copying ./ibdata1 to /data/backup/2018-06-16_11-28-59/ibdata1
 35 180616 11:29:01 >> log scanned up to (1180581356)
 36 180616 11:29:02 >> log scanned up to (1180581356)
 37 180616 11:29:03 >> log scanned up to (1180581356)
 38 180616 11:29:03 [01]        ...done
 39 180616 11:29:04 >> log scanned up to (1180581356)
 40 180616 11:29:04 [01] Copying ./mysql/plugin.ibd to /data/backup/2018-06-16_11-28-59/mysql/plugin.ibd
 41 180616 11:29:04 [01]        ...done
 42 180616 11:29:04 [01] Copying ./mysql/servers.ibd to /data/backup/2018-06-16_11-28-59/mysql/servers.ibd
 43 180616 11:29:04 [01]        ...done
 44 -- To simplify the output,I omitt the other innodb *.ibd file copy contents.
 45 180616 11:29:05 [01] Copying ./zlm/t1.ibd to /data/backup/2018-06-16_11-28-59/zlm/t1.ibd
 46 180616 11:29:05 [01]        ...done
 47 180616 11:29:05 [01] Copying ./zlm/t2.ibd to /data/backup/2018-06-16_11-28-59/zlm/t2.ibd
 48 180616 11:29:05 [01]        ...done
 49 180616 11:29:05 [01] Copying ./zlm/t3.ibd to /data/backup/2018-06-16_11-28-59/zlm/t3.ibd
 50 180616 11:29:05 [01]        ...done
 51 180616 11:29:05 [01] Copying ./zlm/test_flashbk.ibd to /data/backup/2018-06-16_11-28-59/zlm/test_flashbk.ibd
 52 180616 11:29:06 >> log scanned up to (1180581356)
 53 180616 11:29:06 [01]        ...done
 54 180616 11:29:07 >> log scanned up to (1180581356)
 55 180616 11:29:07 [01] Copying ./zlm/test.ibd to /data/backup/2018-06-16_11-28-59/zlm/test.ibd
 56 180616 11:29:07 [01]        ...done
 57 180616 11:29:08 >> log scanned up to (1180581356)
 58 180616 11:29:08 [01] Copying ./zlm/semi_sync_test.ibd to /data/backup/2018-06-16_11-28-59/zlm/semi_sync_test.ibd
 59 180616 11:29:08 [01]        ...done
 60 180616 11:29:09 >> log scanned up to (1180581356)
 61 180616 11:29:09 Executing FLUSH NO_WRITE_TO_BINLOG TABLES...
 62 180616 11:29:09 Executing FLUSH TABLES WITH READ LOCK... -- Add whole instance read-only lock on tables(but not table locks.)
 63 180616 11:29:09 Starting to backup non-InnoDB tables and files
 64 180616 11:29:09 [01] Copying ./mysql/db.opt to /data/backup/2018-06-16_11-28-59/mysql/db.opt
 65 180616 11:29:09 [01]        ...done
 66 180616 11:29:09 [01] Copying ./mysql/db.frm to /data/backup/2018-06-16_11-28-59/mysql/db.frm
 67 180616 11:29:09 [01]        ...done
 68 180616 11:29:09 [01] Copying ./mysql/db.MYI to /data/backup/2018-06-16_11-28-59/mysql/db.MYI
 69 180616 11:29:09 [01]        ...done
 70 180616 11:29:09 [01] Copying ./mysql/db.MYD to /data/backup/2018-06-16_11-28-59/mysql/db.MYD
 71 -- Omitt portion of the contents.
 72 180616 11:29:10 [01] Copying ./performance_schema/file_summary_by_event_name.frm to /data/backup/2018-06-16_11-28-59/performance_schema/file_summary_by_event_name.frm
 73 180616 11:29:10 >> log scanned up to (1180581356)
 74 180616 11:29:10 [01]        ...done
 75 180616 11:29:10 [01] Copying ./performance_schema/file_summary_by_instance.frm to /data/backup/2018-06-16_11-28-59/performance_schema/file_summary_by_instance.frm
 76 180616 11:29:10 [01]        ...done
 77 -- Omitt portion of the contents.
 78 180616 11:29:11 [01] Copying ./sys/waits_by_user_by_latency.frm to /data/backup/2018-06-16_11-28-59/sys/waits_by_user_by_latency.frm
 79 180616 11:29:11 [01]        ...done
 80 180616 11:29:11 >> log scanned up to (1180581356)
 81 180616 11:29:11 [01] Copying ./sys/x@0024innodb_lock_waits.frm to /data/backup/2018-06-16_11-28-59/sys/x@0024innodb_lock_waits.frm
 82 180616 11:29:11 [01]        ...done
 83 -- Omitt portion of the contents.
 84 180616 11:29:12 [01] Copying ./zlm/db.opt to /data/backup/2018-06-16_11-28-59/zlm/db.opt
 85 180616 11:29:12 [01]        ...done
 86 180616 11:29:12 [01] Copying ./zlm/t1.frm to /data/backup/2018-06-16_11-28-59/zlm/t1.frm
 87 180616 11:29:12 [01]        ...done
 88 180616 11:29:12 [01] Copying ./zlm/t2.frm to /data/backup/2018-06-16_11-28-59/zlm/t2.frm
 89 180616 11:29:12 [01]        ...done
 90 180616 11:29:12 [01] Copying ./zlm/t3.frm to /data/backup/2018-06-16_11-28-59/zlm/t3.frm
 91 180616 11:29:12 [01]        ...done
 92 180616 11:29:12 [01] Copying ./zlm/test_flashbk.frm to /data/backup/2018-06-16_11-28-59/zlm/test_flashbk.frm
 93 180616 11:29:12 [01]        ...done
 94 180616 11:29:12 [01] Copying ./zlm/test.frm to /data/backup/2018-06-16_11-28-59/zlm/test.frm
 95 180616 11:29:12 [01]        ...done
 96 180616 11:29:12 [01] Copying ./zlm/semi_sync_test.frm to /data/backup/2018-06-16_11-28-59/zlm/semi_sync_test.frm
 97 180616 11:29:12 [01]        ...done
 98 180616 11:29:12 [00] Writing -help/db.opt
 99 180616 11:29:12 [00]        ...done
100 180616 11:29:12 Finished backing up non-InnoDB tables and files
101 180616 11:29:12 >> log scanned up to (1180581356)
102 180616 11:29:12 [00] Writing xtrabackup_binlog_info
103 180616 11:29:12 [00]        ...done
104 180616 11:29:12 Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS...
105 xtrabackup: The latest check point (for incremental): '1180581347'
106 xtrabackup: Stopping log copying thread.
107 .180616 11:29:12 >> log scanned up to (1180581356)
108 
109 180616 11:29:12 Executing UNLOCK TABLES  -- Release the locks.
110 180616 11:29:12 All tables unlocked
111 180616 11:29:12 [00] Copying ib_buffer_pool to /data/backup/2018-06-16_11-28-59/ib_buffer_pool
112 180616 11:29:12 [00]        ...done
113 180616 11:29:12 Backup created in directory '/data/backup/2018-06-16_11-28-59'
114 MySQL binlog position: filename 'mysql-bin.000046', position '194', GTID of the last change '2a4b3562-2ab6-11e8-be7a-080027de0e0e:1-2700058'
115 180616 11:29:12 [00] Writing backup-my.cnf
116 180616 11:29:12 [00]        ...done
117 180616 11:29:12 [00] Writing xtrabackup_info
118 180616 11:29:12 [00]        ...done
119 xtrabackup: Transaction log of lsn (1180581347) to (1180581356) was copied. -- Point the incremental lsn.
120 180616 11:29:12 completed OK!
121 
122 There's an error at the beginning of the backup output below:
123 
124 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.
125 BEGIN failed--compilation aborted at - line 693.
126 
127 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.
128 
129 The backup is normally generated,too.see details below.
130 
131 [root@zlm1 11:28:16 ~]
132 #ls -l /data/backup
133 total 4
134 drwxr-x--- 7 root root 4096 Jun 16 11:29 2018-06-16_11-28-59 -- All the backup data and informations stored in this directory.
135 
136 [root@zlm1 11:30:27 /data/backup]
137 #cd /data/backup/2018-06-16_11-28-59/
138 
139 [root@zlm1 11:30:57 /data/backup/2018-06-16_11-28-59]
140 #ls -l
141 total 102456
142 -rw-r----- 1 root root       433 Jun 16 11:29 backup-my.cnf
143 drwxr-x--- 2 root root        19 Jun 16 11:29 -help
144 -rw-r----- 1 root root       728 Jun 16 11:29 ib_buffer_pool
145 -rw-r----- 1 root root 104857600 Jun 16 11:29 ibdata1
146 drwxr-x--- 2 root root      4096 Jun 16 11:29 mysql
147 drwxr-x--- 2 root root      8192 Jun 16 11:29 performance_schema
148 drwxr-x--- 2 root root      8192 Jun 16 11:29 sys
149 -rw-r----- 1 root root        68 Jun 16 11:29 xtrabackup_binlog_info -- Contains the binlog position info.
150 -rw-r----- 1 root root       119 Jun 16 11:29 xtrabackup_checkpoints -- Contains teh checkpoint info.
151 -rw-r----- 1 root root       596 Jun 16 11:29 xtrabackup_info -- Contains the Whole info.
152 -rw-r----- 1 root root      2560 Jun 16 11:29 xtrabackup_logfile -- It's a binary file which contains logfile info.
153 drwxr-x--- 2 root root      4096 Jun 16 11:29 zlm
154 
155 [root@zlm1 11:30:58 /data/backup/2018-06-16_11-28-59]
156 #cat xtrabackup_binlog_info
157 mysql-bin.000046    194    2a4b3562-2ab6-11e8-be7a-080027de0e0e:1-2700058
158 
159 [root@zlm1 11:34:09 /data/backup/2018-06-16_11-28-59]
160 #cat xtrabackup_checkpoints
161 backup_type = full-backuped
162 from_lsn = 0
163 to_lsn = 1180581347
164 last_lsn = 1180581356
165 compact = 0
166 recover_binlog_info = 0
167 
168 [root@zlm1 11:34:12 /data/backup/2018-06-16_11-28-59]
169 #cat xtrabackup_info
170 uuid = bad60a4a-7147-11e8-978f-080027de0e0e
171 name = 
172 tool_name = innobackupex
173 tool_command = --defaults-file=/data/mysql/mysql3306/my3306.cnf --user=bkuser --password=... /data/backup
174 tool_version = 2.4.4
175 ibbackup_version = 2.4.4
176 server_version = 5.7.21-log
177 start_time = 2018-06-16 11:28:59
178 end_time = 2018-06-16 11:29:12
179 lock_time = 0
180 binlog_pos = filename 'mysql-bin.000046', position '194', GTID of the last change '2a4b3562-2ab6-11e8-be7a-080027de0e0e:1-2700058'
181 innodb_from_lsn = 0
182 innodb_to_lsn = 1180581347
183 partial = N
184 incremental = N
185 format = file
186 compact = N
187 compressed = N
188 encrypted = N
189 
190 [root@zlm1 11:34:18 /data/backup/2018-06-16_11-28-59]
191 #strings xtrabackup_logfile
192 xtrabkup 180616 11:28:59
193 :/mysql/gtid_executed.ibd
194 F^<.
195  $2a4b3562-2ab6-11e8-be7a-080027de0e0e
196 ./mysql/gtid_executed.ibd
197 ./mysql/gtid_executed.ibd
198 F^=K8
199 F^=s8
200 F^=|
201 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.

 

 1 [root@zlm1 11:35:01 /data/mysql/mysql3306/data]
 2 #cat zlm1.log
 3 
 4 2018-06-16T09:28:59.361317Z        5 Connect    bkuser@localhost on  using Socket
 5 2018-06-16T09:28:59.361444Z        5 Query    SET SESSION wait_timeout=2147483
 6 2018-06-16T09:28:59.361590Z        5 Query    SHOW VARIABLES
 7 2018-06-16T09:28:59.364391Z        5 Query    SHOW ENGINE INNODB STATUS
 8 2018-06-16T09:29:09.442559Z        5 Query    SET SESSION lock_wait_timeout=31536000
 9 2018-06-16T09:29:09.442693Z        5 Query    FLUSH NO_WRITE_TO_BINLOG TABLES -- Finish *.idb file copy operation.
10 2018-06-16T09:29:09.444503Z        5 Query    FLUSH TABLES WITH READ LOCK -- There is also FTWRL lock here.
11 2018-06-16T09:29:12.361073Z        5 Query    SHOW MASTER STATUS
12 2018-06-16T09:29:12.361330Z        5 Query    SHOW VARIABLES
13 2018-06-16T09:29:12.384043Z        5 Query    FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS  -- Finish redo copy operation.
14 2018-06-16T09:29:12.587336Z        5 Query    UNLOCK TABLES -- Release the locks on table.It has spent three seconds in all to hold FTWRL.
15 2018-06-16T09:29:12.600570Z        5 Query    SELECT UUID()
16 2018-06-16T09:29:12.600794Z        5 Query    SELECT VERSION()
17 2018-06-16T09:29:12.804252Z        5 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.