Percona XtraBackup热备份实践
生活随笔
收集整理的這篇文章主要介紹了
Percona XtraBackup热备份实践
小編覺得挺不錯的,現(xiàn)在分享給大家,幫大家做個參考.
實驗環(huán)境及操作步驟:
一、操作系統(tǒng)
| 1 2 3 4 5 | [root@DB-SERVER?~]#?cat?/etc/redhat-release? CentOS?release?6.8?(Final) [root@DB-SERVER?~]#?uname?-a Linux?DB-SERVER?2.6.32-642.el6.x86_64?#1?SMP?Tue?May?10?17:27:01?UTC?2016?x86_64?x86_64?x86_64?GNU/Linux [root@DB-SERVER?~]# |
二、MySQL和percona版本及安裝
| 1 2 3 4 5 6 | [root@DB-SERVER?tools]#?ll total?5 -rw-r--r--??1?root?root????5691656?Apr??5??2015?cmake-2.8.8.tar.gz -rw-r--r--??1?root?root???24596474?Apr??5??2015?mysql-5.5.32.tar.gz -rw-r--r--??1?root?root????5664452?Oct?17??2015?percona-xtrabackup-2.3.2-1.el6.x86_64.rpm [root@DB-SERVER?tools]# |
①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 41 | [root@DB-SERVER?~]#?cd?/opt/tools/ [root@DB-SERVER?tools]#?tar?xf?cmake-2.8.8.tar.gz [root@DB-SERVER?cmake-2.8.8]#?./configure [root@DB-SERVER?cmake-2.8.8]#?gmake [root@DB-SERVER?cmake-2.8.8]#?gmake?install [root@DB-SERVER?cmake-2.8.8]#?cd?.. [root@DB-SERVER?tools]#?yum?install?ncurses-devel?-y [root@DB-SERVER?tools]#groupadd?mysql [root@DB-SERVER?tools]#useradd?mysql?-s?/sbin/nologin?-M?-g?mysql [root@DB-SERVER?tools]#tar?zxf?mysql-5.5.32.tar.gz? [root@DB-SERVER?tools]#cd?mysql-5.5.32 [root@DB-SERVER?mysql-5.5.32]# cmake?.?-DCMAKE_INSTALL_PREFIX=/application/mysql-5.5.32?\ -DMYSQL_DATADIR=/application/mysql-5.5.32/data?\ -DMYSQL_UNIX_ADDR=/application/mysql-5.5.32/tmp/mysql.sock?\ -DDEFAULT_CHARSET=utf8?\ -DDEFAULT_COLLATION=utf8_general_ci?\ -DEXTRA_CHARSETS=gbk,gb2312,utf8,ascii?\ -DENABLED_LOCAL_INFILE=ON?\ -DWITH_INNOBASE_STORAGE_ENGINE=1?\ -DWITH_FEDERATED_STORAGE_ENGINE=1?\ -DWITH_BLACKHOLE_STORAGE_ENGINE=1?\ -DWITHOUT_EXAMPLE_STORAGE_ENGINE=1?\ -DWITHOUT_PARTITION_STORAGE_ENGINE=1?\ -DWITH_FAST_MUTEXES=1?\ -DWITH_ZLIB=bundled?\ -DENABLED_LOCAL_INFILE=1?\ -DWITH_READLINE=1?\ -DWITH_EMBEDDED_SERVER=1?\ -DWITH_DEBUG=0 [root@DB-SERVER?mysql-5.5.32]#make?&&?make?install [root@DB-SERVER?mysql-5.5.32]#cd?.. [root@DB-SERVER?tools]#cp?mysql-5.5.32/support-files/my-small.cnf?/etc/my.cnf [root@DB-SERVER?tools]#echo?'export?PATH=/application/mysql/bin:$PATH'>>/etc/profile [root@DB-SERVER?tools]#chown?-R?mysql:mysql?/application/mysql/data/ [root@DB-SERVER?tools]#cd?/application/mysql/scripts/ [root@DB-SERVER?scripts]#./mysql_install_db?--basedir=/application/mysql?--datadir=/application/mysql/data/?--user=mysql [root@DB-SERVER?scripts]#cd?/opt/tools/mysql-5.5.32 [root@DB-SERVER?mysql-5.5.32]#cp?support-files/mysql.server?/etc/init.d/mysqld [root@DB-SERVER?mysql-5.5.32]#chmod?+x?/etc/init.d/mysqld [root@DB-SERVER?mysql-5.5.32]#netstat?-lntup|grep?3306tcp????????0??????0?0.0.0.0:3306????????????????0.0.0.0:*???????????????????LISTEN??????18591/mysqld???????[root@DB-SERVER?mysql-5.5.32]# |
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 | [client] port????????????=?3306 socket??????????=?/application/mysql-5.5.32/tmp/mysql.sock [mysqld] port????????????=?3306 socket??????????=?/application/mysql-5.5.32/tmp/mysql.sock datadir?????????=?/application/mysql-5.5.32/data basedir?????????=?/application/mysql-5.5.32 tmpdir?????????=?/application/mysql-5.5.32/tmp skip-external-locking key_buffer_size?=?16K max_allowed_packet?=?1M table_open_cache?=?4 sort_buffer_size?=?64K read_buffer_size?=?256K read_rnd_buffer_size?=?256K net_buffer_length?=?2K thread_stack?=?128K innodb_log_file_size?=?256M?#沒有這個參數(shù)恢復后啟動會報錯，所以這里設置下innodb_log_file_size=256 server-id???????=?1 [mysqldump] quick max_allowed_packet?=?16M [mysql] no-auto-rehash [myisamchk] key_buffer_size?=?8M sort_buffer_size?=?8M [mysqlhotcopy] interactive-timeout |
鑒于沒有設置innodb_log_file_size出現(xiàn)錯誤為:
InnoDB: Error: log file ./ib_logfile0 is of different size 0 5242880 bytes
解決方法:
對于使用了默認 my.cnf（一般教程都會教你使用support-files/my-medium.cnf）的Mysql服務來說
如果中間使用了innodb的話，innodb默認的log file大小是56M
如果你的配置文件使用了類似my-innodb-heavy-4G.cnf作為配置文件的話。
Mysql可以正常啟動，但innodb的表無法使用
在錯誤日志里你會看到如下輸出：
InnoDB: Error: log file ./ib_logfile0 is of different size 0 5242880 bytes
現(xiàn)在需要做的事情就是把原來的 innodb 的ib_logfile×備份到一個目錄下，然后刪除掉原來的文件，重啟 mysql。
你會看到ib_logfile*大小變成了你配置文件中指定的大小。
my-innodb-heavy-4G.cnf的話（log file 的大小是256M：innodb_log_file_size = 256M）
你會看到很多個268435456大小的文件。
所以我在MySQL的配置文件中使用了該參數(shù)，并設置為256M
修改root登陸了密碼:
| 1 2 | [root@DB-SERVER?mysql-5.5.32]#/application/mysql/bin/mysqladmin?-u?root?password?'new-password' [root@DB-SERVER?mysql-5.5.32]# |
②percona的安裝
| 1 2 3 | [root@DB-SERVER?~]#?cd?/opt/tools/ [root@DB-SERVER?tools]#?wget?https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.3.2/binary/redhat/6/x86_64/percona-xtrabackup-2.3.2-1.el6.x86_64.rpm? [root@DB-SERVER?tools]# |
安裝依賴庫
| 1 2 3 4 5 6 7 | [root@DB-SERVER?tools]#yum?-y?install?perl?perl-devel?libaio?libaio-devel?perl-Time-HiRes?perl-DBD-MySQL [root@DB-SERVER?tools]#?wget?ftp://rpmfind.net/linux/dag/redhat/el6/en/x86_64/dag/RPMS/libev-4.15-1.el6.rf.x86_64.rpm? [root@DB-SERVER?tools]#rpm?-ivh?libev-4.15-1.el6.rf.x86_64.rpm [root@DB-SERVER?tools]#rpm?-ivh?percona-xtrabackup-2.3.2-1.el6.x86_64.rpm [root@DB-SERVER?tools]#mkdir?-p?/databackup/xtrabackup [root@DB-SERVER?tools]#mkdir?-p?/databackup/xtrabackuplog [root@DB-SERVER?tools] |
③創(chuàng)建用于實踐的數(shù)據(jù)庫并插入數(shù)據(jù)
| 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 41 42 43 44 | CREATE?DATABASE?opark; CREATE?TABLE?`person`?( ??`number`?int(11)?DEFAULT?NULL, ??`name`?varchar(255)?DEFAULT?NULL, ??`birthday`?date?DEFAULT?NULL )?ENGINE=INNODB?DEFAULT?CHARSET=utf8; INSERT?INTO?person?(number,name,birthday)?VALUES?("0001",?"John?Poul",?NOW()); INSERT?INTO?person?(number,name,birthday)?VALUES?("0002",?"John?Hock",?NOW()); INSERT?INTO?person?(number,name,birthday)?VALUES?("0003",?"Rick?Hock",?NOW()); INSERT?INTO?person?(number,name,birthday)?VALUES?("0004",?"Rick?stone",?NOW()); INSERT?INTO?person?(number,name,birthday)?VALUES?("0005",?"John?Green",?NOW()); INSERT?INTO?person?(number,name,birthday)?VALUES?("0006",?"John?Halk",?NOW()); INSERT?INTO?person?(number,name,birthday)?VALUES?("0007",?"Rick?rose",?NOW()); INSERT?INTO?person?(number,name,birthday)?VALUES?("0008",?"Rick?kate",?NOW()); mysql>?show?databases; +--------------------+ |?Database???????????| +--------------------+ |?information_schema?| |?mysql??????????????| |?opark??????????????| |?performance_schema?| |?test???????????????| +--------------------+ 5?rows?in?set?(0.00?sec) mysql>?use?opark; Database?changed mysql>?select?*?from?person; +--------+------------+------------+ |?number?|?name???????|?birthday???| +--------+------------+------------+ |??????1?|?John?Poul??|?2016-09-18?| |??????2?|?John?Hock??|?2016-09-18?| |??????3?|?Rick?Hock??|?2016-09-18?| |??????4?|?Rick?stone?|?2016-09-18?| |??????5?|?John?Green?|?2016-09-18?| |??????6?|?John?Halk??|?2016-09-18?| |??????7?|?Rick?rose??|?2016-09-18?| |??????8?|?Rick?kate??|?2016-09-18?| +--------+------------+------------+ 8?rows?in?set?(0.01?sec) mysql>? ④創(chuàng)建備份用戶和授權 mysql>grant?SELECT,RELOAD,SHOW?DATABASES,SUPER,LOCK?TABLES,REPLICATION?CLIENT,SHOW?VIEW,EVENT,FILE?on?*.*?to?backup@'localhost'?identified?by?'MANAGER'; |
三、實戰(zhàn)備份操作
（1）全備與恢復
?①.全備操作
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 | [root@DB-SERVER?~]#?cd?/databackup/ [root@DB-SERVER?databackup]#?ll total?4 drwx------?6?root?root?4096?Sep?18?01:40?2016-09-18_01-40-28 [root@DB-SERVER?databackup]#? [root@DB-SERVER?databackup]#?innobackupex?--user=backup?--password='MANAGER'?/databackup/ 160918?02:48:10?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!". 160918?02:48:13?Backup?created?in?directory?'/databackup//2016-09-18_02-48-10' 160918?02:48:13?[00]?Writing?backup-my.cnf 160918?02:48:13?[00]????????...done 160918?02:48:13?[00]?Writing?xtrabackup_info 160918?02:48:13?[00]????????...done xtrabackup:?Transaction?log?of?lsn?(1609238)?to?(1609238)?was?copied. 160918?02:48:13?completed?OK! [root@DB-SERVER?databackup]# [root@DB-SERVER?databackup]#?ll total?8 drwx------?6?root?root?4096?Sep?18?01:40?2016-09-18_01-40-28 drwx------?6?root?root?4096?Sep?18?02:48?2016-09-18_02-48-10 [root@DB-SERVER?databackup]# |
②.全備恢復操作
對于一般恢復，都是直接用備份文件還原，如果我們這里也是直接運用該備份文件，則可能會導致一些意想不到的問題，比如:備份的數(shù)據(jù)中可能會包含尚未提交的事務或已經(jīng)提交但尚未同步至數(shù)據(jù)文件中的事務。因此，此時數(shù)據(jù)文件處于不一致的狀態(tài)，我們現(xiàn)在就是要通過回滾未提交的事務及同步已經(jīng)提交的事務至數(shù)據(jù)文件也使得數(shù)據(jù)文件處于一致性狀態(tài)。所以我們要用如下命令進行檢查
| 1 2 3 4 5 6 7 8 9 10 | [root@DB-SERVER?databackup]#?innobackupex?--apply-log?/databackup/2016-09-18_02-48-10/ InnoDB:?128?rollback?segment(s)?are?active. InnoDB:?Waiting?for?purge?to?start InnoDB:?5.6.24?started;?log?sequence?number?1609740 xtrabackup:?starting?shutdown?with?innodb_fast_shutdown?=?1 InnoDB:?FTS?optimize?thread?exiting. InnoDB:?Starting?shutdown... InnoDB:?Shutdown?completed;?log?sequence?number?1609750 160918?02:53:59?completed?OK! [root@DB-SERVER?databackup]# |
關閉數(shù)據(jù)庫，模擬數(shù)據(jù)丟失，可以將MySQL的數(shù)據(jù)目錄刪除，我們這里為了演示方便，直接把數(shù)據(jù)目錄改名
| 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 | [root@DB-SERVER?~]#?/etc/init.d/mysld?stop Shutting?down?MySQL.?SUCCESS!? [root@DB-SERVER?~]#?cd?/application/mysql-5.5.32/ [root@DB-SERVER?mysql-5.5.32]#?ll total?84 drwxr-xr-x??2?mysql?mysql??4096?Sep?17?23:36?bin -rw-r--r--??1?mysql?mysql?17987?Jul??2??2013?COPYING drwx------??6?mysql?mysql??4096?Sep?18?02:54?data drwxr-xr-x??6?mysql?mysql??4096?Sep?18?01:40?data.backup drwxr-xr-x??2?mysql?mysql??4096?Sep?17?23:36?docs drwxr-xr-x??3?mysql?mysql??4096?Sep?17?23:36?include -rw-r--r--??1?mysql?mysql??7470?Jul??2??2013?INSTALL-BINARY drwxr-xr-x??3?mysql?mysql??4096?Sep?17?23:36?lib drwxr-xr-x??4?mysql?mysql??4096?Sep?17?23:36?man drwxr-xr-x?10?mysql?mysql??4096?Sep?17?23:36?mysql-test -rw-r--r--??1?mysql?mysql??2496?Jul??2??2013?README drwxr-xr-x??2?mysql?mysql??4096?Sep?17?23:36?scripts drwxr-xr-x?27?mysql?mysql??4096?Sep?17?23:36?share drwxr-xr-x??4?mysql?mysql??4096?Sep?17?23:36?sql-bench drwxr-xr-x??3?mysql?mysql??4096?Sep?17?23:36?support-files drwxr-xr-x??2?mysql?root???4096?Sep?18?02:54?tmp [root@DB-SERVER?mysql-5.5.32]#?mv?data?data.backup [root@DB-SERVER?mysql-5.5.32]#?mkdir?data [root@DB-SERVER?mysql-5.5.32]# [root@DB-SERVER?mysql-5.5.32]#?ll?data total?2 [root@DB-SERVER?mysql-5.5.32]# |
目錄data下沒有數(shù)據(jù)，接下來執(zhí)行恢復操作
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 | [root@DB-SERVER?databackup]#?innobackupex?--copy-back?/databackup/2016-09-18_02-48-10/ 160918?02:57:41?innobackupex:?Starting?the?copy-back?operation IMPORTANT:?Please?check?that?the?copy-back?run?completes?successfully. ???????????At?the?end?of?a?successful?copy-back?run?innobackupex ???????????prints?"completed?OK!". innobackupex?version?2.3.2?based?on?MySQL?server?5.6.24?Linux?(x86_64)?(revision?id:?306a2e0) 160918?02:57:41?[01]?Copying?ib_logfile0?to?/application/mysql-5.5.32/data/ib_logfile0 160918?02:57:41?[01]????????...done 160918?02:57:42?[01]?Copying?./opark/db.opt?to?/application/mysql-5.5.32/data/opark/db.opt 160918?02:57:42?[01]????????...done 160918?02:57:42?[01]?Copying?./test/db.opt?to?/application/mysql-5.5.32/data/test/db.opt 160918?02:57:42?[01]????????...done 160918?02:57:42?completed?OK! [root@DB-SERVER?databackup]# |
再看MySQL數(shù)據(jù)目錄，數(shù)據(jù)目錄data下數(shù)據(jù)已經(jīng)過來了
| 1 2 3 4 5 6 7 8 9 10 11 | [root@DB-SERVER?mysql-5.5.32]#?ll?data total?116756 -rw-r-----?1?root?root?18874368?Sep?18?02:57?ibdata1 -rw-r-----?1?root?root?50331648?Sep?18?02:57?ib_logfile0 -rw-r-----?1?root?root?50331648?Sep?18?02:57?ib_logfile1 drwx------?2?root?root?????4096?Sep?18?02:57?mysql drwx------?2?root?root?????4096?Sep?18?02:57?opark drwx------?2?root?root?????4096?Sep?18?02:57?performance_schema drwx------?2?root?root?????4096?Sep?18?02:57?test -rw-r-----?1?root?root??????429?Sep?18?02:57?xtrabackup_info [root@DB-SERVER?mysql-5.5.32]# |
啟動數(shù)據(jù)庫
| 1 2 | [root@DB-SERVER?mysql-5.5.32]#?/etc/init.d/mysld?start Starting?MySQL.?ERROR!?The?server?quit?without?updating?PID?file?(/application/mysql-5.5.32/data/DB-SERVER.pid). |
進一步查看錯誤日志:
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | [root@DB-SERVER?data]#?cat?DB-SERVER.err? 160918?18:04:18?mysqld_safe?Starting?mysqld?daemon?with?databases?from?/application/mysql-5.5.32/data 160918?18:04:18?[Note]?Plugin?'FEDERATED'?is?disabled. /application/mysql-5.5.32/bin/mysqld:?Can't?find?file:?'./mysql/plugin.frm'?(errno:?13) 160918?18:04:18?[ERROR]?Can't?open?the?mysql.plugin?table.?Please?run?mysql_upgrade?to?create?it. 160918?18:04:18?InnoDB:?The?InnoDB?memory?heap?is?disabled 160918?18:04:18?InnoDB:?Mutexes?and?rw_locks?use?GCC?atomic?builtins 160918?18:04:18?InnoDB:?Compressed?tables?use?zlib?1.2.3 160918?18:04:18?InnoDB:?Initializing?buffer?pool,?size?=?128.0M 160918?18:04:18?InnoDB:?Completed?initialization?of?buffer?pool 160918?18:04:18??InnoDB:?Operating?system?error?number?13?in?a?file?operation. InnoDB:?The?error?means?mysqld?does?not?have?the?access?rights?to InnoDB:?the?directory. InnoDB:?File?name?./ibdata1 InnoDB:?File?operation?call:?'open'. InnoDB:?Cannot?continue?operation. 160918?18:04:18?mysqld_safe?mysqld?from?pid?file?/application/mysql-5.5.32/data/DB-SERVER.pid?ended [root@DB-SERVER?mysql-5.5.32]# |
出現(xiàn)這樣的錯誤一般就是data目錄下的數(shù)據(jù)用戶名和和屬組不是mysql，所以這里修改下:
| 1 2 3 4 5 6 7 8 9 10 11 12 13 | [root@DB-SERVER?mysql-5.5.32]#?chown?-R?mysql.mysql?data [root@DB-SERVER?mysql-5.5.32]#?ll?data total?116760 -rw-r-----?1?mysql?mysql?????1025?Sep?18?03:00?DB-SERVER.err -rw-r-----?1?mysql?mysql?18874368?Sep?18?02:57?ibdata1 -rw-r-----?1?mysql?mysql?50331648?Sep?18?02:57?ib_logfile0 -rw-r-----?1?mysql?mysql?50331648?Sep?18?02:57?ib_logfile1 drwx------?2?mysql?mysql?????4096?Sep?18?02:57?mysql drwx------?2?mysql?mysql?????4096?Sep?18?02:57?opark drwx------?2?mysql?mysql?????4096?Sep?18?02:57?performance_schema drwx------?2?mysql?mysql?????4096?Sep?18?02:57?test -rw-r-----?1?mysql?mysql??????429?Sep?18?02:57?xtrabackup_info [root@DB-SERVER?mysql-5.5.32]# |
再啟動運行/etc/init.d/mysqld start
| 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 | [root@DB-SERVER?mysql-5.5.32]#?/etc/init.d/mysld?start Starting?MySQL..?SUCCESS!? [root@DB-SERVER?mysql-5.5.32]#?mysql?-uroot?-proot?-hlocalhost Welcome?to?the?MySQL?monitor.??Commands?end?with?;?or?\g. Your?MySQL?connection?id?is?1 Server?version:?5.5.32?Source?distribution Copyright?(c)?2000,?2013,?Oracle?and/or?its?affiliates.?All?rights?reserved. 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??????????????| |?opark??????????????| |?performance_schema?| |?test???????????????| +--------------------+ 5?rows?in?set?(0.00?sec) mysql>?use?opark; Database?changed mysql>?select?*?from?person; +--------+------------+------------+ |?number?|?name???????|?birthday???| +--------+------------+------------+ |??????1?|?John?Poul??|?2016-09-18?| |??????2?|?John?Hock??|?2016-09-18?| |??????3?|?Rick?Hock??|?2016-09-18?| |??????4?|?Rick?stone?|?2016-09-18?| |??????5?|?John?Green?|?2016-09-18?| |??????6?|?John?Halk??|?2016-09-18?| |??????7?|?Rick?rose??|?2016-09-18?| |??????8?|?Rick?kate??|?2016-09-18?| +--------+------------+------------+ 8?rows?in?set?(0.00?sec) mysql> |
說明:
innobackup的--copy-back選項用于執(zhí)行恢復操作，它是通過復制所有數(shù)據(jù)相關文件至MySQL數(shù)據(jù)目錄，因此，需要清空數(shù)據(jù)目錄。我這里是將其重命名，然后再重建目錄。最主要最后一步是將其權限更改
（2）.增量備份
innobackupex --user=backup --password='MANAGER' --incremental /databackup/incrementdir --incremental-basedir=/databackup/2016-09-18_03-35-06/
①.向數(shù)據(jù)庫中添加數(shù)據(jù):
INSERT INTO person (number,name,birthday) VALUES ("0009", "Reh Hat", NOW());
INSERT INTO person (number,name,birthday) VALUES ("0010", "pyhton study", NOW());
INSERT INTO person (number,name,birthday) VALUES ("0008", "Linux system", NOW());
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | mysql>?select?*?from?person; +--------+--------------+------------+ |?number?|?name?????????|?birthday???| +--------+--------------+------------+ |??????1?|?John?Poul????|?2016-09-18?| |??????2?|?John?Hock????|?2016-09-18?| |??????3?|?Rick?Hock????|?2016-09-18?| |??????4?|?Rick?stone???|?2016-09-18?| |??????5?|?John?Green???|?2016-09-18?| |??????6?|?John?Halk????|?2016-09-18?| |??????7?|?Rick?rose????|?2016-09-18?| |??????8?|?Rick?kate????|?2016-09-18?| |??????9?|?Reh?Hat??????|?2016-09-18?| |?????10?|?pyhton?study?|?2016-09-18?| |??????8?|?Linux?system?|?2016-09-18?| +--------+--------------+------------+ 11?rows?in?set?(0.00?sec) |
②.刪除數(shù)據(jù)
| 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 41 42 | mysql>?delete?from?opark.person?where?name="Reh?Hat"; Query?OK,?1?row?affected?(0.01?sec) mysql>?select?*?from?person; +--------+--------------+------------+ |?number?|?name?????????|?birthday???| +--------+--------------+------------+ |??????1?|?John?Poul????|?2016-09-18?| |??????2?|?John?Hock????|?2016-09-18?| |??????3?|?Rick?Hock????|?2016-09-18?| |??????4?|?Rick?stone???|?2016-09-18?| |??????5?|?John?Green???|?2016-09-18?| |??????6?|?John?Halk????|?2016-09-18?| |??????7?|?Rick?rose????|?2016-09-18?| |??????8?|?Rick?kate????|?2016-09-18?| |?????10?|?pyhton?study?|?2016-09-18?| |??????8?|?Linux?system?|?2016-09-18?| +--------+--------------+------------+ 10?rows?in?set?(0.00?sec) mysql>? [root@DB-SERVER?databackup]#?innobackupex?--user=backup?--password='MANAGER'?--incremental?/databackup/incrementdir?--incremental-basedir=/databackup/2016-09-18_03-09-33 160918?03:23:25?Executing?UNLOCK?TABLES 160918?03:23:25?All?tables?unlocked 160918?03:23:25?Backup?created?in?directory?'/databackup/incrementdir/2016-09-18_03-23-22' 160918?03:23:25?[00]?Writing?backup-my.cnf 160918?03:23:25?[00]????????...done 160918?03:23:25?[00]?Writing?xtrabackup_info 160918?03:23:25?[00]????????...done xtrabackup:?Transaction?log?of?lsn?(1611092)?to?(1611092)?was?copied. 160918?03:23:25?completed?OK! [root@DB-SERVER?databackup]# [root@DB-SERVER?databackup]#?ll?incrementdir/ total?4 drwx------?6?root?root?4096?Sep?18?03:23?2016-09-18_03-23-22 [root@DB-SERVER?databackup]#?ll total?24 drwx------?6?root?root?4096?Sep?18?01:40?2016-09-18_01-40-28 drwx------?6?root?root?4096?Sep?18?02:53?2016-09-18_02-48-10 drwx------?6?root?root?4096?Sep?18?03:05?2016-09-18_03-05-15 drwx------?6?root?root?4096?Sep?18?03:09?2016-09-18_03-09-33 drwxr-xr-x?3?root?root?4096?Sep?18?03:23?incrementdir drwxr-xr-x?2?root?root?4096?Sep?18?03:16?xtrabackuplog [root@DB-SERVER?databackup]# |
其中，--incremental-basedir指的是完全備份所在的目錄，此命令執(zhí)行結束后，innobackupex命令會在/data/backup目錄中創(chuàng)建一個新的以時間命名的目錄以存放所有的增量備份數(shù)據(jù)。另外，在執(zhí)行過增量備份之后再一次進行增量備份時，其--incremental-basedir應該指向上一次的增量備份所在的目錄。
需要注意的是，增量備份僅能應用于InnoDB或XtraDB表，對于MyISAM表而言，執(zhí)行增量備份時其實進行的是完全備份。
③.增量備份的恢復，如果需要恢復的話需要做如下操作
| 1 2 | [root@DB-SERVER?databackup]#?innobackupex?--apply-log?--redo-only?/databackup/2016-09-18_03-35-06/ [root@DB-SERVER?databackup]#?innobackupex?--apply-log?--redo-only?/databackup/2016-09-18_03-35-06/?--incremental-dir=/databackup/incrementdir/2016-09-18_03-38-06/ |
如果存在多次增量備份的話，就需要多次執(zhí)行.如
| 1 2 3 | [root@DB-SERVER?~]#innobackupex?--apply-log?--redo-only?BACKUPDIR? [root@DB-SERVER?~]#innobackupex?--apply-log?--redo-only?BACKUPDIR?--incremental-dir=INCREMENTDIR-1 [root@DB-SERVER?~]#innobackupex?--apply-log?--redo-only?BACKUPDIR?--incremental-dir=INCREMENTDIR-2 |
BACKUP是全備目錄，INCREMENTDIR是增量備份目錄，上面是有2次增量備份，如果存在多次增量備份，則需要多次運行如上的命令
另外一種增量恢復方式為:分別將多次的增量備份依次合并到全量備份中，最后執(zhí)行全量恢復，比如:
| 1 2 3 4 | [root@DB-SERVER?~]#innobackupex?--apply-log?--redo-only?--incremental?/databackup/2016-09-18_03-35-06/?--incremental-dir=/databackup/incrementdir/2016-09-18_03-55-12/ [root@DB-SERVER?~]#innobackupex?--apply-log?--redo-only?--incremental?/databackup/2016-09-18_03-35-06/?--incremental-dir=/databackup/incrementdir/2016-09-18_04-11-30/ [root@DB-SERVER?~]#innobackupex?--apply-log?--redo-only?--incremental?/databackup/2016-09-18_03-35-06/?--incremental-dir=/databackup/incrementdir/2016-09-18_04-17-11/ [root@DB-SERVER?~]#innobackupex?--apply-log?--redo-only?/databackup/2016-09-18_03-35-06/ |
其中:2016-09-18_03-35-06是全備，后面的2016-09-18_03-55-12、2016-09-18_04-11-30、2016-09-18_04-17-11為增量備份
（3）.Xtrabackup的備份壓縮
Xtrabackup對備份的數(shù)據(jù)文件支持“流”功能，即可以將備份的數(shù)據(jù)通過STDOUT傳輸給tar程序進行歸檔，而不是默認的直接保存至某備份目錄中。要使用此功能，僅需要使用--stream選項即可。如：
1
innobackupex --user=backup --password='MANAGER' --stream=tar ?/databackup/ | gzip > /databackup/`date +%F_%H-%M-%S`.tar.gz
? ? ? 本文轉(zhuǎn)自027ryan ?51CTO博客，原文鏈接：http://blog.51cto.com/ucode/1853422，如需轉(zhuǎn)載請自行聯(lián)系原作者
總結
以上是生活随笔為你收集整理的Percona XtraBackup热备份实践的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： java计算集合对称差
- 下一篇： 记一次参加 CrossOver Meet