Page 1 of 2 12 LastLast
Results 1 to 10 of 20

Thread: MySQL multiple instances in Ubuntu Server

  1. #1
    Join Date
    May 2009
    Location
    Brazil, Campo Grande / MS
    Beans
    4
    Distro
    Ubuntu 9.04 Jaunty Jackalope

    Thumbs up MySQL multiple instances in Ubuntu Server

    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
    Last edited by mauroferreira; July 1st, 2009 at 02:07 PM. Reason: A correction and a quick-fix

  2. #2
    Join Date
    Apr 2006
    Location
    Seattle
    Beans
    2,893
    Distro
    Ubuntu Development Release

    Re: MySQL multiple instances in Ubuntu Server

    Approved; thank you for your Tutorials & Tips contribution!

  3. #3
    Join Date
    Apr 2006
    Beans
    9

    Re: MySQL multiple instances in Ubuntu Server

    Thanks for the tutorial, it works... but after a reboot the mysql server is not starting anymore.

    Someone can help me with this?

    Thanks.

  4. #4
    Join Date
    Apr 2006
    Beans
    9

    Re: MySQL multiple instances in Ubuntu Server

    After some testing I have seen that the problem is the that the folder /var/run/mysqld is removed every time I reboot the system.
    After the reboot, I can create the folder, give it permissions and start mysql normally.

    Someone knows why this is occurring?

    The second problem that I have now, is that I can't stop mysql server (only rebooting the system). I'm logged as root, but in the mysql log file I get this when I type (as root) "/etc/init.d/mysql stop"

    090618 8:22:29 1 Connect Access denied for user 'root'@'localhost' (using password: NO)

    Please, I need this urgently.

    Thank you

  5. #5
    Join Date
    May 2009
    Location
    Brazil, Campo Grande / MS
    Beans
    4
    Distro
    Ubuntu 9.04 Jaunty Jackalope

    Re: MySQL multiple instances in Ubuntu Server

    About the 'root'@'localhost' user with password:

    Edit the file /usr/bin/mysqld_multi, and change the line:

    $opt_password = undef();

    To:

    $opt_password = "password";

    Changing "password" by the user 'root'@'localhost' password.

    HowTo changed to reflect this. Thanks for your valuable contribution !

    About the /var/run/mysqld directory being deleted after the first time mysqld stops:

    This isn't done by /usr/bin/mysqld_multi; probably a mysql or mysqladmin bug, i'll go after it's sources to search about.

  6. #6
    Join Date
    Apr 2006
    Beans
    9

    Re: MySQL multiple instances in Ubuntu Server

    Thanks!

    Another way to "solve" the folder remove bug is to change the path for the pid and socket from "/var/run/mysqld/" to "/var/run/" and change the mysql user on my.cnf from "mysql" to "root".

    Is the best way I have found at this time to don't need to create the folder after every reboot.

    Hope it helps!

  7. #7
    Join Date
    May 2009
    Location
    Brazil, Campo Grande / MS
    Beans
    4
    Distro
    Ubuntu 9.04 Jaunty Jackalope

    Re: MySQL multiple instances in Ubuntu Server

    Nice one ! I'll change the HowTo to reflect this. Thanks again, ecentinela !

  8. #8
    Join Date
    Feb 2008
    Location
    Colorado
    Beans
    5
    Distro
    Ubuntu 7.10 Gutsy Gibbon

    Re: MySQL multiple instances in Ubuntu Server

    Hello,

    I have recently found a need to run two MySQL instances on one box. I had just performed a clean install of Ubuntu 9.04 and chose the LAMP option during the install just to speed up the process.

    After finding your tutorial, I have found that I cannot, no matter what I have tried, get it to work.

    The problem is, it starts the two servers, but then immediately shuts them down, without logging any errors in any of the log files.

    I am, at this time, going to try to clean install the OS again, then instead of choosing the LAMP stack during the Ubuntu install, I plan on install MySQL first, then Apache, then PHP from the Ubuntu packages.

    Have you ever seen why this config you wrote up would start the servers, then shut them down immediately all without any logged errors and all with out any errors to the console?

    Thanks for the tutorial - am still trying and hope to shed light in case someone else is experiencing this same error.

    Oh, the only difference I have noticed is I tried it with MySQL 5.1.31 rather 5.1.34 because that is what Ubuntu repositories said was the most recent version during the apt-get install mysql-server-5.1.

  9. #9
    Join Date
    May 2009
    Location
    Brazil, Campo Grande / MS
    Beans
    4
    Distro
    Ubuntu 9.04 Jaunty Jackalope

    Re: MySQL multiple instances in Ubuntu Server

    About MySQL version, this is not a problem, er - not a direct one: the HowTo texts in 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.

    If this don't fix the problem, check the "user=" lines in /etc/mysql/my.cnf; all must be "user=root", else the mysql daemons will not be able to create the socket files, and then will silently shutdown.

    Please return information about what you found and / or discovered: this is valuable to improve this HowTo.
    Last edited by mauroferreira; July 1st, 2009 at 03:02 AM.

  10. #10
    Join Date
    Jul 2009
    Beans
    1

    Re: MySQL multiple instances in Ubuntu Server

    Thanks!

    However I had some problems with Apparmor refusing access to new data directories and sockets. This caused init-script to fail silently.

    Allowing these directories and socket creation from apparmor configs fixed the problem though. Also "user=mysql" now also works as expected.

Page 1 of 2 12 LastLast

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •