Hi guys. I recently posted a question about phpMyAdmin, that it won’t connect to the mysql database. I thought that the problem is in the phpMyAdmin not in the mysql. But now i think different. It seems that the problem is right in the mysql. I think that there was no root user, when mysql was freshly installed. Unfortunately i did everything i can find on internet (some tutorials) but they don`t helped me at all, maybe i fu**ed up the mysql service. So i reinstalled it, and when i want to start it there is no file mysql.sock and i get the following:
linux-nepg:~ # service mysql restart
Restarting service MySQL
Shutting down service MySQL done
Starting service MySQL warning: /var/lib/mysql/mysql.sock didn’t appear within 30 seconds
I thought that this file should be recreated after the mysql was reinstalled, but unfortunately not.
Help me i am frustrated, for 3 hours i am trying to solve this problem,and without any success. Please answer, and give some hints or ideas. Thank you!
Check the ownership of /var/lib/mysql and everything in it.
Sometimes people try to run the mysqld process directly. This causes some files to be owned by root and then when they try to start it from the init script, as they should, something doesn’t work.
If you have nothing in the DB, you might consider deleting /var/lib/mysql manually and reinstalling the package, to clear the error. A reinstall or upgrade doesn’t delete the DB directory for obvious reasons.
I reinstalled the mysql, and now i can power it up. But there is still no root user, i don`t know why. I installed it via YaSt, and when i try to login i get this:
mysql -u root
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 15
Server version: 5.0.67 SUSE MySQL RPM
Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the buffer.
mysql> use mysql;
ERROR 1044 (42000): Access denied for user ‘’@‘localhost’ to database ‘mysql’
mysql>
PhpMyAdmin cant access the database with root username, but with any other can access the databases (default ones) but dont have privileges to create a new database.
Is there a file where i can add root user, or add a password to the root user?
Thank you
Yes, that’s correct. Also all the files in it should be owned by mysql:mysql.
Coming back to your problem, it seems that mysqladmin could not connect to the socket to change anything. So the problem is probably earlier when mysqld could not create the socket.
Do you have an old /etc/my.cnf perhaps, or from some other distro, that specifies a different place for the socket? If you do, delete it and reinstall the package to get a correct copy.
Hmm… every file inside mysql dir has the same parameters as i said above. Today i installed 11.1 until then i had 9.0 suse also with mysql installed. In etc there is only one binary file my.cnf. How to check if there is old copy of it from 9.0?
You can check if /etc/my.cnf (which is a text file by the way, and can be viewed with a text editor, a binary file would definitely cause mysql to cough and splutter) is the same as from the package with:
rpm -V mysql
If it reports that the file is changed, there are two possibilities: The file came from somewhere else, or you edited it. Either way it’s not good if this is supposed to be a fresh install.
If you find that the file is not the one from the package, then delete it and reinstall mysql to get the correct version.
Hmm very very strange. The my.cnf is a binary file not the text file… The Doplhin says that this is a binary file, when i try to open it it prompts me which program i should use I accessed it via terminal, and do a vi my.cnf and it opened a textual file.) But still saying in Dolphin that this is a binary file. Could this cause errors?
EDIT: Kate says it cannot open the file. Permissions are the following:
Owner: Can Read & Write
Group: Can Read & Write
Others: Forbidden
Hmm, so everything is lookin fine, but i doesnt have root user at all?
Should i mention that i also have installed Database Administrator, which give me nice info about my mysql server but when i go to User Administration, it says:
Could not retrieve user privilege information.
That’s bizarre too. Not sure what you mean by no root user. This is the Linux root account, which is not related to the MySQL root account, except the same name.
Look, here’s what I would do to start off with a clean slate:
/etc/init.d/mysql stop
rm /etc/my.cnf
rm -fr /var/lib/mysql
Reinstall mysql, force the install if you have to.
Check that /etc/my.cnf is there
Check that /var/lib/mysql and /var/lib/mysql/mysql are there
Check that everything under /var/lib/mysql is owned by mysql:mysql
Start the service with YaST, or /etc/init.d/mysql start
If that doesn’t work, then I am out of ideas.
PS: One more thing, is there anything special about /var, is it a separate partition with special mount options, or even (I don’t think this is even possible) a non-Linux filesystem?
It isn’t a socket problem as you would not have been able to log in if it was. The error message is puzzling. On install, there are normally anonymous users as well as root users on ‘localhost’ and whatever is LOCALSITE.
The message says you are logged on as an anonymous user and not as root which is why you cannot access the mysql database.
I wonder if it is worth trying the full form
mysql - h localhost -u root
to make sure you are connected as root.
I am also not sure whether a single additional space between -u and root would cause mysqld to interpret this as the anonymous user or not.
Boy this thread brings bac soe nightmares…or lack of due to no sleep. i had the same error for about a week. Installed mysql as I had previously and just could not connect to mysql admin on localhost socket. In the end I pointed phpMyAdmin at the test db just to get things going.
In the past when encountering authentication problems with mysql I have installed the packages and started the mysql service via yast>system>runlevel. this gets things running with no users or passwords. I then fire up phpmyAdmin. It complains that no root password is set and I set it via phpMyAdmin. Under this current install 11.1,that tactic just wouldn’t work. As some form of disclaimer- this tactic is a dumbass way to run a production website. DON"T DO IT. Test sites only- ie don’t listen on :80 when using this tactic.
@ken_yap: What i mean about root user, i mean about root user for mysql (not the root suse user,which worsk fine). I will try your solution now…
@john_hudson: I agree that this is not a socket problem, because mysql is running, but there is no users at all i think
I wonder if it is worth trying the full form
mysql - h localhost -u root
to make sure you are connected as root.
I tried that, this is what i got:
mysql -h localhost -u root
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.0.67 SUSE MySQL RPM
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> USE mysql;
ERROR 1044 (42000): Access denied for user ''@'localhost' to database 'mysql'
@saltyp: I won`t do this, but i plan to build a local server, not a server with internet access, so security is not an issue.
I tried that ken_yap, and all you said is ok. There are /var/lib/mysql and /var/lib/mysql/mysql all owned by mysql.
I logged in:
linux-nepg:/var/lib/mysql # mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.67 SUSE MySQL RPM
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> USE mysql;
Database changed
mysql>
Now with MySql ADministrator i can login and see users, and there are 2 i guess. One without a name (blank field) with 2 subclass @localhost and @linux-nepg and other user root with 3 subclasses (subcategory whatever) @localhost@linux-nepg@127.0.0.1
I tried to login with phpmyadmin but it keep saying Access Denied if i use username root pass blank
Hi guys, just to let you know, i finally solved the problem. I don`t know why, but the version of phpMyAdmin keeps saying Access Denied, if i use username root without a password. When i set a password for mysql user root, and enter it in a login field of phpMyAdmin everything goes smoothly. Thanks for your help and patience:)
Glad you got that problem sorted out. It’s still a mystery why mysqld didn’t create a socket, but who knows. Maybe it was slow to appear, or maybe you had an old my.cnf which put the socket somewhere else.
I accessed it via terminal, and do a vi my.cnf and it opened a textual file.) But still saying in Dolphin that this is a binary file. Could this cause errors?