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

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

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

相关推荐

如何为MySQL服务器和客户机启用SSL?

用户想要与MySQL服务器建立一条安全连接时，常常依赖VPN隧道或SSH隧道。不过，获得MySQL连接的另一个办法是，启用MySQL服务器上的SSL封装器(SSLwrapper)。这每一种方法各有其...

Mysql5.7 出现大量 unauthenticated user

线上环境mysql5.7突然出现大量unauthenticateduser,进mysql，showprocesslist;解决办法有：在/etc/hosts中添加客户端ip，如192.16...

MySQL 在 Windows 系统下的安装（mysql安装教程windows）

更多技术文章MySQL在Windows系统下的安装1.下载mysql和Framework链接链接：百度网盘请输入提取码提取码：6w3p双击mysql-installer-communit...

MySql5.7.21.zip绿色版安装（mysql数据库绿色版安装）

1、去网上下载满足系统要求的版本（mysql-5.7.21-winx64.zip）2、直接解压3、mysql的初始化（1）以管理员身份运行cmd，在mysql中的bin目录下shift+右键-在...

MySQL(8.0)中文全文检索 (亲测有效)

在一堆文字中找到含有关键字的应用。当然也可以用以下语句实现：SELECT*FROM<表名>WHERE<字段名>like‘%ABC%’但是它的效率太低，是全盘扫描。...

新手教程，Linux系统下MySQL的安装

看了两三个教程。终于在哔哩哔哩找到一个简单高效的教程，成功安装，up主名叫bili逍遥bili，感兴趣可以去看看。下面这个是我总结的安装方法环境：CentOS764位1.下载安装包，个人觉得在...

麒麟服务器操作系统安装 MySQL 8 实战指南

原文连接：「链接」Hello，大家好啊，今天给大家带来一篇麒麟服务器操作系统上安装MySQL8的文章，欢迎大家分享点赞，点个在看和关注吧！MySQL作为主流开源数据库之一，被广泛应用于各种业务...

用Python玩转MySQL的全攻略，从环境搭建到项目实战全解析

这是一篇关于“MySQL数据库入门实战-Python版”的教程，结合了案例实战分析，帮助初学者快速掌握如何使用Python操作MySQL数据库。一、环境准备1.安装Python访问Pytho...

安装MySQL（中标麒麟 安装mysql）

安装MySQL注意：一定要用root用户操作如下步骤；先卸载MySQL再安装1.安装包准备（1）查看MySQL是否安装rpm-qa|grepmysql（2）如果安装了MySQL，就先卸载rpm-...

Mysql最全笔记，快速入门，干货满满，爆肝

目录一、MySQL的重要性二、MySQL介绍三、软件的服务架构四、MySQL的安装五、SQL语句六、数据库相关（DDL）七、表相关八、DML相关（表中数据）九、DQL（重点）十、数据完...

MAC电脑安装MySQL操作步骤（mac安装mysqldb）

1、在官网下载MySQL：https://dev.mysql.com/downloads/mysql/根据自己的macOS版本，选择适配的MySQL版本根据自己需求选择相应的安装包，我这里选择macO...

mysql主从（mysql主从切换）

1、本章面试题什么是mysql主从，主从有什么好处什么是读写分离，有什么好处，使用mycat如何实现2、知识点2.1、课程回顾dubboORM->MVC->RPC->SOApro...

【linux学习】以MySQL为例，带你了解数据库

做运维的小伙伴在日常工作中难免需要接触到数据库，不管是MySQL，mariadb,达梦还是瀚高等其实命令都差不多，下面我就以MySQL为例带大家一起来了解下数据库。有兴趣的小伙伴不妨评论区一起交流下...

玩玩WordPress - 环境简介(0)（玩玩网络科技有限公司）

简介提到开源博客系统，一般都会直接想到WordPress！WordPress是使用PHP开发的，数据库使用的是MySQL，一般会在Linux上运行，Nginx作为前端。这时候就需要有一套LNMP（Li...

服务器常用端口都有哪些?（服务器端使用的端口号范围）

下面为大家介绍一下，服务器常用的一些默认端口，以及他们的作用：　　21：FTP服务所开放的端口，用于上传、下载文件。　　22：SSH端口，用于通过命令行模式远程连接Linux服务器或vps。　　23：...

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

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