xtrabackupバックアップMySQLデータベースの復元

1.フル・バックアップ・リカバリ：

元のテーブルの内容を表示:

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                    # lsn
last_lsn = 1602080                  # 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      # 
tool_version = 2.3.6                    # 
ibbackup_version = 2.3.6
server_version = 5.5.60-MariaDB
start_time = 2018-09-16 11:56:18        # 
end_time = 2018-09-16 11:56:22          # 
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                           # 

バックアップ準備

[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 

バックアップのリカバリ:

mysqlサービスを停止

削除ライブラリ

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

フルバックアップによるデータ復旧

[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 

復元後のファイルの所有者を変更し、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

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.インクリメンタルバックアップとリカバリ：

フル・バックアップ:

[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!

データベースを変更し、1回目のインクリメンタルバックアップ

を行います.

はtestdbにあります.studentsにデータを追加:

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!

さらに1つのデータを追加し、2回目のインクリメンタルバックアップを行う:

データ

を追加

MariaDB [testdb]> insert into students values (8,'daming',20,'m');
Query OK, 1 row affected (0.00 sec)

2 2 2回目のインクリメンタル・バックアップ(incremental-basedirが1回目のフル・バックアップ・パスを指す場合は差分バックアップ):

[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!

準備データ:

では、すべてのバックアップ(フルおよび各インクリメンタルバックアップを含む)で、コミットされたトランザクションを「再生」する必要があります.「≪リプレイ｜Replay｜Emdb≫」を選択すると、すべてのバックアップ・データがフル・バックアップにマージされます.

すべてのバックアップに基づいてコミットされていないトランザクションをロールバックする

(1)フルバックアップファイルの準備

[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)初回インクリメンタルバックアップファイルを準備する:
注意:--incremental-dirが指すディレクトリは絶対パスでなければなりません.

[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)2回目のインクリメンタルバックアップファイルを準備する:

[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)ロールバック操作の実行

[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)MySQLを閉じて/var/lib/mysql/下のすべてのファイルを削除する
(6)データの回復:

[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/下のファイルの所有者、グループを変更し、データベースを起動して表示する

[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)