MySQL autostart problem

[fmyoen]

Hi. I'm FreeBSD user and new to CentOS, so maybe an answer to my question would be trivial. I need to set up two MySQL servers running on the same OS. Everything works fine but only one instance boots normally at system restart.

Here is description of setup process. I installed MySQL and added it to autorun at reboot time:

>yum update
>yum install mysql-server.i386
>chkconfig mysqld on
>service mysqld start

Everything is OK and MySQL boots normally both at system start and from command line.

Now I try to set up multiple MySQL instances using hxxp://blog.mydream.com.hk/howto/linux/mysql-multiple-instance-on-centos-howto (I can't post URLs because I have less than 15 posts) as a reference. This is how I renamed files for the first instance:

>chkconfig --del mysqld
>mv /etc/my.cnf /etc/my1.cnf
>rm -rf /var/lib/mysql
>mv /etc/init.d/mysqld /etc/init.d/mysqld1
>chkconfig --add mysqld1
>chkconfig mysql1 on
>rm /var/log/mysqld.log

Edited my1.cnf and mysqld1 are attached in archive. In short I have changed paths to the socket, lock, log files and data directory, "added port=3306" line to my1.cnf and "--defaults-file=/etc/my1.cnf" to mysqld1.

Everything is still OK and MySQL instance one still boots normally both at system start and from command line.

Setting up instance two on port 3307:

>cp -vp /etc/my1.cnf /etc/my2.cnf
>cp -vp /etc/init.d/mysqld1 /etc/init.d/mysqld2
>chkconfig --add mysqld2
>chkconfig mysqld2 on
>semanage port -a -t mysqld_port_t -p tcp 3307

Edited my2.cnf and mysqld2 are attached in archive. The only difference between files for MySQL instance one is that paths contain 2 instead of 1 and port changed to 3307.

Both instances can be started from command line but only instance one is starting at system start time. Logs show nothing. According to it MySQL instance two starting up and immidiately shutting down:

>less /var/lib/mysql2/mysqld.log
111106 02:32:32 mysqld started
111106 02:32:32 mysqld ended

111106 02:38:29 mysqld started
111106 02:38:29 mysqld ended

111106 02:44:50 mysqld started
111106 02:44:51 mysqld ended

111106 02:48:21 mysqld started
111106 02:48:21 mysqld ended

111106 02:56:23 mysqld started
111106 02:56:23 mysqld ended

111106 03:06:31 mysqld started
111106 03:06:31 mysqld ended

Do you have any ideas what should I try to do? It looks for me like it's not an ownership, a permissions or a paths problem. It may be some wierd MySQL problem or incorrect setup of the new service since I don't have too much expirience using CentOS.

The config of CentOS is pretty standard, all that I did is disabling of ip6tables service and adding pc.lan to /etc/hosts and /etc/sysconfig/network.

>chkconfig --list mysqld1
mysqld1 0:off 1:off 2:on 3:on 4:on 5:on 6:off

>chkconfig --list mysqld2
mysqld2 0:off 1:off 2:on 3:on 4:on 5:on 6:off

>ls -lA /etc/rc.d/rc?.d/ | grep mysql
lrwxrwxrwx 1 root root 17 Nov 6 02:54 K36mysqld1 -> ../init.d/mysqld1
lrwxrwxrwx 1 root root 17 Nov 6 03:27 K36mysqld2 -> ../init.d/mysqld2
lrwxrwxrwx 1 root root 17 Nov 6 02:54 K36mysqld1 -> ../init.d/mysqld1
lrwxrwxrwx 1 root root 17 Nov 6 03:27 K36mysqld2 -> ../init.d/mysqld2
lrwxrwxrwx 1 root root 17 Nov 6 03:27 K36mysqld2 -> ../init.d/mysqld2
lrwxrwxrwx 1 root root 17 Nov 6 02:54 S64mysqld1 -> ../init.d/mysqld1
lrwxrwxrwx 1 root root 17 Nov 6 03:27 K36mysqld2 -> ../init.d/mysqld2
lrwxrwxrwx 1 root root 17 Nov 6 02:54 S64mysqld1 -> ../init.d/mysqld1
lrwxrwxrwx 1 root root 17 Nov 6 03:27 K36mysqld2 -> ../init.d/mysqld2
lrwxrwxrwx 1 root root 17 Nov 6 02:54 S64mysqld1 -> ../init.d/mysqld1
lrwxrwxrwx 1 root root 17 Nov 6 03:27 K36mysqld2 -> ../init.d/mysqld2
lrwxrwxrwx 1 root root 17 Nov 6 02:54 S64mysqld1 -> ../init.d/mysqld1
lrwxrwxrwx 1 root root 17 Nov 6 02:54 K36mysqld1 -> ../init.d/mysqld1
lrwxrwxrwx 1 root root 17 Nov 6 03:27 K36mysqld2 -> ../init.d/mysqld2

>uname -a
Linux pc.lan 2.6.18-274.7.1.el5 #1 SMP Thu Oct 20 16:20:37 EDT 2011 i686 i686 i386 GNU/Linux

>rpm -qa | grep mysql
mysql-5.0.77-4.el5_6.6
mysql-server-5.0.77-4.el5_6.6

>diff /etc/my1.cnf /etc/my2.cnf
2,4c2,4
< port=3306
< datadir=/var/lib/mysql1
< socket=/var/lib/mysql1/mysql.sock
---
> port=3307
> datadir=/var/lib/mysql2
> socket=/var/lib/mysql2/mysql.sock
16,17c16,17
< log-error=/var/lib/mysql1/mysqld.log
< pid-file=/var/run/mysqld/mysqld1.pid
---
> log-error=/var/lib/mysql2/mysqld.log
> pid-file=/var/run/mysqld/mysqld2.pid

>diff /etc/init.d/mysqld1 /etc/init.d/mysqld2
9,10c9,10
< # config: /etc/my1.cnf
< # pidfile: /var/run/mysqld/mysqld1.pid
---
> # config: /etc/my2.cnf
> # pidfile: /var/run/mysqld/mysqld2.pid
19c19
< prog="MySQL1"
---
> prog="MySQL2"
34c34
< get_mysql_option mysqld datadir "/var/lib/mysql1"
---
> get_mysql_option mysqld datadir "/var/lib/mysql2"
38c38
< get_mysql_option mysqld_safe log-error "/var/lib/mysql1/mysqld.log"
---
> get_mysql_option mysqld_safe log-error "/var/lib/mysql2/mysqld.log"
40c40
< get_mysql_option mysqld_safe pid-file "/var/run/mysqld/mysqld1.pid"
---
> get_mysql_option mysqld_safe pid-file "/var/run/mysqld/mysqld2.pid"
61c61
< /usr/bin/mysqld_safe --defaults-file="/etc/my1.cnf" --datadir="$datadir" --socket="$socketfile" \
---
> /usr/bin/mysqld_safe --defaults-file="/etc/my2.cnf" --datadir="$datadir" --socket="$socketfile" \
86c86
< [ $ret -eq 0 ] && touch /var/lock/subsys/mysqld1
---
> [ $ret -eq 0 ] && touch /var/lock/subsys/mysqld2
107c107
< rm -f /var/lock/subsys/mysqld1
---
> rm -f /var/lock/subsys/mysqld2
127c127
< [ -e /var/lock/subsys/mysqld1 ] && restart || :
---
> [ -e /var/lock/subsys/mysqld2 ] && restart || :

Attachment contains my /etc/my?.cnf, /etc/init.d/mysqld* and screenshot of what I got during startup: cnf_and_rc.zip

If you want I can upload VMware image of the system I trying to set up.

[atreyu]

Is there anything in the /var/log/mysql* logs? Or in /var/log/messages?

Maybe try moving the starting of your additional MySQL from /etc/init.d/ to /etc/rc.d/rc.local. That would isolate the problem a little bit.

Just use chkconfig to disable the extra mysql initscripts and in /etc/rc.d/rc.local, put something like:

/etc/init.d/mysqld1 start

[fmyoen]

Thanks for trying to help. I solved my problem already. Second MySQL instance was blocked by SElinux.