Menu Close

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!