Can't establish connection to MySQL

Tutorialized Forums Sponsor:

November 1st, 2012, 08:25 PM

dobs

Registered User

Join Date: Nov 2012

Posts: 1

Time spent in forums: 1 h 17 m 49 sec
Reputation Power: 0

No Root users

Hello everyone. First post here on these forums. I hope I'm not posting in the wrong area. Forgive me if I am.

I am very new to MySQL and I'm having a bit of trouble. Recently I accidentally deleted all users including the root users in phpMyAdmin. I clicked all of them and accidentally hit GO, deleting everything. So now I've noticed a few issues. First of all, when I try to sign in to mysql I get this:

Code:

ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)

And yes, I've made sure the server is running. Also, in phpMyAdmin I get this error when trying to login:

Code:

#2002 Cannot log in to the MySQL server

I've tried fixing this by doing things like this:

Quote:

" If the GRANT ALL doesn't work, try:

Stop mysqld and restart it with the --skip-grant-tables option.
Connect to the mysqld server with just: mysql (i.e. no -p option, and username may not be required).

Issue the following commands in the mysql client:

UPDATE mysql.user SET Grant_priv='Y', Super_priv='Y' WHERE User='root';

FLUSH PRIVILEGES;

After that, you should be able to run GRANT ALL ON *.* TO 'root'@'localhost'; and have it work."

After I run mysqld_safe --skip-grant-tables &. Then I add mysql -p -u root and enter in the password I get the same error:

Code:

ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)

Not really sure what to do at this point.

EDIT: I fixed the problem by creating a link to /var/lib/mysqld this way:

Code:

sudo ln -s /var/run/mysql/mysqld.sock mysql.sock

Now I am able to login. The problem of all my root users being gone still exists. I have one user labeled root but does not have the GRANT privilege.

UPDATE: I believe I've fixed the problem. I ran mysqld_safe --skip-grant-tables & and then entered this query:

Code:

insert into `user` (`Host`, `User`, `Password`, `Select_priv`, `Insert_priv`, `Update_priv`, `Delete_priv`, `Create_priv`, `Drop_priv`, `Reload_priv`, `Shutdown_priv`, `Process_priv`, `File_priv`, `Grant_priv`, `References_priv`, `Index_priv`, `Alter_priv`, `Show_db_priv`, `Super_priv`, `Create_tmp_table_priv`, `Lock_tables_priv`, `Execute_priv`, `Repl_slave_priv`, `Repl_client_priv`, `Create_view_priv`, `Show_view_priv`, `Create_routine_priv`, `Alter_routine_priv`, `Create_user_priv`, `ssl_type`, `ssl_cipher`, `x509_issuer`, `x509_subject`, `max_questions`, `max_updates`, `max_connections`, `max_user_connections`) 
values('localhost','root','','Y','Y','Y','Y','Y','  Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y'  ,'Y','Y','Y','Y','Y','Y','Y','Y','','','','','0','  0','0','0');

Then restarted mysql and had a root user with full privileges.

Last edited by dobs : November 2nd, 2012 at 01:55 PM.

MySQL

Can't establish connection to MySQL

Developer Shed Advertisers and Affiliates