A colleague requested help saying he was unable to login to his mysql server using root credentials. They had to give a demo and hence were looking for quick help. This is a log of the trouble shooting session for their benefit-
Lets check the command-
ubuntu@ip-10-10-10-10:~$ sudo mysql -uroot -p
Enter password:
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)
ubuntu@ip-10-10-10-10:~$
“Can’t connect…”? Is the server running?
ubuntu@ip-10-10-10-10:~$ ps -aef | grep mysql
ubuntu 29911 29772 0 09:34 pts/0 00:00:00 grep --color=auto mysql
ubuntu@ip-10-10-10-10:~$
Obviously not. Lets try starting the server.
ubuntu@ip-10-10-10-10:~$ sudo service mysql start
start: Job failed to start
ubuntu@ip-10-10-10-10:~$ which mysql
/usr/bin/mysql
ubuntu@ip-10-10-10-10:~$ telnet 127.0.0.1 3306
Trying 127.0.0.1...
telnet: Unable to connect to remote host: Connection refused
ubuntu@ip-10-10-10-10:~$
Ok, its not starting. Lets look into the logs for any errors.
<pre>ubuntu@ip-10-10-10-10:~$ cat /var/log/mysql.err
ubuntu@ip-10-10-10-10:~$ cat /var/log/mysql.log
ubuntu@ip-10-10-10-10:~$ dmesg | grep mysql
<snip> ...</snip>
[29976983.198618] init: mysql main process (29965) terminated with status 1
[29976983.198673] init: mysql main process ended, respawning
[29976984.146692] init: mysql post-start process (29966) terminated with status 1
[29976984.167458] type=1400 audit(1402392890.291:19): apparmor="STATUS" operation="profile_replace" name="/usr/sbin/mysqld" pid=29999 comm="apparmor_parser"
[29976984.294210] init: mysql main process (30003) terminated with status 1
[29976984.294268] init: mysql respawning too fast, stopped
[29977185.261161] type=1400 audit(1402393091.383:20): apparmor="STATUS" operation="profile_replace" name="/usr/sbin/mysqld" pid=30096 comm="apparmor_parser"
<snip> ...</snip>
Googling for “mysql main process ended, respawning” and “mysql respawning too fast, stopped” error messages threw up multiple posts with a variety of solutions. Reconfiguring seemed a good step to start with and that resolved the issue for us.
http://askubuntu.com/questions/125686/mysql-fails-to-start-after-upgrade-installation-etc
http://askubuntu.com/questions/127264/cant-start-mysql-mysql-respawning-too-fast-stopped
ubuntu@ip-10-10-10-10:~$ apt-cache show mysql-server | grep -i version
Version: 5.5.37-0ubuntu0.12.04.1
Description-en: MySQL database server (metapackage depending on the latest version)
This is an empty package that depends on the current "best" version of
version you need. That will install the version recommended by the
<snip> ... </snip>
ubuntu@ip-10-10-10-10:~$ sudo dpkg-reconfigure mysql-server-5.5
perl: warning: Setting locale failed.
perl: warning: Please check that your locale settings:
LANGUAGE = (unset),
LC_ALL = (unset),
LC_PAPER = "en_IN.UTF-8",
LC_ADDRESS = "en_IN.UTF-8",
LC_MONETARY = "en_IN.UTF-8",
LC_NUMERIC = "en_IN.UTF-8",
LC_TELEPHONE = "en_IN.UTF-8",
LC_IDENTIFICATION = "en_IN.UTF-8",
LC_MEASUREMENT = "en_IN.UTF-8",
LC_TIME = "en_IN.UTF-8",
LC_NAME = "en_IN.UTF-8",
LANG = "en_US.UTF-8"
are supported and installed on your system.
perl: warning: Falling back to the standard locale ("C").
locale: Cannot set LC_ALL to default locale: No such file or directory
/usr/bin/locale: Cannot set LC_ALL to default locale: No such file or directory
<snip> ... </snip>
Ok, that was simple. Now, lets try logging in.
ubuntu@ip-10-10-10-10:~$ mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 47
Server version: 5.5.32-0ubuntu0.12.04.1 (Ubuntu)
Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> quit
Bye
Phew! That didn’t take too long.