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

原创

已于 2023-06-21 16:28:24 修改 · 604 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#mysql #数据库 #服务器

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

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

利用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                                                                                                                     |
+------------------+----------+--------------+------------------+---------------------------------------------------------------------------------------------------------------------------------------+
| mysql-bin. |       |              |                  | 8db05acd-a0f1-11e8-ad63-000c2969aede:-,
959b9f31-75ef-11e8-97de-000c2969aede:-,
d20b918a-96c9-11e8-aae4-000c2969aede:- |
+------------------+----------+--------------+------------------+---------------------------------------------------------------------------------------------------------------------------------------+
 row in set (0.00 sec)

可以看到Executed_Gtid_Set中多了8db05acd-a0f1-11e8-ad63-000c2969aede:1-3,需要在slave 中gtid purged掉,否则会报1236错误(因为刚才新建账号产生的log已经被删除了)

四、创建伪装master3308的slave3309

1、用3306的全备恢复

[root@bogon --15_21--]# pwd
/root/backup/--15_21--
[root@bogon --15_21--]# innobackupex --apply-log /root/backup/--15_21--

关闭3309

[root@bogon --15_21--]#mysql -S /tmp/mysql3309.sock -uroot -p
root@localhost:mysql3309.sock [(none)]>shutdown;

先备份3309的datadir，并清除里面的文件

[root@bogon data]# pwd
/data/mysql/mysql3309/data
[root@bogon mysql3309]# cp -a data/ data_bak
[root@bogon mysql3309]# cd data
[root@bogon data]# rm -rf *

将还原文件拷贝过来

[root@bogon data]# cp -r /root/backup/--15_21--/* /data/mysql/mysql3309/data/

修改权限

[root@bogon data]# chown -R mysql. *
 
[root@bogon data]# ll -thr
total 421M
-rw-r-----.  mysql mysql   Aug  : backup-my.cnf
drwxr-x---.  mysql mysql    Aug  : hch
drwxr-x---.  mysql mysql    Aug  : hch1
-rw-r-----.  mysql mysql   Aug  : ib_buffer_pool
-rw-r-----.  mysql mysql 100M Aug  : ibdata1
-rw-r-----.  mysql mysql 100M Aug  : ib_logfile0
-rw-r-----.  mysql mysql 100M Aug  : ib_logfile1
-rw-r-----.  mysql mysql 100M Aug  : ib_logfile2
-rw-r-----.  mysql mysql  12M Aug  : ibtmp1
drwxr-x---.  mysql mysql .0K Aug  : mysql
drwxr-x---.  mysql mysql .0K Aug  : performance_schema
drwxr-x---.  mysql mysql .0K Aug  : sys
drwxr-x---.  mysql mysql    Aug