xtrabackup实现完全备份及增量备份详解

By | 2018年11月20日
目录
[隐藏]

xtrabackup简介

xtrabackup是一个用来对mysql做备份的工具,它可以对innodb引擎的数据库做热备。xtrabackup备份和还原速度快,备份操作不会中断正在执行的事务,备份完成之后可以自动做校验,备份结果可以压缩(节省磁盘和带宽)。实际工作中可以用来做mysql的完全备份,增量备份,以及差异备份等。

xtrabackup有两个主要的工具:innobackupex和xtrabackup,xtrabackup只能备份InnoDB和XtraDB数据表,innobackupex封装了xtrabackup,可以备份MyISAM数据表。

MySQL数据库本身提供的工具并不支持真正的增量备份,二进制日志恢复是point-in-time(时间点)的恢复而不是增量备份。Xtrabackup工具支持对InnoDB存储引擎的增量备份,工作原理如下:

1.首先完成一个完全备份,并记录下此时检查点的LSN(LogSequence Number)。

2.在进程增量备份时,比较表空间中每个页的LSN是否大于上次备份时的LSN,如果是,则备份该页,同时记录当前检查点的LSN。

 

xtrabackup常用选项

2 --incremental-basedir=name           <<仅适用于backup,增量备份目录
3 --incremental-dir=name               <<仅适用于prepare,恢复指定目录下的.delta文件和日志文件
4 --apply-log                          <<从备份恢复。
5 --redo-only                          <<该选项强制跳过rollback阶段,只进行redo。这是有必要使用的,如果备份后,要使用增量改变的。

 

安装xtrabackup

1 下载xtrabackup(下载二进制版本免安装)
2 # wget http://www.percona.com/redir/downloads/XtraBackup/XtraBackup-1.5/Linux/binary/x86_64/xtrabackup-1.5.tar.gz
3 # tar zxvfxtrabackup-1.5.tar.gz -C /usr/local/
4 源码安装过程具体参见源码目录下BUILD.txt文件。

 

演示:基于innobackupex做备份和恢复(完整备份)

第一步:完全备份到/ken下

[root@ken ~]# innobackupex -uroot -pxx /ken <<xx表示密码,输入你的数据库密码
xtrabackup: recognized server arguments: --datadir=/data/mysql/mysql3306/data --tmpdir=/data/mysql/mysql3306/tmp --open_files_limit=65535 --server-id=1003306 --log_bin=/data/mysql/mysql3306/logs/mysql-bin --innodb_buffer_pool_size=100M --innodb_data_file_path=ibdata1:100M:autoextend --innodb_flush_log_at_trx_commit=2 --innodb_log_buffer_size=8M --innodb_log_file_size=100M --innodb_log_files_in_group=3 --innodb_max_dirty_pages_pct=50 --innodb_file_per_table=1 --innodb_io_capacity=100 --innodb_flush_method=O_DIRECT 
xtrabackup: recognized client arguments: --datadir=/data/mysql/mysql3306/data --tmpdir=/data/mysql/mysql3306/tmp --open_files_limit=65535 --server-id=1003306 --log_bin=/data/mysql/mysql3306/logs/mysql-bin --innodb_buffer_pool_size=100M --innodb_data_file_path=ibdata1:100M:autoextend --innodb_flush_log_at_trx_commit=2 --innodb_log_buffer_size=8M --innodb_log_file_size=100M --innodb_log_files_in_group=3 --innodb_max_dirty_pages_pct=50 --innodb_file_per_table=1 --innodb_io_capacity=100 --innodb_flush_method=O_DIRECT 
181023 04:51:51 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!".
...
...

181023 04:51:55 Executing UNLOCK TABLES
181023 04:51:55 All tables unlocked
181023 04:51:55 [00] Copying ib_buffer_pool to /ken/2018-10-23_04-51-51/ib_buffer_pool
181023 04:51:55 [00]        ...done
181023 04:51:55 Backup created in directory '/ken/2018-10-23_04-51-51/'
MySQL binlog position: filename 'mysql-bin.000004', position '154'
181023 04:51:55 [00] Writing /ken/2018-10-23_04-51-51/backup-my.cnf
181023 04:51:55 [00]        ...done
181023 04:51:55 [00] Writing /ken/2018-10-23_04-51-51/xtrabackup_info
181023 04:51:55 [00]        ...done
xtrabackup: Transaction log of lsn (3366440) to (3366449) was copied.
181023 04:51:55 completed OK!                   <<最后出现comleted ok表示备份成功

 

第二步:删除mysql数据库(模拟故障)

[root@ken ~]# mysql -uroot -p     <<登录数据库
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.7.23-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 |
| jobs               |
| ke                 |
| mysql              |
| performance_schema |
| sys                |
| test               |
| test5              |
+--------------------+
8 rows in set (0.06 sec)

mysql> drop database mysql;       <<删除mysql数据库
Query OK, 33 rows affected, 2 warnings (0.24 sec)

 

第三步:退出mysql并关闭mysql

[root@ken ~]# mysqladmin -uroot -p shutdown   <<关闭mysql
[root@ken ~]# ss -tnl | grep 3306             <<查看mysql进程是否关闭

 

第四步:整理备份数据(将备份整理就绪)

[root@ken ~]# innobackupex --apply-log /ken/2018-10-23_04-51-51/
xtrabackup: recognized server arguments: --innodb_checksum_algorithm=crc32 --innodb_log_checksum_algorithm=strict_crc32 --innodb_data_file_path=ibdata1:100M:autoextend --innodb_log_files_in_group=3 --innodb_log_file_size=104857600 --innodb_fast_checksum=0 --innodb_page_size=16384 --innodb_log_block_size=512 --innodb_undo_directory=./ --innodb_undo_tablespaces=0 --server-id=1003306 --redo-log-version=1 
xtrabackup: recognized client arguments: --innodb_checksum_algorithm=crc32 --innodb_log_checksum_algorithm=strict_crc32 --innodb_data_file_path=ibdata1:100M:autoextend --innodb_log_files_in_group=3 --innodb_log_file_size=104857600 --innodb_fast_checksum=0 --innodb_page_size=16384 --innodb_log_block_size=512 --innodb_undo_directory=./ --innodb_undo_tablespaces=0 --server-id=1003306 --redo-log-version=1 
181023 04:59: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!".
...
...
InnoDB: Renaming log file ./ib_logfile101 to ./ib_logfile0
InnoDB: New log files created, LSN=3366546
InnoDB: Highest supported file format is Barracuda.
InnoDB: Log scan progressed past the checkpoint lsn 3366924
InnoDB: Doing recovery: scanned up to log sequence number 3366933 (0%)
InnoDB: Database was not shutdown normally!
InnoDB: Starting crash recovery.
InnoDB: xtrabackup: Last MySQL binlog file position 817, file name mysql-bin.000003
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 3366933
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
InnoDB: FTS optimize thread exiting.
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 3366952
181023 04:59:20 completed OK!

 

第五步:确认是否可以进行恢复

[root@ken ~]# ss -tnl                        <<确保数据库已经关闭
State      Recv-Q Send-Q                   Local Address:Port                                  Peer Address:Port              
LISTEN     0      128                                  *:111                                              *:*                  
LISTEN     0      128                                  *:22                                               *:*                  
LISTEN     0      128                                 :::111                                             :::*                  
LISTEN     0      128                                 :::22                                              :::*                  
[root@ken ~]# rm -rf /data/mysql/mysql3306/data/* <<删除保存数据的目录下的全部文件(找到你自己的数据保存位置)

 

第六步:进行恢复

[root@ken ~]# innobackupex --copy-back /ken/2018-10-23_04-51-51/
xtrabackup: recognized server arguments: --datadir=/data/mysql/mysql3306/data --tmpdir=/data/mysql/mysql3306/tmp --open_files_limit=65535 --server-id=1003306 --log_bin=/data/mysql/mysql3306/logs/mysql-bin --innodb_buffer_pool_size=100M --innodb_data_file_path=ibdata1:100M:autoextend --innodb_flush_log_at_trx_commit=2 --innodb_log_buffer_size=8M --innodb_log_file_size=100M --innodb_log_files_in_group=3 --innodb_max_dirty_pages_pct=50 --innodb_file_per_table=1 --innodb_io_capacity=100 --innodb_flush_method=O_DIRECT 
xtrabackup: recognized client arguments: --datadir=/data/mysql/mysql3306/data --tmpdir=/data/mysql/mysql3306/tmp --open_files_limit=65535 --server-id=1003306 --log_bin=/data/mysql/mysql3306/logs/mysql-bin --innodb_buffer_pool_size=100M --innodb_data_file_path=ibdata1:100M:autoextend --innodb_flush_log_at_trx_commit=2 --innodb_log_buffer_size=8M --innodb_log_file_size=100M --innodb_log_files_in_group=3 --innodb_max_dirty_pages_pct=50 --innodb_file_per_table=1 --innodb_io_capacity=100 --innodb_flush_method=O_DIRECT 
181023 05:03:30 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!".
...
...
181023 05:03:35 [01]        ...done
181023 05:03:35 [01] Copying ./test5/db.opt to /data/mysql/mysql3306/data/test5/db.opt
181023 05:03:35 [01]        ...done
181023 05:03:35 [01] Copying ./test5/ken.frm to /data/mysql/mysql3306/data/test5/ken.frm
181023 05:03:35 [01]        ...done
181023 05:03:35 [01] Copying ./test5/ken.MYI to /data/mysql/mysql3306/data/test5/ken.MYI
181023 05:03:35 [01]        ...done
181023 05:03:35 [01] Copying ./test5/ken.MYD to /data/mysql/mysql3306/data/test5/ken.MYD
181023 05:03:35 [01]        ...done
181023 05:03:35 [01] Copying ./ib_buffer_pool to /data/mysql/mysql3306/data/ib_buffer_pool
181023 05:03:35 [01]        ...done
181023 05:03:35 [01] Copying ./xtrabackup_info to /data/mysql/mysql3306/data/xtrabackup_info
181023 05:03:35 [01]        ...done
181023 05:03:35 [01] Copying ./xtrabackup_binlog_pos_innodb to /data/mysql/mysql3306/data/xtrabackup_binlog_pos_innodb
181023 05:03:35 [01]        ...done
181023 05:03:35 [01] Copying ./xtrabackup_master_key_id to /data/mysql/mysql3306/data/xtrabackup_master_key_id
181023 05:03:35 [01]        ...done
181023 05:03:35 [01] Copying ./ibtmp1 to /data/mysql/mysql3306/data/ibtmp1
181023 05:03:35 [01]        ...done
181023 05:03:35 completed OK!

 

第七步:更改数据权限

[root@ken ~]# ls -l /data/mysql/mysql3306/data/
total 421936
-rw-r----- 1 root root       519 Oct 23 05:03 ib_buffer_pool
-rw-r----- 1 root root 104857600 Oct 23 05:03 ibdata1
-rw-r----- 1 root root 104857600 Oct 23 05:03 ib_logfile0
-rw-r----- 1 root root 104857600 Oct 23 05:03 ib_logfile1
-rw-r----- 1 root root 104857600 Oct 23 05:03 ib_logfile2
-rw-r----- 1 root root  12582912 Oct 23 05:03 ibtmp1
drwxr-x--- 2 root root      4096 Oct 23 05:03 jobs
drwxr-x--- 2 root root        48 Oct 23 05:03 ke
drwxr-x--- 2 root root      4096 Oct 23 05:03 mysql
drwxr-x--- 2 root root      8192 Oct 23 05:03 performance_schema
drwxr-x--- 2 root root      8192 Oct 23 05:03 sys
drwxr-x--- 2 root root       200 Oct 23 05:03 test
drwxr-x--- 2 root root        65 Oct 23 05:03 test5
-rw-r----- 1 root root        21 Oct 23 05:03 xtrabackup_binlog_pos_innodb
-rw-r----- 1 root root       448 Oct 23 05:03 xtrabackup_info
-rw-r----- 1 root root         1 Oct 23 05:03 xtrabackup_master_key_id

可以看到下面恢复的数据文件的属主已经变成root,需要更改为mysql

[root@ken ~]# chown -R mysql.mysql /data/mysql/mysql3306/data

 

第八步:启动mysql并查看mysql数据库是否已经恢复

[root@ken ~]# mysqld &                   <<启动mysql数据库
[1] 7179
[root@ken ~]# mysql -uroot -p            <<登录mysql
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.23-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 |
| jobs               |
| ke                 |
| mysql              |                   <<mysql数据库已经恢复
| performance_schema |
| sys                |
| test               |
| test5              |
+--------------------+
8 rows in set (0.07 sec)

 

演示:基于innobackupex做增量备份和恢复(增量备份)

第一步:首先做一次完全备份

[root@ken ~]# innobackupex -uroot -pxx /ken  <<xx表示你的数据库密码

 

第二步:创建一张表

mysql> use jobs;
Database changed
mysql> create table ken1 as select * from teachers;   <<创建一张ken1的表
Query OK, 6 rows affected (0.09 sec)
Records: 6  Duplicates: 0  Warnings: 0

 

第三步:做一次增量备份

[root@ken ~]# innobackupex -uroot -pxx --incremental --incremental-basedir=/ken/2018-10-23_04-51-51/ /ken
xtrabackup: recognized server arguments: --datadir=/data/mysql/mysql3306/data --tmpdir=/data/mysql/mysql3306/tmp --open_files_limit=65535 --server-id=1003306 --log_bin=/data/mysql/mysql3306/logs/mysql-bin --innodb_buffer_pool_size=100M --innodb_data_file_path=ibdata1:100M:autoextend --innodb_flush_log_at_trx_commit=2 --innodb_log_buffer_size=8M --innodb_log_file_size=100M --innodb_log_files_in_group=3 --innodb_max_dirty_pages_pct=50 --innodb_file_per_table=1 --innodb_io_capacity=100 --innodb_flush_method=O_DIRECT 
xtrabackup: recognized client arguments: --datadir=/data/mysql/mysql3306/data --tmpdir=/data/mysql/mysql3306/tmp --open_files_limit=65535 --server-id=1003306 --log_bin=/data/mysql/mysql3306/logs/mysql-bin --innodb_buffer_pool_size=100M --innodb_data_file_path=ibdata1:100M:autoextend --innodb_flush_log_at_trx_commit=2 --innodb_log_buffer_size=8M --innodb_log_file_size=100M --innodb_log_files_in_group=3 --innodb_max_dirty_pages_pct=50 --innodb_file_per_table=1 --innodb_io_capacity=100 --innodb_flush_method=O_DIRECT 
181023 05:17:04 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!".
...
...
181023 05:17:08 Executing UNLOCK TABLES
181023 05:17:08 All tables unlocked
181023 05:17:08 [00] Copying ib_buffer_pool to /ken/2018-10-23_05-17-04/ib_buffer_pool
181023 05:17:08 [00]        ...done
181023 05:17:08 Backup created in directory '/ken/2018-10-23_05-17-04/'
MySQL binlog position: filename 'mysql-bin.000005', position '794'
181023 05:17:08 [00] Writing /ken/2018-10-23_05-17-04/backup-my.cnf
181023 05:17:08 [00]        ...done
181023 05:17:08 [00] Writing /ken/2018-10-23_05-17-04/xtrabackup_info
181023 05:17:08 [00]        ...done
xtrabackup: Transaction log of lsn (3373349) to (3373358) was copied.
181023 05:17:08 completed OK!

 

第四步:删除刚才创建的表(模拟故障)

mysql> use jobs;
Database changed
mysql> drop table ken1;
Query OK, 0 rows affected (0.03 sec)

 

第五步:整理完整备份

[root@ken ~]# innobackupex --apply-log --redo-only /ken/2018-10-23_04-51-51/
xtrabackup: recognized server arguments: --innodb_checksum_algorithm=crc32 --innodb_log_checksum_algorithm=strict_crc32 --innodb_data_file_path=ibdata1:100M:autoextend --innodb_log_files_in_group=3 --innodb_log_file_size=104857600 --innodb_fast_checksum=0 --innodb_page_size=16384 --innodb_log_block_size=512 --innodb_undo_directory=./ --innodb_undo_tablespaces=0 --server-id=1003306 --redo-log-version=1 
xtrabackup: recognized client arguments: --innodb_checksum_algorithm=crc32 --innodb_log_checksum_algorithm=strict_crc32 --innodb_data_file_path=ibdata1:100M:autoextend --innodb_log_files_in_group=3 --innodb_log_file_size=104857600 --innodb_fast_checksum=0 --innodb_page_size=16384 --innodb_log_block_size=512 --innodb_undo_directory=./ --innodb_undo_tablespaces=0 --server-id=1003306 --redo-log-version=1 
181023 05:20:49 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: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 3366961
InnoDB: Number of pools: 1
181023 05:20:50 completed OK!

 

第六步:将第一个增量备份文件合并到完全备份中

[root@ken ~]# innobackupex --apply-log --redo-only /ken/2018-10-23_04-51-51/ --incremental-dir=/ken/2018-10-23_05-17-04/
xtrabackup: recognized server arguments: --innodb_checksum_algorithm=crc32 --innodb_log_checksum_algorithm=strict_crc32 --innodb_data_file_path=ibdata1:100M:autoextend --innodb_log_files_in_group=3 --innodb_log_file_size=104857600 --innodb_fast_checksum=0 --innodb_page_size=16384 --innodb_log_block_size=512 --innodb_undo_directory=./ --innodb_undo_tablespaces=0 --server-id=1003306 --redo-log-version=1 
xtrabackup: recognized client arguments: --innodb_checksum_algorithm=crc32 --innodb_log_checksum_algorithm=strict_crc32 --innodb_data_file_path=ibdata1:100M:autoextend --innodb_log_files_in_group=3 --innodb_log_file_size=104857600 --innodb_fast_checksum=0 --innodb_page_size=16384 --innodb_log_block_size=512 --innodb_undo_directory=./ --innodb_undo_tablespaces=0 --server-id=1003306 --redo-log-version=1 
181023 05:23: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!".
...
...
181023 05:23:17 [01]        ...done
181023 05:23:17 [01] Copying /ken/2018-10-23_05-17-04/test5/db.opt to ./test5/db.opt
181023 05:23:17 [01]        ...done
181023 05:23:17 [01] Copying /ken/2018-10-23_05-17-04/test5/ken.frm to ./test5/ken.frm
181023 05:23:17 [01]        ...done
181023 05:23:17 [01] Copying /ken/2018-10-23_05-17-04/test5/ken.MYI to ./test5/ken.MYI
181023 05:23:17 [01]        ...done
181023 05:23:17 [01] Copying /ken/2018-10-23_05-17-04/test5/ken.MYD to ./test5/ken.MYD
181023 05:23:17 [01]        ...done
181023 05:23:17 [00] Copying /ken/2018-10-23_05-17-04//xtrabackup_binlog_info to ./xtrabackup_binlog_info
181023 05:23:17 [00]        ...done
181023 05:23:17 [00] Copying /ken/2018-10-23_05-17-04//xtrabackup_info to ./xtrabackup_info
181023 05:23:17 [00]        ...done
181023 05:23:17 completed OK!

 

第七步:确认是否可以进行恢复

[root@ken ~]# ss -tnl
State      Recv-Q Send-Q                   Local Address:Port                                  Peer Address:Port              
LISTEN     0      128                                  *:111                                              *:*                  
LISTEN     0      128                                  *:22                                               *:*                  
LISTEN     0      128                                 :::111                                             :::*                  
LISTEN     0      128                                 :::22                                              :::*                  
[root@ken ~]# rm -rf /data/mysql/mysql3306/data/*

 

第八步:数据恢复

[root@ken ~]# innobackupex --copy-back /ken/2018-10-23_04-51-51/
xtrabackup: recognized server arguments: --datadir=/data/mysql/mysql3306/data --tmpdir=/data/mysql/mysql3306/tmp --open_files_limit=65535 --server-id=1003306 --log_bin=/data/mysql/mysql3306/logs/mysql-bin --innodb_buffer_pool_size=100M --innodb_data_file_path=ibdata1:100M:autoextend --innodb_flush_log_at_trx_commit=2 --innodb_log_buffer_size=8M --innodb_log_file_size=100M --innodb_log_files_in_group=3 --innodb_max_dirty_pages_pct=50 --innodb_file_per_table=1 --innodb_io_capacity=100 --innodb_flush_method=O_DIRECT 
xtrabackup: recognized client arguments: --datadir=/data/mysql/mysql3306/data --tmpdir=/data/mysql/mysql3306/tmp --open_files_limit=65535 --server-id=1003306 --log_bin=/data/mysql/mysql3306/logs/mysql-bin --innodb_buffer_pool_size=100M --innodb_data_file_path=ibdata1:100M:autoextend --innodb_flush_log_at_trx_commit=2 --innodb_log_buffer_size=8M --innodb_log_file_size=100M --innodb_log_files_in_group=3 --innodb_max_dirty_pages_pct=50 --innodb_file_per_table=1 --innodb_io_capacity=100 --innodb_flush_method=O_DIRECT 
181023 05:27:13 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!".
...
...
181023 05:27:18 [01] Copying ./test5/db.opt to /data/mysql/mysql3306/data/test5/db.opt
181023 05:27:18 [01]        ...done
181023 05:27:18 [01] Copying ./test5/ken.frm to /data/mysql/mysql3306/data/test5/ken.frm
181023 05:27:18 [01]        ...done
181023 05:27:18 [01] Copying ./test5/ken.MYI to /data/mysql/mysql3306/data/test5/ken.MYI
181023 05:27:18 [01]        ...done
181023 05:27:18 [01] Copying ./test5/ken.MYD to /data/mysql/mysql3306/data/test5/ken.MYD
181023 05:27:18 [01]        ...done
181023 05:27:18 [01] Copying ./ib_buffer_pool to /data/mysql/mysql3306/data/ib_buffer_pool
181023 05:27:18 [01]        ...done
181023 05:27:18 [01] Copying ./xtrabackup_binlog_pos_innodb to /data/mysql/mysql3306/data/xtrabackup_binlog_pos_innodb
181023 05:27:18 [01]        ...done
181023 05:27:18 [01] Copying ./xtrabackup_master_key_id to /data/mysql/mysql3306/data/xtrabackup_master_key_id
181023 05:27:18 [01]        ...done
181023 05:27:18 [01] Copying ./ibtmp1 to /data/mysql/mysql3306/data/ibtmp1
181023 05:27:18 [01]        ...done
181023 05:27:18 [01] Copying ./xtrabackup_info to /data/mysql/mysql3306/data/xtrabackup_info
181023 05:27:18 [01]        ...done
181023 05:27:19 completed OK!

 

第九步:数据权限修改

[root@ken ~]# chown -R mysql.mysql /data/mysql/mysql3306/data/

 

第十步:启动mysql并查看删除的表是否应恢复

[root@ken ~]# mysqld &
[1] 7358
[root@ken ~]# mysql -uroot -pxx
mysql: [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 2
Server version: 5.7.23-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> use jobs;
Database changed
mysql> show tables;
+----------------+
| Tables_in_jobs |
+----------------+
| classes        |
| courses        |
| ken            |
| ken1           |            <<删除的ken1表已经恢复
| ken4           |
| kenken         |
| kenken1        |
| scores         |
| students       |

发表评论

您的电子邮箱地址不会被公开。 必填项已用*标注