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

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

nanshan 2024-10-08 05:33 17 浏览 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

相关推荐

雷军1994年写的老代码曝光，被称像诗一样优雅

大数据文摘授权转载自程序员的那些事雷军的代码像诗一样优雅↓↓↓有些网友在评论中质疑，说雷军代码不会是“屎”一样优雅吧。说这话的网友，也许是开玩笑的，也许是真没看过雷军写过的代码。在2011年的时候，我...

原创经验分享:低级bug耗费12小时Fix

调试某程序非常简单的程序，简单到认为不可能存在缺陷，但该BUG处理时间超过12小时：程序属于后台进程，监控系统每隔15秒检查外设IO状态，IO异常后发出报警或复位外设，外设都在linux下有/sys/...

SpringBoot实现的简单停车位管理系统附带导入和演示教程视频

这一次为大家带来的是简单的停车位管理系统，基于SpringBoot+Thymeleaf+Mybatis框架，这个系统相对来说比较简单，很容易学习并快速上手，因为逻辑很清晰，没有太复杂的代码逻辑，所以学...

一个开箱即用的代码生成器（代码自动生成工具开源）

今天给大家推荐一个好用的代码生成器，名为renren-generator，该项目附带前端页面，可以很方便的选择我们所需要生成代码的表。首先我们通过git工具克隆下来代码（地址见文末），导入idea。...

【免费开源】JeecgBoot单点登录源码全部开源了

JeecgBoot单点登录源码全部开源了，有需要的朋友可以来薅羊毛了。一、JeecgBoot介绍JeecgBoot是一款企业级的低代码平台！前后端分离架构SpringBoot2.x，SpringCl...

SpringBoot+JWT+Shiro+Mybatis实现Restful快速开发后端脚手架

作者：lywJee来源：cnblogs.com/lywJ/p/11252064.html一、背景前后端分离已经成为互联网项目开发标准，它会为以后的大型分布式架构打下基础。SpringBoot使编码配置...

为什么越来越多的人选择使用idea软件

IDEA软件是什么？IDEA软件是干什么的？为什么越来越多的人选择使用IDEA软件？IDEA软件，全称IntelliJIDEA，它是由JetBrains公司开发开发的一款功能强大的集成开发环境（ID...

开题报告大学生互助系统(附源码)java毕设

本系统（程序+源码）带文档lw万字以上文末可获取一份本项目的java源码和数据库参考。系统程序文件列表开题报告内容选题背景随着互联网技术的飞速发展，大学生群体对信息共享与互助的需求日益增长。关于大...

SpringBoot项目快速开发框架JeecgBoot——项目简介及系统架构!

项目简介及系统架构JeecgBoot是一款基于SpringBoot的开发平台，它采用前后端分离架构，集成的框架有SpringBoot2.x、SpringCloud、AntDesignof...

新手配电脑13代CPU怎么选择（新手配电脑13代cpu怎么选择好）

Intel第13代酷睿i3、i5、i7、i9系列处理器的核心参数、性能差异及适用群体的详细说明（以桌面端为例）：一、13代酷睿全系参数对比（桌面端主流型号）参数i3-13100i5-13600Ki7-...

加速 SpringBoot 应用开发，官方热部署神器真带劲

平时使用SpringBoot开发应用时，修改代码后需要重新启动才能生效。如果你的应用足够大的话，启动可能需要好几分钟。有没有什么办法可以加速启动过程，让我们开发应用代码更高效呢？今天给大家推荐一款Sp...

基于微信小程序的移动端物流系统-计算机毕业设计源码+LW文档

摘要随着Internet的发展，人们的日常生活已经离不开网络。未来人们的生活与工作将变得越来越数字化，网络化和电子化。网上管理，它将是直接管理移动端物流系统app的最新形式。本论文是以构建移动端物流系...

springboot教务管理系统+微信小程序云开发附带源码

今天给大家分享的程序是基于springboot的管理，前端是小程序，系统非常的nice，不管是学习还是毕设都非常的靠谱。本系统主要分为pc端后台管理和微信小程序端,pc端有三个角色：管理员、学生、教师...

SpringBoot全家桶:23篇博客加23个可运行项目让你对它了如指掌

SpringBoot现在已经成为Java开发领域的一颗璀璨明珠，它本身是包容万象的，可以跟各种技术集成。本项目对目前Web开发中常用的各个技术，通过和SpringBoot的集成，并且对各种技术通...

Maven+JSP+Servlet+C3P0+Mysql实现的音乐库管理系统

本系统基于Maven+JSP+Servlet+C3P0+Mysql实现的音乐库管理系统。简单实现了充值、购买歌曲、poi数据导入导出、歌曲上传下载、歌曲播放、用户注册登录注销等功能。难度等级：简单技术...

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

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