java伪装成mysql从节点_MySQL全备+binlog恢复方法之伪装master【原创】

利用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.000004 | 234 |

| mysql-bin.000005 | 234 |

+------------------+-----------+

2 rows in set (0.00sec)

cd/data/mysql/mysql3306/backup

[root@bogon backup]# nohup mysqlbinlog--raw --read-from-remote-server --host=10.72.7.40 --port=3306 --user=root --password=hch123 --stop-never mysql-bin.000004 &

3、查看3306的数据

root@localhost:mysql3306.sock [zst1]>select count(*) from tb1;+----------+

| count(*) |

+----------+

| 35 |

+----------+

1 row in set (0.00sec)

模拟数据写入

root@localhost:mysql3306.sock [zst1]>insert into tb1(c1, c2) selectuser,host from mysql.user;

Query OK,9 rows affected (0.09sec)

Records:9 Duplicates: 0 Warnings: 0root@localhost:mysql3306.sock [zst1]>insert into tb1(c1, c2) selectuser,host from mysql.user;

Query OK,9 rows affected (0.14sec)

Records:9 Duplicates: 0 Warnings: 0root@localhost:mysql3306.sock [zst1]>select count(*) from tb1;+----------+

| count(*) |

+----------+

| 53 |

+----------+

1 row in set (0.00sec)

root@localhost:mysql3306.sock [zst1]>select@@server_uuid;+--------------------------------------+

| @@server_uuid |

+--------------------------------------+

| d20b918a-96c9-11e8-aae4-000c2969aede |

+--------------------------------------+

1 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.000005 | 1248 | | | 959b9f31-75ef-11e8-97de-000c2969aede:1-61970,

d20b918a-96c9-11e8-aae4-000c2969aede:1-17 |

+------------------+----------+--------------+------------------+-----------------------------------------------------------------------------------------+

1 row in set (0.00 sec)

模拟误操作

root@localhost:mysql3306.sock [zst1]>truncate table tb1;

Query OK,0 rows affected (0.08sec)

root@localhost:mysql3306.sock [zst1]>select *from tb1;

Empty set (0.00sec)

root@localhost:mysql3306.sock [zst1]>select count(*) from tb1;+----------+

| count(*) |

+----------+

| 0 |

+----------+

1 row in set (0.00 sec)

继续写入数据

root@localhost:mysql3306.sock [zst1]>insert into tb1(c1, c2) selectuser,host from mysql.user;

Query OK,9 rows affected (0.09sec)

Records:9 Duplicates: 0 Warnings: 0root@localhost:mysql3306.sock [zst1]>select count(*) from tb1;+----------+

| count(*) |

+----------+

| 9 |

+----------+

1 row in set (0.00 sec)

刷新binlog

root@localhost:mysql3306.sock [zst1]>flush logs;

Query OK,0 rows affected (0.11 sec)

查看binlog信息

root@localhost:mysql3306.sock [zst1]>show binary logs;+------------------+-----------+

| Log_name | File_size |

+------------------+-----------+

| mysql-bin.000004 | 234 |

| mysql-bin.000005 | 1952 |

| mysql-bin.000006 | 234 |

+------------------+-----------+

3 rows in set (0.00sec)

root@localhost:mysql3306.sock [zst1]>show master status;+------------------+----------+--------------+------------------+-----------------------------------------------------------------------------------------+

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

+------------------+----------+--------------+------------------+-----------------------------------------------------------------------------------------+

| mysql-bin.000006 | 234 | | | 959b9f31-75ef-11e8-97de-000c2969aede:1-61970,

d20b918a-96c9-11e8-aae4-000c2969aede:1-19 |

+------------------+----------+--------------+------------------+-----------------------------------------------------------------------------------------+

1 row in set (0.00 sec)

二、查看恢复位置

解析binlog

[root@bogon data]# mysqlbinlog -v --base64-output=decode-rows mysql-bin.000005 > 5.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 |

+--------------------------------------+

1 row in set (0.00sec)

root@localhost:mysql3308.sock [(none)]>show binary logs;+------------------+-----------+

| Log_name | File_size |

+------------------+-----------+

| mysql-bin.000001 | 177 |

| mysql-bin.000002 | 845 |

+------------------+-----------+

2 rows in set (0.00 sec)

3、将3306的binlog复制到3308上

先关闭3308实例

root@localhost:mysql3308.sock [(none)]>shutdown;

Query OK,0 rows affected (0.00sec)

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-----. 1 mysql mysql 177 Aug 15 21:13 mysql-bin.000001

-rw-r-----. 1 mysql mysql 88 Aug 15 21:16 mysql-bin.index-rw-r-----. 1 mysql mysql 868 Aug 15 21:50 mysql-bin.000002[root@bogon logs]#cat mysql-bin.index/data/mysql/mysql3308/logs/mysql-bin.000001

/data/mysql/mysql3308/logs/mysql-bin.000002

拷贝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.00000* > mysql-bin.index

[root@bogon logs]#cat mysql-bin.index/data/mysql/mysql3308/logs/mysql-bin.000004

/data/mysql/mysql3308/logs/mysql-bin.000005

/data/mysql/mysql3308/logs/mysql-bin.000006[root@bogon logs]# ll-thr

total 20K-rw-------. 1 root root 279 Aug 15 21:53nohup.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

-rw-r--r--. 1 root root 258 Aug 15 21:55 mysql-bin.index

[root@bogon logs]#chown mysql. *[root@bogon logs]# ll-thr

total 20K-rw-------. 1 mysql mysql 279 Aug 15 21:53nohup.out-rw-r-----. 1 mysql mysql 234 Aug 15 21:53 mysql-bin.000006

-rw-r-----. 1 mysql mysql 2.0K Aug 15 21:53 mysql-bin.000005

-rw-r-----. 1 mysql mysql 234 Aug 15 21:53 mysql-bin.000004

-rw-r--r--. 1 mysql mysql 258 Aug 15 21:55 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 |

+--------------------------------------+

1 row in set (0.00 sec)

可以看到binlog已经识别出来了

root@localhost:mysql3308.sock [(none)]>show binary logs;+------------------+-----------+

| Log_name | File_size |

+------------------+-----------+

| mysql-bin.000004 | 234 |

| mysql-bin.000005 | 1952 |