Category Archives: MySQL

MySQL Job failed to start on Ubuntu

That’s one reason (of many) for this blog title (grinding, ranting…)

Today, when I tried to access the blog I got a nice “Database connection error”. I really hadn’t done anything unusual (other than an apt-get upgrade, but I don’t really think it should fall at the “unusual” category), so I logged on my VPS provider and restarted the server, only to have the same error displayed.

(sigh here)

As you guessed by the post title, I use MySQL, so I logged on my machine (my VPS offers me full ssh access, which is both a bless and a curse) and found out (obviously) that the mysql daemon was not started. Tried to restart it with (I know, I’m both lazy and stubborn):

# service mysql start

and had the informative (not!) error displayed:

start: Job failed to start

That’s it! The only thing printed on the console after I tried to start the service… Since the console was not helping me, I decided to take a look a the logs at /var/log. Both the mysql.err and mysql.log files were empty. The /var/log/mysql/error.log file was also empty. Now, that’s helpful.

So, I Googled the problem and found some hints pointing to permission errors regarding the place where the mysqld.lock file is stored. I looked at /etc/mysql/my.cnf and at the directory /var/run/mysqld (that’s where the /etc/mysql/my.cnf file told me it should be), but the permissions were ok, mysql was the owner of the directory and it had 755 permission.

So I came back to /var/log and noticed that /var/log/mysql directory and that both the /var/log/mysql.err file were both root owned. Hm… Weird… That’s what ‘ls -l’ returned:

drwxr-s--- 2 root root 4096 Mar 20 07:35 mysql
-rw-r----- 1 root root    0 Mar 6  19:53 mysql.err

I then changed the owner of both the file and directory:

# chown -R mysql:adm /var/log/mysql
# chown mysql:adm /var/log/mysql.err

Maybe now some error would be written to the logs… And it really did after I tried to start the service once more; at least now the /var/log/mysql/error.log file had some content, and upon inspecting it I saw a really suspect line:

/usr/sbin/mysqld: Can't find file: './mysql/plugin.frm' (errno: 13)
130320 18:40:13 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it.

Ok, so either the ./mysql/plugin.frm file really did not exist or its permissions were wrong. Now I had to find out where this file should be. Upon inspecting once again the /etc/mysql/my.cnf file, I saw some directories worth looking at:

datadir = /var/lib/mysql
tmpdir = /tmp
lc-messages-dir = /usr/share/mysql

And that was it, the /var/lib/mysql directory had root as owner and 700 permissions:

drwx------ 6 root root 4096 Mar 20 18:46 mysql

One more chown:

# sudo chown -R mysql:root /var/lib/mysql

And I was able to start mysql again!

 

Share Button

MySQL Server on Ubuntu 12.04

Install mysql-server via apt-get:

$ sudo apt-get install mysql-server

Set the root password

Activate MySQL:

$ sudo mysql_install_db

Finish the setup by running:

$ sudo /usr/bin/mysql_secure_installation

If you have setup the root password on the first step, confirm it and answer “No” to the change root password question, and accept the default answer for the rest (just press Enter).

To access the mysql console:

$ mysql -u root -p

Here you can issue any other command you want to…

That’s it!

Share Button

Change charset encoding on MySQL databases

It’s pretty simple, just issue an ALTER DATABASE command:

ALTER DATABASE database_name CHARACTER SET charset

To check if it was changed:

SELECT default_character_set_name FROM information_schema.SCHEMATA
WHERE schema_name = "database_name";

 

Share Button