百度360必应搜狗淘宝本站头条
tomcat 乱码无法与服务器建立安全连接是什么意思sysv-rc-confdocker映射容器目录到宿主机internet explorer 增强的安全配置已启用
当前位置：网站首页 > 技术文章 > 正文

利用mysql全备 +binlog server恢复方法之伪装master(单台服务器)

nanshan 2024-10-08 05:33 13 浏览 0 评论

单实例试验

一、试验环境

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.00 sec)
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.00 sec)
模拟数据写入
root@localhost:mysql3306.sock [zst1]>insert into tb1(c1, c2) select user,host from mysql.user;
Query OK, 9 rows affected (0.09 sec)
Records: 9 Duplicates: 0 Warnings: 0
root@localhost:mysql3306.sock [zst1]>insert into tb1(c1, c2) select user,host from mysql.user;
Query OK, 9 rows affected (0.14 sec)
Records: 9 Duplicates: 0 Warnings: 0
root@localhost:mysql3306.sock [zst1]>select count(*) from tb1;
+----------+
| count(*) |
+----------+
| 53 |
+----------+
1 row in set (0.00 sec)
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.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(*) |
+----------+
| 0 |
+----------+
1 row in set (0.00 sec)

继续写入数据

root@localhost:mysql3306.sock [zst1]>insert into tb1(c1, c2) select user,host from mysql.user;
Query OK, 9 rows affected (0.09 sec)
Records: 9 Duplicates: 0 Warnings: 0
root@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.00 sec)
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.00 sec)
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.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-----. 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: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
-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:53 nohup.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 |
| mysql-bin.000006 | 234 |
| mysql-bin.000007 | 257 |
| mysql-bin.000008 | 234 |
+------------------+-----------+
5 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.000008 | 234 | | | 8db05acd-a0f1-11e8-ad63-000c2969aede:1-3,
959b9f31-75ef-11e8-97de-000c2969aede:61965-61970,
d20b918a-96c9-11e8-aae4-000c2969aede:1-19 |
+------------------+----------+--------------+------------------+---------------------------------------------------------------------------------------------------------------------------------------+
1 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 2018-08-15_21-23-26]# pwd
/root/backup/2018-08-15_21-23-26
[root@bogon 2018-08-15_21-23-26]# innobackupex --apply-log /root/backup/2018-08-15_21-23-26

关闭3309

[root@bogon 2018-08-15_21-23-26]#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/2018-08-15_21-23-26/* /data/mysql/mysql3309/data/

修改权限

[root@bogon data]# chown -R mysql. *
[root@bogon data]# ll -thr
total 421M
-rw-r-----. 1 mysql mysql 537 Aug 15 22:17 backup-my.cnf
drwxr-x---. 2 mysql mysql 86 Aug 15 22:17 hch
drwxr-x---. 2 mysql mysql 50 Aug 15 22:17 hch1
-rw-r-----. 1 mysql mysql 553 Aug 15 22:17 ib_buffer_pool
-rw-r-----. 1 mysql mysql 100M Aug 15 22:17 ibdata1
-rw-r-----. 1 mysql mysql 100M Aug 15 22:17 ib_logfile0
-rw-r-----. 1 mysql mysql 100M Aug 15 22:17 ib_logfile1
-rw-r-----. 1 mysql mysql 100M Aug 15 22:17 ib_logfile2
-rw-r-----. 1 mysql mysql 12M Aug 15 22:17 ibtmp1
drwxr-x---. 2 mysql mysql 4.0K Aug 15 22:17 mysql
drwxr-x---. 2 mysql mysql 8.0K Aug 15 22:17 performance_schema
drwxr-x---. 2 mysql mysql 8.0K Aug 15 22:17 sys
drwxr-x---. 2 mysql mysql 52 Aug 15 22:17 test
drwxr-x---. 2 mysql mysql 48 Aug 15 22:17 userdb
drwxr-x---. 2 mysql mysql 4.0K Aug 15 22:17 wubx
-rw-r--r--. 1 mysql mysql 22 Aug 15 22:17 xtrabackup_binlog_pos_innodb
-rw-r-----. 1 mysql mysql 109 Aug 15 22:17 xtrabackup_binlog_info
-rw-r-----. 1 mysql mysql 651 Aug 15 22:17 xtrabackup_info
-rw-r-----. 1 mysql mysql 117 Aug 15 22:17 xtrabackup_checkpoints
-rw-r-----. 1 mysql mysql 8.0M Aug 15 22:17 xtrabackup_logfile
-rw-r--r--. 1 mysql mysql 1 Aug 15 22:17 xtrabackup_master_key_id
drwxr-x---. 2 mysql mysql 192 Aug 15 22:17 zst
drwxr-x---. 2 mysql mysql 50 Aug 15 22:17 zst1

查看备份的binlog位置为959b9f31-75ef-11e8-97de-000c2969aede:1-61970,d20b918a-96c9-11e8-aae4-000c2969aede:1-15

启动3309实例

/usr/local/mysql/bin/mysqld --defaults-file=/data/mysql/mysql3309/mysql3309.cnf &
[root@bogon data]# mysql -S /tmp/mysql3309.sock -uroot -phch123
root@localhost:mysql3309.sock [(none)]>show master status;
+------------------+----------+--------------+------------------+-----------------------------------------------------------------------------------------------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-----------------------------------------------------------------------------------------------------------------------------------+
| mysql-bin.000003 | 194 | | | 61cfd125-a0f2-11e8-b8bc-000c2969aede:1-2,
959b9f31-75ef-11e8-97de-000c2969aede:1-61970,
d20b918a-96c9-11e8-aae4-000c2969aede:1-15 |
+------------------+----------+--------------+------------------+-----------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

发现又多了个61cfd125-a0f2-11e8-b8bc-000c2969aede:1-2，不太清楚是哪个gtid，查看3307的uuid也不是这个，为了影响实验效果，下面的gtid_purged也把这个加入进去了

查看数据

root@localhost:mysql3309.sock [(none)]>select count(*) from zst1.tb1;
+----------+
| count(*) |
+----------+
| 35 |
+----------+
1 row in set (0.00 sec)

设置gtid_purged，别忘记加入3308改密码的gtid，并且加入了61cfd125-a0f2-11e8-b8bc-000c2969aede:1-2

root@localhost:mysql3309.sock [(none)]>reset master;
Query OK, 0 rows affected (0.03 sec)
root@localhost:mysql3309.sock [(none)]>set global gtid_purged='959b9f31-75ef-11e8-97de-000c2969aede:1-61970,d20b918a-96c9-11e8-aae4-000c2969aede:1-15,8db05acd-a0f1-11e8-ad63-000c2969aede:1-3,61cfd125-a0f2-11e8-b8bc-000c2969aede:1-2';
Query OK, 0 rows affected (0.00 sec)
root@localhost:mysql3309.sock [(none)]>change master to master_host='10.72.7.40', master_port=3308, master_user='hch', master_password='hch123', master_auto_position=1;
Query OK, 0 rows affected, 2 warnings (0.02 sec)
root@localhost:mysql3309.sock [(none)]>start slave sql_thread until sql_before_gtids='d20b918a-96c9-11e8-aae4-000c2969aede:18';
Query OK, 0 rows affected (0.01 sec)
root@localhost:mysql3309.sock [(none)]>show slave status\G; 
*************************** 1. row ***************************
 Slave_IO_State:
 Master_Host: 10.72.7.40
 Master_User: hch
 Master_Port: 3308
 Connect_Retry: 60
 Master_Log_File:
 Read_Master_Log_Pos: 4
 Relay_Log_File: bogon-relay-bin.000001
 Relay_Log_Pos: 4
 Relay_Master_Log_File:
 Slave_IO_Running: No
 Slave_SQL_Running: Yes
 Replicate_Do_DB:
 Replicate_Ignore_DB:
 Replicate_Do_Table:
 Replicate_Ignore_Table:
 Replicate_Wild_Do_Table:
 Replicate_Wild_Ignore_Table:
 Last_Errno: 0
 Last_Error:
 Skip_Counter: 0
 Exec_Master_Log_Pos: 0
 Relay_Log_Space: 154
 Until_Condition: SQL_BEFORE_GTIDS
 Until_Log_File:
 Until_Log_Pos: 0
 Master_SSL_Allowed: No
 Master_SSL_CA_File:
 Master_SSL_CA_Path:
 Master_SSL_Cert:
 Master_SSL_Cipher:
 Master_SSL_Key:
 Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
 Last_IO_Errno: 0
 Last_IO_Error:
 Last_SQL_Errno: 0
 Last_SQL_Error:
 Replicate_Ignore_Server_Ids:
 Master_Server_Id: 0
 Master_UUID:
 Master_Info_File: mysql.slave_master_info
 SQL_Delay: 0
 SQL_Remaining_Delay: NULL
 Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
 Master_Retry_Count: 86400
 Master_Bind:
 Last_IO_Error_Timestamp:
 Last_SQL_Error_Timestamp:
 Master_SSL_Crl:
 Master_SSL_Crlpath:
 Retrieved_Gtid_Set:
 Executed_Gtid_Set: 61cfd125-a0f2-11e8-b8bc-000c2969aede:1-2,
8db05acd-a0f1-11e8-ad63-000c2969aede:1-3,
959b9f31-75ef-11e8-97de-000c2969aede:1-61970,
d20b918a-96c9-11e8-aae4-000c2969aede:1-15
 Auto_Position: 1
 Replicate_Rewrite_DB:
 Channel_Name:
 Master_TLS_Version:
1 row in set (0.00 sec)
ERROR:
No query specified
root@localhost:mysql3309.sock [(none)]>select count(*) from zst1.tb1; 
+----------+
| count(*) |
+----------+
| 35 |
+----------+
1 row in set (0.01 sec)
root@localhost:mysql3309.sock [(none)]>start slave io_thread;
Query OK, 0 rows affected (0.00 sec)
root@localhost:mysql3309.sock [(none)]>select count(*) from zst1.tb1;
+----------+
| count(*) |
+----------+
| 53 |
+----------+
1 row in set (0.00 sec)

发现此时数据已经恢复了。

问题

报错ERROR 1872 (HY000): Slave failed to initialize relay log info structure from the repository

原因：设置gtid_purged，没有加入了61cfd125-a0f2-11e8-b8bc-000c2969aede:1-2

root@localhost:mysql3309.sock [(none)]>set global gtid_purged='959b9f31-75ef-11e8-97de-000c2969aede:1-61970,d20b918a-96c9-11e8-aae4-000c2969aede:1-15,8db05acd-a0f1-11e8-ad63-000c2969aede:1-3';

Query OK, 0 rows affected (0.02 sec)

root@localhost:mysql3309.sock [(none)]>change master to master_host='10.72.7.40', master_port=3308, master_user='hch', master_password='hch123', master_auto_position=1;

Query OK, 0 rows affected, 2 warnings (0.09 sec)

root@localhost:mysql3309.sock [(none)]>start slave io_thread;

ERROR 1872 (HY000): Slave failed to initialize relay log info structure from the repository

root@localhost:mysql3309.sock [(none)]>show slave status\G;

*************************** 1. row ***************************

Slave_IO_State:

Master_Host: 10.72.7.40

Master_User: hch

Master_Port: 3308

Connect_Retry: 60

Master_Log_File:

Read_Master_Log_Pos: 4

Relay_Log_File: bogon-relay-bin.000001

Relay_Log_Pos: 4

Relay_Master_Log_File:

Slave_IO_Running: No

Slave_SQL_Running: No

Replicate_Do_DB:

Replicate_Ignore_DB:

Replicate_Do_Table:

Replicate_Ignore_Table:

Replicate_Wild_Do_Table:

Replicate_Wild_Ignore_Table:

Last_Errno: 1872

Last_Error: Slave failed to initialize relay log info structure from the repository

Skip_Counter: 0

Exec_Master_Log_Pos: 0

Relay_Log_Space: 154

Until_Condition: None

Until_Log_File:

Until_Log_Pos: 0

Master_SSL_Allowed: No

Master_SSL_CA_File:

Master_SSL_CA_Path:

Master_SSL_Cert:

Master_SSL_Cipher:

Master_SSL_Key:

Seconds_Behind_Master: NULL

Master_SSL_Verify_Server_Cert: No

Last_IO_Errno: 0

Last_IO_Error:

Last_SQL_Errno: 1872

Last_SQL_Error: Slave failed to initialize relay log info structure from the repository

Replicate_Ignore_Server_Ids:

Master_Server_Id: 0

Master_UUID:

Master_Info_File: mysql.slave_master_info

SQL_Delay: 0

SQL_Remaining_Delay: NULL

Slave_SQL_Running_State:

Master_Retry_Count: 86400

Master_Bind:

Last_IO_Error_Timestamp:

Last_SQL_Error_Timestamp: 180815 22:43:54

Master_SSL_Crl:

Master_SSL_Crlpath:

Retrieved_Gtid_Set:

Executed_Gtid_Set: 8db05acd-a0f1-11e8-ad63-000c2969aede:1-3,

959b9f31-75ef-11e8-97de-000c2969aede:1-61970,

d20b918a-96c9-11e8-aae4-000c2969aede:1-15

Auto_Position: 1

Replicate_Rewrite_DB:

Channel_Name:

Master_TLS_Version:

1 row in set (0.00 sec)

ERROR:

No query specified

解决方法：

1、设置gtid_purged加入了61cfd125-a0f2-11e8-b8bc-000c2969aede:1-2

2、reset slave all，重新设置主从

reset slave all

change master to master_host='10.72.7.40', master_port=3308, master_user='hch', master_password='hch123', master_auto_position=1;

start slave sql_thread until sql_before_gtids='d20b918a-96c9-11e8-aae4-000c2969aede:18';

具体操作过程如下：

root@localhost:mysql3309.sock [(none)]>reset master;

Query OK, 0 rows affected (0.03 sec)

root@localhost:mysql3309.sock [(none)]>set global gtid_purged='959b9f31-75ef-11e8-97de-000c2969aede:1-61970,d20b918a-96c9-11e8-aae4-000c2969aede:1-15,8db05acd-a0f1-11e8-ad63-000c2969aede:1-3,61cfd125-a0f2-11e8-b8bc-000c2969aede:1-2';

Query OK, 0 rows affected (0.00 sec)

root@localhost:mysql3309.sock [(none)]>show slave status\G;

*************************** 1. row ***************************

Slave_IO_State:

Master_Host: 10.72.7.40

Master_User: hch

Master_Port: 3308

Connect_Retry: 60

Master_Log_File:

Read_Master_Log_Pos: 4

Relay_Log_File: bogon-relay-bin.000001

Relay_Log_Pos: 4

Relay_Master_Log_File:

Slave_IO_Running: No

Slave_SQL_Running: No

Replicate_Do_DB:

Replicate_Ignore_DB:

Replicate_Do_Table:

Replicate_Ignore_Table:

Replicate_Wild_Do_Table:

Replicate_Wild_Ignore_Table:

Last_Errno: 1872

Last_Error: Slave failed to initialize relay log info structure from the repository

Skip_Counter: 0

Exec_Master_Log_Pos: 0

Relay_Log_Space: 154

Until_Condition: None

Until_Log_File:

Until_Log_Pos: 0

Master_SSL_Allowed: No

Master_SSL_CA_File:

Master_SSL_CA_Path:

Master_SSL_Cert:

Master_SSL_Cipher:

Master_SSL_Key:

Seconds_Behind_Master: NULL

Master_SSL_Verify_Server_Cert: No

Last_IO_Errno: 0

Last_IO_Error:

Last_SQL_Errno: 1872

Last_SQL_Error: Slave failed to initialize relay log info structure from the repository

Replicate_Ignore_Server_Ids:

Master_Server_Id: 0

Master_UUID:

Master_Info_File: mysql.slave_master_info

SQL_Delay: 0

SQL_Remaining_Delay: NULL

Slave_SQL_Running_State:

Master_Retry_Count: 86400

Master_Bind:

Last_IO_Error_Timestamp:

Last_SQL_Error_Timestamp: 180815 22:43:54

Master_SSL_Crl:

Master_SSL_Crlpath:

Retrieved_Gtid_Set:

Executed_Gtid_Set: 61cfd125-a0f2-11e8-b8bc-000c2969aede:1-2,

8db05acd-a0f1-11e8-ad63-000c2969aede:1-3,

959b9f31-75ef-11e8-97de-000c2969aede:1-61970,

d20b918a-96c9-11e8-aae4-000c2969aede:1-15

Auto_Position: 1

Replicate_Rewrite_DB:

Channel_Name:

Master_TLS_Version:

1 row in set (0.00 sec)

ERROR:

No query specified

root@localhost:mysql3309.sock [(none)]>stop slave;

Query OK, 0 rows affected, 1 warning (0.00 sec)

root@localhost:mysql3309.sock [(none)]>start slave sql_thread until sql_before_gtids='d20b918a-96c9-11e8-aae4-000c2969aede:18';

ERROR 1872 (HY000): Slave failed to initialize relay log info structure from the repository

root@localhost:mysql3309.sock [(none)]>reset slave all;

Query OK, 0 rows affected (0.03 sec)

root@localhost:mysql3309.sock [(none)]>change master to master_host='10.72.7.40', master_port=3308, master_user='hch', master_password='hch123', master_auto_position=1;

Query OK, 0 rows affected, 2 warnings (0.02 sec)

root@localhost:mysql3309.sock [(none)]>start slave sql_thread until sql_before_gtids='d20b918a-96c9-11e8-aae4-000c2969aede:18';

Query OK, 0 rows affected (0.01 sec)

root@localhost:mysql3309.sock [(none)]>show slave status\G;

*************************** 1. row ***************************

Slave_IO_State:

Master_Host: 10.72.7.40

Master_User: hch

Master_Port: 3308

Connect_Retry: 60

Master_Log_File:

Read_Master_Log_Pos: 4

Relay_Log_File: bogon-relay-bin.000001

Relay_Log_Pos: 4

Relay_Master_Log_File:

Slave_IO_Running: No

Slave_SQL_Running: Yes

Replicate_Do_DB:

Replicate_Ignore_DB:

Replicate_Do_Table:

Replicate_Ignore_Table:

Replicate_Wild_Do_Table:

Replicate_Wild_Ignore_Table:

Last_Errno: 0

Last_Error:

Skip_Counter: 0

Exec_Master_Log_Pos: 0

Relay_Log_Space: 154

Until_Condition: SQL_BEFORE_GTIDS

Until_Log_File:

Until_Log_Pos: 0

Master_SSL_Allowed: No

Master_SSL_CA_File:

Master_SSL_CA_Path:

Master_SSL_Cert:

Master_SSL_Cipher:

Master_SSL_Key:

Seconds_Behind_Master: 0

Master_SSL_Verify_Server_Cert: No

Last_IO_Errno: 0

Last_IO_Error:

Last_SQL_Errno: 0

Last_SQL_Error:

Replicate_Ignore_Server_Ids:

Master_Server_Id: 0

Master_UUID:

Master_Info_File: mysql.slave_master_info

SQL_Delay: 0

SQL_Remaining_Delay: NULL

Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates

Master_Retry_Count: 86400

Master_Bind:

Last_IO_Error_Timestamp:

Last_SQL_Error_Timestamp:

Master_SSL_Crl:

Master_SSL_Crlpath:

Retrieved_Gtid_Set:

Executed_Gtid_Set: 61cfd125-a0f2-11e8-b8bc-000c2969aede:1-2,

8db05acd-a0f1-11e8-ad63-000c2969aede:1-3,

959b9f31-75ef-11e8-97de-000c2969aede:1-61970,

d20b918a-96c9-11e8-aae4-000c2969aede:1-15

Auto_Position: 1

Replicate_Rewrite_DB:

Channel_Name:

Master_TLS_Version:

1 row in set (0.00 sec)

ERROR:

No query specified

root@localhost:mysql3309.sock [(none)]>select count(*) from zst1.tb1;

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

| count(*) |

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

| 35 |

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

1 row in set (0.01 sec)

root@localhost:mysql3309.sock [(none)]>start slave io_thread;

Query OK, 0 rows affected (0.00 sec)

root@localhost:mysql3309.sock [(none)]>select count(*) from zst1.tb1;

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

| count(*) |

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

| 53 |

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

1 row in set (0.00 sec)

参考

利用binlogserver恢复单表实验【转】 - paul_hch - 博客园 https://www.cnblogs.com/paul8339/p/9378269.html

通过全备+binlog_server同步恢复被drop的库或表 - 2森林 - 博客园 https://www.cnblogs.com/2woods/p/9394625.html

ERROR 1872 (HY000): Slave failed to initialize relay log info structure from the repository - 西橙 - 博客园 https://www.cnblogs.com/Bccd/p/5856716.html

利用伪master主机来增量恢复mysql - CSDN博客 https://blog.csdn.net/zengxuewen2045/article/details/51465078

相关推荐

服务器数据恢复—Raid5数据灾难不用愁，Raid5数据恢复原理了解下

Raid5数据恢复算法原理：分布式奇偶校验的独立磁盘结构（被称之为raid5）的数据恢复有一个“奇偶校验”的概念。可以简单的理解为二进制运算中的“异或运算”，通常使用的标识是xor。运算规则：若二者值...

服务器数据恢复—多次异常断电导致服务器raid不可用的数据恢复

服务器数据恢复环境&故障：由于机房多次断电导致一台服务器中raid阵列信息丢失。该阵列中存放的是文档，上层安装的是Windowsserver操作系统，没有配置ups。因为服务器异常断电重启后，rai...

服务器数据恢复-V7000存储更换磁盘数据同步失败的数据恢复案例

服务器数据恢复环境：P740+AIX+Sybase+V7000存储，存储阵列柜上共12块SAS机械硬盘（其中一块为热备盘）。服务器故障：存储阵列柜中有磁盘出现故障，工作人员发现后更换磁盘，新更换的磁盘...

「服务器数据恢复」重装系统导致XFS文件系统分区丢失的数据恢复

服务器数据恢复环境：DellPowerVault系列磁盘柜；用RAID卡创建的一组RAID5；分配一个LUN。服务器故障：在Linux系统层面对LUN进行分区，划分sdc1和sdc2两个分区。将sd...

服务器数据恢复-ESXi虚拟机被误删的数据恢复案例

服务器数据恢复环境：一台服务器安装的ESXi虚拟化系统，该虚拟化系统连接了多个LUN，其中一个LUN上运行了数台虚拟机，虚拟机安装WindowsServer操作系统。服务器故障&分析：管理员因误操作...

「服务器数据恢复」Raid5阵列两块硬盘亮黄灯掉线的数据恢复案例

服务器数据恢复环境：HPStorageWorks某型号存储；虚拟化平台为vmwareexsi；10块磁盘组成raid5(有1块热备盘)。服务器故障：raid5阵列中两块硬盘指示灯变黄掉线，无法读取...

服务器数据恢复—基于oracle数据库的SAP数据恢复案例

服务器存储数据恢复环境：某品牌服务器存储中有一组由6块SAS硬盘组建的RAID5阵列，其中有1块硬盘作为热备盘使用。上层划分若干lun，存放Oracle数据库数据。服务器存储故障&分析：该RAID5阵...

「服务器虚拟化数据恢复」Xen Server环境下数据库数据恢复案例

服务器虚拟化数据恢复环境：Dell某型号服务器；数块STAT硬盘通过raid卡组建的RAID10；XenServer服务器虚拟化系统；故障虚拟机操作系统：WindowsServer，部署Web服务...

服务器数据恢复—RAID故障导致oracle无法启动的数据恢复案例

服务器数据恢复环境：某品牌服务器中有一组由4块SAS磁盘做的RAID5磁盘阵列。该服务器操作系统为windowsserver，运行了一个单节点Oracle，数据存储为文件系统，无归档。该oracle...

服务器数据恢复—服务器磁盘阵列常见故障表现&解决方案

RAID（磁盘阵列）是一种将多块物理硬盘整合成一个虚拟存储的技术，raid模块相当于一个存储管理的中间层，上层接收并执行操作系统及文件系统的数据读写指令，下层管理数据在各个物理硬盘上的存储及读写。相对...

「服务器数据恢复」IBM某型号服务器RAID5磁盘阵列数据恢复案例

服务器数据恢复环境：IBM某型号服务器；5块SAS硬盘组成RAID5磁盘阵列；存储划分为1个LUN和3个分区：第一个分区存放windowsserver系统，第二个分区存放SQLServer数据库，...

服务器数据恢复—Zfs文件系统下误删除文件如何恢复数据?

服务器故障：一台zfs文件系统服务器，管理员误操作删除服务器上的数据。服务器数据恢复过程：1、将故障服务器所有磁盘编号后取出，硬件工程师检测所有硬盘后没有发现有磁盘存在硬件故障。以只读方式将全部磁盘做...

服务器数据恢复—Linux+raid5服务器数据恢复案例

服务器数据恢复环境：某品牌linux操作系统服务器，服务器中有4块SAS接口硬盘组建一组raid5阵列。服务器中存放的数据有数据库、办公文档、代码文件等。服务器故障&检测：服务器在运行过程中突然瘫痪，...

服务器数据恢复—Sql Server数据库数据恢复案例

服务器数据恢复环境：一台安装windowsserver操作系统的服务器。一组由8块硬盘组建的RAID5，划分LUN供这台服务器使用。在windows服务器内装有SqlServer数据库。存储空间LU...

服务器数据恢复—阿里云ECS网站服务器数据恢复案例

云服务器数据恢复环境：阿里云ECS网站服务器，linux操作系统+mysql数据库。云服务器故障：在执行数据库版本更新测试时，在生产库误执行了本来应该在测试库执行的sql脚本，导致生产库部分表被tru...

取消回复欢迎 你 发表评论:

一周热门
最近发表
标签列表