Example analysis of emptying logs using the reset master command
This article shares with you a sample analysis of emptying logs using the reset master command. The editor thinks it is very practical, so share it with you as a reference and follow the editor to have a look.
Today, I saw that the binlog log of the main library is very large, so I used the reset master command to clear the log.
Mysql > reset master
Query OK, 0 rows affected (1 min 59.87 sec)
As a result, it was reported wrong from the database.
Mysql > show slave status\ G
* * 1. Row *
Slave_IO_State:
Master_Host: 192.168.129.150
Master_User: replicat
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000312
Read_Master_Log_Pos: 2029035
Relay_Log_File: mysql-relay-bin.000945
Relay_Log_Pos: 2020198
Relay_Master_Log_File: mysql-bin.000312
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: 1
Exec_Master_Log_Pos: 2029035
Relay_Log_Space: 2029418
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: 2
Master_UUID: 124deecd-401b-11e6-b4e1-40f2e9de1e12
Master_Info_File: / data/DB/mysql/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: 160808 11:08:49
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)
If you take a look at the main database log, it has been started all over again, and it is no longer mysql-bin.000312.
Mysql > show master status
+-+
| | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | |
+-+
| | mysql-bin.000001 | 216484 | |
+-+
1 row in set (0.00 sec)
Workaround, reset the slave process
Mysql > stop slave
Query OK, 0 rows affected (0.01 sec)
Mysql > change master to master_host='192.168.129.150'
-> master_user='replicat'
-> master_password='passw'
-> master_log_file='mysql-bin.000001'
-> master_log_pos=120
Query OK, 0 rows affected, 2 warnings (0.05 sec)
Mysql > start slave
Query OK, 0 rows affected (0.02 sec)
Mysql > show slave status\ G
* * 1. Row *
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.129.150
Master_User: replicat
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 310425
Relay_Log_File: mysql-relay-bin.000002
Relay_Log_Pos: 310588
Relay_Master_Log_File: mysql-bin.000001
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: 310425
Relay_Log_Space: 310761
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: 2
Master_UUID: 124deecd-401b-11e6-b4e1-40f2e9de1e12
Master_Info_File: / data/DB/mysql/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)
Although the problem has been solved, according to the error report record,
Read_Master_Log_Pos: 2029035
Relay_Log_Pos: 2020198
Some of the data has not been synchronized to the slave database, but because of the OLAP system of this database, it is okay to understand from the business, so there is no need to worry about data inconsistencies, so it is very dangerous for the main library to execute reset matser.
Thank you for reading! This is the end of the article on "sample Analysis of emptying logs with reset master Command". I hope the above content can be helpful to you, so that you can learn more knowledge. if you think the article is good, you can share it for more people to see!
