| Thread Previous • Date Previous • Date Next • Thread Next |
Hi, thanks for the reply. yes, Exec_Master_Log_Pos: 967647646 stays always the same.I attaced a file with the data you requested. Seems there is a process stuck with "Waiting for table metadata lock ".
Am 01.02.2021 um 11:32 schrieb andrei.elkin@xxxxxxxxxx:
Thomas <thomas@plant.systems> writes:Hello,Howdy, Tomas.we have upgraded our MySQL Master/Slave server vom 10.1.24 to 10.1.48 on 25.01. Backups worked as usual, but since friday (29.01.) our backups do not work anymore. I have seen that entering a 'stop slave;' on mysql cli hangs forever, while in the background (on a second console, I see the replication going on, but the gtid is far away from the master. According to MaxScale, on the slave it is '0-1-8696229561So select @@global.gtid_slave_pos = 0-1-8696229561 remains all the time since 2021-02-01 8:45:40 140290041063168 [Note] Slave SQL thread initialized, starting replication in log 'mysql-bin.005586' at position 1073742303, relay log '/var/lib/mysql/relay-bin.000001' position: 4; GTID position '0-1-8696229561' (paste from a followup mail)? And the same to Exec_Master_Log_Pos: 967647646 that also has not moved? As you mention backup, could it have been running to block slave thread(s).. Could you please make few selects: #--connection slave show processlist; show slave status; select @@global.gtid_slave_pos; select @@global.slave_parallel_threads; select @@global.slave_parallel_mode; It may be interesting to see what are events starting from mysql-bin.005587:967647646 unless it gets clear from show-processist results. I'll try to help. Cheers, Andreiand on the master '0-1-8705275411' and not moving forward on the slave. Slave 'show slave status': MariaDB [(none)]> show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 172.16.1.1 Master_User: replication_user Master_Port: 3306 Connect_Retry: 10 Master_Log_File: mysql-bin.005595 Read_Master_Log_Pos: 581894706 Relay_Log_File: relay-bin.000002 Relay_Log_Pos: 693 Relay_Master_Log_File: mysql-bin.005587 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: 967647646 Relay_Log_Space: 8205026277 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: 1 Master_SSL_Crl: Master_SSL_Crlpath: Using_Gtid: Slave_Pos Gtid_IO_Pos: 0-1-8705427292 Replicate_Do_Domain_Ids: Replicate_Ignore_Domain_Ids: Parallel_Mode: optimistic 1 row in set (0.00 sec) Master status is this: MariaDB [(none)]> show master status\G *************************** 1. row *************************** File: mysql-bin.005595 Position: 581594839 Binlog_Do_DB: Binlog_Ignore_DB: 1 row in set (0.01 sec) If I try to stop the server it hangs also. Waited half an hour after I saw the backups hanging on the 'stop slave' command, then killed the server with kill -9. It restarted without complaining, but the hang is still occuring when doing a 'stop slave'. Can anybody help me diagnosing and possibly resolving this issue?
MariaDB [(none)]> show processlist;
+----+-------------+---------------------+------------+---------+------+-----------------------------------------------+-----------------------------+----------+
| Id | User | Host | db | Command | Time | State | Info | Progress |
+----+-------------+---------------------+------------+---------+------+-----------------------------------------------+-----------------------------+----------+
| 3 | system user | | NULL | Connect | 38 | Queueing master event to the relay log | NULL | 0.000 |
| 4 | system user | | NULL | Connect | 37 | Waiting for prior transaction to commit | NULL | 0.000 |
| 5 | system user | | NULL | Connect | 37 | Waiting for prior transaction to commit | NULL | 0.000 |
| 6 | system user | | NULL | Connect | 38 | Waiting for prior transaction to commit | NULL | 0.000 |
| 7 | system user | | NULL | Connect | 37 | Waiting for prior transaction to commit | NULL | 0.000 |
| 8 | system user | | NULL | Connect | 38 | Waiting for prior transaction to commit | NULL | 0.000 |
| 9 | system user | | NULL | Connect | 37 | Waiting for prior transaction to commit | NULL | 0.000 |
| 10 | system user | | NULL | Connect | 37 | Waiting for prior transaction to commit | NULL | 0.000 |
| 11 | system user | | db37037 | Connect | 37 | Waiting for table metadata lock | OPTIMIZE TABLE `wp_options` | 0.000 |
| 12 | system user | | NULL | Connect | 37 | Waiting for room in worker thread event queue | NULL | 0.000 |
| 13 | maxmonitor | 10.10.100.122:49218 | NULL | Sleep | 0 | | NULL | 0.000 |
| 14 | root | 10.9.9.76:57357 | SpotSystem | Sleep | 38 | | NULL | 0.000 |
| 15 | root | localhost | NULL | Query | 0 | init | show processlist | 0.000 |
+----+-------------+---------------------+------------+---------+------+-----------------------------------------------+-----------------------------+----------+
MariaDB [(none)]> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.16.1.1
Master_User: replication_user
Master_Port: 3306
Connect_Retry: 10
Master_Log_File: mysql-bin.005596
Read_Master_Log_Pos: 84428512
Relay_Log_File: relay-bin.000002
Relay_Log_Pos: 693
Relay_Master_Log_File: mysql-bin.005587
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: 967647646
Relay_Log_Space: 8781623025
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: 1
Master_SSL_Crl:
Master_SSL_Crlpath:
Using_Gtid: Slave_Pos
Gtid_IO_Pos: 0-1-8706014502
Replicate_Do_Domain_Ids:
Replicate_Ignore_Domain_Ids:
Parallel_Mode: optimistic
1 row in set (0.00 sec)
MariaDB [(none)]> select @@global.gtid_slave_pos;
+-------------------------+
| @@global.gtid_slave_pos |
+-------------------------+
| 0-1-8696229561 |
+-------------------------+
1 row in set (0.00 sec)
MariaDB [(none)]> select @@global.slave_parallel_threads;
+---------------------------------+
| @@global.slave_parallel_threads |
+---------------------------------+
| 8 |
+---------------------------------+
1 row in set (0.00 sec)
MariaDB [(none)]> select @@global.slave_parallel_mode;
+------------------------------+
| @@global.slave_parallel_mode |
+------------------------------+
| optimistic |
+------------------------------+
| Thread Previous • Date Previous • Date Next • Thread Next |
