Configuring Multiple Instances of MySQL in Solaris 11

This article is an update to some older notes I had written on configuring multiple MySQL instances in Solaris 10. However, I've rewritten the entire procedure to cater for Solaris 11.

This article assumes that you have Solaris 11 already installed on your server, or as a virtual instance in virtualbox. I do not discuss the how to install the Solaris OE

Once you have your Solaris 11 Operating System up and running and have internet connectivity to gain access to the Image Packaging System (IPS), use the following steps to install MySQL and configure multiple instances:

  1. Install the MySQL package
    # pkg install mysql-51
  2. Verify if the mysql is installed:
    # svcs mysql
    STATE          STIME    FMRI
    online         May_07   svc:/application/database/mysql:version_51
  3. Prepare data file system for MySQL Instance #1
    # zfs create rpool/mysql
    # zfs create rpool/mysql/inst1
    # zfs set mountpoint=/mysql/inst1 rpool/mysql/inst1
  4. Prepare data file system for MySQL Instance #2
    # zfs create rpool/mysql/inst2
    # zfs set mountpoint=/mysql/inst2 rpool/mysql/inst2
  5. Change the mysql/datadir of the MySQL Service (SMF) to point to /mysql/inst1
    # svcprop mysql:version_51 | grep mysql/data
    # svccfg -s mysql:version_51 setprop mysql/data=/mysql/inst1
  6. Create a new instance of MySQL 5.1
    1. Copy the manifest of the default instance to temporary directory:
      # cp /lib/svc/manifest/application/database/mysql_51.xml /var/tmp/mysql_51_2.xml
    2. Make appropriate modifications on the XML file
      # vi /var/tmp/mysql_51_2.xml
      1. Change the “instance name” section to a new value “version_51_2
      2. Change the value of property name “data” to point to the ZFS file system “/mysql/inst2"
  7. Import the manifest to the SMF repository:
    # svccfg import /var/tmp/mysql_51_2.xml
  8. Before starting the service, copy the file /etc/mysql/my.cnf to the data directories /mysql/inst1 & /mysql/inst2.
    # cp /etc/mysql/my.cnf /mysql/inst1/
    # cp /etc/mysql/my.cnf /mysql/inst2/
  9. Make modifications to the my.cnf in each of the data directories as required:
    • Instance #1
      # vi /mysql/inst1/my.cnf
      • Under the [client] section, change the port and socket names
        port=3306
        socket=/tmp/mysql1.sock
      • Under the [mysqld] section, change the port, socket to match the entries in the [client] section. Change the datadir to match your datadir. Also change the server_id to be a unique instance number (ie 1):
        port=3306
        socket=/tmp/mysql1.sock
        datadir=/mysql/inst1
           :
           :
        server-id=1
    • Instance #2
      # vi /mysql/inst2/my.cnf
      • Under the [client] section, change the port and socket names
        port=3307
        socket=/tmp/mysql2.sock
      • Under the [mysqld] section, change the port, socket, datadir. Also change the server_id to be a unique instance number (ie 2):
        port=3307
        socket=/tmp/mysql2.sock
        datadir=/mysql/inst2
           :
           :
        server-id=2
  10. Make appropriate modification to the startup script of MySQL (managed by SMF) to point to the appropriate my.cnf for each instance:
    # vi /lib/svc/method/mysql_51
    Note: Search for all occurances of mysqld_safe command and modify it to include the —defaults-file option. An example entry would look as follows:
    ${MySQLBIN}/mysqld_safe —defaults-file=${MYSQLDATA}/my.cnf —user=mysql —datadir=${MYSQLDATA} —pid=file=${PIDFILE}
  11. Start the service:
    # svcadm enable mysql:version_51_2
    # svcadm enable mysql:version_51
  12. Verify that the two services are running by using:
    # svcs mysql
  13. Verify the processes:
    # ps -ef | grep mysqld
  14. Connect to each mysqld instance and verify:
    # mysql —defaults-file=/mysql/inst1/my.cnf -u root -p
    # mysql —defaults-file=/mysql/inst2/my.cnf -u root -p