Errors while creating multiple mysql-5.5 instances

Question

I have installed 3rd mysql instance on my testing server.

2 instances already running without any issues.

When I installed 3rd instance by mysql-5.5.30 zip source, it installed successfully but when I tried to restart 3rd instance of mysql it says,

MySQL server PID file could not be found! [FAILED]

Starting MySQL........................................................

The server quit without updating PID file.

1st instance running on 3305

BASEDIR: /usr/local/mysql
Configuration File: /etc/my.cnf
Socket: /tmp/mysql.stock

2nd instance running on 3306

BASEDIR: /backup/mysql-cbt
Configuration File: /backup/mysql-cbt/my.cnf
Socket: /backup/mysql-cbt/mysql.stock

3rd instance running on 3307

BASEDIR: /home/mysql-5/
Configuration File: /home/mysql-5/my.cnf
Socket: /home/mysql-5/mysql.stock

Error Log is as follows.

130513 11:22:23 mysqld_safe Starting mysqld daemon with databases from /backup/mysql-cbt/data<br>
130513 11:22:23 InnoDB: The InnoDB memory heap is disabled<br>
130513 11:22:23 InnoDB: Mutexes and rw_locks use GCC atomic builtins<br>
130513 11:22:23 InnoDB: Compressed tables use zlib 1.2.3<br>
130513 11:22:23 InnoDB: Using Linux native AIO<br>
130513 11:22:23 InnoDB: Initializing buffer pool, size = 128.0M<br>
130513 11:22:23 InnoDB: Completed initialization of buffer pool<br>
130513 11:22:23 InnoDB: highest supported file format is Barracuda.<br>
130513 11:22:23 InnoDB: Waiting for the background threads to start<br>
130513 11:22:24 InnoDB: 5.5.30 started; log sequence number 1595675<br>
130513 11:22:24 [ERROR] /backup/mysql-cbt/bin/mysqld: unknown option '--safe- show- database'<br>
130513 11:22:24 [ERROR] Aborting<br>
130513 11:22:24 InnoDB: Starting shutdown...<br>
130513 11:22:25 InnoDB: Shutdown completed; log sequence number 1595675<br>
130513 11:22:25 [Note] /backup/mysql-cbt/bin/mysqld: Shutdown complete<br>
130513 11:22:25 mysqld_safe mysqld from pid file /backup/mysql-cbt/cbt-instance.pid ended

Still unable to figure out this error. How can I start the 3rd instance?

Installation

Here is the story from beginning. I have installed mysql via source:

1. extracted source mysql-5.5.30-linux2.6-x86_64.tar.gz to directory, then recursively change permission of mysql directory to user & group mysql.
2. in scripts directory of mysql I run this command as root:

[root@server /backup/mysql-cbt/scripts]# ./mysql_install_db --basedir=/backup/mysql-cbt/ --datadir=/backup/mysql-cbt/data --defaults-file=/backup/mysql-cbt/my.cnf --user=mysql**
Installing MySQL system tables...
OK
Filling help tables...
OK
To start mysqld at boot time you have to copy
support-files/mysql.server to the right place for your system
PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !
To do so, start the server, then issue the following commands:
/backup/mysql-cbt//bin/mysqladmin -u root password 'new-password'
/backup/mysql-cbt//bin/mysqladmin -u root -h 69.16.196.128 password 'new-password'
Alternatively you can run:
/backup/mysql-cbt//bin/mysql_secure_installation
which will also give you the option of removing the test
databases and anonymous user created by default. This is
strongly recommended for production servers.
See the manual for more instructions.
You can start the MySQL daemon with:
cd /backup/mysql-cbt/ ; /backup/mysql-cbt//bin/mysqld_safe &
You can test the MySQL daemon with mysql-test-run.pl
cd /backup/mysql-cbt//mysql-test ; perl mysql-test-run.pl
Please report any problems with the /backup/mysql-cbt//scripts/mysqlbug script!

When I restart this instance it gives error of updating pid and exit.

Which step is missing?

My my.cnf file (/backup/mysql-cbt/my.cnf):

 [mysqld]
 federated
 basedir = /backup/mysql-cbt
 datadir = /backup/mysql-cbt/data
 log-error = /backup/mysql-cbt/cbt-instance.err
 pid-file = /backup/mysql-cbt/cbt-instance.pid
 #socket = /tmp/mysql.sock
 socket = /backup/mysql-cbt/mysql.sock
 port = 3310
 user = mysql
 tmpdir = /backup/mysql-cbt/tmpdir

/etc/init.d/mysql_cbt

# If you want to affect other MySQL variables, you should make your changes
# in the /etc/my.cnf, ~/.my.cnf or other MySQL configuration files.
# If you change base dir, you must also change datadir. These may get
# overwritten by settings in the MySQL configuration files.
basedir=/backup/mysql-cbt
datadir=/backup/mysql-cbt/data
# Default value, in seconds, afterwhich the script should timeout waiting
# for server start. 
# Value here is overriden by value in my.cnf. 
# 0 means don't wait at all
# Negative numbers mean to wait indefinitely
service_startup_timeout=900
# Lock directory for RedHat / SuSE.
lockdir='/var/lock/subsys'
lock_file_path="$lockdir/mysql"
# The following variables are only set for letting mysql.server find things.
# Set some defaults
mysqld_pid_file_path=/backup/mysql-cbt/mysql-cbt-instance.pid
if test -z "$basedir"
then
 basedir=/usr/local/mysql-cbt
 bindir=/usr/local/mysql-cbt/bin
 if test -z "$datadir"
 then
 datadir=/backup/mysql-cbt/data
 fi
 sbindir=/backup/mysql-cbt/bin
 libexecdir=/backup/mysql-cbt/bin
else
 bindir="$basedir/bin"
 if test -z "$datadir"
 then
 datadir="$basedir/data"
 fi
 sbindir="$basedir/sbin"
 libexecdir="$basedir/libexec"
fi

Answer 1

Looking in the error log you can see

130513 11:22:24 [ERROR] /backup/mysql-cbt/bin/mysqld: unknown option '--safe- show- database'
130513 11:22:24 [ERROR] Aborting
130513 11:22:24 InnoDB: Starting shutdown...

On documentation for your version of MySQL you can see

This option is deprecated and does not do anything because there is a SHOW DATABASES privilege that can be used to control access to database names on a per-account basis. See Section 6.2.1, "Privileges Provided by MySQL". --safe-show-database was removed in MySQL 5.5.3

How to solve:

Comment out or remove safe-show-database of your my.cnf for your 3rd instance(as it is deprecated on MySQL 5.5 and removed since MySQL 5.5.3) and try to start 3rd instance again.

Using mysql init.d script:

If you install many instances of MySQL and want to control it via init.d script you need to do some changes to the script:

• change the basedir variable
• change the datadir variable
• change any mention of /etc/my.cnf to /backup/mysql-cbt/my.cnf

One good option is to start the server by use the command line (/backup/mysql-cbt/bin/mysqld_safe --defaults-file=/backup/mysql-cbt/my.cnf &), this way you eliminate possible miss configuration on the init.d script side, and make sure you don't have anything wrong in your my.cnf once your server start ok, you know that your my.cnf is right, then start to work to change the script to control it as a service.

• Shoaib: i have not included safe-show-database in my.cnf file but still it is including safe-show-database by it self.
• altmannmarcelo: make sure your are not confusing between the instances (seems like you are starting the mysql 5.5 using your mysql 5.1 file located on /etc/my.cnf), as you say your base-dir is /home/mysql-5/ and your cnf file is located at /home/mysql-5/my.cnf and this file does not have the option safe-show-database the follow command should start up mysql - /home/mysql-5/bin/mysqld_safe --defaults-file=/home/mysql-5/my.cnf
• Shoaib: Still not working
• altmannmarcelo: your info seems a bit odd, your say that your base dir is on /home/mysql-5/ but the error shows that mysqld is on /backup/mysql-cbt/bin/, how are your starting the third instance? if you use any script, can you post that on your question?
• Shoaib: i'm using init.d script /etc/init.dmysql_cbt restart

Answer 2

With MySQL 5.5...

./mysql_install_db --basedir=/backup/mysql-cbt/ --datadir=/backup/mysql-cbt/data --defaults-file=/backup/mysql-cbt/my.cnf --user=mysql

Hmm...

I did this a lot with 5.6 GA (the latest release last year) and found after (a lot) of trail and error, it would work with this.

./scripts/mysql_install_db --defaults-file=/home/pol/Downloads/mysql/src/my.cnf

The reason I was doing it in this convoluted fashion is that I was a student and I wanted to get MySQL working on College machines to which I didn't have root privileges. It was getting what went into the my.cnf that was the tricky part (at least for me) - yours looks pretty much like mine does now for source installs of 5.6.

So far, so easy-peasy.

Then for a reason that I can no longer remember (IIRC, it was to do with answering a question here about MySQL 5.5), I tried to install 5.5. My simple script didn't work! I found that after (again a lot of) trial and error, 5.5 required the different options to be explicitly specified on the command line for the mysql_install_db script. Try to do it as you have done, but add the specs for the pid-file and socket and it worked for me. Maybe it will for you too?