Mysql基础运维及复制架构——PRIT非完整恢复

1，表误删除恢复

2018-05-21 17:54左右，三张表被意外truncate，数据全部丢失

root@localhost:xtrabackup 05:54:57>drop table xtrabackup;

root@localhost:xtrabackup 05:56:08>flush logs;

考虑到该MySQL服务器核心库每天都是在定时备份的，希望通过备份进行数据恢复

[root@master xdatabase]# pwd

/XtraBackup/xdatabase

[root@master xdatabase]# ls -l

drwx------ 3 root root 4096 May 21 16:20 2018-05-21_16-19-44

Eg:

[root@master xdatabase]# innobackupex --defaults-file=/etc/my.cnf --socket=/tmp/mysql.sock --port=3306 -- databases=xtrabackup -uroot -pmysql /XtraBackup/xdatabase

 MySQL目标服务器环境准备

[root@master xdatabase]# tar -cvf 2018-05-21_16-19-44.tar 2018-05-21_16-19-44

[root@master xdatabase]# scp 2018-05-21_16-19-44.tar root@192.168.210.126:/XtraBackup/ root@192.168.210.126's password:

2018-05-21_16-19-44.tar 100% 587MB 24.5MB/s 00:24

[root@slave XtraBackup]# tar -xvf 2018-05-21_16-19-44.tar

 MySQL目标服务器环境全备恢复

[root@slave XtraBackup]# innobackupex --apply-log /XtraBackup/2018-05-21_16-19-44

[root@slave data]# service mysqld stop

Shutting down MySQL.... [ OK ]

[root@slave data]# pwd /mysql/app/data

[root@slave data]# rm -rf xtrabackup

[root@slave data]# rm -rf ib*

[root@slave 2018-05-21_16-19-44]# innobackupex --defaults-file=/etc/my.cnf --copy-back -- force-non-empty-directories /XtraBackup/2018-05-21_16-19-44

[root@slave 2018-05-21_16-19-44]# chown -R mysql:mysql /mysql/app/data

[root@slave 2018-05-21_16-19-44]# /etc/init.d/mysqld start

Starting MySQL.. [ OK ]

 MySQL服务器定位binlog二进制文件

root@localhost:(none) 05:34:10>show variables like 'log_bin%';

[root@master xdatabase]# ls -l /mysql/app/data/master-bin*

 MySQL服务器定位二进制上下文

 

 

 MySQL目标服务器执行PITR

[root@slave data]# more xtrabackup_binlog_pos_innodb

master-bin.000003 404387016

[root@slave ~]# mysqlbinlog --read-from-remote-server --host=192.168.210.125 --user=repl --password=mysql -- database=xtrabackup --force-if-open --base64-output=never --start-position=404387016 --stopposition=404390810 master-bin.000003 | mysql -uroot –pmysql

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

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