當前位置：首頁 > 编程资源 > 编程问答 >内容正文

编程问答

innobackupex备份工具

發布時間：2023/12/20 编程问答 38 豆豆

生活随笔收集整理的這篇文章主要介紹了 innobackupex备份工具小編覺得挺不錯的,現在分享給大家,幫大家做個參考.

Top

NSD DBA1 DAY07

innobackupex備份工具

1 innobackupex備份工具

1.1 問題

安裝percona軟件包
innobackupex完整備份、增量備份操作。
恢復數據

1.2 步驟

實現此案例需要按照如下步驟進行。

步驟一：安裝XtraBackup軟件包

1）了解軟件包描述信息

[root@dbsvr1 pub]# rpm -qpi percona-xtrabackup-24-2.4.6-2.el7.x86_64.rpm

Name : percona-xtrabackup-24

Version : 2.4.6

Release : 2.el7

Architecture: x86_64

Install Date: (not installed)

Group : Applications/Databases

Size : 32416340

License : GPLv2

Signature : DSA/SHA1, 2017年02月27日星期一 20時28分17秒, Key ID 1c4cbdcdcd2efd2a

Source RPM : percona-xtrabackup-24-2.4.6-2.el7.src.rpm

Build Date : 2017年02月27日星期一 20時27分21秒

Build Host : vps-centos7-x64-01.ci.percona.com

Relocations : (not relocatable)

URL : http://www.percona.com/software/percona-xtrabackup

Summary : XtraBackup online backup for MySQL / InnoDB

Description :

Percona XtraBackup is OpenSource online (non-blockable) backup solution for InnoDB and XtraDB engines

2）安裝依賴包perl-DBD-MySQL perl-Digest-MD5 libev

使用RHEL 7自帶的即可，yum方式安裝：

[root@dbsvr1 pub]# yum -y install perl-DBD-MySQL perl-Digest-MD5

libev使用網上找的rpm包 libev-4.15-1.el6.rf.x86_64.rpm //該包由講師提供

[root@dbsvr1 pub]#rpm –ivh libev-4.15-1.el6.rf.x86_64.rpm

如果未安裝這些依賴包，則直接安裝percona-xtrabackup時會報錯：

代碼

3）安裝percona-xtrabackup

[root@dbsvr1 pub]#rpm -ivh percona-xtrabackup-*.rpm

警告：percona-xtrabackup-24-2.4.6-2.el7.x86_64.rpm: 頭V4 DSA/SHA1 Signature, 密鑰 ID cd2efd2a: NOKEY

準備中... ################################# [100%]

正在升級/安裝...

1:percona-xtrabackup-24-2.4.6-2.el7################################# [ 33%]

2:percona-xtrabackup-test-24-2.4.6-################################# [ 67%]

3:percona-xtrabackup-24-debuginfo-2################################# [100%]

4）確認安裝的主要程序/腳本

[root@dbsvr1 pub]# rpm -ql percona-xtrabackup-24-2.4.6-2.el7.x86_64

/usr/bin/innobackupex

/usr/bin/xbcloud

/usr/bin/xbcloud_osenv

/usr/bin/xbcrypt

/usr/bin/xbstream

/usr/bin/xtrabackup

/usr/share/doc/percona-xtrabackup-24-2.4.6

/usr/share/doc/percona-xtrabackup-24-2.4.6/COPYING

/usr/share/man/man1/innobackupex.1.gz

/usr/share/man/man1/xbcrypt.1.gz

/usr/share/man/man1/xbstream.1.gz

/usr/share/man/man1/xtrabackup.1.gz

步驟二：innobackupex完整備份、增量備份操作

--host 主機名

--port 3306

--user 用戶名

--password 密碼

--databases="庫名"

--databases="庫1 庫2"

--databases="庫.表"

--no-timestamp 不用日期命名備份文件存儲的子目錄，使用備份的數據庫名做備份目錄名

--no-timestmap 不使用日期命名備份目錄名

1）做一個完整備份

默認情況下，備份文件存儲的子目錄會用日期命名，

innobackupex作為客戶端工具，以mysql協議連入mysqld，將數據備份到/backup文件夾：

[root@dbsvr1 ~]# innobackupex --user=root --password=1234567 /backup/mysql –no-timestamp

170425 11:05:44 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!".

Unrecognized character \x01; marked by <-- HERE after <-- HERE near column 1 at - line 1374.

170425 11:05:45 Connecting to MySQL server host: localhost, user: root, password: set, port: not set, socket: not set

Using server version 5.7.17

innobackupex version 2.4.6 based on MySQL server 5.7.13 Linux (x86_64) (revision id: 8ec05b7)

xtrabackup: uses posix_fadvise().

xtrabackup: cd to /var/lib/mysql

xtrabackup: open files limit requested 0, set to 1024

xtrabackup: using the following InnoDB configuration:

xtrabackup: innodb_data_home_dir = .

xtrabackup: innodb_data_file_path = ibdata1:12M:autoextend

xtrabackup: innodb_log_group_home_dir = ./

xtrabackup: innodb_log_files_in_group = 2

xtrabackup: innodb_log_file_size = 50331648

InnoDB: Number of pools: 1

170425 11:05:45 >> log scanned up to (2543893)

xtrabackup: Generating a list of tablespaces

InnoDB: Allocated tablespace ID 2 for mysql/plugin, old maximum was 0

170425 11:05:45 [01] Copying ./ibdata1 to /backup/ibdata1

170425 11:05:45 [01] ...done

170425 11:05:46 [01] Copying ./mysql/plugin.ibd to /backup/mysql/plugin.ibd

170425 11:05:46 [01] ...done

170425 11:05:46 [01] Copying ./mysql/servers.ibd to /backup/mysql/servers.ibd

170425 11:05:46 [01] ...done

170425 11:05:46 [01] Copying ./mysql/help_topic.ibd to /backup/mysql/help_topic.ibd

170425 11:05:46 [01] ...done

170425 11:05:46 >> log scanned up to (2543893)

.. ..

170425 11:06:00 [01] Copying ./sys/x@0024waits_global_by_latency.frm to /backup/sys/x@0024waits_global_by_latency.frm

170425 11:06:00 [01] ...done

170425 11:06:00 [01] Copying ./sys/session_ssl_status.frm to /backup/sys/session_ssl_status.frm

170425 11:06:00 [01] ...done

170425 11:06:00 [01] Copying ./db1/db.opt to /backup/db1/db.opt

170425 11:06:00 [01] ...done

170425 11:06:00 [01] Copying ./db1/tb1.frm to /backup/db1/tb1.frm

170425 11:06:00 [01] ...done

170425 11:06:00 Finished backing up non-InnoDB tables and files

170425 11:06:00 Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS...

xtrabackup: The latest check point (for incremental): '2543884'

xtrabackup: Stopping log copying thread.

.170425 11:06:00 >> log scanned up to (2543893)

170425 11:06:00 Executing UNLOCK TABLES

170425 11:06:00 All tables unlocked

170425 11:06:00 [00] Copying ib_buffer_pool to /backup/ib_buffer_pool

170425 11:06:00 [00] ...done

170425 11:06:00 Backup created in directory '/backup/'

170425 11:06:00 [00] Writing backup-my.cnf

170425 11:06:00 [00] ...done

170425 11:06:00 [00] Writing xtrabackup_info

170425 11:06:00 [00] ...done

xtrabackup: Transaction log of lsn (2543884) to (2543893) was copied.

170425 11:06:01 completed OK

確認備份好的文件數據：

[root@dbsvr1 ~]#ls /backup/

backup-my.cnf ib_buffer_pool mysql sys xtrabackup_info

db1 ibdata1 performance_schema xtrabackup_checkpoints xtrabackup_logfile

2）做一個增量備份（基于前一步的完整備份）

隨意做一些新增或更改庫表的操作，比如在db1庫中新建一個mytb的表：

mysql> USE db1;

Database changed

mysql> CREATE TABLE mytb(id int(4), name varchar(24));

Query OK, 0 rows affected (0.38 sec)

mysql> INSERT INTO tb1 VALUES

-> (1,'bon'),

-> (2,'bo'),

Query OK, 2 rows affected (0.12 sec)

Records: 2 Duplicates: 0 Warnings: 0

mysql> SELECT * FROM tb1;

+------+------+

| id | name |

+------+------+

| 1 | bob |

| 2 | bo |

+------+------+

2 rows in set (0.00 sec)

以前一次保存到/backup的完整備份為基礎，做一個增量備份，保存到/incr01/，指定增量備份參照的基本目錄（完整備份目錄）需要用到選項--incremental-basedir。相關操作如下：

[root@dbsvr1 ~]# innobackupex --user=root --password=12345678 --incremental /incr01 --incremental-basedir=/backup/ --no-timestamp

170425 11:30:14 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!".

Unrecognized character \x01; marked by <-- HERE after <-- HERE near column 1 at - line 1374.

170425 11:30:14 Connecting to MySQL server host: localhost, user: root, password: set, port: not set, socket: not set

Using server version 5.7.17

innobackupex version 2.4.6 based on MySQL server 5.7.13 Linux (x86_64) (revision id: 8ec05b7)

incremental backup from 2543884 is enabled.

xtrabackup: uses posix_fadvise().

xtrabackup: cd to /var/lib/mysql

xtrabackup: open files limit requested 0, set to 1024

xtrabackup: using the following InnoDB configuration:

xtrabackup: innodb_data_home_dir = .

xtrabackup: innodb_data_file_path = ibdata1:12M:autoextend

xtrabackup: innodb_log_group_home_dir = ./

xtrabackup: innodb_log_files_in_group = 2

xtrabackup: innodb_log_file_size = 50331648

InnoDB: Number of pools: 1

170425 11:30:14 >> log scanned up to (2549933)

xtrabackup: Generating a list of tablespaces

InnoDB: Allocated tablespace ID 2 for mysql/plugin, old maximum was 0

xtrabackup: using the full scan for incremental backup

170425 11:30:15 [01] Copying ./ibdata1 to /incr01/ibdata1.delta

170425 11:30:15 [01] ...done

170425 11:30:15 >> log scanned up to (2549933)

170425 11:30:15 [01] Copying ./mysql/plugin.ibd to /incr01/mysql/plugin.ibd.delta

170425 11:30:15 [01] ...done

... ...

170425 11:30:35 Executing UNLOCK TABLES

170425 11:30:35 All tables unlocked

170425 11:30:35 [00] Copying ib_buffer_pool to /incr01/ib_buffer_pool

170425 11:30:35 [00] ...done

170425 11:30:35 Backup created in directory '/incr01/'

170425 11:30:35 [00] Writing backup-my.cnf

170425 11:30:35 [00] ...done

170425 11:30:35 [00] Writing xtrabackup_info

170425 11:30:35 [00] ...done

xtrabackup: Transaction log of lsn (2549924) to (2549933) was copied.

170425 11:30:35 completed OK!

確認備份好的文件數據：

[root@dbsvr1 ~]# ls /incr01/

backup-my.cnf ib_buffer_pool ibdata1.meta performance_schema xtrabackup_checkpoints xtrabackup_logfile

db1 ibdata1.delta mysql sys

對比完整備份、增量備份的大小：

[root@dbsvr1 ~]# du -sh /backup/ /incr01/

142M????/backup/ //完整備份的大小

3.5M????/incr01/ //增量備份的大小

步驟三：恢復數據

通過XtraBackup工具備份的數據庫目錄，若要恢復到另一個MySQL服務器，需要先做一個“--apply-log --redo-only ”的準備操作。

1）準備恢復“完整備份”

完成準備以后，最終/backup可用來重建MySQL服務器。這種情況下，需要先做一個“--apply-log --redo-only ”的準備操作，以確保數據一致性：

[root@dbsvr1 ~]#innobackupex --user=root --password=12345678 --apply-log --redo-only /backup/

170425 11:42:19 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!".

innobackupex version 2.4.6 based on MySQL server 5.7.13 Linux (x86_64) (revision id: 8ec05b7)

xtrabackup: cd to /backup/

xtrabackup: This target seems to be already prepared.

InnoDB: Number of pools: 1

xtrabackup: notice: xtrabackup_logfile was already used to '--prepare'.

xtrabackup: using the following InnoDB configuration for recovery:

xtrabackup: innodb_data_home_dir = .

xtrabackup: innodb_data_file_path = ibdata1:12M:autoextend

xtrabackup: innodb_log_group_home_dir = .

xtrabackup: innodb_log_files_in_group = 2

xtrabackup: innodb_log_file_size = 50331648

xtrabackup: using the following InnoDB configuration for recovery:

xtrabackup: innodb_data_home_dir = .

xtrabackup: innodb_data_file_path = ibdata1:12M:autoextend

xtrabackup: innodb_log_group_home_dir = .

xtrabackup: innodb_log_files_in_group = 2

xtrabackup: innodb_log_file_size = 50331648

xtrabackup: Starting InnoDB instance for recovery.

xtrabackup: Using 104857600 bytes for buffer pool (set by --use-memory parameter)

InnoDB: PUNCH HOLE support available

InnoDB: Mutexes and rw_locks use GCC atomic builtins

InnoDB: Uses event mutexes

InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier

InnoDB: Compressed tables use zlib 1.2.7

InnoDB: Number of pools: 1

InnoDB: Not using CPU crc32 instructions

InnoDB: Initializing buffer pool, total size = 100M, instances = 1, chunk size = 100M

InnoDB: Completed initialization of buffer pool

InnoDB: page_cleaner coordinator priority: -20

InnoDB: Highest supported file format is Barracuda.

xtrabackup: starting shutdown with innodb_fast_shutdown = 1

InnoDB: Starting shutdown...

InnoDB: Shutdown completed; log sequence number 2544177

InnoDB: Number of pools: 1

170425 11:42:20 completed OK!

準備恢復“增量備份”

[root@dbsvr1 ~]#innobackupex --user=root --password=12345678 --apply-log --redo-only /backup/ --incremental-dir=/incr01

170425 11:42:55 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!".

innobackupex version 2.4.6 based on MySQL server 5.7.13 Linux (x86_64) (revision id: 8ec05b7)

incremental backup from 2543884 is enabled.

xtrabackup: cd to /backup/

xtrabackup: This target seems to be already prepared with --apply-log-only.

InnoDB: Number of pools: 1

xtrabackup: xtrabackup_logfile detected: size=8388608, start_lsn=(2549924)

xtrabackup: using the following InnoDB configuration for recovery:

xtrabackup: innodb_data_home_dir = .

xtrabackup: innodb_data_file_path = ibdata1:12M:autoextend

xtrabackup: innodb_log_group_home_dir = /incr01/

xtrabackup: innodb_log_files_in_group = 1

xtrabackup: innodb_log_file_size = 8388608

xtrabackup: Generating a list of tablespaces

InnoDB: Allocated tablespace ID 2 for mysql/plugin, old maximum was 0

xtrabackup: page size for /incr01//ibdata1.delta is 16384 bytes

Applying /incr01//ibdata1.delta to ./ibdata1...

... ...

170425 11:43:09 [01] Copying /incr01/performance_schema/global_status.frm to ./performance_schema/global_status.frm

170425 11:43:09 [01] ...done

170425 11:43:09 [01] Copying /incr01/performance_schema/session_status.frm to ./performance_schema/session_status.frm

170425 11:43:09 [01] ...done

170425 11:43:09 [00] Copying /incr01//xtrabackup_info to ./xtrabackup_info

170425 11:43:09 [00] ...done

170425 11:43:10 completed OK!

2）關閉mysql服務，并將/var/lib/mysql/下的文件刪除，假設數據被刪除。

[root@dbsvr1 ~]#systemctl stop mysqld

[root@dbsvr1 ~]#rm -rf /var/lib/mysql

3）恢復“完整備份+增量備份”

完成準備以后，最終仍然是/backup用來重建MySQL服務器，但這種情況下需提前合并相關增量備份的數據

[root@dbsvr1 ~]# innobackupex --user=root --password=12345678 --copy-back /backup/

... ...

170425 11:51:39 [01] Copying ./performance_schema/global_status.frm to /var/lib/mysql/performance_schema/glo.frm

170425 11:51:39 [01] ...done

170425 11:51:39 [01] Copying ./performance_schema/session_status.frm to /var/lib/mysql/performance_schema/seus.frm

170425 11:51:39 [01] ...done

170425 11:51:39 [01] Copying ./ib_buffer_pool to /var/lib/mysql/ib_buffer_pool

170425 11:51:39 [01] ...done

170425 11:51:39 [01] Copying ./ibtmp1 to /var/lib/mysql/ibtmp1

170425 11:51:39 [01] ...done

170425 11:51:39 [01] Copying ./xtrabackup_info to /var/lib/mysql/xtrabackup_info

170425 11:51:39 [01] ...done

170425 11:51:39 completed OK!

4）修改/var/lib/mysql/下文件屬主與屬組,查看數據：

恢復后，/var/lib/mysql下文件屬組與屬主皆為root，需要更改為mysql

[root@dbsvr1 ~]#chown -R mysql:mysql /var/lib/mysql

[root@dbsvr1 ~]#systemctl start mysqld.service

[root@dbsvr1 ~]#mysql -uroot -p12345678 -e "select * from db1.tb1"

mysql: [Warning] Using a password on the command line interface can be insecure.

+------+------+

| id | name |

+------+------+

| 1 | bob |

| 2 | bo |

+------+------+

轉載于:https://www.cnblogs.com/tiki/p/10783402.html

總結

以上是生活随笔為你收集整理的innobackupex备份工具的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。

上一篇：图书馆管理系统可行性分析报告----软件
下一篇：勤哲服务器仓库管理系统,用勤哲Excel