利用mysql全备 +binlog server恢复方法之伪装master
单实例试验
一、试验环境
10.72.7.40
实例 mysql3306为要恢复的对象,mysql3306的全备+binlog server(目录/data/mysql/mysql3306/backup)
实例mysql3307为伪装master
实例mysql3308为伪装master的slave,在其上恢复数据
1、mysql3306全备
innobackupex --defaults-file=/data/mysql/mysql3306/mysql3306.cnf -S /tmp/mysql3306.sock -uroot -phch123 /root/backup
2、mysql3306的binlog server
root@localhost:mysql3306.sock [zst1]>show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin. | |
| mysql-bin. | |
+------------------+-----------+
rows in set (0.00 sec)
cd /data/mysql/mysql3306/backup
[root@bogon backup]# nohup mysqlbinlog --raw --read-from-remote-server --host=10.72.7.40 --port= --user=root --password=hch123 --stop-never mysql-bin. &
3、查看3306的数据
root@localhost:mysql3306.sock [zst1]>select count(*) from tb1;
+----------+
| count(*) |
+----------+
| |
+----------+
row in set (0.00 sec)
模拟数据写入
root@localhost:mysql3306.sock [zst1]>insert into tb1(c1, c2) select user,host from mysql.user;
Query OK, rows affected (0.09 sec)
Records: Duplicates: Warnings:
root@localhost:mysql3306.sock [zst1]>insert into tb1(c1, c2) select user,host from mysql.user;
Query OK, rows affected (0.14 sec)
Records: Duplicates: Warnings:
root@localhost:mysql3306.sock [zst1]>select count(*) from tb1;
+----------+
| count(*) |
+----------+
| |
+----------+
row in set (0.00 sec)
root@localhost:mysql3306.sock [zst1]>select @@server_uuid;
+--------------------------------------+
| @@server_uuid |
+--------------------------------------+
| d20b918a-96c9-11e8-aae4-000c2969aede |
+--------------------------------------+
row in set (0.00 sec)
查看目前binlog位置
root@localhost:mysql3306.sock [zst1]>show master status;
+------------------+----------+--------------+------------------+-----------------------------------------------------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-----------------------------------------------------------------------------------------+
| mysql-bin. | | | | 959b9f31-75ef-11e8-97de-000c2969aede:-,
d20b918a-96c9-11e8-aae4-000c2969aede:- |
+------------------+----------+--------------+------------------+-----------------------------------------------------------------------------------------+
row in set (0.00 sec)
模拟误操作
root@localhost:mysql3306.sock [zst1]>truncate table tb1;
Query OK, rows affected (0.08 sec)
root@localhost:mysql3306.sock [zst1]>select * from tb1;
Empty set (0.00 sec)
root@localhost:mysql3306.sock [zst1]>select count(*) from tb1;
+----------+
| count(*) |
+----------+
| |
+----------+
row in set (0.00 sec)
继续写入数据
root@localhost:mysql3306.sock [zst1]>insert into tb1(c1, c2) select user,host from mysql.user;
Query OK, rows affected (0.09 sec)
Records: Duplicates: Warnings:
root@localhost:mysql3306.sock [zst1]>select count(*) from tb1;
+----------+
| count(*) |
+----------+
| |
+----------+
row in set (0.00 sec)
刷新binlog
root@localhost:mysql3306.sock [zst1]>flush logs;
Query OK, rows affected (0.11 sec)
查看binlog信息
root@localhost:mysql3306.sock [zst1]>show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin. | |
| mysql-bin. | |
| mysql-bin. | |
+------------------+-----------+
rows in set (0.00 sec)
root@localhost:mysql3306.sock [zst1]>show master status;
+------------------+----------+--------------+------------------+-----------------------------------------------------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-----------------------------------------------------------------------------------------+
| mysql-bin. | | | | 959b9f31-75ef-11e8-97de-000c2969aede:-,
d20b918a-96c9-11e8-aae4-000c2969aede:- |
+------------------+----------+--------------+------------------+-----------------------------------------------------------------------------------------+
row in set (0.00 sec)
二、查看恢复位置
解析binlog
[root@bogon data]# mysqlbinlog -v --base64-output=decode-rows mysql-bin. > .sql
恢复的位置为 mysql-bin.000005 1248,d20b918a-96c9-11e8-aae4-000c2969aede:18
三、创建伪装master 3308
1、初始化实例3308、启动并修改密码,省略……
2、查看3308 uuid信息
[root@bogon backup]# mysql -S /tmp/mysql3308.sock -uroot -phch123
查看uuid
root@localhost:mysql3308.sock [(none)]>select @@server_uuid;
+--------------------------------------+
| @@server_uuid |
+--------------------------------------+
| 8db05acd-a0f1-11e8-ad63-000c2969aede |
+--------------------------------------+
row in set (0.00 sec)
root@localhost:mysql3308.sock [(none)]>show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin. | |
| mysql-bin. | |
+------------------+-----------+
rows in set (0.00 sec)
3、将3306的binlog复制到3308上
先关闭3308实例
root@localhost:mysql3308.sock [(none)]>shutdown;
Query OK, rows affected (0.00 sec)
root@localhost:mysql3308.sock [(none)]>exit
删除3308的binlog
[root@bogon logs]# pwd
/data/mysql/mysql3308/logs
[root@bogon logs]# ll -thr
total 12K
-rw-r-----. mysql mysql Aug : mysql-bin.
-rw-r-----. mysql mysql Aug : mysql-bin.index
-rw-r-----. mysql mysql Aug : mysql-bin.
[root@bogon logs]# cat mysql-bin.index
/data/mysql/mysql3308/logs/mysql-bin.
/data/mysql/mysql3308/logs/mysql-bin.
拷贝3306binlog server的binlog至3308
[root@bogon logs]# cp /data/mysql/mysql3306/backup/* ./
[root@bogon logs]# ll -thr
total 16K
-rw-------. 1 root root 279 Aug 15 21:53 nohup.out
-rw-r-----. 1 root root 234 Aug 15 21:53 mysql-bin.000006
-rw-r-----. 1 root root 2.0K Aug 15 21:53 mysql-bin.000005
-rw-r-----. 1 root root 234 Aug 15 21:53 mysql-bin.000004
生成mysql-bin.index
[root@bogon logs]# ls /data/mysql/mysql3308/logs/mysql-bin.* > mysql-bin.index
[root@bogon logs]# cat mysql-bin.index
/data/mysql/mysql3308/logs/mysql-bin.
/data/mysql/mysql3308/logs/mysql-bin.
/data/mysql/mysql3308/logs/mysql-bin.
[root@bogon logs]# ll -thr
total 20K
-rw-------. root root Aug : nohup.out
-rw-r-----. root root Aug : mysql-bin.
-rw-r-----. root root .0K Aug : mysql-bin.
-rw-r-----. root root Aug : mysql-bin.
-rw-r--r--. root root Aug : mysql-bin.index
[root@bogon logs]# chown mysql. *
[root@bogon logs]# ll -thr
total 20K
-rw-------. mysql mysql Aug : nohup.out
-rw-r-----. mysql mysql Aug : mysql-bin.
-rw-r-----. mysql mysql .0K Aug : mysql-bin.
-rw-r-----. mysql mysql Aug : mysql-bin.
-rw-r--r--. mysql mysql Aug : mysql-bin.index
[root@bogon logs]# rm -rf nohup.out
启动3308实例
[root@bogon backup]# /usr/local/mysql/bin/mysqld --defaults-file=/data/mysql/mysql3308/mysql3308.cnf &
[root@bogon backup]# mysql -S /tmp/mysql3308.sock -uroot -phch123
root@localhost:mysql3308.sock [(none)]>select @@server_uuid;
+--------------------------------------+
| @@server_uuid |
+--------------------------------------+
| 8db05acd-a0f1-11e8-ad63-000c2969aede |
+--------------------------------------+
row in set (0.00 sec)
可以看到binlog已经识别出来了
root@localhost:mysql3308.sock [(none)]>show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin. | |
| mysql-bin. | |
| mysql-bin. | |
| mysql-bin. | |
| mysql-bin. | |
+------------------+-----------+
rows in set (0.00 sec)
root@localhost:mysql3308.sock [(none)]>show master status;
+------------------+----------+--------------+------------------+---------------------------------------------------------------------------------------------------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set