Category: Sun/Oracle

Recently someone asked me for steps to configure multiple instances of MySQL database in an Operating Platform. Coz of my familiarity with Solaris OE, I prepared some notes on configuring multiple instances of MySQL database on Solaris 11. Maybe it’s useful for some:

If you want to run Solaris Operating System (or any other OS of your choice) as a virtualized instance in desktop, consider using Virtual Box.
To download Solaris Operating System, click here.

Once you have your Solaris Operating System (Version 11) up and running and have Internet connectivity to gain access to the Image Packaging System (IPS), please follow the steps as mentioned below to install MySQL and configure multiple instances:

1. Install MySQL Database in Solaris 11
$ sudo pkg install mysql-51
2. Verify if the mysql is installed:
$ svcs -a | grep mysql
Note: Service FMRI will look similar to the one here: svc:/application/database/mysql:version_51
3. Prepare data file system for MySQL Instance 1
zfs create rpool/mysql
zfs create rpool/mysql/data
zfs set mountpoint=/mysql/data rpool/mysql/data
4. Prepare data file system for MySQL Instance 2
zfs create rpool/mysql/data2
zfs set mountpoint=/mysql/data rpool/mysql/data2
5. Change the mysql/datadir of the MySQL Service (SMF) to point to /mysql/data
$ svcprop mysql:version_51 | grep mysql/data
$ svccfg -s mysql:version_51 setprop mysql/data=/mysql/data
6. Create a new instance of MySQL 5.1
(a) Copy the manifest of the default instance to temporary directory:
$ sudo cp /lib/svc/manifest/application/database/mysql_51.xml /var/tmp/mysql_51_2.xml
(b) Make appropriate modifications on the XML file
$ sudo vi /var/tmp/mysql_51_2.xml
-> Change the “instance name” section to a new value “version_51_2”
-> Change the value of property name “data” to point to the ZFS file system “/mysql/data2”
7. Import the manifest to the SMF repository:
$ sudo 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/data & /mysql/data2.
$ sudo cp /etc/mysql/my.cnf /mysql/data/
$ sudo cp /etc/mysql/my.cnf /mysql/data2/
9. Make modifications to the my.cnf in each of the data directories as required:
$ sudo vi /mysql/data/my.cnf
Under the [client] section
port=3306
socket=/tmp/mysql.sock
—-
—-
Under the [mysqld] section
port=3306
socket=/tmp/mysql.sock
datadir=/mysql/data
—–
—–
server-id=1
$ sudo vi /mysql/data2/my.cnf
Under the [client] section
port=3307
socket=/tmp/mysql2.sock
—–
—–
Under the [mysqld] section
port=3307
socket=/tmp/mysql2.sock
datadir=/mysql/data2
—–
—–
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:
$ sudo vi /lib/svc/method/mysql_51
Note: Search for all occurences 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:
$ sudo svcadm enable mysql:version_51_2
$ sudo 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/data/my.cnf -u root -p
$ mysql –defaults-file=/mysql/data2/my.cnf -u root -p

Some references for Solaris 11 newbies
Taking your first steps with Solaris 11
Introducing the basics of Image Packaging System
Service Management Facility How To Guide

For a detailed list of official educational modules available on Solaris 11, please visit here
For MySQL courses from Oracle University access this page.