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:
- Install the MySQL package
# pkg install mysql-51
- Verify if the mysql is installed:
# svcs mysql STATE STIME FMRI online May_07 svc:/application/database/mysql:version_51
- 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
- Prepare data file system for MySQL Instance #2
# zfs create rpool/mysql/inst2 # zfs set mountpoint=/mysql/inst2 rpool/mysql/inst2
- 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
- Create a new instance of MySQL 5.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
- Make appropriate modifications on the XML file
# 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/inst2
"
- Change the “instance name” section to a new value “
- Copy the manifest of the default instance to temporary directory:
- Import the manifest to the SMF repository:
# svccfg import /var/tmp/mysql_51_2.xml
- 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/
- 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
- Under the [client] section, change the port and socket names
- 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
- Under the [client] section, change the port and socket names
- Instance #1
- 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}
- Start the service:
# svcadm enable mysql:version_51_2 # svcadm enable mysql:version_51
- Verify that the two services are running by using:
# svcs mysql
- Verify the processes:
# ps -ef | grep mysqld
- 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