Mysql学习之XtraBackup(1)-------全量备份与恢复

XtraBackup

innobackupex常用参数：

-u, --user=name	specifies the MySQL username used when connecting to the server, if that's not the current user.
-p, --password[=name]	specifies the password to use when connecting to the database.
-H, --host=name	specifies the host to use when connecting to the database server with TCP/IP.
-P, --port=#	specifies the port to use when connecting to the database server with TCP/IP.
-S, --socket=name	specifies the socket to use when connecting to the local database server with a UNIX domain socket.
--apply-log	Prepare a backup in BACKUP-DIR by applying the transaction log file named "xtrabackup_logfile" located in the same directory. Also, create new transaction logs. The InnoDB configuration is read from the file "backup-my.cnf".
--redo-only	This forces xtrabackup to skip the "rollback" phase  and do a "redo" only.
--no-timestamp	This option prevents creation of a time-stamped subdirectory of the BACKUP-ROOT-DIR given on the command line.
--databases=name	specifies the list of databases that innobackupex should back up.
--export	This option is passed directly to xtrabackup's --export option. It enables exporting individual tables for import into another server.

xtrabackup常用参数：

--defaults-file=#	Only read default options from the given file #.
--target-dir=name	destination directory
--backup	take backup to target-dir
--prepare	prepare a backup for starting mysql server on the backup.
--export	create files to import to another database when prepare.
--apply-log-only	stop recovery process not to progress LSN after applying log when prepare.
--databases=name	filtering by list of databases.
--tables=name	filtering by regexp for table names.
-u, --user=name	specifies the MySQL username used when connecting to the server, if that's not the current user.
-p, --password[=name]	specifies the password to use when connecting to the database.
-H, --host=name	specifies the host to use when connecting to the database server with TCP/IP.
-P, --port=#	specifies the port to use when connecting to the database server with TCP/IP.
-S, --socket=name	specifies the socket to use when connecting to the local database server with a UNIX domain socket.

全量备份：

[root@rhel6 u01]# innobackupex --defaults-file=/etc/my.cnf --user=bkpuser --password=xxx --no-timestamp /u01/mysqlbackup/all-20190729bak/
...
...
190727 12:38:06 completed OK!

or

[root@rhel6 mysqlbackup]# xtrabackup --defaults-file=/etc/my.cnf -ubkpuser -pxxx --backup   --target-dir=/u01/mysqlbackup/all-20190728bak
...
...
190727 13:41:00 completed OK!

备份出的文件目录：

[root@rhel6 all-20190729bak]# ls -l
total 77960
-rw-r----- 1 root root      491 Jul 27 12:38 backup-my.cnf
-rw-r----- 1 root root      562 Jul 27 12:38 ib_buffer_pool
-rw-r----- 1 root root 79691776 Jul 27 12:37 ibdata1
drwxr-x--- 2 root root     4096 Jul 27 12:38 mysql
drwxr-x--- 2 root root     4096 Jul 27 12:38 performance_schema
drwxr-x--- 2 root root    12288 Jul 27 12:38 sys
drwxr-x--- 2 root root     4096 Jul 27 12:38 test
drwxr-x--- 2 root root     4096 Jul 27 12:38 testrecovery
drwxr-x--- 2 root root     4096 Jul 27 12:38 testrecovery2
-rw-r----- 1 root root       63 Jul 27 12:38 xtrabackup_binlog_info
-rw-r----- 1 root root      141 Jul 27 12:38 xtrabackup_checkpoints
-rw-r----- 1 root root      608 Jul 27 12:38 xtrabackup_info
-rw-r----- 1 root root     2560 Jul 27 12:38 xtrabackup_logfile

其中：
        xtrabackup_info记录的是备份的详细信息，如：Mysql和Xtrabackup版本、备份命令、备份的起止时间等

[root@rhel6 all-20190729bak]# cat xtrabackup_info
uuid = 539db65f-b028-11e9-b2fc-000c296ee978
name = 
tool_name = innobackupex
tool_command = --defaults-file=/etc/my.cnf --user=bkpuser --password=... --no-timestamp /u01/mysqlbackup/all-20190729bak/
tool_version = 2.4.15
ibbackup_version = 2.4.15
server_version = 5.7.19-log
start_time = 2019-07-27 12:37:56
end_time = 2019-07-27 12:38:05
lock_time = 0
binlog_pos = filename 'mysql-bin.000002', position '194', GTID of the last change '7842c967-0958-11e9-9f6c-000c296ee978:1-36'
innodb_from_lsn = 0
innodb_to_lsn = 235566786
partial = N
incremental = N
format = file
compact = N
compressed = N
encrypted = N

         xtrabackup_binlog_info记录的是当前的binlog和pos，如果开启了gtid，还包括gtid set

[root@rhel6 all-20190729bak]# cat xtrabackup_binlog_info
mysql-bin.000002	194	7842c967-0958-11e9-9f6c-000c296ee978:1-36

         xtrabackup_checkpoints记录的是备份类型、起止lsn号等

[root@rhel6 all-20190729bak]# cat xtrabackup_checkpoints
backup_type = full-backuped
from_lsn = 0
to_lsn = 235566786
last_lsn = 235566795
compact = 0
recover_binlog_info = 0
flushed_lsn = 235566795

 全量备份恢复
          xtrabackup的备份文件在正式备份恢复之前要有个prepare的过程，其目的就是为了回滚那些在备份完成时还未提交的事务和应用那些在备份过程中以提交的事务(利用xtrabackup_logfile文件)，从而保证数据文件的一致性。

prepare过程：

[root@rhel6 all-20190729bak]# innobackupex  --user=bkpuser --password=xxx --apply-log /u01/mysqlbackup/all-20190729bak/
...
xtrabackup: This target seems to be not prepared yet.
...
190727 12:52:57 completed OK!

 or

[root@rhel6 mysqlbackup]# xtrabackup  --prepare -ubkpuser -pxxx --target-dir=/u01/mysqlbackup/all-20190728bak/
...
xtrabackup: This target seems to be not prepared yet.
...
190727 13:48:44 completed OK!

恢复过程：
       1)停Mysql--------service mysql stop
       2)备份data文件夹--------mv /u01/mysql/data  /u01/mysql/data_bak0729
       3)把已经prepare的全量备份copy过来--------cp -R /u01/mysqlbackup/all-20190729bak  /u01/mysql/data
       4)修改文件夹及子文件权限--------chown -R mysql:mysql /u01/mysql/data
       5)起Mysql--------service mysql start