Friday 28 October 2011

mysqld_multi is now officially my 'white whale'

mysqld_multi is now officially my 'white whale'

I am setting up multiple instances of mysql 5.5 server on a single host and trying to manage the instances using the mysqld_multi tool. Ia m able to get each instance to run without issue when I start each manually via nthe following command.

Code:

mysqld_safe --datadir=/var/lib/mysql --pid-file=/var/lib/mysql/$HOSTNAME.pid --socket=/var/lib/mysql/mysql.sock --port=3306 &
 
mysqld_safe --datadir=/usr/local/mysql/var2 --pid-file=/usr/local/mysql/var2/$HOSTNAME.pid2 --socket=/tmp/mysql.sock2 --port=3308 &

mysqld_safe --datadir=/usr/local/mysql/var3 --pid-file=/usr/local/mysql/var2/$HOSTNAME.pid3 --socket=/tmp/mysql.sock3 --port=3309 &

Code:

netstat -ntpl | grep mysql
tcp        0      0 0.0.0.0:3306                0.0.0.0:*                  LIS
tcp        0      0 0.0.0.0:3308                0.0.0.0:*                  LIS
tcp        0      0 0.0.0.0:3309                0.0.0.0:*                  LIS

I have also verified seperate instances are running using ps -eaf, but will spare you the output.


When I try to run them with the mysqld_multi I get:

Code:

mysqld_multi start 1-3 --verbose
WARNING: mysqld_safe is being used to start mysqld. In this case you may need to pass
"ledir=..." under groups [mysqldN] to mysqld_safe in order to find the actual mysqld binary.
ledir (library executable directory) should be the path to the wanted mysqld binary.

/etc/my.cnf
Code:

[mysqld_multi]
mysqld        =/usr/bin/mysqld_safe
mysqladmin    =/usr/bin/mysqladmin

[mysqld1]
port            = 3306
socket          = /var/lib/mysql/mysql.sock
skip-external-locking
key_buffer_size = 16K
max_allowed_packet = 1M
table_open_cache = 4
sort_buffer_size = 64K
read_buffer_size = 256K
read_rnd_buffer_size = 256K
net_buffer_length = 2K
thread_stack = 128K


[mysql2]
socket          =/tmp/mysql.sock2
port            =3308
pid-file        =/usr/local/mysql/var2/$HOSTNAME.pid2
datadir        =/usr/local/mysql/var2

[mysql3]
socket          =/tmp/mysql.sock3
port            =3309
pid-file        =/usr/local/mysql/var3/$HOSTNAME.pid3
datadir        =/usr/local/mysql/var3

any tips to help trouble shoot this or configuration pointers would be appreciated

1 comment:

  1. The two special lines of the configurations requires more explanation.

    Under the [mysqld] section -
    The language option tells this version of mysql the location of its errmsg.sys file. If we did not do this, the correct file will not be loaded, and the server will complain about "the incorrect number of lines" (a pretty unhelpful and misleading error message).

    Under the [mysql_safe] section -
    The option ledir is used by the mysqld_safe script to "find" mysqld-bin. If we did not do this, the mysqld_safe script will try to start the mysql server using the binaries precompiled internal "default" location: /usr/sbin/mysqld.

    From
    http://blogs.component.hu/index.php/attila/2010/04/20/mysql_multi_version_instances

    ReplyDelete