xtrabackup備份恢復(fù)MySQL數(shù)據(jù)庫
1. 全量備份恢復(fù)：
- 查看原表內(nèi)容：
MariaDB [(none)]> select * from testdb.students;
+----+------------+------+--------+
| id | name | age | gender |
+----+------------+------+--------+
| 1 | zhangsan | 15 | f |
| 2 | lisi | 15 | m |
| 3 | wanger | 25 | m |
| 4 | liuwu | 24 | f |
| 5 | wangermazi | 28 | f |
+----+------------+------+--------+
5 rows in set (0.00 sec)- 備份：
[root@jenkins ~]# innobackupex --user=lxk --host=localhost --password=lxkpass /tmp
180916 11:56:18 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!".
......
中間省略
......
180916 11:56:22 Executing UNLOCK TABLES
180916 11:56:22 All tables unlocked
180916 11:56:22 Backup created in directory '/tmp/2018-09-16_11-56-18'
180916 11:56:22 [00] Writing backup-my.cnf
180916 11:56:22 [00] ...done
180916 11:56:22 [00] Writing xtrabackup_info
180916 11:56:22 [00] ...done
xtrabackup: Transaction log of lsn (1602080) to (1602080) was copied.
180916 11:56:23 completed OK!
[root@jenkins ~]# cat /tmp/2018-09-16_11-56-18/xtrabackup_checkpoints
backup_type = full-backuped #備份類型:全量備份
from_lsn = 0 #起始lsn
to_lsn = 1602080 #結(jié)束lsn
last_lsn = 1602080 #總共多少個(gè)lsn
compact = 0
recover_binlog_info = 0
[root@jenkins ~]# cat /tmp/2018-09-16_11-56-18/xtrabackup_info
uuid = 7a05430c-b964-11e8-889e-000c29080758
name =
tool_name = innobackupex #備份工具名稱
tool_command = --user=lxk --host=localhost --password=... /tmp #備份時(shí)使用的命令
tool_version = 2.3.6 #工具版本
ibbackup_version = 2.3.6
server_version = 5.5.60-MariaDB
start_time = 2018-09-16 11:56:18 #備份開始時(shí)間
end_time = 2018-09-16 11:56:22 #備份結(jié)束時(shí)間
lock_time = 0
binlog_pos =
innodb_from_lsn = 0
innodb_to_lsn = 1602080
partial = N
incremental = N
format = file
compact = N
compressed = N #是否啟用壓縮
encrypted = N #是否加密- 準(zhǔn)備(apply)備份
[root@jenkins ~]# innobackupex --apply-log /tmp/2018-09-16_11-56-18/
180916 12:06:16 innobackupex: Starting the apply-log operation
IMPORTANT: Please check that the apply-log run completes successfully.
At the end of a successful apply-log run innobackupex
prints "completed OK!".
......
中間省略
......
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
InnoDB: FTS optimize thread exiting.
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 1602582
180916 12:06:19 completed OK! #此處顯示completed OK即表示完成
- 恢復(fù)備份:
- 停止MySQL服務(wù)
- 刪庫
[root@jenkins ~]# rm -rf /var/lib/mysql/*- 通過全量備份恢復(fù)數(shù)據(jù)
[root@jenkins ~]# innobackupex --copy-back /tmp/2018-09-16_11-56-18/
180916 12:11:19 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.6 based on MySQL server 5.6.24 Linux (x86_64) (revision id: )
180916 12:11:19 [01] Copying ib_logfile0 to /var/lib/mysql/ib_logfile0
180916 12:11:19 [01] ...done
.....
中間省略
.....
180916 12:11:20 [01] ...done
180916 12:11:20 [01] Copying ./xtrabackup_info to /var/lib/mysql/xtrabackup_info
180916 12:11:20 [01] ...done
180916 12:11:20 [01] Copying ./test/db.opt to /var/lib/mysql/test/db.opt
180916 12:11:20 [01] ...done
180916 12:11:20 completed OK! #顯示completed OK即為完成- 修改恢復(fù)后文件的屬主,屬組為mysql
[root@jenkins ~]# ls /var/lib/mysql -l
total 28692
-rw-r----- 1 root root 18874368 Sep 16 12:11 ibdata1
-rw-r----- 1 root root 5242880 Sep 16 12:11 ib_logfile0
-rw-r----- 1 root root 5242880 Sep 16 12:11 ib_logfile1
drwx------ 2 root root 4096 Sep 16 12:11 mysql
drwx------ 2 root root 4096 Sep 16 12:11 performance_schema
drwx------ 2 root root 4096 Sep 16 12:11 test
drwx------ 2 root root 4096 Sep 16 12:11 testdb
-rw-r----- 1 root root 437 Sep 16 12:11 xtrabackup_info
[root@jenkins ~]# chown -R mysql.mysql /var/lib/mysql/*
[root@jenkins ~]# ll /var/lib/mysql/
total 28692
-rw-r----- 1 mysql mysql 18874368 Sep 16 12:11 ibdata1
-rw-r----- 1 mysql mysql 5242880 Sep 16 12:11 ib_logfile0
-rw-r----- 1 mysql mysql 5242880 Sep 16 12:11 ib_logfile1
drwx------ 2 mysql mysql 4096 Sep 16 12:11 mysql
drwx------ 2 mysql mysql 4096 Sep 16 12:11 performance_schema
drwx------ 2 mysql mysql 4096 Sep 16 12:11 test
drwx------ 2 mysql mysql 4096 Sep 16 12:11 testdb
-rw-r----- 1 mysql mysql 437 Sep 16 12:11 xtrabackup_info- 啟動(dòng)MySQL并查看
[root@jenkins ~]# systemctl start mariadb
[root@jenkins ~]# mysql
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 2
Server version: 5.5.60-MariaDB MariaDB Server
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> select * from testdb.students;
+----+------------+------+--------+
| id | name | age | gender |
+----+------------+------+--------+
| 1 | zhangsan | 15 | f |
| 2 | lisi | 15 | m |
| 3 | wanger | 25 | m |
| 4 | liuwu | 24 | f |
| 5 | wangermazi | 28 | f |
+----+------------+------+--------+
5 rows in set (0.00 sec)2. 增量備份及恢復(fù):
- 全量備份:
[root@jenkins ~]# innobackupex --user=lxk --host=localhost --password=lxkpass /tmp/
180916 12:17:01 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!".
.....
中間省略
.....
180916 12:17:03 Executing UNLOCK TABLES
180916 12:17:03 All tables unlocked
180916 12:17:03 Backup created in directory '/tmp//2018-09-16_12-17-01'
180916 12:17:03 [00] Writing backup-my.cnf
180916 12:17:03 [00] ...done
180916 12:17:03 [00] Writing xtrabackup_info
180916 12:17:03 [00] ...done
xtrabackup: Transaction log of lsn (1602592) to (1602592) was copied.
180916 12:17:03 completed OK!- 修改數(shù)據(jù)庫,進(jìn)行第一次增量備份
- 在testdb.students中添加一條數(shù)據(jù):
MariaDB [testdb]> insert into students values (6,'xiaoming',20,'f');
Query OK, 1 row affected (0.00 sec)
MariaDB [testdb]> select * from students;
+----+------------+------+--------+
| id | name | age | gender |
+----+------------+------+--------+
| 1 | zhangsan | 15 | f |
| 2 | lisi | 15 | m |
| 3 | wanger | 25 | m |
| 4 | liuwu | 24 | f |
| 5 | wangermazi | 28 | f |
| 6 | xiaoming | 20 | f |
+----+------------+------+--------+
6 rows in set (0.00 sec)- 增量備份:
[root@jenkins ~]# innobackupex --incremental /tmp --incremental-basedir=/tmp/2018-09-16_12-17-01/
180916 12:23:28 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!".
.....
中間省略
.....
180916 12:23:30 [00] ...done
xtrabackup: Transaction log of lsn (1602735) to (1602735) was copied.
180916 12:23:30 completed OK!- 再添加一條數(shù)據(jù),進(jìn)行第二次增量備份:
- 增加一條數(shù)據(jù)
MariaDB [testdb]> insert into students values (8,'daming',20,'m');
Query OK, 1 row affected (0.00 sec)- 第二次增量備份(若此時(shí)--incremental-basedir指的是第一次全量備份路徑,則為差異備份):
[root@jenkins ~]# innobackupex --incremental /tmp --incremental-basedir=/tmp/2018-09-16_12-23-28/
180916 12:29:08 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!".
.....
中間省略
.....
180916 12:29:10 [00] Writing xtrabackup_info
180916 12:29:10 [00] ...done
xtrabackup: Transaction log of lsn (1603615) to (1603615) was copied.
180916 12:29:10 completed OK!- 準(zhǔn)備(prepare)數(shù)據(jù):
- 需要在每個(gè)備份(包括完全和各個(gè)增量備份)上，將已經(jīng)提交的事務(wù)進(jìn)行“重放”?！爸胤拧敝螅械膫浞輸?shù)據(jù)將合并到完全備份上。
- 基于所有的備份將未提交的事務(wù)進(jìn)行“回滾”
(1) 準(zhǔn)備全量備份文件
創(chuàng)新互聯(lián)服務(wù)項(xiàng)目包括吳江網(wǎng)站建設(shè)、吳江網(wǎng)站制作、吳江網(wǎng)頁制作以及吳江網(wǎng)絡(luò)營銷策劃等。多年來，我們專注于互聯(lián)網(wǎng)行業(yè)，利用自身積累的技術(shù)優(yōu)勢(shì)、行業(yè)經(jīng)驗(yàn)、深度合作伙伴關(guān)系等，向廣大中小型企業(yè)、政府機(jī)構(gòu)等提供互聯(lián)網(wǎng)行業(yè)的解決方案，吳江網(wǎng)站推廣取得了明顯的社會(huì)效益與經(jīng)濟(jì)效益。目前，我們服務(wù)的客戶以成都為中心已經(jīng)輻射到吳江省份的部分城市，未來相信會(huì)繼續(xù)擴(kuò)大服務(wù)區(qū)域并繼續(xù)獲得客戶的支持與信任！
[root@jenkins tmp]# innobackupex --apply-log --redo-only 2018-09-16_12-17-01
180916 12:34:06 innobackupex: Starting the apply-log operation
IMPORTANT: Please check that the apply-log run completes successfully.
At the end of a successful apply-log run innobackupex
prints "completed OK!".
.....
中間省略
.....
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 1602592
180916 12:34:06 completed OK!(2) 準(zhǔn)備第一次增量備份文件:
注: --incremental-dir所指的目錄必須為絕對(duì)路徑
[root@jenkins 2018-09-16_12-17-01]# innobackupex --apply-log --redo-only ./ --incremental-dir=/tmp/2018-09-16_12-23-28
180916 12:38:17 innobackupex: Starting the apply-log operation
IMPORTANT: Please check that the apply-log run completes successfully.
At the end of a successful apply-log run innobackupex
prints "completed OK!".
.....
中間省略
.....
180916 12:38:18 [00] Copying /tmp/2018-09-16_12-23-28/xtrabackup_info to ./xtrabackup_info
180916 12:38:18 [00] ...done
180916 12:38:18 completed OK!(3) 準(zhǔn)備第二次增量備份文件:
[root@jenkins 2018-09-16_12-17-01]# innobackupex --apply-log --redo-only ./ --incremental-dir=/tmp/2018-09-16_12-29-08/
180916 12:42:56 innobackupex: Starting the apply-log operation
IMPORTANT: Please check that the apply-log run completes successfully.
At the end of a successful apply-log run innobackupex
prints "completed OK!".
.....
中間省略
.....
180916 12:42:57 [01] ...done
180916 12:42:57 [00] Copying /tmp/2018-09-16_12-29-08//xtrabackup_info to ./xtrabackup_info
180916 12:42:57 [00] ...done
180916 12:42:57 completed OK!(4) 執(zhí)行回滾操作
[root@jenkins tmp]# innobackupex --apply-log /tmp/2018-09-16_12-17-01
180916 12:46:15 innobackupex: Starting the apply-log operation
IMPORTANT: Please check that the apply-log run completes successfully.
At the end of a successful apply-log run innobackupex
prints "completed OK!".
.....
中間省略
.....
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
InnoDB: FTS optimize thread exiting.
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 1604128
180916 12:46:18 completed OK!
(5) 關(guān)閉MySQL并刪除/var/lib/mysql/下所有文件
(6) 恢復(fù)數(shù)據(jù)：
[root@jenkins tmp]# innobackupex --copy-back 2018-09-16_12-17-01/
180916 12:48:39 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!".
.....
中間省略
.....
180916 12:48:40 [01] Copying ./test/db.opt to /var/lib/mysql/test/db.opt
180916 12:48:40 [01] ...done
180916 12:48:40 completed OK!(6) 修改/var/lib/mysql/下文件的屬主、屬組并啟動(dòng)數(shù)據(jù)庫并查看
[root@jenkins mysql]# chown -R mysql.mysql /var/lib/mysql/*
[root@jenkins mysql]# ll
total 28692
-rw-r----- 1 mysql mysql 18874368 Sep 16 12:48 ibdata1
-rw-r----- 1 mysql mysql 5242880 Sep 16 12:48 ib_logfile0
-rw-r----- 1 mysql mysql 5242880 Sep 16 12:48 ib_logfile1
drwx------ 2 mysql mysql 4096 Sep 16 12:48 mysql
drwx------ 2 mysql mysql 4096 Sep 16 12:48 performance_schema
drwx------ 2 mysql mysql 4096 Sep 16 12:48 test
drwx------ 2 mysql mysql 4096 Sep 16 12:48 testdb
-rw-r----- 1 mysql mysql 462 Sep 16 12:48 xtrabackup_info
[root@jenkins mysql]# systemctl start mariadb
[root@jenkins mysql]# mysql
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 2
Server version: 5.5.60-MariaDB MariaDB Server
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> select * from testdb.students;
+----+------------+------+--------+
| id | name | age | gender |
+----+------------+------+--------+
| 1 | zhangsan | 15 | f |
| 2 | lisi | 15 | m |
| 3 | wanger | 25 | m |
| 4 | liuwu | 24 | f |
| 5 | wangermazi | 28 | f |
| 6 | xiaoming | 20 | f |
| 8 | daming | 20 | m |
+----+------------+------+--------+
7 rows in set (0.00 sec)
分享題目：xtrabackup備份恢復(fù)MySQL數(shù)據(jù)庫
當(dāng)前網(wǎng)址：http://www.rwnh.cn/article46/jipgeg.html
成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供網(wǎng)站設(shè)計(jì)、標(biāo)簽優(yōu)化、響應(yīng)式網(wǎng)站、定制開發(fā)、網(wǎng)站內(nèi)鏈、微信小程序
聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請(qǐng)盡快告知，我們將會(huì)在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場，如需處理請(qǐng)聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時(shí)需注明來源： 創(chuàng)新互聯(lián)
- 網(wǎng)站生成靜態(tài)頁面是不是會(huì)更好？ 2016-03-07
- 靜態(tài)網(wǎng)站跟動(dòng)態(tài)網(wǎng)站在制作上有哪些區(qū)別呢 2016-11-12
- ?成都網(wǎng)站建設(shè)生成真靜態(tài)有什么好處？ 2016-11-18
- 靜態(tài)網(wǎng)站生成和動(dòng)態(tài)網(wǎng)站生成有什么區(qū)別？ 2021-09-18
- 談?wù)劸W(wǎng)站建設(shè)靜態(tài)網(wǎng)站與動(dòng)態(tài)網(wǎng)站的區(qū)別 2020-07-18
- 靜態(tài)網(wǎng)頁對(duì)seo提升的益處是啥？ 2014-01-20
- URL靜態(tài)化之網(wǎng)站結(jié)構(gòu)優(yōu)化基礎(chǔ)方法 2016-11-07
- 網(wǎng)站建設(shè)的時(shí)候應(yīng)該選擇靜態(tài)頁面還是動(dòng)態(tài)頁面 2016-10-30
- 網(wǎng)站建設(shè)中Apache服務(wù)器上如何使用偽靜態(tài) 2016-08-12
- 什么是靜態(tài)網(wǎng)站、動(dòng)態(tài)網(wǎng)站？有何優(yōu)缺點(diǎn)？ 2016-11-10
- 做動(dòng)態(tài)網(wǎng)站好還是靜態(tài)網(wǎng)站好 2021-10-18
- 靜態(tài)網(wǎng)站設(shè)計(jì)要素 2016-11-04