Create Multiple MySQL Instances

How to Create Multiple MySQL Instance

Step 1: Create a new MySQL data directory.

[root@server ~]# mkdir -p /var/lib/mysql2

Step 2: Now give permission and ownership .Here we are taking ownership and permission reference from original /var/lib/mysql data directory.
Note: /var/lib/mysql is by default data directory path

[root@server ~]# chmod --reference /var/lib/mysql /var/lib/mysql2

[root@server ~]# chown --reference /var/lib/mysql /var/lib/mysql2

Step 3: Now create a new my2.cnf file and paste the below given contents. We will run the new mysql instance in port no. 3337.

Copy the my.cnf file and make it blank

[root@server ~]# cp -r /etc/my.cnf /etc/my2.cnf

Now edit with vi editor or your favorite editor.

[root@server ~]# vi /etc/my2.cnf

[mysqld]

datadir=/var/lib/mysql2

socket=/var/lib/mysql/mysql2.sock

port=3337

[mysqld_safe]

log-error=/var/log/mysqld2.log

pid-file=/var/run/mysqld/mysqld2.pid

Step 4: Now install the database in the new mysql data directory

Below given is the output from my server (as a Reference)

[root@server ~]# mysql_install_db --user=mysql --datadir=/var/lib/mysql2

Installing MySQL system tables...

OK

Filling help tables...

OK

To start mysqld at boot time you have to copy

support-files/mysql.server to the right place for your system

PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER!

To do so, start the server, then issue the following commands:

/usr/bin/mysqladmin -u root password 'new-password'

/usr/bin/mysqladmin -u root -h localhost.localdomain password 'new-password'

Alternatively you can run:

/usr/bin/mysql_secure_installation

which will also give you the option of removing the test

databases and anonymous user created by default.  This is

strongly recommended for production servers.

See the manual for more instructions.

You can start the MySQL daemon with:

cd /usr ; /usr/bin/mysqld_safe &

You can test the MySQL daemon with mysql-test-run.pl

cd /usr/mysql-test ; perl mysql-test-run.pl

Please report any problems with the /usr/bin/mysqlbug script!

[root@server ~]#

Step 5: Now the new instance is ready. Now in below section we will learn how to start, stop the mysql instance and how to connect with it.

Note: new mysql instance is running in port no. 3337 as per this tutorial

To start new mysql instance, use below given command

[root@server ~]# mysqld_safe --defaults-file=/etc/my2.cnf &

Now check the port no. 3337 is listening or not

[root@server ~]# netstat -anp |grep 3337

Step 6: Edit the init file and make some minor changes to make it this instance specific [Four edits required]

[root@server ~]# vi /etc/init.d/mysql2

Edit 1: Add the following line after line 138 for the init script to handle ports

--port=*)     port=`echo "$arg" | sed -e 's/^[^=]*=//'` ;;

Edit 2: At line 215 update my.cnf to point to the new config file my2.cnf for this instance

conf=/etc/my2.cnf

Edit 3: At line 257 add -c flag to the arguments to read this config file while parsing server arguments

parse_server_arguments `$print_defaults $extra_args mysqld server mysql_server mysql.server -c/etc/my2.cnf`

Edit 4: Add port argument to mysql_safe command on line 284

$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 &

To connect new mysql instance

Syntax: mysql -u username -p -h 127.0.0.1 -P port-no

As per our tutorial:

mysql -u root -p -h 127.0.0.1 -p 3337

It will ask password, hit enter because root password is blank
Now set root password (It is up to your choice, for security reason it is recommended).You can see mysql prompt.

mysql > use mysql;

mysql > update user set password=PASSWORD("Give-here-new-root-PASSWORD") where User='root';

mysql > flush privileges;

mysql > exit

Recheck if MySQL root password is applied or not

mysql -u root -p -h 127.0.0.1 -p 3337

To stop new mysql instance use below given command

mysqladmin -S /var/lib/mysql/mysql2.sock shutdown -p