1

My VPS had a hardware failure, and I was knocked out of service for the past 24 hours. Now that it's back up, MySQL refuses to work. Please keep in mind that my VPS with these exact configurations has been running for the past 2 months without error, I have not changed any code/database settings since the hardware failure.

At the command line, simply typing "sudo mysql" returns

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

I can not login with PhpMyAdmin, I receive 2 errors:

#2002 Cannot log in to the MySQL server
Connection for controluser as defined in your configuration failed.

My web app (Codeigniter) returns the following error

A Database Error Occurred

Unable to connect to your database server using the provided settings.

Filename: core/Loader.php

Line Number: 346

I am running a typical LAMP stack on Ubuntu 10.04. I have more than enough free space on my hard disk still (over 20 gigs left), so being out of space is not an issue.

/var/log/mysql.err has nothing in it, it is an empty file. I have searched high and low on Google, but seem to be at a complete loss right now.

EDIT:

Running mysqld gave me

mysqld: Can't find file: './mysql/plugin.frm' (errno: 13)
120630 18:41:10 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it.
120630 18:41:10  InnoDB: Initializing buffer pool, size = 8.0M
120630 18:41:10  InnoDB: Completed initialization of buffer pool
120630 18:41:10  InnoDB: Operating system error number 13 in a file operation.
InnoDB: The error means mysqld does not have the access rights to
InnoDB: the directory.
InnoDB: File name ./ibdata1
InnoDB: File operation call: 'open'.
InnoDB: Cannot continue operation.

at first, now running mysqld, I get nothing, just returns me to the prompt.

EDIT 2: Running strace -e trace=file mysqld returns:

120630 19:17:12 [Note] Plugin 'FEDERATED' is disabled.
open("./mysql/plugin.frm", O_RDONLY)    = -1 EACCES (Permission denied)
mysqld: Can't find file: './mysql/plugin.frm' (errno: 13)
120630 19:17:12 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it.
open("/tmp/ibzhYSAn", O_RDWR|O_CREAT|O_EXCL, 0600) = 1
unlink("/tmp/ibzhYSAn")                 = 0
open("/tmp/ibRf9302", O_RDWR|O_CREAT|O_EXCL, 0600) = 1
unlink("/tmp/ibRf9302")                 = 0
open("/tmp/ibN6AjrI", O_RDWR|O_CREAT|O_EXCL, 0600) = 1
unlink("/tmp/ibN6AjrI")                 = 0
120630 19:17:12  InnoDB: Initializing buffer pool, size = 8.0M
120630 19:17:12  InnoDB: Completed initialization of buffer pool
open("/tmp/ibGh8aSn", O_RDWR|O_CREAT|O_EXCL, 0600) = 1
unlink("/tmp/ibGh8aSn")                 = 0
open("./ibdata1", O_RDWR|O_CREAT|O_EXCL, 0660) = -1 EEXIST (File exists)
open("./ibdata1", O_RDWR)               = -1 EACCES (Permission denied)
120630 19:17:12  InnoDB: Operating system error number 13 in a file operation.
InnoDB: The error means mysqld does not have the access rights to
InnoDB: the directory.
InnoDB: File name ./ibdata1
InnoDB: File operation call: 'open'.
InnoDB: Cannot continue operation.
Bert B.
  • 155
  • 1
  • 1
  • 8
  • http://serverfault.com/questions/65050/cant-connect-to-local-mysql-server-through-socket-var-lib-mysql-mysql-sock?rq=1 – user9517 Jun 30 '12 at 22:29
  • Iain, that is not a helpful link, his problem was mainly focused on disk space being low, that is not the case for me. – Bert B. Jun 30 '12 at 22:33
  • @BertB. What happens when you run `service mysql start`? – Shane Madden Jun 30 '12 at 22:38
  • running `service mysql start` returns `start: Unable to connect to system bus: Failed to connect to socket /var/run/dbus/system_bus_socket: No such file or directory` running `sudo service mysql start` returns `start: Job is already running: mysql` – Bert B. Jun 30 '12 at 22:40

2 Answers2

1

If the logfile is empty, try to start mysqld manually, literally by just running mysqld at a terminal.

It should pick up your configuration file automatically (e.g. /etc/my.cnf or /etc/mysql/my.cnf) and run with it.

After that, you should have errors in plain view for you to fix. If you put them into your original question too, we can help further. The issue could be at the host: not enough memory or even the physical disks are out of space, even though you haven't yet reached your allocation.

Also, my 2 cents: upstart is crap when stuff fails. When I had MySQL issues, I already got these "already running" messages even though there wasn't a single mysql[d] - so ignore it for now.

Can you check your data directory? Sounds like that crash has corrupted it and you've lost some files. It's normally /var/lib/mysql.

  1. Can you check the permissions (do they match your .cnf file for what they should be)?
  2. Look ibdata1, how are the permissions on that?

If you have backups, it might be a good idea to use them.

Jay
  • 6,544
  • 25
  • 34
  • Please check my edits – Bert B. Jun 30 '12 at 22:47
  • It failed to start, so it's good that you got some error messages at least. Please see my edits. – Jay Jun 30 '12 at 22:51
  • My permissions match the .cnf file. The permissions on ibdata1 also look good: `-rw-rw---- 1 mysql mysql 60817408 2012-06-30 01:00 ibdata1` Unfortunately, I don't have a recent backup. Could I salvage the data from the /var/lib/mysql since it's all there? – Bert B. Jun 30 '12 at 22:57
  • Can you first verify what `/var/lib/mysql/mysql/` looks like? Is there a `plugin.frm` and such? How many files in that directory? – Jay Jun 30 '12 at 23:05
  • It looks like it is properly structured, there are 70 files, and yes there is a `plugin.frm` – Bert B. Jun 30 '12 at 23:07
  • Something else has gotten a bit confused then. `sudo -u mysql touch /var/lib/mysql/mysql/plugin.frm` - does that definitely work (i.e. no errors)? If so, then my next step would just be to `strace -e trace=file mysqld` and look and see what it's trying to open (incorrectly, it would seem). Maybe that'll give some hints. If you can't figure it out, post that in your question. – Jay Jun 30 '12 at 23:15
  • The touch command worked, I edited the initial post with some output from strace – Bert B. Jun 30 '12 at 23:20
  • Permission denied to open `plugin.frm`. All I can come up with now is mysqld is not running as `mysql`. Can you triple check your configuration file? The earlier lines in your strace will tell you exactly which `.cnf` file it's using. – Jay Jun 30 '12 at 23:25
  • It looks all good to me, it's using the /etc/mysql/my.cnf file. What do you mean by `mysqld` is not running as `mysql`? What could I do to fix that? – Bert B. Jun 30 '12 at 23:30
  • Look inside the `/etc/mysql/my.cnf` file, in the `[mysqld]` portion and check what `user` is. – Jay Jun 30 '12 at 23:32
  • `user` is mysql – Bert B. Jun 30 '12 at 23:47
  • I'm running thin on ideas, and getting worried it's an 'obvious' issue that I can't see because I don't have the terminal in front of me. I would really like to see a `strace -e trace=all mysqld`, can you try getting a full one of those in the question (or in pastebin, as it will be very large) – Jay Jul 01 '12 at 00:00
  • Here's a pastebin of the full strace http://pastebin.com/ABFhDCUD – Bert B. Jul 01 '12 at 00:05
  • Oh my! Are you running this as `bert`? Please run it as `root` (my fault, I thought I mentioned that - but did you cut some entries from your error log? I see `One can only use the --user switch if running as root` in the strace)! Go to a pure root shell (`sudo -i` or `sudo su -`), then start again. If `mysqld` fails with errors, post those, and post the `strace -e trace=file mysqld` for good measure in the question. – Jay Jul 01 '12 at 00:08
  • Sorry, here is it ran as root http://pastebin.com/Bb7esYZ9 (I cut off the end of this log because it started to just repeat all those `write` lines and had to quit it with control-c) and running `mysqld` in a root shell doesn't throw any errors, just gives me a new prompt – Bert B. Jul 01 '12 at 00:15
  • Leaving that strace open, can you now connect to MySQL in a new terminal? – Jay Jul 01 '12 at 00:19
  • Left it open for a while, but ended up finishing with `End of page dump`. Running `mysql` returns `ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)` – Bert B. Jul 01 '12 at 00:22
  • Your InnoDB files are corrupted. You can try something like this: http://www.mysqlperformanceblog.com/2008/07/04/recovering-innodb-table-corruption/ but take a backup of what you have first. – Jay Jul 01 '12 at 00:28
  • Darn. How can I follow that guide if I can't even run the `mysql` command? I return the error `ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)` – Bert B. Jul 01 '12 at 00:55
  • You might want to play with `innodb_force_recovery` in your configuration file (try from 1 through 6). Eventually MySQL *should* boot, and you can go from there. – Jay Jul 01 '12 at 01:31
0

Check /var/log/daemon.log for startup errors in mysqld. You have an issue that's not letting MySQL start, more than likely.

grep mysql /var/log/daemon.log should give you a listing of the pertinent error messages. Post back with the log complaints.

Fiasco Labs
  • 563
  • 4
  • 10
  • Don't use `cat`: `grep mysql /var/log/daemon.log` – Jay Jun 30 '12 at 22:53
  • I see just a bunch of `Jun 30 18:42:48 webby7117 init: mysql main process (2386) terminated with status 1 Jun 30 18:42:48 webby7117 init: mysql main process ended, respawning Jun 30 18:43:18 webby7117 init: mysql post-start process (2387) terminated with status 1` Repeating. – Bert B. Jun 30 '12 at 22:56
  • @JayShah usual unix TMTOWTDI. Corrected to just grep. I always pipe through less as it allows me to back up through the file to see where the problem started. – Fiasco Labs Jun 30 '12 at 23:22
  • @FiascoLabs I had no issue about using `less`, I use that all the time. I was just pointing out the `cat` gave only disadvantages. Lots of SO devs tend to post this link: http://partmaps.org/era/unix/award.html when someone does it. – Jay Jun 30 '12 at 23:27
  • @JayShah Heh, kind of like that "useless use of a" error that compilers throw out. – Fiasco Labs Jun 30 '12 at 23:35
  • @BertB sorry it didn't help us much. We already know it's terminating with an error. – Fiasco Labs Jun 30 '12 at 23:36