MySQL全备+binlog恢复方法之伪装master

lmr廖

已于 2023-06-21 16:28:24 修改

阅读量490

点赞数

文章标签： mysql 数据库服务器

于 2023-06-21 16:15:10 首次发布

本文链接：https://blog.csdn.net/lmrylll/article/details/131329822

版权

本文详细介绍了如何利用MySQL全备和binlog恢复数据，包括伪装master的方法。首先，介绍了试验环境和步骤，如全备、binlog定位、创建伪装master。然后，演示了在伪装master上创建slave并解决1872错误的过程。最后，提供了多台机器上的试验环境和步骤，强调了gtid在恢复过程中关键作用。

摘要由CSDN通过智能技术生成

利用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

最低0.47元/天解锁文章

lmr廖

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
打赏
0
评论
MySQL全备+binlog恢复方法之伪装master

发现又多了个61cfd125-a0f2-11e8-b8bc-000c2969aede:1-2，不太清楚是哪个gtid，查看3307的uuid也不是这个，为了影响实验效果，下面的gtid_purged也把这个加入进去了。查看备份的binlog位置为959b9f31-75ef-11e8-97de-000c2969aede:1-61970,d20b918a-96c9-11e8-aae4-000c2969aede:1-15。先备份3309的datadir，并清除里面的文件。
复制链接

扫一扫