Installing MySQL 5.5 Part 2 – Multiple instances with mysqld_multi

Continuing our  previous post, today we’ll go up several instances of MySQL on the same server, very common practice today, instead of creating a single instance in100% of the resources of the server splits the load of QPS (queries per seconds) between multiple instances.
The groups will look for mysqld_multi [mysqldN] within our my.cnf configuration file, where N is an integer that will be used later to refer to what server we’ll see the STOP or START REPORT

        1. We have the following structure on our server:
          | _3306 /
          | _data /
          | _logs /
        2. Let’s create the structure of the 3307 instance and set her rights:
          mkdir 3307
          mkdir 3307/data
          mkdir 3307/logs
          chown-R mysql: mysql / mysql/3307
        3. We will now create the default structure of the script contained in the database foldermysql
          cd / mysql / mysql / scripts /
          . / mysql_install_db - user = mysql - basedir = / mysql / mysql - datadir = /mysql/3307/data /
        4. Recalling that we must be careful to get the OK’s 2 below:
          Installing MySQL system tables ...
          Filling help tables …
        5. That done, we go to our configuration file, the my.cnf, today it looks like this:
          datadir = / mysql/3306/data
          socket = / mysql/3306/mysql.sock
          user = mysql
          log-error = / mysql/3306/logs/mysqld.log
          pid-file = / mysql/3306/

          Now let’s add a new group, called [mysqld_multi] it will put three items:
          mysqld – path to our binary mysql, I suggest here, put the path to mysqld_safe
          mysqladmin – path to the mysqladmin binary, it is utilized to stop the instances
          And we will also add the groups [mysqldN] that are similar to the old [mysqld] wherethe data will be specific to each instance, in our case, we will climb the [mysqld1]and [mysqld2] getting so then our my.cnf:
          mysqld = / mysql / mysql / bin / mysqld_safe
          mysqladmin = / mysql / mysql / bin / mysqladmin
          port = 3306
          datadir = / mysql/3306/data
          socket = / mysql/3306/mysql.sock
          log-error = / mysql/3306/logs/mysqld.log
          pid-file = / mysql/3306/
          port = 3307
          datadir = / mysql/3307/data
          socket = / mysql/3307/mysql.sock
          log-error = / mysql/3307/logs/mysqld.log
          pid-file = / mysql/3307/

          6. Now then, let the party interests, start our instances, we only have to enter the directory where the binaries are, call the mysqld_multi pass the configuration file and the desired option in this case START:
          cd / mysql / mysql
          mysqld_multi - defaults-file = / mysql / my_rep.cnf start

          Okay, if everything went right we should get the message below:
          110511 07:57:10 mysqld_safe Logging to '/mysql/3306/logs/mysqld.log'.
          110511 07:57:10 mysqld_safe Starting mysqld daemon with databases from /mysql/3306/data
          110511 07:57:10 mysqld_safe Logging to '/mysql/3307/logs/mysqld.log'.
          110511 07:57:10 mysqld_safe Starting mysqld daemon with databases from /mysql/3307/data

          Recalling that the mysqld_multi have two more options, one is the report that weenter mysqld_multi - defaults-file = / mysql / my_rep.cnf report and he will tell uswhich groups are running:
          MySQL server from group: mysqld1 is running
          MySQL server from group: mysqld2 is running
          And the other is the STOP option, remembering that it uses mysqladmin have to goafter the configuration file the user name and password that has access to shutdown with the - user and - password in this case, I’m with you root without a password, then the command looks like this:
          mysqld_multi - defaults-file = / mysql / my_rep.cnf - user = root stop
          We’ll have to get the message saying that the PID was waxed FILE:
          110511 08:02:17 mysqld_safe mysqld from pid file / mysql/3306/ ended
          110511 08:02:17 mysqld_safe mysqld from pid file / mysql/3307/ ended

          7.Remembering that we can specify which group we are trying to make the manager, for example, if I want to go only to the specified instance in [mysqld2] command would look like this:
          mysqld_multi - defaults-file = / mysql / 2e my_rep.cnf start
          That’s it folks, more information can be acquired in the MySQL documentation, any questions or suggestions please comment!




Liked? Help and Share!
This entry was posted in install, MySQL, mysqld_multi. Bookmark the permalink.
  • JediPinguim

    Parabéns pelo artigo e pelo blog.

  • JediPinguim


    Só não entendi pq você utilizou o /mysql/my_rep.cnf invés do /mysql/my.cnf

    Mesmo assim, parabéns.

    • marceloaltmann

      Opa, quanto ao arquivo de configuracao nao tem nada em especial referente ao nome, tu pode utilizar o nome que quiser, basta apontar o arquivo certo no parametro –defaults-file, caso tu crie teu arquivo .cnf em /mysql/my.cnf quando der o start no mysqld_multi tu tem que passar ele no defaults file –defaults-file=/mysql/my.cnf


  • J.


    I using Rhel 6.2 and mysql version 5.1.x now i m going to configure mysql multiple instances with mysqld_multi. but i want that first instance i can able to login through remote but second instance how to connect from remote. and mysqld_multi should start automatically at the booting time. please suggest to mail id also.

    • marceloaltmann

      In order to remotely connect to the second instance, you need to make sure you are passing the right port parameter on mysql command line tool (-P or –port).

      to start mysqld_multi at boot time you can copy the mysqld_multi.server file located at BASE_DIR/support-files to /etc/init.d and then run chkconfig mysqld_multi.server on.

  • Keith Gibson

    Can you tell me if it’s possible to have multiple instances of MySQL 5.5 SPECIFICALLY, WITH EACH ADDITIONAL INSTANCE HAVING A UNIQUE ROOT PASSWORD?
    If so, do you have a blog that shows the “cmd line” for setting the root password for each additional instance?