溫馨提示×
您好，登錄后才能下訂單哦！
點擊 登錄注冊 即表示同意《億速云用戶服務條款》
您好，登錄后才能下訂單哦！
Xtrabackup由percona提供
percona Xtrabackup是一個自由、開源的完整的在線備份工具，支持mysql、perconna server、mariadb
到官網https://www.percona.com/下載安裝包，并配置好epel源安裝需要依賴libev這個包
[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的備份是通過日志序列號（log sequence number <LSN>）來實現的
備份需自行創建備份用戶，賦予備份用戶相應的一些權限（reload;lock tables;replication client;create tablespace;process;super;create;insert;select）
創建備份恢復用戶：
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僅對InnoDB支持熱備； 查看數據庫信息：
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
全備恢復：
[root@localhost ~]# mysql -e 'drop database hellodb;' #模擬環境先將要恢復的數據庫刪除； 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/ #驗證數據庫有沒恢復 [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)
增備：
增備之前要先做全備，因為增備是依據全備的變化來做的
[root@localhost ~]# innobackupex --user='backup' --password='xtrabackup123' /backup/
[root@localhost ~]# ls /backup/2016-07-05_08-28-54
修改數據庫
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全備+增備恢復：
增備合并到全備，恢復數據的時候只需要恢復合并的全備就可以了 [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一些參數說明：
--include:可選定備份的庫或表，支持正則表達式
--tables-file:指定一個文件中所列出的所有表名
--databasea：以上兩種的合并
--stream=tar:以流的方式壓縮備份
[root@localhost ~]# innobackupex --user='backup' --password='xtrabackup123' --include='hellodb' --stream=tar /backup/ | gzip > /backup/`data +%F_%H_%M%S`.tar.gz
免責聲明：本站發布的內容（圖片、視頻和文字）以原創、轉載和分享為主，文章觀點不代表本網站立場，如果涉及侵權請聯系站長郵箱：is@yisu.com進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。
