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

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

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

相关推荐

0722-6.2.0-如何在RedHat7.2使用rpm安装CDH(无CM)

文档编写目的在前面的文档中，介绍了在有CM和无CM两种情况下使用rpm方式安装CDH5.10.0，本文档将介绍如何在无CM的情况下使用rpm方式安装CDH6.2.0，与之前安装C5进行对比。环境介绍：...

ARM64 平台基于 openEuler + iSula 环境部署 Kubernetes

为什么要在arm64平台上部署Kubernetes，而且还是鲲鹏920的架构。说来话长。。。此处省略5000字。介绍下系统信息；o架构：鲲鹏920(Kunpeng920)oOS：ope...

生产环境starrocks 3.1存算一体集群部署

集群规划FE：节点主要负责元数据管理、客户端连接管理、查询计划和查询调度。>3节点。BE:节点负责数据存储和SQL执行。>3节点。CN:无存储功能能的BE。环境准备CPU检查JDK...

在CentOS上添加swap虚拟内存并设置优先级

现如今很多云服务器都会自己配置好虚拟内存，当然也有很多没有配置虚拟内存的，虚拟内存可以让我们的低配服务器使用更多的内存，可以减少很多硬件成本，比如我们运行很多服务的时候，内存常常会满，当配置了虚拟内存...

国产深度(deepin)操作系统优化指南

1.升级内核随着deepin版本的更新，会自动升级系统内核，但是我们依旧可以通过命令行手动升级内核，以获取更好的性能和更多的硬件支持。具体操作：-添加PPAs使用以下命令添加PPAs：```...

postgresql-15.4 多节点主从(读写分离)

1、下载软件[root@TX-CN-PostgreSQL01-252software]#wgethttps://ftp.postgresql.org/pub/source/v15.4/postg...

Docker 容器 Java 服务内存与 GC 优化实施方案

一、设置Docker容器内存限制（生产环境建议）1.查看宿主机可用内存bashfree-h#示例输出（假设宿主机剩余16GB可用内存）#Mem:64G...

虚拟内存设置、解决linux内存不够问题

虚拟内存设置（解决linux内存不够情况）背景介绍　　Memory指机器物理内存，读写速度低于CPU一个量级，但是高于磁盘不止一个量级。所以，程序和数据如果在内存的话，会有非常快的读写速度。但是，内存...

Elasticsearch性能调优(5):服务器配置选择

在选择elasticsearch服务器时，要尽可能地选择与当前业务量相匹配的服务器。如果服务器配置太低，则意味着需要更多的节点来满足需求，一个集群的节点太多时会增加集群管理的成本。如果服务器配置太高，...

Es如何落地

一、配置准备节点类型CPU内存硬盘网络机器数操作系统data节点16C64G2000G本地SSD所有es同一可用区3(ecs)Centos7master节点2C8G200G云SSD所有es同一可用区...

针对Linux内存管理知识学习总结

现在的服务器大部分都是运行在Linux上面的，所以，作为一个程序员有必要简单地了解一下系统是如何运行的。对于内存部分需要知道：地址映射内存管理的方式缺页异常先来看一些基本的知识，在进程看来，内存分为内...

MySQL进阶之性能优化

概述MySQL的性能优化，包括了服务器硬件优化、操作系统的优化、MySQL数据库配置优化、数据库表设计的优化、SQL语句优化等5个方面的优化。在进行优化之前，需要先掌握性能分析的思路和方法，找出问题，...

Linux Cgroups(Control Groups)原理

LinuxCgroups（ControlGroups）是内核提供的资源分配、限制和监控机制，通过层级化进程分组实现资源的精细化控制。以下从核心原理、操作示例和版本演进三方面详细分析：一、核心原理与...

linux 常用性能优化参数及理解

1.优化内核相关参数配置文件/etc/sysctl.conf配置方法直接将参数添加进文件每条一行.sysctl-a可以查看默认配置sysctl-p执行并检测是否有错误例如设置错了参数:[roo...

如何在 Linux 中使用 Sysctl 命令?

sysctl是一个用于配置和查询Linux内核参数的命令行工具。它通过与/proc/sys虚拟文件系统交互，允许用户在运行时动态修改内核参数。这些参数控制着系统的各种行为，包括网络设置、文件...

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

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