Page 2 of 2 FirstFirst 12
Results 11 to 20 of 20

Thread: MySQL multiple instances in Ubuntu Server

  1. #11
    Join Date
    Feb 2007
    Beans
    Hidden!

    Re: MySQL multiple instances in Ubuntu Server

    Hi all,

    I have followed this tutorial step-by-step and I am sure I haven't missed any step but I still cannot get it to work.

    I am sure I am having problems with pid and socket files and the datadir.

    When I set the datadir to the original folder /var/lib/mysql and use the original pid and socket file, the instance starts correctly but if I change to what the tutorial suggests (/var/lib/mysql1 - /var/run/mysqld/mysqld1.pid and /var/run/mysqld/mysqld1.sock), nothing works.

    One doubt, might be stupid but, do I need to install mysql twice, one for each pair of pid and socket?

    I have only installed mysql once using apt-get and then tried the tutorial.

    Do you guys have any idea what I am doing wrong?

    Any help is very much appreciated.

  2. #12
    Join Date
    Dec 2008
    Beans
    2

    Re: MySQL multiple instances in Ubuntu Server

    I got bit by apparmor as well.

    I modified the file:

    /etc/apparmor.d/usr.sbin.mysqld to contain:

    /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,

    And then restarted apparmor (/etc/init.d/apparmor restart), and now I can start databases with mysqld_multi etc.

    Thanks for the howto.

    /Erland

  3. #13
    Join Date
    Dec 2008
    Beans
    2

    Exclamation Re: MySQL multiple instances in Ubuntu Server

    Also, having the myql database root password in the publicly readable file /usr/bin/mysqld_multi seems like a bad idea, so I did chmod o-rx /usr/bin/mysqld_multi.

  4. #14
    Join Date
    May 2009
    Beans
    10

    Re: MySQL multiple instances in Ubuntu Server

    You shouldn't be running mysql as root. If someone managed a database exploit, they'd have root permissions. Take the time to figure out where permissions are going wrong.

  5. #15
    Join Date
    Dec 2009
    Location
    Saskatchewan, Canada
    Beans
    1
    Distro
    Ubuntu 9.10 Karmic Koala

    Re: MySQL multiple instances in Ubuntu Server

    This tutorial was great, since I need to run two mysql servers on my dev server (one for each virtual host), but I didn't want to run mysql as root, or disable apparmor.

    [edit] About the third time through the mysqld_multi man, I realized that the start of the example my.conf file had these lines for a [mysqld_multi] block, so I changed them to fit my environment:
    Code:
    [mysqld_multi]
    mysqld = /usr/bin/mysqd_safe
    mysqladmin = /usr/bin/mysqladmin
    user = root
    Note that the example code has user "multi_admin" and password "multipass", and requires user "multi_pass" to have an account for each MySQL server with shutdown priveleges; therefore, I just used user root in the mysqld_mutil block since the servers themselves run as mysql and not root. This "fixes" the problem of not being able to recreate the /var/run/mysqld directory on startup, without having to run the servers as root. Which, as correctly pointed out above, is a bad idea.[/edit]

    These were the other changes:

    I did not edit /usr/bin/mysqld_multi

    As mentioned earlier, I changed these four lines in /etc/apparmor.d/usr.sbin.mysql to:
    Code:
    /var/lib/mysql?/ r,
    /var/lib/mysql?/** rwk,
    /var/run/mysqld/mysqld?.pid w,
    /var/run/mysqld/mysqld?.sock w,
    And finally, my /etc/mysql/my.cnf file is:
    Code:
    [mysqld1]
    user      = mysql
    pid-file  = /tmp/mysqld1.pid
    socket    = /var/run/mysqld/mysqld1.sock
    port      = 3301
    datadir   = /var/lib/mysql1
    server-id = 1
    (only the pertinent lines shown above, and code repeated for each server).

    I haven't done extensive testing, but as far as I can tell, everything is working correctly. i.e. after a reboot I can login to both servers, and mysql doesn't have to run as root.
    Last edited by mcksmith; December 23rd, 2009 at 02:59 AM. Reason: Posted better solution to mysqld directory issue

  6. #16
    Join Date
    Nov 2008
    Beans
    33

    Red face Re: MySQL multiple instances in Ubuntu Server

    Great write-up!

    It works perfectly but I have a problem, it won't start on boot.

    I'm using Ubuntu 12 64bit, and apparmor was uninstalled before starting the procedure so I don't think that's the problem.

    I don't see anything in the system log or mysql log.

    After boot, I can start it just fine using

    sudo /etc/init.d/mysql start

    Any ideas? I really need this to work so I can replace my Windows server with this!!

    **EDIT: I just tried with 10.04 and I get the same behavior.
    Last edited by JoeSabido; December 24th, 2012 at 01:36 AM. Reason: Add info

  7. #17
    Join Date
    Nov 2008
    Beans
    33

    Re: MySQL multiple instances in Ubuntu Server

    I made it run on startup by editing the file

    /etc/init.d/rc.local

    and adding the line "/etc/init.d/mysql start"

    It works now, the instances start on boot, but is this the solution? Or is it not the right thing to do?

  8. #18
    Join Date
    Apr 2008
    Location
    Kansas City
    Beans
    509

    Re: MySQL multiple instances in Ubuntu Server

    When I run /etc/init.d/mysql start

    I can see the processes running:
    root 5979 1 1 11:00 pts/0 00:00:00 /bin/sh /usr/bin/mysqld_safe --user=root --pid-file=/tmp/mysqld1.pid --socket=/var/run/mysqld/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

    root 5986 1 1 11:00 pts/0 00:00:00 /bin/sh /usr/bin/mysqld_safe --user=root --pid-file=/tmp/mysqld2.pid --socket=/var/run/mysqld/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
    But then a few seconds later they disappear and I don't seee anything in the log about it stopping or any errors.

  9. #19
    Join Date
    Jul 2011
    Beans
    1

    Re: MySQL multiple instances in Ubuntu Server

    On my ubuntu(Ubuntu 10.04.4 LTS):

    /var/run/mysqld1.pid
    /var/run/mysqld1.sock are wrong.

    Also the user should be mysql and not root.
    Here is the config I use:

    Code:
    [mysqld1]
    user = mysql
    pid-file = /var/run/mysqld/mysqld1.pid
    socket = /var/run/mysqld/mysqld1.sock
    port = 3306
    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
    log_error = /var/log/mysql/error1.log
    expire_logs_days = 10
    max_binlog_size = 100M
    
    [mysqld2]
    user = mysql
    pid-file = /var/run/mysqld/mysqld2.pid
    socket = /var/run/mysqld/mysqld2.sock
    port = 3307
    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
    log_error = /var/log/mysql/error2.log
    expire_logs_days = 10
    max_binlog_size = 100M

  10. #20
    Join Date
    Apr 2011
    Beans
    2

    Disable or suspend apparmor to let mysqld_multi start properly

    Using kubuntu 13.04 I was not able to get apparmor to allow the socket and pid files to be written anywhere, I've set permissions on /tmp, /var, /var/run etc.. to no effect. Nor was I able to instruct apparmor (as explained in this thread) to allow this. I tried all the tips in all the relevant posts here to no effect. Perhaps the apparmor syntax changed??? Upgraded??? Who knows... Too much reading to do in too short a time.

    Soultion A:

    My first solution was to disable apparmor using these commands (under root or with sudo):

    Code:
    ## run as root or use sudo
    ## stop apparmor
    /etc/init.d/apparmor stop
    /etc/init.d/apparmor teardown
    
    ## start mysqld_multi
    mysqld_multi start
    
    ##verify mysql instances up and running
    ps aux | grep mysql | grep -v grep
    This worked but the init.d script would not start so I had to run this manually after each reboot or login

    Soultion B:

    With this approach you can run mysqld using your user 'root', or the better 'mysql' user. It's not an issue anymore since we will disable apparmor to stop protecting locations on the file system for the usr.sbin.mysqld profile. There is a package called apparmor-utils you will need to install, and then use the aa-complain command to make apparmor 'complain' instead of blocking the usr.sbin.mysqld profile

    Code:
    ## run as root or use sudo
    ## install apparmor-utils
    apt-get install apparmor-utils
    Now you can instruct apparmor to complain about the usr.sbin.mysqld profile

    Code:
    ## run as root or use sudo
    aa-complain /usr/sbin/mysqld
    Once this is done apparmor will echo the status of the profiles and you will be able to see the usr.sbin.mysqld profile is in complain mode:

    Code:
    ## run as root or use sudo
    /etc/init.d/apparmor status
    This will show something like:

    Code:
    apparmor module is loaded.
    6 profiles are loaded.
    5 profiles are in enforce mode.
       /sbin/dhclient
       /usr/lib/NetworkManager/nm-dhcp-client.action
       /usr/lib/connman/scripts/dhclient-script
       /usr/lib/cups/backend/cups-pdf
       /usr/sbin/cupsd
    1 profiles are in complain mode.
       /usr/sbin/mysqld
    4 processes have profiles defined.
    2 processes are in enforce mode.
       /sbin/dhclient (2043) 
       /usr/sbin/cupsd (1113)
    Next, you'll want to let init.d run the mysql script in /etc/init.d on startup. This is done with:

    Code:
    ## run as root or use sudo
    update-rc.d mysql defaults
    You can now reboot and have your instances up and running properly.

    The /etc/init.d/mysql script calls mysqld_multi and looks like this

    Code:
    #!/bin/sh
    #
    # A simple startup script for mysqld_multi by Tim Smith and Jani Tolonen.
    # This script assumes that my.cnf file exists either in /etc/my.cnf or
    # /root/.my.cnf and has groups [mysqld_multi] and [mysqldN]. See the
    # mysqld_multi documentation for detailed instructions.
    #
    # This script can be used as /etc/init.d/mysql.server
    #
    # Comments to support chkconfig on RedHat Linux
    # chkconfig: 2345 64 36
    # description: A very fast and reliable SQL database engine.
    #
    # Version 1.0
    #
    
    basedir=/usr/
    bindir=/usr/bin
    
    if test -x $bindir/mysqld_multi
    then
      mysqld_multi="$bindir/mysqld_multi";
    else
      echo "Can't execute $bindir/mysqld_multi from dir $basedir";
      exit;
    fi
    
    case "$1" in
        'start' )
            "$mysqld_multi" start $2
            ;;
        'stop' )
            "$mysqld_multi" stop $2
            ;;
        'report' )
            "$mysqld_multi" report $2
            ;;
        'restart' )
            "$mysqld_multi" stop $2
            "$mysqld_multi" start $2
            ;;
        *)
            echo "Usage: $0 {start|stop|report|restart}" >&2
            ;;
    esac
    Good luck!
    Last edited by tnsilver; June 26th, 2013 at 07:08 PM.

Page 2 of 2 FirstFirst 12

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
  •