Shulou(Shulou.com)05/31 Report--

Percona xtrabackup-based innobackupex how to achieve point-in-time database recovery, many novices are not very clear about this, in order to help you solve this problem, the following editor will explain in detail for you, people with this need can come to learn, hope you can get something.

Preface

During the operation of the database, it may be due to human misoperation or storage failure, resulting in some tables cannot be accessed normally. At this time, we can use point-in-time recovery, database backup and binary log to restore the misoperation or failure data back. It is recommended that you do not carry out point-in-time recovery in the production system, but restore the data in a temporary environment. Then let the business and development confirm, and then import the data back to the production database, which does not affect the operation of other businesses in the database.

Database recovery based on point-in-time or binary log location

1, the current database of the database

Mysql > select * from zxydb.t_go

+-+ +

| | a | b | |

+-+ +

| | 1 | 1 |

| | 2 | 2 |

| | 3 | 3 |

| | 5 | 5 |

| | 8 | 8 |

| | 10 | 10 |

| | 11 | 11 |

| | 12 | 12 |

+-+ +

8 rows in set (0.00 sec)

2. Build the benchmark backup of the database

-- build a directory to store database benchmark backups

[root@standbygtid mysql] # mkdir-p / database_base_dir

[root@standbygtid mysql] # innobackupex-uroot-psystem / database_base_dir-no-timestamp

[root@standbygtid mysql] # cd / database_base_dir/

[root@standbygtid database_base_dir] # ll

The total dosage is 77868

-rw-r- 1 root root 418 November 5 00:56 backup-my.cnf

Drwxr-x--- 2 root root 4096 November 5 00:56 completedb

-rw-r- 1 root root 79691776 November 5 00:56 ibdata1

Drwxr-x--- 2 root root 4096 November 5 00:56 mysql

Drwxr-x--- 2 root root 4096 November 5 00:56 performance_schema

Drwxr-x--- 2 root root 4096 November 5 00:56 test

-rw-r- 1 root root 18 November 5 00:56 xtrabackup_binlog_info

-rw-r- 1 root root 115 November 5 00:56 xtrabackup_checkpoints

-rw-r- 1 root root 507 November 5 00:56 xtrabackup_info

-rw-r- 1 root root 2560 November 5 00:56 xtrabackup_logfile

Drwxr-x--- 2 root root 4096 November 5 00:56 zxydb

3. Prepare to restore the above baseline backup

(note: in fact, online logs are applied to the baseline backup to ensure data consistency.)

[root@standbygtid database_base_dir] # innobackupex-uroot-psystem-- apply-log / database_base_dir

[root@standbygtid database_base_dir] # ll

The total dosage is 196652

-rw-r- 1 root root 418 November 5 00:56 backup-my.cnf

Drwxr-x--- 2 root root 4096 November 5 00:56 completedb

-rw-r- 1 root root 79691776 November 5 01:01 ibdata1

-rw-r- 1 root root 50331648 November 5 01:01 ib_logfile0

-rw-r- 1 root root 50331648 November 5 01:01 ib_logfile1

-rw-r- 1 root root 12582912 November 5 01:01 ibtmp1

Drwxr-x--- 2 root root 4096 November 5 00:56 mysql

Drwxr-x--- 2 root root 4096 November 5 00:56 performance_schema

Drwxr-x--- 2 root root 4096 November 5 00:56 test

-rw-r- 1 root root 18 November 5 00:56 xtrabackup_binlog_info

-rw-r--r-- 1 root root 19 November 5 01:01 xtrabackup_binlog_pos_innodb

-rw-r- 1 root root 115 November 5 01:01 xtrabackup_checkpoints

-rw-r- 1 root root 507 November 5 00:56 xtrabackup_info

-rw-r- 1 root root 8388608 November 5 01:01 xtrabackup_logfile

Drwxr-x--- 2 root root 4096 November 5 00:56 zxydb

4. View the binary log and location corresponding to the above benchmark backup

(note: the following point-in-time recovery starts from this binary log and location to a certain point in time, which is very important.)

[root@standbygtid database_base_dir] # more xtrabackup_binlog_info

Binlog.000001120

[root@standbygtid database_base_dir] #

5. In order to better simulate point-in-time recovery, switch binary logs and generate new binary logs

Mysql > flush logs

Query OK, 0 rows affected (0.01 sec)

6. View the current binary log and location

Mysql > show master status

+-+

| | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | |

+-+

| | binlog.000002 | 337 |

+-+

1 row in set (0.00 sec)

-- A total of 2 binary logs

Mysql > show binary logs

+-+ +

| | Log_name | File_size |

+-+ +

| | binlog.000001 | 164|

| | binlog.000002 | 337 |

+-+ +

2 rows in set (0.00 sec)

7, generate data changes

Mysql > use zxydb

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

Mysql > select * from t_go

+-+ +

| | a | b | |

+-+ +

| | 1 | 1 |

| | 2 | 2 |

| | 3 | 3 |

| | 5 | 5 |

| | 8 | 8 |

| | 10 | 10 |

| | 11 | 11 |

| | 12 | 12 |

+-+ +

8 rows in set (0.00 sec)

Mysql > insert into t_go select 13pr 13

Query OK, 1 row affected (0.00 sec)

Records: 1 Duplicates: 0 Warnings: 0

Mysql > commit

Query OK, 0 rows affected (0.00 sec)

8. View the event information corresponding to the current binary log

Mysql > show binlog events in 'binlog.000002'

+- -+

| | Log_name | Pos | Event_type | Server_id | End_log_pos | Info | |

+- -+

| | binlog.000002 | 4 | Format_desc | 1 | 120 | Server ver: 5.6.25-enterprise-commercial-advanced-log, Binlog ver: 4 |

| | binlog.000002 | 120 | Query | 1 | 201 | BEGIN |

| | binlog.000002 | 201 | Query | 1 | 306 | use `zxydb`; insert into t_go select 1313 |

| | binlog.000002 | 306 | Xid | 1 | 337 | COMMIT / * xid=41 * / | |

+- -+

4 rows in set (0.00 sec)

9, continue to change the data

Mysql > insert into t_go select 18pr 18

Query OK, 1 row affected (0.02 sec)

Records: 1 Duplicates: 0 Warnings: 0

Mysql > commit

Query OK, 0 rows affected (0.00 sec)

Mysql > show binlog events in 'binlog.000002'

+- -+

| | Log_name | Pos | Event_type | Server_id | End_log_pos | Info | |

+- -+

| | binlog.000002 | 4 | Format_desc | 1 | 120 | Server ver: 5.6.25-enterprise-commercial-advanced-log, Binlog ver: 4 |

| | binlog.000002 | 120 | Query | 1 | 201 | BEGIN |

| | binlog.000002 | 201 | Query | 1 | 306 | use `zxydb`; insert into t_go select 1313 |

| | binlog.000002 | 306 | Xid | 1 | 337 | COMMIT / * xid=41 * / | |

| | binlog.000002 | 337 | Query | 1 | 418 | BEGIN |

| | binlog.000002 | 418 | Query | 1 | 523 | use `zxydb`; insert into t_go select 181.18 |

| | binlog.000002 | 523 | Xid | 1 | 554 | COMMIT / * xid=48 * / | |

+- -+

7 rows in set (0.00 sec)

10, because point-in-time recovery is used for binary logs, back up the following binaries to another directory

(note: even if the binary log is very important, it must be backed up regularly, otherwise it is really based on a point-in-time recovery, and the required binary log is gone, which will result in data loss.)

[root@standbygtid database_base_dir] # cd / var/lib/mysql

[root@standbygtid mysql] # ll

The total dosage is 188464

-rw-rw---- 1 mysql mysql 56 November 4 23:52 auto.cnf

-rw-rw---- 1 mysql mysql 164.11 5 01:06 binlog.000001

-rw-rw---- 1 mysql mysql 554 November 5 01:12 binlog.000002

-rw-rw---- 1 mysql mysql 32 November 5 01:06 binlog.index

Drwxr-x--- 2 mysql mysql 4096 November 4 23:48 completedb

-rw-r- 1 mysql mysql 79691776 November 5 01:12 ibdata1

-rw-r- 1 mysql mysql 50331648 November 5 01:12 ib_logfile0

-rw-r- 1 mysql mysql 50331648 November 4 23:48 ib_logfile1

-rw-r- 1 mysql mysql 12582912 November 4 23:48 ibtmp1

Drwxr-x--- 2 mysql mysql 4096 November 4 23:48 mysql

Srwxrwxrwx 1 mysql mysql 0 November 4 23:52 mysql.sock

-rw- 1 root root 159 November 4 23:52 nohup.out

Drwxr-x--- 2 mysql mysql 4096 November 4 23:48 performance_schema

-rw-r- 1 mysql root 2508 November 4 23:52 standbygtid.err

-rw-rw---- 1 mysql mysql 6 November 4 23:52 standbygtid.pid

Drwxr-x--- 2 mysql mysql 4096 November 4 23:48 test

Drwxr-x--- 2 mysql mysql 4096 November 4 23:48 zxydb

[root@standbygtid mysql] # mysqlbinlog binlog.000001 binlog.000002-- start-position=120-- stop-position=337 > / base_binlog_pos.sql

[root@standbygtid mysql] #

11, close the database

Mysqladmin-uroot-psystem shutdown

12. Physically delete data files

[root@standbygtid mysql] # pwd

/ var/lib/mysql

[root@standbygtid mysql] # rm-rf *

13, restore the above backup to the data file directory of the database

[root@standbygtid mysql] # innobackupex-- datadir=/var/lib/mysql-- copy-back / database_base_dir

14, the data file directory of the authorization database

[root@standbygtid mysql] # chown-Rf mysql:mysql *

[root@standbygtid mysql] # ll

The total dosage is 188448

Drwxr-x--- 2 mysql mysql 4096 November 5 01:49 completedb

-rw-r- 1 mysql mysql 79691776 November 5 01:49 ibdata1

-rw-r- 1 mysql mysql 50331648 November 5 01:49 ib_logfile0

-rw-r- 1 mysql mysql 50331648 November 5 01:49 ib_logfile1

-rw-r- 1 mysql mysql 12582912 November 5 01:49 ibtmp1

Drwxr-x--- 2 mysql mysql 4096 November 5 01:49 mysql

Drwxr-x--- 2 mysql mysql 4096 November 5 01:49 performance_schema

Drwxr-x--- 2 mysql mysql 4096 November 5 01:49 test

-rw-r- 1 mysql mysql 19 November 5 01:49 xtrabackup_binlog_pos_innodb

-rw-r- 1 mysql mysql 507 November 5 01:49 xtrabackup_info

Drwxr-x--- 2 mysql mysql 4096 November 5 01:49 zxydb

15. Delete the above files related to xtrabackup

[root@standbygtid mysql] # rm-rf xtrabackup_*

16, restart the database

[root@standbygtid mysql] # nohup mysqld_safe-- user=mysql&

17, point-in-time recovery

(note: we will revert to 337 of the binary log, see step 9 above)

[root@standbygtid mysql] # mysql-uroot-psystem

< /base_binlog_pos.sql Warning: Using a password on the command line interface can be insecure. [root@standbygtid mysql]# mysql>

Select * from zxydb.t_go

+-+ +

| | a | b | |

+-+ +

| | 1 | 1 |

| | 2 | 2 |

| | 3 | 3 |

| | 5 | 5 |

| | 8 | 8 |

| | 10 | 10 |

| | 11 | 11 |

| | 12 | 12 |

| | 13 | 13 |

+-+ +

9 rows in set (0.00 sec)

Is it helpful for you to read the above content? If you want to know more about the relevant knowledge or read more related articles, please follow the industry information channel, thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.