Mysql – In MySQL Multiple instance, default instance is running, second instance is not

MySQLmysqld-multi

I was made a setup to create multiple instances on the ubuntu machine. When I start mysql instances mysqld1 is running but mysqld2 is not running.

root@ubuntu:/var/lib# mysqld_multi report
Reporting MySQL servers
MySQL server from group: mysqld1 is running
MySQL server from group: mysqld2 is not running


Below is my my.cnf file :

[client]
port            = 3306
socket          = /var/run/mysqld/mysqld.sock

[mysqld_safe]
socket          = /var/run/mysqld/mysqld.sock
nice            = 0

[mysqld]
basedir         = /usr
tmpdir          = /tmp
skip-external-locking
bind-address            = 127.0.0.1
key_buffer              = 16M
max_allowed_packet      = 16M
thread_stack            = 192K
thread_cache_size       = 8
myisam-recover         = BACKUP
query_cache_limit       = 1M
query_cache_size        = 16M
expire_logs_days        = 10
max_binlog_size         = 100M

[mysqld_multi]
mysqld     = /usr/bin/mysqld_safe
mysqladmin = /usr/bin/mysqladmin
log        = /var/log/mysqld_multi.log
user       = multi_admin
password   = admin123

[mysqld1]
port       = 3306
datadir    = /var/lib/mysql
pid-file   = /var/lib/mysql/mysqld.pid
socket     = /var/lib/mysql/mysql.sock
user       = mysql
log-error  = /var/log/mysql1.err
[mysqld2]
port       = 3307
datadir    = /var/lib/mysql-databases/mysqld2
pid-file   = /var/lib/mysql-databases/mysqld2/mysql.pid
socket     = /var/lib/mysql-databases/mysqld2/mysql.sock
user       = mysql
log-error  = /var/log/mysql2.err

[mysqldump]
quick
quote-names
max_allowed_packet      = 16M

[mysql]

[isamchk]
key_buffer              = 16M

!includedir /etc/mysql/conf.d/

When I check my error log file mysql2.err, the error was like,

130120 18:41:59 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql-databases/mysqld2
130120 18:41:59 [Warning] Can't create test file /var/lib/mysql-databases/mysqld2/ubuntu.lower-test
130120 18:41:59 [Warning] Can't create test file /var/lib/mysql-databases/mysqld2/ubuntu.lower-test
130120 18:41:59 [Note] Plugin 'FEDERATED' is disabled.
/usr/sbin/mysqld: Can't find file: './mysql/plugin.frm' (errno: 13)
130120 18:41:59 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it.
130120 18:41:59  InnoDB: Initializing buffer pool, size = 8.0M
130120 18:41:59  InnoDB: Completed initialization of buffer pool
130120 18:41:59  InnoDB: Operating system error number 13 in a file operation.
InnoDB: The error means mysqld does not have the access rights to
InnoDB: the directory.
InnoDB: File name ./ibdata1
InnoDB: File operation call: 'create'.
InnoDB: Cannot continue operation.
130120 18:41:59 mysqld_safe mysqld from pid file /var/lib/mysql-databases/mysqld2/mysql.pid ended

Below is my apparmor file.

#include <tunables/global>

/usr/sbin/mysqld {
capability dac_override,
capability sys_resource,
capability setgid,
capability setuid,
network tcp,
/etc/hosts.allow r,
/etc/hosts.deny r,
/etc/mysql/*.pem r,
/etc/mysql/conf.d/ r,
/etc/mysql/conf.d/* r,
/etc/mysql/*.cnf r,
/usr/lib/mysql/plugin/ r,
/usr/lib/mysql/plugin/*.so* mr,
/usr/sbin/mysqld mr,
/usr/share/mysql/** r,
/var/log/mysql.log rw,
/var/log/mysql.err rw,
/var/lib/mysql/ r,
/var/lib/mysql/** rwk,
/var/log/mysql/ r,
/var/log/mysql/* rw,
/{,var/}run/mysqld/mysqld.pid w,
/{,var/}run/mysqld/mysqld.sock w,
/sys/devices/system/cpu/ r,
}

My second instance data directory path is : /var/lib/mysql-databases/mysqld2/mysql
Please let me know how to fix this issue.
Thanks in advance.

Best Answer

I looked and saw this

/usr/sbin/mysqld: Can't find file: './mysql/plugin.frm' (errno: 13)

That's a file permission issue.

Please go to Linux and run

cd /var/lib/mysql-databases/
chown -R mysql:mysql mysqld2

Now, you should be able to restart mysql for port 3307