How to create and run multiple instances of MySQL on the same machine
8:55 PM
There are various methods to run multiple instances of mysql (on different ports)
on the same machine. One simple way is to use the same binary and use a
separate configuration file (with separate port, pid, socket and data
directory) as illustrated below:
Next, we need to edit our new configuration file and at least update the mysql port (default to 3306), the pid and socket to be different than the default ones, and also point the data and log folders to the ones created before
Edit 1: Add the following line after line 138 for the init script to handle ports
Edit 2: At line 215 update my.cnf to point to the new config file my2.cnf for this instance
Edit 3: At line 257 add -c flag to the arguments to read this config file while parsing server arguments
Edit 4: Add port argument to mysql_safe command on line 284
Finally we can start our new mysql instance with:
Create separate data directory
mkdir /var/lib/mysql2 chown -R mysql.mysql /var/lib/mysql2/
Create a new mysql configuration file
Next we need a separate configuration file. We can start by copying the existing one and changing the needed valuescp /etc/my.cnf /etc/my2.cnf
cd /etc sed -i 's/3306/3307/g' my2.cnf sed -i 's/mysqld.sock/mysqld2.sock/g' my2.cnf sed -i 's/mysqld.pid/mysqld2.pid/g' my2.cnf sed -i 's/var\/lib\/mysql/var\/lib\/mysql2/g' my.cnf
Create/copy existing mysql init file to start/stop/reload etc on this new instance
cp /etc/init.d/mysql /etc/init.d/mysql2 vi /etc/init.d/mysql2
--port=*) port=`echo "$arg" | sed -e 's/^[^=]*=//'` ;;
conf=/etc/my2.cnf
parse_server_arguments `$print_defaults $extra_args mysqld server mysql_server mysql.server -c/etc/my2.cnf`
$bindir/mysqld_safe --defaults-file=/etc/my2.cnf --datadir="$datadir" --pid-file="$mysqld_pid_file_path" --port="$port" --socket="$datadir"/mysql2.sock $other_args >/dev/null 2>&1 &
Initializing and starting
To initialize the default dbs:mysql_install_db --datadir=/var/lib/mysql2 --defaults-file=/etc/my2.cnf --user=mysql
service mysql2 start
Set password for this instance and connect to this new instance
/usr/bin/mysqladmin -u root -h127.0.0.1 -P3307 password 'somepassword'
Connecting and working with new instance
We can connect to our new instance using:mysql -u username -p -S /var/lib/mysql2/mysqld2.sock or mysql -u username -p -h 127.0.0.1 -P 3307
Add second instance service to server start-up list
chkconfig --add mysql2 chkconfig mysql2 on
0 comments