Background
It wasn’t working! But in more detail:
- An R1Soft restore had taken place, restoring from the backup server to the live server
- The files were MySQL data files originally from /var/lib/mysql/
- The files were restored to /root/temp/db_wordpress/ and checked
- Files were relocated from /root/temp/db_wordpress/ to /var/lib/mysql/db_wordpress2/
- Permissions were adjusted on /var/lib/mysql/db_wordpress2/ and /var/lib/mysql
- MySQL was restarted to load the new database
- A repair on the new database was completed
- I’d been asleep for a total of 3 hours then woken to deal with an issue, and it was now 5am.
What happened next came to us by surprise. Every single website using MySQL (all WordPress sites), went from working perfectly to “Error establishing a database connection”. There were no further errors.
- MySQL was restarted. It reported start and stop successfully. There was however no change in the websites.
- We checked the MySQL error logs. To my absolute shock there was nothing more logged beyond a successful stop/start of MySQL.
- MySQL was stopped again and the new database data files were removed from the /var/lib/mysql/ directory, then MySQL was started again. No change.
We’d removed what we thought to be damaged data files, the only thing foreign we introduced to the environment. What was more annoying was that WordPress was giving nothing more than “Error establishing a database connection”.
The Plot
Here’s where the mental head spin occurred. Stay tuned:
$ mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 4.1.22
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> show processlist;
+----+------+-----------+------+---------+------+-------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------+------+---------+------+-------+------------------+
| 2 | root | localhost | NULL | Query | 0 | NULL | show processlist |
+----+------+-----------+------+---------+------+-------+------------------+
1 row in set (0.00 sec)
MySQL connects from the command line perfectly and runs a command.
mysql> use db_wordpress2;
Database changed
mysql> show tables;
+-------------------------+
| Tables_in_db_wordpress2 |
+-------------------------+
| wp_comments
| wp_links
| wp_options
| wp_postmeta
| wp_posts
| wp_term_relationships
| wp_term_taxonomy
| wp_terms
| wp_usermeta
| wp_users
+-----------------------+
10 rows in set (0.00 sec)
MySQL just accessed a database and listed the available tables – all present and correct.
mysql> select * from wp_users;
+----+------------+------------------------------------+---------------+--------------------------+--------------------------+---------------------+---------------------+-------------+--------------+
| ID | user_login | user_pass | user_nicename | user_email | user_url | user_registered | user_activation_key | user_status | display_name |
+----+------------+------------------------------------+---------------+--------------------------+--------------------------+---------------------+---------------------+-------------+--------------+
...
+----+------------+------------------------------------+---------------+--------------------------+--------------------------+---------------------+---------------------+-------------+--------------+
10 row in set (0.20 sec)
MySQL just accessed a database and listed data from a table, but all the WordPress websites were still down with “Error establishing a database connection”. Head-spin!
Absolutely furious and in rage from the long day, lack of sleep, the sole and meaningless WordPress errors, and the zero errors MySQL gave. To top it, I couldn’t see the screen properly with my blotchy tired eyes and I couldn’t type without missing keys and putting spaces in the wrong place. I knew I had to escape the WordPress environment. But it felt like I was looking up at a large mountain.
On a side note, I recommend anyone and everyone who wants to learn the basics of a programming language to W3Schools. I simply don’t have time to teach everyone, so it’s a great resource. I knew they had a great PHP snippet for connecting to a MySQL database.
<?php
$con = mysql_connect("localhost","peter","abc123");
if (!$con)
{
die('Could not connect: ' . mysql_error());
}
// some code
echo "done";
?>
Source: http://www.w3schools.com/PHP/php_mysql_connect.asp
I replaced the variables with the login details from the wp-config.php file and ran it in the browser. I could have cried with joy at this exhausted point.
Warning: mysql_connect() [function.mysql-connect]: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (13) in /var/www/db.php on line 2 Could not connect: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (13)
Typically when you see this type of error it means MySQL isn’t running, is hanging or in the process of shutting down. But it’s been clearly established that is not the case.
It took me a little while to realise the little bit of information I was overlooking. In fact I admit, it took me way too long, but I put that down to the lack of sleep. Oh that magic little number was staring right at me.
...sql/mysql.sock' (13)
What is the significance of the number 13? There are three important factors:
- It’s one more than 12
- It’s one less than 14
- And its an operating system error code relating to something specific.
It was as simple as looking it up to point me in the right direction:
$ perror 13
OS error code 13: Permission denied
Because it’s an operating system error code, it rules out any permissions issues within MySQL. While I’d removed the database I thought was causing the issues, I didn’t revert the permissions changes I had made, more specifically and stupidly to the /var/lib/mysql directory.
The correct permissions were 755 and look as follows:
$ ls -alh /var/lib | grep mysql
drwxr-xr-x 8 mysql mysql 4.0K Sep 3 18:02 mysql
Conclusion
So what is the lesson learned here? There are two very key points:
- Pay more attention to the error codes you are give. The messages are standard, but the number give it more specific meaning.
- Never opt to do third shift on a weekend where you’ve done the first and second shifts as well.
All the servers lived happily ever after.
