Shulou(Shulou.com)06/01 Report--

Percona MySQL 5.6 Primary replication environment error:

mysql> show slave status\G

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

Slave_IO_State:

Master_Host: vsql02

Master_User: mysql_slave

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysqld-bin.000153

Read_Master_Log_Pos: 120

Relay_Log_File: mysqld-relay-bin.000002

Relay_Log_Pos: 284

Relay_Master_Log_File: mysqld-bin.000153

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: 120

Relay_Log_Space: 601

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: 1236

Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'Could not find first log file name in binary log index file'

Last_SQL_Errno: 0

Last_SQL_Error:

Replicate_Ignore_Server_Ids:

Master_Server_Id: 22

Master_UUID: d49b3291-289a-11e6-9b9b-2c44fd7f8364

Master_Info_File: /data/dmcdb/mysql/database/master.info

SQL_Delay: 0

SQL_Remaining_Delay: NULL

Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it

Master_Retry_Count: 86400

Master_Bind:

Last_IO_Error_Timestamp: 160809 11:42:02

Last_SQL_Error_Timestamp:

Master_SSL_Crl:

Master_SSL_Crlpath:

Retrieved_Gtid_Set:

Executed_Gtid_Set:

Auto_Position: 0

1 row in set (0.00 sec)

Reasons for error:

The binary log on the Master side is deleted, resulting in the IO thread on the slave side unable to read the specified binary log on the Master side.

mysql> show variables like '%log%bin%';

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

| Variable_name | Value |

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

| log_bin | ON |

| log_bin_basename | /var/lib/mysql/binlog/mysqld-bin |

| log_bin_index | /var/lib/mysql/binlog/mysqld-bin.index |

| log_bin_trust_function_creators | ON |

| log_bin_use_v1_row_events | OFF |

| sql_log_bin | ON |

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

6 rows in set (0.00 sec)

[root@dmcdb2 ~]# cd /var/lib/mysql/binlog

[root@dmcdb2 binlog]# ls -trl

total 1613364

-rw-r--r-- 1 root root 1651957529 Feb 17 14:29 3962.sql

-rw-rw---- 1 mysql mysql 40 Aug 9 11:16 mysqld-bin.index

-rw-rw---- 1 mysql mysql 174 Aug 9 14:17 mysqld-relay-bin.000001

-rw-rw---- 1 mysql mysql 92 Aug 9 14:17 mysqld-relay-bin.index

-rw-rw---- 1 mysql mysql 461 Aug 9 14:21 mysqld-bin.000155

-rw-rw---- 1 mysql mysql 104500 Aug 9 14:39 mysqld-relay-bin.000002

Repair method:

Due to the loss of binary logs on the Master side, the replication environment can only be reconfigured from existing binary logs

--Master end

mysql> flush tables with read lock;

Query OK, 0 rows affected (0.00 sec)

mysql> show master status;

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

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

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

| mysqld-bin.000155 | 120 | | | |

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

1 row in set (0.00 sec)

--Slave end

mysql> change master to master_host='vsql02', master_port=3306, master_user='mysql_slave', master_password='mysql_slave', master_log_file='mysqld-bin.000155', master_log_pos=120;

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

mysql> start slave;

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

mysql> show slave status\G

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

Slave_IO_State: Waiting for master to send event

Master_Host: vsql02

Master_User: mysql_slave

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysqld-bin.000155

Read_Master_Log_Pos: 120

Relay_Log_File: mysqld-relay-bin.000002

Relay_Log_Pos: 284

Relay_Master_Log_File: mysqld-bin.000155

Slave_IO_Running: Yes

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: 120

Relay_Log_Space: 458

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: 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: 22

Master_UUID: d49b3291-289a-11e6-9b9b-2c44fd7f8364

Master_Info_File: /data/dmcdb/mysql/database/master.info

SQL_Delay: 0

SQL_Remaining_Delay: NULL

Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it

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:

Auto_Position: 0

1 row in set (0.00 sec)

--Master end

mysql> unlock tables;

Query OK, 0 rows affected (0.00 sec)

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.