本文继Xtrabackup安装和参数详解，本次介绍全备和还原以及增量备份和还原

MySQL环境介绍

阿里云云主机

系统信息以及数据库版本，数据存放目录具体如下：

[root@node ~]# uname -r

4.18.16-1.el7.elrepo.x86_64

[root@node ~]# cat /etc/redhat-release

CentOS Linux release 7.4.1708 (Core)

[root@node ~]# /opt/mysql/bin/mysql -V

/opt/mysql/bin/mysql  Ver 14.14 Distrib 5.6.39, for linux-glibc2.12 (x86_64) using  EditLine wrapper

[root@node ~]# ls /opt/mysql/data/

auto.cnf  ibdata1  ib_logfile0  ib_logfile1  mysql  performance_schema  test

[root@node ~]# ls -ld /opt/mysql/my.cnf

-rw-r--r-- 1 mysql mysql 1468 Dec  6 21:37 /opt/mysql/my.cnf

全备和还原

准备备份目录

[root@node ~]# cd /opt/

[root@node opt]# ls

mysql  mysql-5.6  src

[root@node opt]# mkdir backup/{full,incr} -p

[root@node opt]# ls backup/

full  incr

full： 文件中存放全量备份
incr： 文件中存放增量备份

创建测试数据

创建一个测试数据库，库名为：xtra_test

在xtra_test库中，创建一个friends表，字段只有id和name，并插入一条数据

具体操作如下：

[root@node opt]# mysql -uroot -p234567

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

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 19

Server version: 5.6.39 MySQL Community Server (GPL)

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create database xtra_test;

Query OK, 1 row affected (0.00 sec)

mysql> show databases;

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

| Database           |

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

| information_schema |

| mysql              |

| performance_schema |

| xtra_test          |

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

4 rows in set (0.00 sec)

mysql> use xtra_test;

Database changed

mysql> create table friends (

    -> id int(3) not null,

    -> name varchar(8) not null

    -> );

Query OK, 0 rows affected (0.01 sec)

mysql> show tables;

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

| Tables_in_xtra_test |

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

| friends             |

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

1 row in set (0.00 sec)

mysql> desc friends;

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

| Field | Type       | Null | Key | Default | Extra |

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

| id    | int(3)     | NO   |     | NULL    |       |

| name  | varchar(8) | NO   |     | NULL    |       |

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

2 rows in set (0.00 sec)

mysql> INSERT INTO friends( id, name ) VALUES ( 1, 'jack' ) ;

Query OK, 1 row affected (0.00 sec)

mysql> select * from friends;

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

| id | name |

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

|  1 | jack |

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

1 row in set (0.00 sec)

全量备份

这里的具体参数含义请查看：Xtrabackup安装和参数详解

[root@node opt]# innobackupex --defaults-file=/opt/mysql/my.cnf --user=root --password=234567 /opt/backup/full/

......

181211 11:18:44 [01]        ...done

181211 11:18:44 Finished backing up non-InnoDB tables and files

181211 11:18:44 Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS...

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

xtrabackup: Stopping log copying thread.

.181211 11:18:44 >> log scanned up to (1645416)

181211 11:18:44 Executing UNLOCK TABLES

181211 11:18:44 All tables unlocked

181211 11:18:44 Backup created in directory '/opt/backup/full/2018-12-11_11-18-42/'

181211 11:18:44 [00] Writing /opt/backup/full/2018-12-11_11-18-42/backup-my.cnf

181211 11:18:44 [00]        ...done

181211 11:18:44 [00] Writing /opt/backup/full/2018-12-11_11-18-42/xtrabackup_info

181211 11:18:44 [00]        ...done

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

181211 11:18:44 completed OK!

最后提示 OK 了，查看备份目录中

[root@node opt]# ls /opt/backup/full/

2018-12-11_11-18-42

[root@node opt]# ls /opt/backup/full/2018-12-11_11-18-42/

backup-my.cnf  ibdata1  mysql  performance_schema  xtrabackup_binlog_info  xtrabackup_checkpoints  xtrabackup_info  xtrabackup_logfile  xtra_test

备份完后，备份目录下有几个文件需要注意：

xtrabackup_binlog_info

记录了binlog的position，若开启了GTID，也会将GTID取出。在用于备份+binlog恢复或建立slave的场景里十分有用

注意如果MySQL没有开启log-bin参数，则不会生产次文件

backup-my.cnf

记录了备份时可能涉及到的选项参数，比如系统表空间信息，独立undo表空间信息，redo-log信息等

xtrabackup_checkpoints

记录了此次备份的类型和lsn号的起始值，是否压缩等

xtrabackup_info

记录了备份工具的信息，时间，备份对象（是针对全实例还是某库表），是否是增量，binlog位置等

模拟删除数据

直接删除xtra_test数据库；

此操作为模拟，请勿真是在线上操作

[root@node opt]# mysql -uroot -p234567

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

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 4

Server version: 5.6.39-log MySQL Community Server (GPL)

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> drop database xtra_test;

Query OK, 1 row affected (0.01 sec)

mysql> show databases;

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

| Database           |

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

| information_schema |

| mysql              |

| performance_schema |

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

3 rows in set (0.00 sec)

还原数据操作

还原步骤：

先把xtrabackup备份下来的目录进行再次备份，防止在还原的时候误操作，导致备份不可用；
关闭要回复的数据库实例；
移除需要恢复数据的MySQL实例下的data目录，建议mv移动到别的目录下；
使用--apply-log 参数进行恢复前准备操作，如事物回滚等操作；
使用--copy-back 和 --rsync 参数进行恢复；
授权MySQL实例下的data目录后，启动数据库
验证恢复数据

第一步备份备份文件

[root@node ~]# cd /opt/backup/full/

[root@node full]# ls

2018-12-11_14-04-15

[root@node full]# cp -a 2018-12-11_14-04-15 2018-12-11_14-04-15_back

[root@node full]# ll

total 8

drwxr-x--- 5 root root 4096 Dec 11 14:04 2018-12-11_14-04-15

drwxr-x--- 5 root root 4096 Dec 11 14:04 2018-12-11_14-04-15_back

第二步关闭数据库

[root@node full]# /etc/init.d/mysqld  stop

Shutting down MySQL..

[root@node full]# ps aux|grep mysql

root     19368  0.0  0.1 112720  2232 pts/1    S+   15:01   0:00 grep --color=auto mysql

[root@node full]# netstat -lntup | grep 3306

第三步移除数据库的data目录

[root@node full]# mv /opt/mysql/data /tmp/

[root@node full]# ls -ld /opt/mysql/data

ls: cannot access /opt/mysql/data: No such file or directory

[root@node full]# ls /tmp/data/

auto.cnf  ibdata1  ib_logfile0  ib_logfile1  mysql  mysql-bin.000001  mysql-bin.index  performance_schema

第四步恢复前准备

[root@node full]# innobackupex --defaults=/opt/mysql/my.cnf --apply-log /opt/backup/full/2018-12-11_14-04-15/

.......

InnoDB: Renaming log file ./ib_logfile101 to ./ib_logfile0

InnoDB: New log files created, LSN=1654036

InnoDB: Highest supported file format is Barracuda.

InnoDB: Log scan progressed past the checkpoint lsn 1654284

InnoDB: Doing recovery: scanned up to log sequence number 1654293 (0%)

InnoDB: Database was not shutdown normally!

InnoDB: Starting crash recovery.

InnoDB: xtrabackup: Last MySQL binlog file position 635, file name mysql-bin.000001

InnoDB: Removed temporary tablespace data file: "ibtmp1"

InnoDB: Creating shared tablespace for temporary tables

InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...

InnoDB: File './ibtmp1' size is now 12 MB.

InnoDB: 96 redo rollback segment(s) found. 1 redo rollback segment(s) are active.

InnoDB: 32 non-redo rollback segment(s) are active.

InnoDB: Waiting for purge to start

InnoDB: 5.7.19 started; log sequence number 1654293

xtrabackup: starting shutdown with innodb_fast_shutdown = 1

InnoDB: FTS optimize thread exiting.

InnoDB: Starting shutdown...

InnoDB: Shutdown completed; log sequence number 1654312

181211 14:42:07 completed OK!

[root@node full]# ls 2018-12-11_14-04-15/ -l

total 294956

-rw-r----- 1 root root       481 Dec 11 14:04 backup-my.cnf

-rw-r----- 1 root root  12582912 Dec 11 14:42 ibdata1

-rw-r----- 1 root root 134217728 Dec 11 14:42 ib_logfile0

-rw-r----- 1 root root 134217728 Dec 11 14:42 ib_logfile1

-rw-r----- 1 root root  12582912 Dec 11 14:42 ibtmp1

drwxr-x--- 2 root root      4096 Dec 11 14:04 mysql

drwxr-x--- 2 root root      4096 Dec 11 14:04 performance_schema

-rw-r----- 1 root root        21 Dec 11 14:04 xtrabackup_binlog_info

-rw-r--r-- 1 root root        21 Dec 11 14:42 xtrabackup_binlog_pos_innodb

-rw-r----- 1 root root       113 Dec 11 14:42 xtrabackup_checkpoints

-rw-r----- 1 root root       509 Dec 11 14:04 xtrabackup_info

-rw-r----- 1 root root   8388608 Dec 11 14:42 xtrabackup_logfile

-rw-r--r-- 1 root root         1 Dec 11 14:42 xtrabackup_master_key_id

drwxr-x--- 2 root root      4096 Dec 11 14:04 xtra_test

注意文件时间的变化，说明准备全备文件的操作只是对备份集本身做了相关处理，所以在准备恢复前最好对备份集再次备份

第五步恢复数据

[root@node full]# innobackupex --defaults-file=/opt/mysql-5.6/my.cnf --copy-back /opt/backup/full/2018-12-11_14-04-15/

......

181211 15:02:41 [01]        ...done

181211 15:02:41 [01] Copying ./performance_schema/events_statements_summary_by_thread_by_event_name.frm to /opt/mysql/data/performance_schema/events_statements_summary_by_thread_by_event_name.frm

181211 15:02:41 [01]        ...done

181211 15:02:41 [01] Copying ./performance_schema/mutex_instances.frm to /opt/mysql/data/performance_schema/mutex_instances.frm

181211 15:02:41 [01]        ...done

181211 15:02:41 [01] Copying ./performance_schema/events_waits_summary_global_by_event_name.frm to /opt/mysql/data/performance_schema/events_waits_summary_global_by_event_name.frm

181211 15:02:41 [01]        ...done

181211 15:02:41 [01] Copying ./xtrabackup_info to /opt/mysql/data/xtrabackup_info

181211 15:02:41 [01]        ...done

181211 15:02:41 [01] Copying ./xtrabackup_master_key_id to /opt/mysql/data/xtrabackup_master_key_id

181211 15:02:41 [01]        ...done

181211 15:02:41 completed OK!

[root@node full]# ls /opt/mysql/data/ -l

total 286748

-rw-r----- 1 root root  12582912 Dec 11 15:02 ibdata1

-rw-r----- 1 root root 134217728 Dec 11 15:02 ib_logfile0

-rw-r----- 1 root root 134217728 Dec 11 15:02 ib_logfile1

-rw-r----- 1 root root  12582912 Dec 11 15:02 ibtmp1

drwxr-x--- 2 root root      4096 Dec 11 15:02 mysql

drwxr-x--- 2 root root      4096 Dec 11 15:02 performance_schema

-rw-r----- 1 root root        21 Dec 11 15:02 xtrabackup_binlog_pos_innodb

-rw-r----- 1 root root       509 Dec 11 15:02 xtrabackup_info

-rw-r----- 1 root root         1 Dec 11 15:02 xtrabackup_master_key_id

drwxr-x--- 2 root root      4096 Dec 11 15:02 xtra_test

第六步授权data目录启动MySQL

[root@node full]# ls -l /opt/mysql/data/

total 286748

-rw-r----- 1 mysql mysql  12582912 Dec 11 15:02 ibdata1

-rw-r----- 1 mysql mysql 134217728 Dec 11 15:02 ib_logfile0

-rw-r----- 1 mysql mysql 134217728 Dec 11 15:02 ib_logfile1

-rw-r----- 1 mysql mysql  12582912 Dec 11 15:02 ibtmp1

drwxr-x--- 2 mysql mysql      4096 Dec 11 15:02 mysql

drwxr-x--- 2 mysql mysql      4096 Dec 11 15:02 performance_schema

-rw-r----- 1 mysql mysql        21 Dec 11 15:02 xtrabackup_binlog_pos_innodb

-rw-r----- 1 mysql mysql       509 Dec 11 15:02 xtrabackup_info

-rw-r----- 1 mysql mysql         1 Dec 11 15:02 xtrabackup_master_key_id

drwxr-x--- 2 mysql mysql      4096 Dec 11 15:02 xtra_test

[root@node full]# /etc/init.d/mysqld  start

Starting MySQL..                                           [  OK  ]

[root@node full]# ps aux|grep mysql

root     19450  0.0  0.1 113324  3196 pts/1    S    15:04   0:00 /bin/sh /opt/mysql/bin/mysqld_safe --datadir=/opt/mysql/data --pid-file=/opt/mysql/mysqld.pid

mysql    20257  2.2 44.1 2299484 901804 pts/1  Sl   15:04   0:00 /opt/mysql/bin/mysqld --basedir=/opt/mysql --datadir=/opt/mysql/data --plugin-dir=/opt/mysql/lib/plugin --user=mysql --log-error=/opt/mysql/error.log --open-files-limit=4161 --pid-file=/opt/mysql/mysqld.pid --socket=/tmp/mysql.sock --port=3306

root     20288  0.0  0.1 112720  2244 pts/1    S+   15:04   0:00 grep --color=auto mysql

[root@node full]# netstat -lntup | grep 3306

tcp6       0      0 :::3306                 :::*                    LISTEN      20257/mysqld

第七步验证数据恢复操作

[root@node full]# mysql -uroot -p234567

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

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 1

Server version: 5.6.39-log MySQL Community Server (GPL)

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;

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

| Database           |

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

| information_schema |

| mysql              |

| performance_schema |

| xtra_test          |

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

4 rows in set (0.00 sec)

mysql> use xtra_test;

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> show tables;

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

| Tables_in_xtra_test |

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

| friends             |

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

1 row in set (0.00 sec)

mysql> select * from friends;

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

| id | name |

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

|  1 | jack |

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

1 row in set (0.01 sec

巴特西

Xtrabackup 全备和还原以及增量备份和还原

MySQL环境介绍

全备和还原

准备备份目录

创建测试数据

全量备份

模拟删除数据

还原数据操作

第一步备份备份文件

第二步关闭数据库

第三步移除数据库的data目录

第四步恢复前准备

第五步恢复数据

第六步授权data目录启动MySQL

第七步验证数据恢复操作

最新文章

热门文章

巴特西

Xtrabackup 全备和还原以及增量备份和还原

MySQL环境介绍

全备和还原

准备备份目录

创建测试数据

全量备份

模拟删除数据

还原数据操作

第一步 备份备份文件

第二步 关闭数据库

第三步 移除数据库的data目录

第四步 恢复前准备

第五步 恢复数据

第六步 授权data目录启动MySQL

第七步 验证数据恢复操作

最新文章

热门文章

第一步备份备份文件

第二步关闭数据库

第三步移除数据库的data目录

第四步恢复前准备

第五步恢复数据

第六步授权data目录启动MySQL

第七步验证数据恢复操作