Tested by the author with mysql 5.1.30 and 5.1.34 in Ubuntu 8.10 and 9.04. Anyway, no guaranties of any kind: worked for me, that's all.
There are 2 cases where multiple instances of the same version of mysql in a server are necessary, or desirable:
You truly need A LOT of simultaneous connections for read-only and just one for write on the same database: in this case, you must know very well what you are doing, and be capable of develop applications that read from a read-only instance and write into a write-only instance. There can be variations, but the principle is the same: in a given time interval or moment, just one database engine instance can have write permission over a given information (database, table, field, etc). It's very like an one-machine-cluster. Sounds complex AND IT IS ! So, can't be described in just any howto.
The second cause is very trivial: you want or need to have multiple development versions of the same database(s) in the same machine and with the same name, and maybe (not recommended but still possible) a production version too. THIS is the scope of this howto.
First, install mysql: you will found lots of explanations about in this forum and in the Internet. Just Google 'ubuntu mysql install' or 'ubuntu mysql 5.1 install' and be flooded by explanations for every known taste, and more
BEFORE START: Read twice or more times, and just start doing after understand the entire sequence, and then do it, checking every step. I tried hard to make this howto very linear and as simple as possible, but nothing (made by men) is foolproof. As this text isn't. Any constructive comment will be welcome.
Well, assuming that you have mysql installed, let's start.
As near all steps need root privileges, let's simplify things, working in a root privileged bash instance:
# sudo bash
(enter the password when asked for)
Now, let's find some files we will need:
(the following command line is just for ensure that locate will give a proper response )
# updatedb
(let's find the scripts we need)
# locate mysqld_multi
/usr/bin/mysqld_multi
/usr/share/man/man1/mysqld_multi.1.gz
/usr/share/mysql/mysqld_multi.server
#
Bingo ! The first one is a Perl script that 'does the job'; the second is a man page (try 'man mysqld_multi' ), and the third is a script to start/stop/etc the mysqld instances.
Now, let's prepare the automatic running of the mysqld instances:
(the following command line is just for the sake of reversibility )
# mv /etc/init.d/mysql /etc/init.d/mysql_mono.server
And then:
# cp /usr/share/mysql/mysqld_multi.server /etc/init.d/mysql
With your favourite text editor, edit /etc/init.d/mysql, then find the lines:
basedir=/usr/local/mysql
bindir=/usr/local/mysql/bin
and change to:
basedir=/usr
bindir=/usr/bin
If your have a root password in localhost, edit the file /usr/bin/mysqld_multi, then find the line:
$opt_password = undef()
and change to:
$opt_password = "password"
changing "password" by the 'root'@'localhost' password. (thank's, ecentinela ).
Now create a database directory for each additional mysqld instance:
# cp -pr /var/lib/mysql /var/lib/mysql1
# cp -pr /var/lib/mysql /var/lib/mysql2
And so on... (yep, the /var/lib/mysql folder and it's contents stay untouched, for the sake of reversibility or to help in make more instances or 'reset' a existent one).
Now we are going to explain to the mysqld_multi perl script about the mysqld instances that we want to have:
(the following command line is just for the sake of reversibility
# cp -p /etc/mysql/my.cnf /etc/my.cnf.mono
Next, with your favourite text editor, edit /etc/mysql/my.cnf to have [mysqld1], [mysqld2], etc. sessions instead of the [mysqld] one. The [mysqld#] (where # is the instance number) lines that need to be changed, in order to tell to the mysqld instances about directories, files, sockets and tcp ports to use, are:
user = root
pid-file = /var/run/mysqld#.pid
socket = /var/run/mysqld#.sock
port = 330#
datadir = /var/lib/mysql#
log = /var/log/mysql/mysql#.log
log_bin = /var/log/mysql/mysql#-bin.log
server-id = #
Notes: 1) server-id isn't necessary, but sometimes is nice to know in what instance the application, procedure or trigger is going over just doing a 'SELECT @@server_id', and
2) the user = root line is to assure that the mysql daemons will have enough rights to create the socket, pid and log files.
Just to give an example, my /etc/mysql/my.cnf look like:
...
nice = 0
[mysqld1]
user = root
pid-file = /var/run/mysqld1.pid
socket = /var/run/mysqld1.sock
port = 3301
basedir = /usr
datadir = /var/lib/mysql1
tmpdir = /tmp
language = /usr/share/mysql/english
old_passwords = 1
key_buffer = 16M
max_allowed_packet = 16M
thread_stack = 128K
thread_cache_size = 8
myisam-recover = BACKUP
query_cache_limit = 1M
query_cache_size = 16M
log = /var/log/mysql/mysql1.log
server-id = 1
log_bin = /var/log/mysql/mysql1-bin.log
expire_logs_days = 10
max_binlog_size = 100M
[mysqld2]
user = root
pid-file = /var/run/mysqld2.pid
socket = /var/run/mysqld2.sock
port = 3302
basedir = /usr
datadir = /var/lib/mysql2
tmpdir = /tmp
language = /usr/share/mysql/english
old_passwords = 1
key_buffer = 16M
max_allowed_packet = 16M
thread_stack = 128K
thread_cache_size = 8
myisam-recover = BACKUP
query_cache_limit = 1M
query_cache_size = 16M
log = /var/log/mysql/mysql2.log
server-id = 2
log_bin = /var/log/mysql/mysql2-bin.log
expire_logs_days = 10
max_binlog_size = 100M
[mysqldump]
...
Note: texts in light gray are just for show that can be another parameters, and not to be copied: use your own parameters or those found in the my.cnf that is installed by default, as this file can change (and usually changes) for each mysql version: any not yet (or no more) recognized parameter can and do stop a mysql daemon at its start.
Now, let's start the instances:
# /etc/init.d/mysql start
And then, if you did all right, the instances will silently run. Just for peace of mind, if you do:
# ps aux | grep mysql | grep -v grep
will appear a big entry for each instance. In my case, it is:
# ps aux | grep mysql | grep -v grep
mysql 8395 0.0 2.0 158832 20768 pts/1 Sl 12:41 0:00 /usr/sbin/mysqld --no-defaults --user=mysql --pid-file=/var/run/mysqld1.pid --socket=/var/run/mysqld1.sock --port=3301 --basedir=/usr --datadir=/var/lib/mysql1 --tmpdir=/tmp --language=/usr/share/mysql/english --old_passwords=1 --key_buffer=16M --max_allowed_packet=16M --thread_stack=128K --thread_cache_size=8 --myisam-recover=BACKUP --query_cache_limit=1M --query_cache_size=16M --log=/var/log/mysql/mysql1.log --server-id=1 ...
mysql 8400 0.0 2.0 224368 20768 pts/1 Sl 12:41 0:00 /usr/sbin/mysqld --no-defaults --user=mysql --pid-file=/var/run/mysqld2.pid --socket=/var/run/mysqld2.sock --port=3302 --basedir=/usr --datadir=/var/lib/mysql2 --tmpdir=/tmp --language=/usr/share/mysql/english --old_passwords=1 --key_buffer=16M --max_allowed_packet=16M --thread_stack=128K --thread_cache_size=8 --myisam-recover=BACKUP --query_cache_limit=1M
--query_cache_size=16M --log=/var/log/mysql/mysql2.log --server-id=2 ..
#
From now, to use the mysql console you need to add the socket in the command line:
# mysql --socket=/var/run/mysqld#.sock
(where # is the mysqld instance number)
...quite lengthy, huh ? Well, let's simplify this.
With your favourite text editor, edit (create) /usr/sbin/mysql1, and put the following lines on it:
#!/bin/bash
mysql --socket=/var/run/mysqld1.sock $1 $2 $3 $4 $5 $6 $7 $8
Now, make the /usr/sbin/mysql1 file executable:
# chmod a+x /usr/sbin/mysql1
Now, to invoke a console for the first mysqld instance, just type:
# mysql1
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.1.34-0.dotdeb.1-log (Debian)
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> select @@server_id;
+-------------+
| @@server_id |
+-------------+
| 1 |
+-------------+
1 row in set (0.00 sec)
mysql> (ctr+D) Aborted
Now, make copies for each of the other mysqld instances:
# cp /usr/sbin/mysql1 /usr/sbin/mysql#
And edit each /usr/sbin/mysql# to ajust to the right socket(s):
#!/bin/bash
mysql --socket=/var/run/mysqld#.sock $1 $2 $3 $4 $5 $6 $7 $8
And so on, for each additional mysqld instance you may have. Btw, the $1 thru $8 are parameters that you may use, like -uroot, -p'mysecret', mydatabase and so, just the same as for the mysql console.
Finally, to stop the root privileged bash:
# ctrl+D
And now, you (finally) have your mysqld multiple instances ready for use.
Technical explanations about, and examples to start/stop selected mysqld instances can be found at:
http://dev.mysql.com/doc/refman/5.1/...qld-multi.html
Now, have fun ! Or work, or whatever...
Mauro M. Ferreira
IPVoxVoIP
Bookmarks