当前位置：首页 > news >正文

为什么做视频网站违法/潍坊网站外包

news 2025/7/1 9:50:38

为什么做视频网站违法,潍坊网站外包,b s架构网站怎么做,安徽建筑工程信息网利用mysql全备 binlog server恢复方法之伪装master单实例试验一、试验环境10.72.7.40实例 mysql3306为要恢复的对象，mysql3306的全备binlog server(目录/data/mysql/mysql3306/backup)实例mysql3307为伪装master实例mysql3308为伪装master的slave，在其上…

利用mysql全备 +binlog server恢复方法之伪装master

单实例试验

一、试验环境

10.72.7.40

实例 mysql3306为要恢复的对象，mysql3306的全备+binlog server(目录/data/mysql/mysql3306/backup)

实例mysql3307为伪装master

实例mysql3308为伪装master的slave，在其上恢复数据

1、mysql3306全备

innobackupex --defaults-file=/data/mysql/mysql3306/mysql3306.cnf -S /tmp/mysql3306.sock -uroot -phch123 /root/backup

2、mysql3306的binlog server

root@localhost:mysql3306.sock [zst1]>show binary logs;+------------------+-----------+

| Log_name | File_size |

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

| mysql-bin.000004 | 234 |

| mysql-bin.000005 | 234 |

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

2 rows in set (0.00sec)

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.00sec)

模拟数据写入

root@localhost:mysql3306.sock [zst1]>insert into tb1(c1, c2) selectuser,host from mysql.user;

Query OK,9 rows affected (0.09sec)

Records:9 Duplicates: 0 Warnings: 0root@localhost:mysql3306.sock [zst1]>insert into tb1(c1, c2) selectuser,host from mysql.user;

Query OK,9 rows affected (0.14sec)

Records:9 Duplicates: 0 Warnings: 0root@localhost:mysql3306.sock [zst1]>select count(*) from tb1;+----------+

| count(*) |

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

| 53 |

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

1 row in set (0.00sec)

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;+------------------+----------+--------------+------------------+-----------------------------------------------------------------------------------------+

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

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.08sec)

root@localhost:mysql3306.sock [zst1]>select *from tb1;

Empty set (0.00sec)

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) selectuser,host from mysql.user;

Query OK,9 rows affected (0.09sec)

Records:9 Duplicates: 0 Warnings: 0root@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.00sec)

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

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.00sec)

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.00sec)

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:53nohup.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:53nohup.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.00sec)

root@localhost:mysql3308.sock [(none)]>show master status;+------------------+----------+--------------+------------------+---------------------------------------------------------------------------------------------------------------------------------------+

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

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@bogon2018-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:17hch

drwxr-x---. 2 mysql mysql 50 Aug 15 22:17hch1-rw-r-----. 1 mysql mysql 553 Aug 15 22:17ib_buffer_pool-rw-r-----. 1 mysql mysql 100M Aug 15 22:17ibdata1-rw-r-----. 1 mysql mysql 100M Aug 15 22:17ib_logfile0-rw-r-----. 1 mysql mysql 100M Aug 15 22:17ib_logfile1-rw-r-----. 1 mysql mysql 100M Aug 15 22:17ib_logfile2-rw-r-----. 1 mysql mysql 12M Aug 15 22:17ibtmp1

drwxr-x---. 2 mysql mysql 4.0K Aug 15 22:17mysql

drwxr-x---. 2 mysql mysql 8.0K Aug 15 22:17performance_schema

drwxr-x---. 2 mysql mysql 8.0K Aug 15 22:17sys

drwxr-x---. 2 mysql mysql 52 Aug 15 22:17test

drwxr-x---. 2 mysql mysql 48 Aug 15 22:17userdb

drwxr-x---. 2 mysql mysql 4.0K Aug 15 22:17wubx-rw-r--r--. 1 mysql mysql 22 Aug 15 22:17xtrabackup_binlog_pos_innodb-rw-r-----. 1 mysql mysql 109 Aug 15 22:17xtrabackup_binlog_info-rw-r-----. 1 mysql mysql 651 Aug 15 22:17xtrabackup_info-rw-r-----. 1 mysql mysql 117 Aug 15 22:17xtrabackup_checkpoints-rw-r-----. 1 mysql mysql 8.0M Aug 15 22:17xtrabackup_logfile-rw-r--r--. 1 mysql mysql 1 Aug 15 22:17xtrabackup_master_key_id

drwxr-x---. 2 mysql mysql 192 Aug 15 22:17zst

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;+------------------+----------+--------------+------------------+-----------------------------------------------------------------------------------------------------------------------------------+

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

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.03sec)

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.00sec)

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.02sec)

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.01sec)

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

Master_Host:10.72.7.40Master_User: hch

Master_Port:3308Connect_Retry:60Master_Log_File:

Read_Master_Log_Pos:4Relay_Log_File: bogon-relay-bin.000001Relay_Log_Pos:4Relay_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:0Last_Error:

Skip_Counter:0Exec_Master_Log_Pos:0Relay_Log_Space:154Until_Condition: SQL_BEFORE_GTIDS

Until_Log_File:

Until_Log_Pos:0Master_SSL_Allowed: No

Master_SSL_CA_File:

Master_SSL_CA_Path:

Master_SSL_Cert:

Master_SSL_Cipher:

Master_SSL_Key:

Seconds_Behind_Master:0Master_SSL_Verify_Server_Cert: No

Last_IO_Errno:0Last_IO_Error:

Last_SQL_Errno:0Last_SQL_Error:

Replicate_Ignore_Server_Ids:

Master_Server_Id:0Master_UUID:

Master_Info_File: mysql.slave_master_info

SQL_Delay:0SQL_Remaining_Delay: NULL

Slave_SQL_Running_State: Slave has read all relay log; waitingfor moreupdates

Master_Retry_Count:86400Master_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-15Auto_Position:1Replicate_Rewrite_DB:

Channel_Name:

Master_TLS_Version:1 row in set (0.00sec)

ERROR:

No query specified

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

| count(*) |

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

| 35 |

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

1 row in set (0.01sec)

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

Query OK,0 rows affected (0.00sec)

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)

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

多台机器试验

主机10.72.16.50的MySQL 3306实例

伪装master 10.72.16.50的3307实例

伪装master的slave 10.72.16.112 3306实例

一、创建试验环境

10.72.16.50的MySQL 3306实例执行

1、备份数据库

innobackupex --defaults-file=/etc/my.cnf -uroot -phch123 /root/test

2、模拟误删除

mysql>use hch;

Reading table informationforcompletion of table and column names

You can turn off this feature to get a quicker startup with-A

Database changed

mysql> select *from tb1;+----+-------+

| id | cname |

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

| 1 | php |

| 2 | java |

| 3 | go |

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

3 rows in set (0.03sec)

mysql> insert into tb1(cname) values('test');

Query OK,1 row affected (0.06sec)

mysql> insert into tb1(cname) values('test1');

Query OK,1 row affected (0.00sec)

mysql> insert into tb1(cname) values('test2');

Query OK,1 row affected (0.00sec)

mysql> insert into tb1(cname) values('test3');

Query OK,1 row affected (0.00sec)

mysql> insert into tb1(cname) values('test4');

Query OK,1 row affected (0.00sec)

mysql> select *from tb1;+----+-------+

| id | cname |

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

| 1 | php |

| 2 | java |

| 3 | go |

| 4 | test |

| 5 | test1 |

| 6 | test2 |

| 7 | test3 |

| 8 | test4 |

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

8 rows in set (0.00sec)

mysql> insert into tb1(cname) values('test5');

Query OK,1 row affected (0.00sec)

mysql> insert into tb1(cname) values('test6');

Query OK,1 row affected (0.00sec)

mysql> insert into tb1(cname) values('test7');

Query OK,1 row affected (0.00sec)

mysql> insert into tb1(cname) values('test8');

Query OK,1 row affected (0.01sec)

mysql> select *from tb1;+----+-------+

| id | cname |

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

| 1 | php |

| 2 | java |

| 3 | go |

| 4 | test |

| 5 | test1 |

| 6 | test2 |

| 7 | test3 |

| 8 | test4 |

| 9 | test5 |

| 10 | test6 |

| 11 | test7 |

| 12 | test8 |

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

12 rows in set (0.00 sec)

执行误操作

mysql> truncate table tb1;

Query OK, 0 rows affected (0.15 sec)

查看binlog位置

mysql> show master status\G;

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

File: mysql-bin.000002

Position: 2731

Binlog_Do_DB:

Binlog_Ignore_DB:

Executed_Gtid_Set: 22dc7409-2cd5-11e7-888c-000c29bf3444:1-984109,

671bac73-a032-11e8-a493-000c29bf3444:1-11,

909c25b1-7f67-11e8-9b9d-000c29bf3444:1,

ee639e4e-358a-11e7-87fa-000c29466957:1-753

1 row in set (0.00 sec)

ERROR:

No query specified

刷新日志

mysql> flush logs;

Query OK, 0 rows affected (0.08 sec)

mysql> select @@server_uuid;

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

| @@server_uuid |

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

| 671bac73-a032-11e8-a493-000c29bf3444 |

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

1 row in set (0.02 sec)

mysql> show master status;

671bac73-a032-11e8-a493-000c29bf3444:1-11,

909c25b1-7f67-11e8-9b9d-000c29bf3444:1,

ee639e4e-358a-11e7-87fa-000c29466957:1-753 |

1 row in set (0.00 sec)

3、查看误操作binlog日志位置

mysqlbinlog -v --base64-output=decode-rows mysql-bin.000002 > 2.sql

位置为mysql-bin.000002的2582，gtid为671bac73-a032-11e8-a493-000c29bf3444:11

二、在10.72.16.50上创建3307的伪装master

1、初始化3307

[root@hchtest3 data]# /usr/local/mysql/bin/mysqld --defaults-file=/data/mysql/mysql3307/my3307.cnf --initialize

[root@hchtest3 data]#caterror.log2018-08-15T09:21:50.046760Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for moredetails).2018-08-15T09:21:53.749883Z 0 [Warning] InnoDB: New log files created, LSN=45790

2018-08-15T09:21:54.164271Z 0[Warning] InnoDB: Creating foreign key constraint system tables.2018-08-15T09:21:54.572772Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: a68949d2-a06c-11e8-bba3-000c29bf3444.2018-08-15T09:21:54.581852Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed'cannot be opened.2018-08-15T09:21:54.586219Z 1 [Note] A temporary password is generated for root@localhost: 8/IHqjytdouM

2、启动3307

/usr/local/mysql/bin/mysqld --defaults-file=/data/mysql/mysql3307/my3307.cnf &

登录修改密码并关闭

[root@hchtest3 data]# mysql -S /tmp/mysql3307.sock -uroot -p

mysql>show databases;+--------------------+

| Database |

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

| information_schema |

| mysql |

| performance_schema |

| sys |

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

4 rows in set (0.03sec)

mysql> alter user user() identified by 'hch123';

创建复制账号

mysql> grant all privileges on *.* to hch@'%' identified by 'hch123';

mysql>flush privileges;

mysql>shutdown;

Query OK,0 rows affected (0.02 sec)

3、拷贝50的3306 binlog至3307 datadir下

rm -rf /data/mysql/mysql3307/data/mysql-bin.*

cp -a /data/mysql/mysql3306/backup/*/data/mysql/mysql3306/data/

创建mysql-bin.index

[root@hchtest3 data]# ls /data/mysql/mysql3307/data/mysql-bin.* > mysql-bin.index

[root@hchtest3 data]# cat mysql-bin.index

/data/mysql/mysql3307/data/mysql-bin.000001

/data/mysql/mysql3307/data/mysql-bin.000002

/data/mysql/mysql3307/data/mysql-bin.000003

修改权限

chown mysql. mysql-bin.*

4、启动伪装master 3307实例

/usr/local/mysql/bin/mysqld --defaults-file=/data/mysql/mysql3307/my3307.cnf &

5、查看binlog日志

mysql>show master logs;+------------------+-----------+

| Log_name | File_size |

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

| mysql-bin.000001 | 493 |

| mysql-bin.000002 | 2778 |

| mysql-bin.000003 | 234 |

| mysql-bin.000004 | 234 |

+------------------+-----------+mysql>show master status;+------------------+----------+--------------+------------------+---------------------------------------------------------------------------------------------------------------------------+

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

909c25b1-7f67-11e8-9b9d-000c29bf3444:1,

a68949d2-a06c-11e8-bba3-000c29bf3444:1 |

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

1 row in set (0.03sec)

mysql> select@@server_uuid;+--------------------------------------+

| @@server_uuid |

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

| a68949d2-a06c-11e8-bba3-000c29bf3444 |

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

1 row in set (0.00sec)

查看binlog位置发现多了a68949d2-a06c-11e8-bba3-000c29bf3444:1，这个为3307初始化后添加密码操作产生的gtid，设置伪装master从库时gtid_purged需要添加这个否则会1236报错

三、设置伪装master 10.72.16.50 3307的slave 10.72.16.112 3306

1、用10.72.16.50 3306的全备还原

innobackupex --apply-log /root/test/2018-08-15_15-54-24

2、查看binlog日志备份位置

[root@hchtest3 2018-08-15_15-54-24]# catxtrabackup_binlog_info

mysql-bin.000002 234 22dc7409-2cd5-11e7-888c-000c29bf3444:1-984109,

671bac73-a032-11e8-a493-000c29bf3444:1,

909c25b1-7f67-11e8-9b9d-000c29bf3444:1,

ee639e4e-358a-11e7-87fa-000c29466957:1-753[root@hchtest32018-08-15_15-54-24]# catxtrabackup_info

uuid= 7b379338-a060-11e8-8a15-000c29bf3444

name=tool_name=innobackupex

tool_command= --defaults-file=/data/mysql/mysql3306/my3306.cnf -uroot -phch123 /root/test

tool_version= 2.4.12ibbackup_version= 2.4.12server_version= 5.7.17-log

start_time= 2018-08-15 15:54:29end_time= 2018-08-15 15:54:47lock_time= 0binlog_pos = filename 'mysql-bin.000002', position '234', GTID of the last change '22dc7409-2cd5-11e7-888c-000c29bf3444:1-984109,

671bac73-a032-11e8-a493-000c29bf3444:1,

909c25b1-7f67-11e8-9b9d-000c29bf3444:1,

ee639e4e-358a-11e7-87fa-000c29466957:1-753'

位置为

mysql-bin.000002 234 22dc7409-2cd5-11e7-888c-000c29bf3444:1-984109,

671bac73-a032-11e8-a493-000c29bf3444:1,

909c25b1-7f67-11e8-9b9d-000c29bf3444:1,

ee639e4e-358a-11e7-87fa-000c29466957:1-753

拷贝10.72.16.112的3306 datadir下，之前要停止10.72.16.112的3306，并清除datadir目录。

[root@hchtest3 2018-08-15_15-54-24]# scp -r * root@10.72.16.112:/usr/local/mysql/data/

3、启动slave并设置同步

[root@hchtest4 script]# service mysql status

MySQL running (16977) [ OK ]

mysql>reset master;

Query OK,0 rows affected (0.00sec)

此处要加入50的3307的gtid

mysql> SET @@GLOBAL.GTID_PURGED='22dc7409-2cd5-11e7-888c-000c29bf3444:1-984109,671bac73-a032-11e8-a493-000c29bf3444:1,909c25b1-7f67-11e8-9b9d-000c29bf3444:1,ee639e4e-358a-11e7-87fa-000c29466957:1-753,a68949d2-a06c-11e8-bba3-000c29bf3444:1';

mysql> change master to master_host='10.72.16.50', master_port=3307, master_user='hch', master_password='hch123', master_auto_position=1;

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

mysql> start slave sql_thread until sql_before_gtids='671bac73-a032-11e8-a493-000c29bf3444:11';

Query OK,0 rows affected (0.03sec)

mysql>show slave status\G;*************************** 1. row ***************************Slave_IO_State:

Master_Host:10.72.16.50Master_User: hch

Master_Port:3307Connect_Retry:60Master_Log_File:

Read_Master_Log_Pos:4Relay_Log_File: mysql-relay-bin.000001Relay_Log_Pos:4Relay_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:0Last_Error:

Skip_Counter:0Exec_Master_Log_Pos:0Relay_Log_Space:154Until_Condition: SQL_BEFORE_GTIDS

Until_Log_File:

Until_Log_Pos:0Master_SSL_Allowed: No

Master_SSL_CA_File:

Master_SSL_CA_Path:

Master_SSL_Cert:

Master_SSL_Cipher:

Master_SSL_Key:

Seconds_Behind_Master:0Master_SSL_Verify_Server_Cert: No

Last_IO_Errno:0Last_IO_Error:

Last_SQL_Errno:0Last_SQL_Error:

Replicate_Ignore_Server_Ids:

Master_Server_Id:0Master_UUID:

Master_Info_File: mysql.slave_master_info

SQL_Delay:0SQL_Remaining_Delay: NULL

Slave_SQL_Running_State: Slave has read all relay log; waitingfor moreupdates

Master_Retry_Count:86400Master_Bind:

Last_IO_Error_Timestamp:

Last_SQL_Error_Timestamp:

Master_SSL_Crl:

Master_SSL_Crlpath:

Retrieved_Gtid_Set:

Executed_Gtid_Set: 22dc7409-2cd5-11e7-888c-000c29bf3444:1-984109,

671bac73-a032-11e8-a493-000c29bf3444:1,

909c25b1-7f67-11e8-9b9d-000c29bf3444:1,

a68949d2-a06c-11e8-bba3-000c29bf3444:1,

ee639e4e-358a-11e7-87fa-000c29466957:1-753Auto_Position:1Replicate_Rewrite_DB:

Channel_Name:

Master_TLS_Version:1 row in set (0.00sec)

ERROR:

No query specified

mysql>start slave io_thread;

Query OK,0 rows affected (0.00sec)

mysql>show slave status\G;*************************** 1. row ***************************Slave_IO_State: Waitingformaster to send event

Master_Host:10.72.16.50Master_User: hch

Master_Port:3307Connect_Retry:60Master_Log_File: mysql-bin.000004Read_Master_Log_Pos:665Relay_Log_File: mysql-relay-bin.000002Relay_Log_Pos:2715Relay_Master_Log_File: mysql-bin.000002Slave_IO_Running: Yes

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:0Last_Error:

Skip_Counter:0Exec_Master_Log_Pos:2582Relay_Log_Space:4716Until_Condition: SQL_BEFORE_GTIDS

Until_Log_File:

Until_Log_Pos:0Master_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:0Last_IO_Error:

Last_SQL_Errno:0Last_SQL_Error:

Replicate_Ignore_Server_Ids:

Master_Server_Id:503307Master_UUID: a68949d2-a06c-11e8-bba3-000c29bf3444

Master_Info_File: mysql.slave_master_info

SQL_Delay:0SQL_Remaining_Delay: NULL

Slave_SQL_Running_State:

Master_Retry_Count:86400Master_Bind:

Last_IO_Error_Timestamp:

Last_SQL_Error_Timestamp:

Master_SSL_Crl:

Master_SSL_Crlpath:

Retrieved_Gtid_Set: 671bac73-a032-11e8-a493-000c29bf3444:2-11,

a68949d2-a06c-11e8-bba3-000c29bf3444:2-3Executed_Gtid_Set: 22dc7409-2cd5-11e7-888c-000c29bf3444:1-984109,

671bac73-a032-11e8-a493-000c29bf3444:1-10,

909c25b1-7f67-11e8-9b9d-000c29bf3444:1,

a68949d2-a06c-11e8-bba3-000c29bf3444:1,

ee639e4e-358a-11e7-87fa-000c29466957:1-753Auto_Position:1Replicate_Rewrite_DB:

Channel_Name:

Master_TLS_Version:1 row in set (0.00sec)

ERROR:

No query specified

查看发现数据已经恢复

mysql> select *from hch.tb1;+----+-------+

| id | cname |

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

| 1 | php |

| 2 | java |

| 3 | go |

| 4 | test |

| 5 | test1 |

| 6 | test2 |

| 7 | test3 |

| 8 | test4 |

| 9 | test5 |

| 10 | test6 |

| 11 | test7 |

| 12 | test8 |

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

12 rows in set (0.02 sec)

问题

slave设置时报错1236

mysql>start slave io_thread;

Query OK,0 rows affected (0.00sec)

mysql>show slave status\G;*************************** 1. row ***************************Slave_IO_State:

Master_Host:10.72.16.50Master_User: hch

Master_Port:3307Connect_Retry:60Master_Log_File:

Read_Master_Log_Pos:4Relay_Log_File: mysql-relay-bin.000001Relay_Log_Pos:4Relay_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:0Last_Error:

Skip_Counter:0Exec_Master_Log_Pos:0Relay_Log_Space:154Until_Condition: SQL_BEFORE_GTIDS

Until_Log_File:

Until_Log_Pos:0Master_SSL_Allowed: No

Master_SSL_CA_File:

Master_SSL_CA_Path:

Master_SSL_Cert:

Master_SSL_Cipher:

Master_SSL_Key:

Seconds_Behind_Master:0Master_SSL_Verify_Server_Cert: No

Last_IO_Errno:1236Last_IO_Error: Got fatal error1236 from master when reading data from binary log: 'The slave is connecting using CHANGE MASTER TO MASTER_AUTO_POSITION = 1, but the master has purged binary logs containing GTIDs that the slave requires.'Last_SQL_Errno:0Last_SQL_Error:

Replicate_Ignore_Server_Ids:

Master_Server_Id:503307Master_UUID: a68949d2-a06c-11e8-bba3-000c29bf3444

Master_Info_File: mysql.slave_master_info

SQL_Delay:0SQL_Remaining_Delay: NULL

Slave_SQL_Running_State: Slave has read all relay log; waitingfor moreupdates

Master_Retry_Count:86400Master_Bind:

Last_IO_Error_Timestamp:180815 18:02:12Last_SQL_Error_Timestamp:

Master_SSL_Crl:

Master_SSL_Crlpath:

Retrieved_Gtid_Set:

Executed_Gtid_Set: 22dc7409-2cd5-11e7-888c-000c29bf3444:1-984109,

671bac73-a032-11e8-a493-000c29bf3444:1,