Problem with restarting MySQL server installed through aptitude on Ubuntu Lucid Lynx

Question

I'm using Ubuntu Lucid Lynx (10.04) and installed MySQL server through aptitude. The installation automatically added a user called mysql (it didn't exist in /etc/passwd file at the beginning and it exists there after the installation.) and started MySQL server.

Then I needed to modify my.cnf (in order to enable remote access to the MySQL server). When I restarted MySQL server with

$ sudo /etc/init.d/mysql restart

it hang. But displaying processes by "top" command in another terminal indicated that MySQL server was running with root user. But my.cnf clearly sets

user = mysql

Then I tried

$ sudo service mysql restart

And the result was even worse. It didn't start the MySQL server at all.

As far as I remember, I have never encountered such a problem in the previous versions of Ubuntu.

So my questions are:

1. How can I start MySQL server with a user called mysql in Ubuntu Lucid Lynx (10.04)?

2. Which is the most reliable way to start MySQL server in Ubuntu Lucid Lynx (10.04),

   /etc/init.d/mysql start

   or

   service mysql start

   or

   start mysql

   and what is the difference?

Thank you in advance.

Tadatoshi

Answer 1

/etc/init.d/xxxx start is used to start services using SysV init scripts. Ubuntu uses upstart instead of SysV init and the init scripts are getting converted to native upstart jobs. Usually the init scripts for converted jobs display a warning and call upstart.

start xxxx is used with services using upstart and doesn't work with services using SysV init.

service xxxx start automatically chooses the right one, so it's safe to always use service

As far as I know MySQL has been converted to use upstart in 10.04

Answer 2

if it didn't start, i.e. ps ax | grep mysql doesn't show safe_mysqld and mysqld running, take a look at /var/lib/mysql/hostname.err (where hostname is your machine's hostname) and it has a somewhat detailed log. restart in the default package does a stop, start, but, I wonder if mysqld isn't actually stopping.

/etc/init.d/mysql stop

ps ax|grep mysql (make sure there are no processes running)

/etc/init.d/mysql start

now, if you are looking at the processes, safe_mysqld runs as root, the mysqld process runs as mysql. If mysqld crashes, safe_mysqld loops and tries to restart mysql. Is safe_mysqld or mysqld the process you saw running as root?

Answer 3

Thank you, Florian and user46528,

Based on the Florian's answer, I started to use only "service mysql start".

Then following user46528's answer, I started to investigate the problem.

In short, it turned out the problem was setting bind-address to the IP address of the server in my.cnf.

The key issues I found are:

• If Amazon EC2 is used, bind-address must be set to the private IP address.

• Once "service mysql start" fails because of the problem of bind-address in my.cnf in Ubuntu Lucid Lynx, no matter what is done to resolve it, it keeps hanging. I needed to set the bind-address to a correct IP address and reboot the Ubuntu.

The more detailed explanation of what happened is:

1. I'm using Amazon EC2, which has public IP address and private IP address for each instance, in my case Ubuntu Lucid Lynx instance.

2. With the default bind-address setting in my.cnf, which is bind-address = 127.0.0.1 (localhost), "service mysql stop" and "service mysql start" worked.

3. In my.cnf, I changed the bind-address to the Amazon EC2 public IP address. Then "service mysql stop" worked but "service mysql start" hang.

4. In /var/log/mysql/error.log, I saw "[ERROR] Can't start server: Bind on TCP/IP port: Cannot assign requested address".

5. In my.cnf, I changed the bind-address to the Amazon EC2 private IP address. Then both "service mysql stop" and "service mysql start" hang.

6. In my.cnf, I changed the bind-address back to the default 127.0.0.1. Still both "service mysql stop" and "service mysql start" hang.

7. In my.cnf, I changed the bind-address to the Amazon EC2 private IP address. Then I rebooted the Linux server (by Amazon EC2 reboot command). Then MySQL server started correctly with the user called mysql.

8. "sudo netstat -na" displayed the port 3306 (port for MySQL server) with the private IP address with the State "LISTEN".

9. Accessing the port from another Amazon EC2 instance by "telnet private-ip 3306" indicated that it's connected.

Thank you again for your helps. Without them, I was not able to resolve the problem.

Sincerely, Tadatoshi

Answer 4

The default my.cnf says port = 3306 and bind-address = 127.0.0.1. If you don't have a loopback interface configured, this binding will fail.

To create a loopback interface:

ifconfig lo up 127.0.0.1