Ugh, so the Ubuntu 14.04 VPS crashed, corrupting MySQL as it went. The initial symptom was that web apps couldn’t connect to MySQL… the error message mentioned not being able to connect via a socket, but I knew the web apps were configured to connect to 127.0.0.1 – IE connect via TCP/IP, not over a socket.
Eventually I ran
tail -f /var/log/mysql/error.log
…and I could see entries appearing in the log that suggested something was wrong with the InnoDB tablespace: data corruption, causing MySQL startup to fail.
Here are the steps I took to get it working (…as the server’s root user):
This was surprisingly difficult – I think because mysqld_safe was running, and was restarting mysqld every time I ran a command like service mysql stop or kill -9 [mysqld PID goes here].
What seemed to work for me was the mysqladmin shut command but, from the tone of forum posts discussing similar problems, I suspect YMMV.
Start MySQL, forcing INNODB recovery
So you can set a MySQL configuration value that forces MySQL to start even a normal startup would abort due to data corruption issues. The configuration flag is innodb_force_recovery.
You need to write it into your MySQL configuration file – mine’s at /etc/mysql/my.cnf:
user = mysql
innodb_force_recovery = 1
# a value of 0 means do not force recovery at all; 6 means just start, no matter the risk
WARNING: Depending on the value (1 … 6) MySQL skips more and more safety-checking steps in order to start up. Once you get to 4, MySQL is taking significant risks as it starts – IE you risk making corruption worse, or causing more serious problems, if you start with innodb_force_recovery = 4 (or above).
I tried with a value of 1; that didn’t work; so I raised it to 2, and in my case that was enough to get MySQL started.
Backup MySQL data as SQL dump
Next I ran the command
mysqldump --skip-lock-tables -A > alldb.sql
Backup old MySQL data files
First, check where MySQL is keeping its data:
mysql -NBe "SELECT @@datadir"
Now, you can move the existing files to back them up and, from MySQL’s point of view, disappear them at the same time:
mv /var/lib/mysql /var/lib/mysql.old
Create new version of MySQL data file directory
Restart MySQL and import your SQL
mysql < alldb.sql
These steps, I think, sorted out the data itself. But I still needed to reset the MySQL root password; and the web apps still couldn’t access the database – although the errors now were to do with permissions, not actual connection to the DB.
Resetting MySQL root password
mysqld_safe --skip-grant-tables &
mysql>update user set password=PASSWORD("mynewpassword") where User='root';
sudo /etc/init.d/mysql stop
sudo /etc/init.d/mysql start
I think that last step (MySQL flush privileges command) may have helped all user accounts, because having done all that, the web apps could reach the database again… and they seemed to work OK.