mysql show processlist hanging processes;

Question

We needed to move services from one co-location patner to another so we had to copy some files from main server to back up server so these services can now be run on back up server at the new location.

The Systems Admin did a replication of the mysql database. However, since moving the servers, the users created do not work. I cannot login as of the database users created after moving the servers.

when i do a show processlist; i see some two processes that have been hanging for a very long time; I believe they have to do with the replication process.

even when i try to make a change to the password of any of the original users, they too are affected and cant now login.

mysql> show processlist;
+-------+-------------+-----------+-------+---------+---------+-----------------------------------------------------------------------+------------------+
| Id    | User        | Host      | db    | Command | Time    | State                                                                 | Info             |
+-------+-------------+-----------+-------+---------+---------+-----------------------------------------------------------------------+------------------+
|     1 | system user |           | NULL  | Connect | 2156939 | Connecting to master                                                  | NULL             |
|     2 | system user |           | NULL  | Connect | 2156939 | Has read all relay log; waiting for the slave I/O thread to update it | NULL             |

I don't know if these processes are the one's affecting my login. But I need to clean them just because they're annoying.

Someone please show me how to

1. get rid of these two processes;
2. troubleshoot possible reason for my new users not being able to login.

Thanks.

Answer 1

If the users were migrated by adding records to the mysql.user table, rather than by issuing GRANT statements, then you need to...

mysql> FLUSH PRIVILEGES;

...so that these new records will be read and used by the server.

(Yes, "flush" sounds like "delete" but it's not -- in MySQL, it's more like "close and re-open.")

The two threads you posted are not hung, they're idle. Your "backup server" appears to be (or have been) a replica/slave server. The first thread one is the replication IO_thread that reads events from the master, and the second one is the replication SQL_thread, that executes those events. The second one looks normal; the first one is attempting to re-connect to the master server, which, I gather from your question, may now be offline.

They aren't affecting the inability to log in, but if you really want them to go away... this should do it...

mysql> STOP SLAVE;