Xtrabackup由percona提供

做網(wǎng)站、成都網(wǎng)站制作,成都做網(wǎng)站公司-成都創(chuàng)新互聯(lián)公司已向近千家企業(yè)提供了,網(wǎng)站設(shè)計(jì),網(wǎng)站制作,網(wǎng)絡(luò)營(yíng)銷等服務(wù)!設(shè)計(jì)與技術(shù)結(jié)合,多年網(wǎng)站推廣經(jīng)驗(yàn),合理的價(jià)格為您打造企業(yè)品質(zhì)網(wǎng)站。

percona Xtrabackup是一個(gè)自由、開(kāi)源的完整的在線備份工具，支持MySQL、perconna server、mariadb

到官網(wǎng)https://www.percona.com/下載安裝包，并配置好epel源安裝需要依賴libev這個(gè)包

[root@localhost ~]# wget 
 [root@localhost ~]# vim /etc/yum.repos.d/ali-epel.repo
 [epel]
name=ali-epel
baseurl=
gpgcheck=0
enabled=1
[root@localhost ~]# yum install percona-xtrabackup-24-2.4.6-2.el7.x86_64.rpm -y

Xtrabackup的備份是通過(guò)日志序列號(hào)（log sequence number <LSN>）來(lái)實(shí)現(xiàn)的

備份需自行創(chuàng)建備份用戶，賦予備份用戶相應(yīng)的一些權(quán)限（reload;lock tables;replication client;create tablespace;process;super;create;insert;select）

創(chuàng)建備份恢復(fù)用戶：

MariaDB [(none)]> create user 'backup'@'localhost' identified by 'xtrabackup123';
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> grant reload,lock tables,replication client,insert,select,process,super,create,create tablespace on *.* to 'backup'@'localhost';
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.00 sec)

Xtrabackup僅對(duì)InnoDB支持熱備； 查看數(shù)據(jù)庫(kù)信息：

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| hellodb            |
| mysql              |
| performance_schema |
| test               |
+--------------------+
5 rows in set (0.00 sec)

MariaDB [(none)]> use hellodb;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [hellodb]> show table status\G*************************** 1. row ***************************
           Name: classes
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 8
 Avg_row_length: 2048
    Data_length: 16384
Max_data_length: 0
   Index_length: 0
      Data_free: 9437184
 Auto_increment: 9
    Create_time: 2016-07-05 08:16:44
    Update_time: NULL
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options: 
        Comment: 
*************************** 2. row ***************************
           Name: coc
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 14
 Avg_row_length: 1170
    Data_length: 16384
Max_data_length: 0
   Index_length: 0
      Data_free: 9437184
 Auto_increment: 15
    Create_time: 2016-07-05 08:16:44
    Update_time: NULL
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options: 
        Comment: 
*************************** 3. row ***************************
           Name: courses
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 7
 Avg_row_length: 2340
    Data_length: 16384
Max_data_length: 0
   Index_length: 0
      Data_free: 9437184
 Auto_increment: 8
    Create_time: 2016-07-05 08:16:44
    Update_time: NULL
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options: 
        Comment: 
*************************** 4. row ***************************
           Name: scores
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 15
 Avg_row_length: 1092
    Data_length: 16384
Max_data_length: 0
   Index_length: 0
      Data_free: 9437184
 Auto_increment: 16
    Create_time: 2016-07-05 08:16:44
    Update_time: NULL
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options: 
        Comment: 
*************************** 5. row ***************************
           Name: students
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 25
 Avg_row_length: 655
    Data_length: 16384
Max_data_length: 0
   Index_length: 0
      Data_free: 9437184
 Auto_increment: 26
    Create_time: 2016-07-05 08:16:44
    Update_time: NULL
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options: 
        Comment: 
*************************** 6. row ***************************
           Name: teachers
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 4
 Avg_row_length: 4096
    Data_length: 16384
Max_data_length: 0
   Index_length: 0
      Data_free: 9437184
 Auto_increment: 5
    Create_time: 2016-07-05 08:16:44
    Update_time: NULL
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options: 
        Comment: 
*************************** 7. row ***************************
           Name: toc
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 0
 Avg_row_length: 0
    Data_length: 16384
Max_data_length: 0
   Index_length: 0
      Data_free: 9437184
 Auto_increment: 1
    Create_time: 2016-07-05 08:16:44
    Update_time: NULL
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options: 
        Comment: 
7 rows in set (0.00 sec)#全部都是InnoDB的，可以做熱備。

全備：

[root@localhost ~]# mkdir /backupdir
[root@localhost ~]# innobackupex --user='backup' --password='xtrabackup123' /backupdir
[root@localhost ~]# ls /backupdir/
2016-07-05_08-42-50

全備恢復(fù)：

[root@localhost ~]# mysql -e 'drop database hellodb;'       #模擬環(huán)境先將要恢復(fù)的數(shù)據(jù)庫(kù)刪除；
MariaDB [(none)]> show databases;
+--------------------+| Database           |
+--------------------+| information_schema |
| mysql              |
| performance_schema || test               |
+--------------------+4 rows in set (0.00 sec)


[root@localhost ~]# innobackupex --apply-log /backupdir/2016-07-05_08-42-50/
[root@localhost ~]# systemctl stop mariadb
[root@localhost ~]# innobackupex --copy-back /backupdir/2016-07-05_08-42-50/

#驗(yàn)證數(shù)據(jù)庫(kù)有沒(méi)恢復(fù)
[root@localhost ~]# ls /var/lib/mysql/
hellodb  ibdata1  ib_logfile0  ib_logfile1  ibtmp1  mysql  performance_schema  test  xtrabackup_info

MariaDB [(none)]> show databases;+--------------------+| Database           |
+--------------------+| information_schema |
| hellodb            |
| mysql              |
| performance_schema || test               |
+--------------------+5 rows in set (0.00 sec)

增備：

 增備之前要先做全備，因?yàn)樵鰝涫且罁?jù)全備的變化來(lái)做的

[root@localhost ~]# innobackupex --user='backup' --password='xtrabackup123'  /backup/
[root@localhost ~]# ls /backup/2016-07-05_08-28-54

修改數(shù)據(jù)庫(kù)
MariaDB [hellodb]> select * from courses;
+----------+----------------+
| CourseID | Course         |
+----------+----------------+
|        1 | Hamo Gong      |
|        2 | Kuihua Baodian |
|        3 | Jinshe Jianfa  |
|        4 | Taiji Quan     |
|        5 | Daiyu Zanghua  |
|        6 | Weituo Zhang   |
|        7 | Dagou Bangfa   |
+----------+----------------+
7 rows in set (0.00 sec)

MariaDB [hellodb]> insert into courses(Course) values('zhangsan'),('lisi');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

MariaDB [hellodb]> select * from courses;
+----------+----------------+
| CourseID | Course         |
+----------+----------------+
|        1 | Hamo Gong      |
|        2 | Kuihua Baodian |
|        3 | Jinshe Jianfa  |
|        4 | Taiji Quan     |
|        5 | Daiyu Zanghua  |
|        6 | Weituo Zhang   |
|        7 | Dagou Bangfa   |
|        8 | zhangsan       |
|        9 | lisi           |
+----------+----------------+
9 rows in set (0.00 sec)

做增備
[root@localhost ~]# innobackupex --user='backup' --password='xtrabackup123' --incremental /incbackup/ --incremental-basedir=/backup/2016-07-05_08-28-54/
[root@localhost ~]# cat /incbackup/2016-07-05_08-42-06/
xtrabackup_checkpoints backup_type = incremental
from_lsn = 1628321
to_lsn = 1629233
last_lsn = 1629233
compact = 0
recover_binlog_info = 0

全備+增備恢復(fù)：

增備合并到全備，恢復(fù)數(shù)據(jù)的時(shí)候只需要恢復(fù)合并的全備就可以了
[root@localhost ~]# innobackupex --apply-log --redo-only /backup/2016-07-05_08-28-54/
[root@localhost ~]# innobackupex --apply-log --redo-only /backup/2016-07-05_08-28-54/ --incremental-dir=/incbackup/2016-07-05_08-42-06/
[root@localhost ~]# mysql -e 'use hellodb;drop table courses; '
[root@localhost ~]# mysql -e 'use hellodb;
MariaDB [(none)]> show tables; 
'+-------------------+| Tables_in_hellodb |
+-------------------+| classes           |
| coc               |
| scores            |
| students          |
| teachers          || toc               |
+-------------------+
[root@localhost ~]# innobackupex --copy-back /backup/2016-07-05_08-28-54/
MariaDB [(none)]> show databases;
+--------------------+| Database           |
+--------------------+| information_schema |
| hellodb            |
| mysql              |
| performance_schema || test               |
+--------------------+5 rows in set (0.00 sec)

MariaDB [(none)]> use hellodb
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [hellodb]> show tables;
+-------------------+| Tables_in_hellodb |
+-------------------+| classes           |
| coc               |
| courses           |
| scores            |
| students          |
| teachers          || toc               |
+-------------------+7 rows in set (0.00 sec)

MariaDB [hellodb]> select * from courses;
+----------+----------------+| CourseID | Course         |
+----------+----------------+|        1 | Hamo Gong      |
|        2 | Kuihua Baodian |
|        3 | Jinshe Jianfa  |
|        4 | Taiji Quan     |
|        5 | Daiyu Zanghua  |
|        6 | Weituo Zhang   |
|        7 | Dagou Bangfa   |
|        8 | zhangsan       ||        9 | lisi           |
+----------+----------------+9 rows in set (0.00 sec)

innobackupex一些參數(shù)說(shuō)明：

--include:可選定備份的庫(kù)或表，支持正則表達(dá)式

--tables-file:指定一個(gè)文件中所列出的所有表名

--databasea：以上兩種的合并

--stream=tar:以流的方式壓縮備份

[root@localhost ~]# innobackupex --user='backup' --password='xtrabackup123' --include='hellodb' --stream=tar /backup/ | gzip >  /backup/`data +%F_%H_%M%S`.tar.gz

當(dāng)前名稱：Xtrabackup備份mysql數(shù)據(jù)庫(kù)
標(biāo)題URL：http://chinadenli.net/article48/geoihp.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供搜索引擎優(yōu)化、網(wǎng)站改版、外貿(mào)建站、品牌網(wǎng)站制作、手機(jī)網(wǎng)站建設(shè)、微信公眾號(hào)

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請(qǐng)盡快告知，我們將會(huì)在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng)，如需處理請(qǐng)聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時(shí)需注明來(lái)源： 創(chuàng)新互聯(lián)