Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

migrated from stackoverflow.com Sep 24 '12 at 16:22

This question came from our site for professional and enthusiast programmers.

1 Answer 1

up vote 0 down vote accepted

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;
share|improve this answer
    
Hello Michael; Thanks alot for the response on both the user privileges and the idle thread. Both suggestions worked great! Bless You! –  Ombongi Moraa Sep 25 '12 at 6:54

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.