mysql小白系列_06 备份与恢复

1.使用mydumper工具全库备份。
  1)源码编译安装
  2)全库备份

2.误操作truncate table gyj_t1;利用mysqldump的备份和binlog日志对表gyj_t1做完全恢复。
  如下场景：
  create table gyj_t1(id int,name varchar(10));
  insert into gyj_t1 values(1,'AAAAA');
  commit;
  mysqldump全库备份。。。
  insert into gyj_t1 values(2,'BBBBBB');
  commit;
  truncate table gyj;
  开始恢复。。。
  恢复完成！！！
  验证：select * from gyj_t1;有2条记录

3.误操作MySQL数据库：rm /u01/my3306/data/*;利用Innobackupex的备份和binlog日志对MySQL数据库做完全恢复。
  如下场景：
   create table gyj_t2(id int,name varchar(10));
   insert into gyj_t1 values(1,'AAAAA');
   insert into gyj_t1 values(2,'BBBBBB');
   commit;
   select * from gyj_t2; #（2条记）
   使用Innobackupex备份全库
   insert into gyj_t1 values(3,'CCCCC');
   commit;
    select * from gyj_t2; #（3条记)
   误操作：rm –rf  /u01/my3306/data/*
  开始恢复。。。
  恢复完成！！！
  验证：select * from gyj_t3;有3条记录

---

1.使用mydumper工具全库备份。

wget https://launchpad.net/mydumper/0.9/0.9.1/+download/mydumper-0.9.1.tar.gz
cd mydumper-0.9.1
[root@mysql01 mydumper-0.9.1]# cmake .
-- Using mysql-config: /data/my3306/bin/mysql_config
-- Found MySQL: /data/my3306/include, /data/my3306/lib/libmysqlclient.so;/usr/lib64/libpthread.so;/usr/lib64/libm.so;/usr/lib64/librt.so;/usr/lib64/libssl.so;/usr/lib64/libcrypto.so;/usr/lib64/libdl.so
-- checking for one of the modules 'glib-2.0'
-- checking for one of the modules 'gthread-2.0'

CMake Warning at docs/CMakeLists.txt:9 (message):
  Unable to find Sphinx documentation generator

-- ------------------------------------------------
-- MYSQL_CONFIG = /data/my3306/bin/mysql_config
-- CMAKE_INSTALL_PREFIX = /usr/local
-- BUILD_DOCS = ON
-- WITH_BINLOG = OFF
-- RUN_CPPCHECK = OFF
-- Change a values with: cmake -D<Variable>=<Value>
-- ------------------------------------------------
--
-- Configuring done
-- Generating done
-- Build files have been written to: /dvd/mydumper-0.9.1
[root@mysql01 mydumper-0.9.1]# make
Scanning dependencies of target mydumper
[ 25%] Building C object CMakeFiles/mydumper.dir/mydumper.c.o
[ 50%] Building C object CMakeFiles/mydumper.dir/server_detect.c.o
[ 75%] Building C object CMakeFiles/mydumper.dir/g_unix_signal.c.o
Linking C executable mydumper
[ 75%] Built target mydumper
Scanning dependencies of target myloader
[100%] Building C object CMakeFiles/myloader.dir/myloader.c.o
Linking C executable myloader
[100%] Built target myloader
[root@mysql01 mydumper-0.9.1]# make install
[ 75%] Built target mydumper
[100%] Built target myloader
Install the project...
-- Install configuration: ""
-- Installing: /usr/local/bin/mydumper
-- Removed runtime path from "/usr/local/bin/mydumper"
-- Installing: /usr/local/bin/myloader
-- Removed runtime path from "/usr/local/bin/myloader"

https://github.com/maxbube/mydumper

https://launchpad.net/mydumper

http://blog.****.net/leshami/article/details/46815553

update mysql.user set authentication_string=password('123456') where user='root' and host='127.0.0.1';
mydumper --user=root --password='123456' --host=127.0.0.1 --socket=/data/my3306/run/mysql.sock --regex '^(?!(mysql))' --outputdir=/backup/mydumper --compress --verbose=3 --logfile=/backup/mydumper/mydumper.log
[root@mysql01 mydumper]# cat mydumper.log
2018-02-21 23:50:58 [INFO] - Connected to a MySQL server
2018-02-21 23:50:58 [INFO] - Started dump at: 2018-02-21 23:50:58

2018-02-21 23:50:58 [INFO] - Written master status
2018-02-21 23:50:58 [INFO] - Thread 1 connected using MySQL connection ID 6
2018-02-21 23:50:59 [INFO] - Thread 2 connected using MySQL connection ID 7
2018-02-21 23:50:59 [INFO] - Thread 3 connected using MySQL connection ID 8
2018-02-21 23:50:59 [INFO] - Thread 4 connected using MySQL connection ID 9
2018-02-21 23:50:59 [INFO] - Thread 4 dumping data for `db1`.`t1`
2018-02-21 23:50:59 [INFO] - Non-InnoDB dump complete, unlocking tables
2018-02-21 23:50:59 [INFO] - Thread 1 dumping data for `sys`.`sys_config`
2018-02-21 23:50:59 [INFO] - Thread 2 dumping schema for `db1`.`t1`
2018-02-21 23:50:59 [INFO] - Thread 3 dumping schema for `sys`.`sys_config`
2018-02-21 23:50:59 [INFO] - Thread 2 dumping view for `sys`.`host_summary`
2018-02-21 23:50:59 [INFO] - Thread 3 dumping view for `sys`.`host_summary_by_file_io`
2018-02-21 23:50:59 [INFO] - Thread 1 dumping view for `sys`.`host_summary_by_file_io_type`
2018-02-21 23:50:59 [INFO] - Thread 3 dumping view for `sys`.`host_summary_by_stages`
2018-02-21 23:50:59 [INFO] - Thread 3 dumping view for `sys`.`host_summary_by_statement_latency`
2018-02-21 23:50:59 [INFO] - Thread 4 dumping view for `sys`.`host_summary_by_statement_type`
2018-02-21 23:50:59 [INFO] - Thread 1 dumping view for `sys`.`innodb_buffer_stats_by_schema`
2018-02-21 23:50:59 [INFO] - Thread 3 dumping view for `sys`.`innodb_buffer_stats_by_table`
2018-02-21 23:50:59 [INFO] - Thread 2 dumping view for `sys`.`innodb_lock_waits`
2018-02-21 23:50:59 [INFO] - Thread 1 dumping view for `sys`.`io_by_thread_by_latency`
2018-02-21 23:50:59 [INFO] - Thread 1 dumping view for `sys`.`io_global_by_file_by_bytes`
2018-02-21 23:50:59 [INFO] - Thread 3 dumping view for `sys`.`io_global_by_file_by_latency`
2018-02-21 23:50:59 [INFO] - Thread 4 dumping view for `sys`.`io_global_by_wait_by_bytes`
2018-02-21 23:50:59 [INFO] - Thread 3 dumping view for `sys`.`io_global_by_wait_by_latency`
2018-02-21 23:50:59 [INFO] - Thread 2 dumping view for `sys`.`latest_file_io`
2018-02-21 23:50:59 [INFO] - Thread 1 dumping view for `sys`.`memory_by_host_by_current_bytes`
2018-02-21 23:50:59 [INFO] - Thread 4 dumping view for `sys`.`memory_by_thread_by_current_bytes`
2018-02-21 23:50:59 [INFO] - Thread 3 dumping view for `sys`.`memory_by_user_by_current_bytes`
2018-02-21 23:50:59 [INFO] - Thread 1 dumping view for `sys`.`memory_global_by_current_bytes`
2018-02-21 23:50:59 [INFO] - Thread 4 dumping view for `sys`.`memory_global_total`
2018-02-21 23:50:59 [INFO] - Thread 3 dumping view for `sys`.`metrics`
2018-02-21 23:50:59 [INFO] - Thread 2 dumping view for `sys`.`processlist`
2018-02-21 23:50:59 [INFO] - Thread 1 dumping view for `sys`.`ps_check_lost_instrumentation`
2018-02-21 23:50:59 [INFO] - Thread 4 dumping view for `sys`.`schema_auto_increment_columns`
2018-02-21 23:50:59 [INFO] - Thread 1 dumping view for `sys`.`schema_index_statistics`
2018-02-21 23:50:59 [INFO] - Thread 1 dumping view for `sys`.`schema_object_overview`
2018-02-21 23:50:59 [INFO] - Thread 3 dumping view for `sys`.`schema_redundant_indexes`
2018-02-21 23:50:59 [INFO] - Thread 4 dumping view for `sys`.`schema_table_lock_waits`
2018-02-21 23:50:59 [INFO] - Thread 2 dumping view for `sys`.`schema_table_statistics`
2018-02-21 23:50:59 [INFO] - Thread 2 dumping view for `sys`.`schema_table_statistics_with_buffer`
2018-02-21 23:50:59 [INFO] - Thread 3 dumping view for `sys`.`schema_tables_with_full_table_scans`
2018-02-21 23:50:59 [INFO] - Thread 3 dumping view for `sys`.`schema_unused_indexes`
2018-02-21 23:50:59 [INFO] - Thread 3 dumping view for `sys`.`session`
2018-02-21 23:50:59 [INFO] - Thread 1 dumping view for `sys`.`session_ssl_status`
2018-02-21 23:50:59 [INFO] - Thread 2 dumping view for `sys`.`statement_analysis`
2018-02-21 23:50:59 [INFO] - Thread 1 dumping view for `sys`.`statements_with_errors_or_warnings`
2018-02-21 23:50:59 [INFO] - Thread 4 dumping view for `sys`.`statements_with_full_table_scans`
2018-02-21 23:50:59 [INFO] - Thread 1 dumping view for `sys`.`statements_with_runtimes_in_95th_percentile`
2018-02-21 23:50:59 [INFO] - Thread 3 dumping view for `sys`.`statements_with_sorting`
2018-02-21 23:50:59 [INFO] - Thread 2 dumping view for `sys`.`statements_with_temp_tables`
2018-02-21 23:50:59 [INFO] - Thread 4 dumping view for `sys`.`user_summary`
2018-02-21 23:50:59 [INFO] - Thread 2 dumping view for `sys`.`user_summary_by_file_io`
2018-02-21 23:50:59 [INFO] - Thread 4 dumping view for `sys`.`user_summary_by_file_io_type`
2018-02-21 23:50:59 [INFO] - Thread 1 dumping view for `sys`.`user_summary_by_stages`
2018-02-21 23:50:59 [INFO] - Thread 4 dumping view for `sys`.`user_summary_by_statement_latency`
2018-02-21 23:50:59 [INFO] - Thread 2 dumping view for `sys`.`user_summary_by_statement_type`
2018-02-21 23:50:59 [INFO] - Thread 4 dumping view for `sys`.`version`
2018-02-21 23:50:59 [INFO] - Thread 1 dumping view for `sys`.`wait_classes_global_by_avg_latency`
2018-02-21 23:50:59 [INFO] - Thread 3 dumping view for `sys`.`wait_classes_global_by_latency`
2018-02-21 23:50:59 [INFO] - Thread 2 dumping view for `sys`.`waits_by_host_by_latency`
2018-02-21 23:50:59 [INFO] - Thread 4 dumping view for `sys`.`waits_by_user_by_latency`
2018-02-21 23:50:59 [INFO] - Thread 1 dumping view for `sys`.`waits_global_by_latency`
2018-02-21 23:50:59 [INFO] - Thread 2 dumping view for `sys`.`x$host_summary`
2018-02-21 23:50:59 [INFO] - Thread 4 dumping view for `sys`.`x$host_summary_by_file_io`
2018-02-21 23:50:59 [INFO] - Thread 3 dumping view for `sys`.`x$host_summary_by_file_io_type`
2018-02-21 23:50:59 [INFO] - Thread 1 dumping view for `sys`.`x$host_summary_by_stages`
2018-02-21 23:50:59 [INFO] - Thread 4 dumping view for `sys`.`x$host_summary_by_statement_latency`
2018-02-21 23:50:59 [INFO] - Thread 1 dumping view for `sys`.`x$host_summary_by_statement_type`
2018-02-21 23:50:59 [INFO] - Thread 2 dumping view for `sys`.`x$innodb_buffer_stats_by_schema`
2018-02-21 23:50:59 [INFO] - Thread 4 dumping view for `sys`.`x$innodb_buffer_stats_by_table`
2018-02-21 23:50:59 [INFO] - Thread 1 dumping view for `sys`.`x$innodb_lock_waits`
2018-02-21 23:50:59 [INFO] - Thread 2 dumping view for `sys`.`x$io_by_thread_by_latency`
2018-02-21 23:50:59 [INFO] - Thread 4 dumping view for `sys`.`x$io_global_by_file_by_bytes`
2018-02-21 23:50:59 [INFO] - Thread 3 dumping view for `sys`.`x$io_global_by_file_by_latency`
2018-02-21 23:50:59 [INFO] - Thread 2 dumping view for `sys`.`x$io_global_by_wait_by_bytes`
2018-02-21 23:50:59 [INFO] - Thread 1 dumping view for `sys`.`x$io_global_by_wait_by_latency`
2018-02-21 23:50:59 [INFO] - Thread 4 dumping view for `sys`.`x$latest_file_io`
2018-02-21 23:50:59 [INFO] - Thread 4 dumping view for `sys`.`x$memory_by_host_by_current_bytes`
2018-02-21 23:50:59 [INFO] - Thread 1 dumping view for `sys`.`x$memory_by_thread_by_current_bytes`
2018-02-21 23:50:59 [INFO] - Thread 3 dumping view for `sys`.`x$memory_by_user_by_current_bytes`
2018-02-21 23:50:59 [INFO] - Thread 2 dumping view for `sys`.`x$memory_global_by_current_bytes`
2018-02-21 23:50:59 [INFO] - Thread 3 dumping view for `sys`.`x$memory_global_total`
2018-02-21 23:50:59 [INFO] - Thread 1 dumping view for `sys`.`x$processlist`
2018-02-21 23:50:59 [INFO] - Thread 2 dumping view for `sys`.`x$ps_digest_95th_percentile_by_avg_us`
2018-02-21 23:50:59 [INFO] - Thread 4 dumping view for `sys`.`x$ps_digest_avg_latency_distribution`
2018-02-21 23:50:59 [INFO] - Thread 3 dumping view for `sys`.`x$ps_schema_table_statistics_io`
2018-02-21 23:50:59 [INFO] - Thread 4 dumping view for `sys`.`x$schema_flattened_keys`
2018-02-21 23:50:59 [INFO] - Thread 2 dumping view for `sys`.`x$schema_index_statistics`
2018-02-21 23:50:59 [INFO] - Thread 3 dumping view for `sys`.`x$schema_table_lock_waits`
2018-02-21 23:50:59 [INFO] - Thread 4 dumping view for `sys`.`x$schema_table_statistics`
2018-02-21 23:50:59 [INFO] - Thread 1 dumping view for `sys`.`x$schema_table_statistics_with_buffer`
2018-02-21 23:50:59 [INFO] - Thread 2 dumping view for `sys`.`x$schema_tables_with_full_table_scans`
2018-02-21 23:50:59 [INFO] - Thread 2 dumping view for `sys`.`x$statement_analysis`
2018-02-21 23:50:59 [INFO] - Thread 3 dumping view for `sys`.`x$statements_with_errors_or_warnings`
2018-02-21 23:50:59 [INFO] - Thread 1 dumping view for `sys`.`x$statements_with_full_table_scans`
2018-02-21 23:50:59 [INFO] - Thread 2 dumping view for `sys`.`x$statements_with_runtimes_in_95th_percentile`
2018-02-21 23:50:59 [INFO] - Thread 3 dumping view for `sys`.`x$statements_with_sorting`
2018-02-21 23:50:59 [INFO] - Thread 1 dumping view for `sys`.`x$statements_with_temp_tables`
2018-02-21 23:50:59 [INFO] - Thread 4 dumping view for `sys`.`x$user_summary`
2018-02-21 23:50:59 [INFO] - Thread 2 dumping view for `sys`.`x$user_summary_by_file_io`
2018-02-21 23:50:59 [INFO] - Thread 3 dumping view for `sys`.`x$user_summary_by_file_io_type`
2018-02-21 23:50:59 [INFO] - Thread 1 dumping view for `sys`.`x$user_summary_by_stages`
2018-02-21 23:50:59 [INFO] - Thread 2 dumping view for `sys`.`x$user_summary_by_statement_latency`
2018-02-21 23:50:59 [INFO] - Thread 4 dumping view for `sys`.`x$user_summary_by_statement_type`
2018-02-21 23:50:59 [INFO] - Thread 1 dumping view for `sys`.`x$wait_classes_global_by_avg_latency`
2018-02-21 23:50:59 [INFO] - Thread 4 dumping view for `sys`.`x$wait_classes_global_by_latency`
2018-02-21 23:50:59 [INFO] - Thread 2 dumping view for `sys`.`x$waits_by_host_by_latency`
2018-02-21 23:50:59 [INFO] - Thread 4 dumping view for `sys`.`x$waits_by_user_by_latency`
2018-02-21 23:50:59 [INFO] - Thread 1 dumping view for `sys`.`x$waits_global_by_latency`
2018-02-21 23:50:59 [INFO] - Thread 4 shutting down
2018-02-21 23:50:59 [INFO] - Thread 2 shutting down
2018-02-21 23:50:59 [INFO] - Thread 3 shutting down
2018-02-21 23:50:59 [INFO] - Thread 1 shutting down
2018-02-21 23:50:59 [INFO] - Finished dump at: 2018-02-21 23:50:59

备份前插入一条数据

mysql> use db1
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_db1 |
+---------------+
| t1            |
+---------------+
1 row in set (0.00 sec)

mysql>  create table gyj_t1(id int,name varchar(10));
Query OK, 0 rows affected (0.20 sec)

mysql>   insert into gyj_t1 values(1,'AAAAA');
Query OK, 1 row affected (0.00 sec)

mysql>   commit;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from gyj_t1;
+------+-------+
| id   | name  |
+------+-------+
|    1 | AAAAA |
+------+-------+
1 row in set (0.00 sec)

使用mysqldump备份

mysqldump -uroot -p123456 -h127.0.0.1 -P3306 -S=/data/my3306/run/mysql.sock --single-transaction --master-data=2 db1 > /backup/db1.sql

日志

) ENGINE=CSV DEFAULT CHARSET=utf8 COMMENT='Slow log';
/*!40101 SET character_set_client = @saved_cs_client */;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2018-02-22  0:03:00

再插入一条数据

mysql> insert into gyj_t1 values(2,'BBBBBB');
Query OK, 1 row affected (0.12 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from gyj_t1;
+------+--------+
| id   | name   |
+------+--------+
|    1 | AAAAA  |
|    2 | BBBBBB |
+------+--------+
2 rows in set (0.00 sec)

mysql> truncate gyj_t1;
Query OK, 0 rows affected (0.12 sec)

mysql> select * from gyj_t1;
Empty set (0.00 sec)

https://www.cnblogs.com/chenmh/p/5300370.html

从dump中恢复备份

mysql -uroot -p123456 -h127.0.0.1 -S=/data/my3306/run/mysql.sock db1 < /backup/db1.sql

恢复了备份中的一条数据

mysql> select * from gyj_t1;
+------+-------+
| id   | name  |
+------+-------+
|    1 | AAAAA |
+------+-------+
1 row in set (0.00 sec)

从binlog中恢复另外的数据

-- CHANGE MASTER TO MASTER_LOG_FILE='binlog.000013', MASTER_LOG_POS=599;

从599开始进行前滚

mysql -S /data/my3306/run/mysql.sock  -e "show binlog events in 'binlog.000013'" |grep -i truncate
binlog.000013   929     Query   3306    1010    use `db1`; truncate gyj_t1

或者用mysqlbinglog查看

mysqlbinlog binlog.000013
# at 929
#180222  0:40:34 server id 3306  end_log_pos 1010 CRC32 0x28417934      Query   thread_id=13    exec_time=0     error_code=0
SET TIMESTAMP=1519231234/*!*/;
truncate gyj_t1

前滚到929

mysqlbinlog binlog.000013 --start-position 599 --stop-position 929| mysql -uroot -p123456 -h127.0.0.1 -S=/data/my3306/run/mysql.sock

最终如下

mysql>  select * from gyj_t1;
+------+--------+
| id   | name   |
+------+--------+
|    1 | AAAAA  |
|    2 | BBBBBB |
+------+--------+
2 rows in set (0.00 sec)

3.误操作MySQL数据库：rm /u01/my3306/data/*;利用Innobackupex的备份和binlog日志对MySQL数据库做完全恢复。

https://www.percona.com/downloads/XtraBackup/LATEST/

wget https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.4.9/binary/tarball/percona-xtrabackup-2.4.9-Linux-x86_64.tar.gz
tar -zxvf percona-xtrabackup-2.4.9-Linux-x86_64.tar.gz
cd percona-xtrabackup-2.4.9-Linux-x86_64
cp -rp bin /usr/bin/xtrabackup

先插入数据

mysql> create table gyj_t2(id int,name varchar(10));
Query OK, 0 rows affected (0.14 sec)

mysql> insert into gyj_t2 values(1,'AAAAA');
Query OK, 1 row affected (0.10 sec)

mysql> insert into gyj_t2 values(2,'BBBBBB');
Query OK, 1 row affected (0.00 sec)

mysql> select * from gyj_t2;
+------+--------+
| id   | name   |
+------+--------+
|    1 | AAAAA  |
|    2 | BBBBBB |
+------+--------+
2 rows in set (0.00 sec)

使用xtrabackup备份全库

innobackupex --defaults-file=/data/my3306/my.cnf --user=root --password='123456' --host=127.0.0.1 --socket=/data/my3306/run/mysql.sock --port=3306 /backup

日志

180222 09:27:05 Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS...
xtrabackup: The latest check point (for incremental): '3014721'
xtrabackup: Stopping log copying thread.
.180222 09:27:05 >> log scanned up to (3014730)

180222 09:27:05 Executing UNLOCK TABLES
180222 09:27:05 All tables unlocked
180222 09:27:05 Backup created in directory '/backup/2018-02-22_09-26-59/'
MySQL binlog position: filename 'binlog.000013', position '5538'
180222 09:27:05 [00] Writing /backup/2018-02-22_09-26-59/backup-my.cnf
180222 09:27:05 [00]        ...done
180222 09:27:05 [00] Writing /backup/2018-02-22_09-26-59/xtrabackup_info
180222 09:27:05 [00]        ...done
xtrabackup: Transaction log of lsn (3014721) to (3014730) was copied.
180222 09:27:05 completed OK!

备份后插入数据

mysql> insert into gyj_t2 values(3,'CCCCC');
Query OK, 1 row affected (0.09 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from gyj_t2;select * from gyj_t2;
+------+--------+
| id   | name   |
+------+--------+
|    1 | AAAAA  |
|    2 | BBBBBB |
|    3 | CCCCC  |
+------+--------+
3 rows in set (0.00 sec)

误操作删除数据目录rm -rf /data/my3306/data/*

[root@mysql01 my3306]# mv data data.bak
[root@mysql01 my3306]# ps -ef|grep mysql
root      3114  6440  0 09:28 pts/2    00:00:00 grep --color=auto mysql
root      5092  2345  0 Feb21 pts/0    00:00:00 /bin/sh /data/my3306/bin/mysqld_safe --defaults-file=/data/my3306/my.cnf
mysql     5956  5092  0 Feb21 pts/0    00:00:16 /data/my3306/bin/mysqld --defaults-file=/data/my3306/my.cnf --basedir=/data/my3306 --datadir=/data/my3306/data --plugin-dir=/data/my3306/lib/plugin --user=mysql --log-error=/data/my3306/log/error.log --open-files-limit=65535 --pid-file=/data/my3306/run/mysqld.pid --socket=/data/my3306/run/mysql.sock --port=3306
root      7515  2345  0 00:10 pts/0    00:00:00 mysql
[root@mysql01 my3306]# kill -9 5092 5956

对备份进行日志重做apply-log

innobackupex --defaults-file=/data/my3306/my.cnf --apply-log --user=root --password='123456' --port=3306 --host=127.0.0.1 --socket=/data/my3306/run/mysql.sock /backup/2018-02-22_09-26-59

日志

InnoDB: page_cleaner: 1000ms intended loop took 14313ms. The settings might not be optimal. (flushed=0 and evicted=0, during the time.)
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
InnoDB: FTS optimize thread exiting.
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 3015208
180222 09:32:06 completed OK!

[root@mysql01 2018-02-22_09-26-59]# cat xtrabackup_checkpoints
backup_type = full-prepared
from_lsn = 0
to_lsn = 3014721
last_lsn = 3014730
compact = 0
recover_binlog_info = 0

copy-back数据文件（需要先执行目录date和iblog清空）

innobackupex --defaults-file=/data/my3306/my.cnf --copy-back --user=root --password='123456' --port=3306 --host=127.0.0.1  /backup/2018-02-22_09-26-59

日志

180222 09:38:00 [01]        ...done
180222 09:38:00 [01] Copying ./xtrabackup_info to /data/my3306/data/xtrabackup_info
180222 09:38:00 [01]        ...done
180222 09:38:00 [01] Copying ./xtrabackup_binlog_pos_innodb to /data/my3306/data/xtrabackup_binlog_pos_innodb
180222 09:38:00 [01]        ...done
180222 09:38:00 [01] Copying ./ibtmp1 to /data/my3306/data/ibtmp1
180222 09:38:01 [01]        ...done
180222 09:38:01 completed OK!

开启实例查看数据

mysql> select * from gyj_t2;
+------+--------+
| id   | name   |
+------+--------+
|    1 | AAAAA  |
|    2 | BBBBBB |
+------+--------+
2 rows in set (0.10 sec)

从binlog前滚恢复

[root@mysql01 2018-02-22_09-26-59]# cat xtrabackup_binlog_info
binlog.000013   5538
[root@mysql01 binlog]# pwd
/data/my3306/log/binlog
[root@mysql01 2018-02-22_09-26-59]# mysqlbinlog binlog.000013 --start-position 5538 | mysql -uroot -p123456 -h127.0.0.1 -S=/data/my3306/run/mysql.sock

查看最终结果

mysql> select * from gyj_t2;
+------+--------+
| id   | name   |
+------+--------+
|    1 | AAAAA  |
|    2 | BBBBBB |
|    3 | CCCCC  |
+------+--------+
3 rows in set (0.00 sec)