In this tutorial we will learn about how to create multiple mysql instance in CentOS 6.4 and Red Hat 6.4. Multiple instance means in single system we can create multiple MySQL server. These multiple mysql server will run in different ports.In this practical we expect you already have MySQL server installed in your system.
To create multiple MySQL instance,follow the given below steps
Login as a root in a system
Step (1): Create a new MySQL data directory.
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 bydefault data directory path
chmod --reference /var/lib/mysql /var/lib/mysql2 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
cp -p /etc/my.cnf /etc/my2.cnf
Now edit with vi editor or your favorite editor.
Edit in [mysqld] and [mysqld_safe] section as given below in my2.cnf file
[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
mysql_install_db --user=mysql --datadir=/var/lib/mysql2
Below given is the output from my server (as a Reference)
[root@localhost etc]# 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@localhost etc]#
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
mysqld_safe --defaults-file=/etc/my2.cnf &
Now check the port no. 3337 is listening or not
netstat -tanp |grep 3337
To connect new mysql instance
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 upto 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
Note: By using same method you can create multiple instance ,only you have to change the values like mysql2 to mysql3 or so on
please how can i do for set start the new instance of mysql on boot?
mysql_install_db –user=mysql –datadir=/var/lib/mysql2
2017-03-17 10:48:30 [WARNING] mysql_install_db is deprecated. Please consider switching to mysqld –initialize
2017-03-17 10:48:30 [ERROR] The data directory ‘/var/lib/mysql2’ already exist and is not empty.
se ven says
MySQL 5.7.6 or later deprecates the mysql_install_db command and introduces a mysqld –initialize command as a replacement, the data directory would be initialized automatically and no need for running mysql_install_db in install step, so ignore this warning in your case.
Clear the directory you create recently to do this operation is required.
Sharad Chhetri says
Thanks Se Ven!
Nice tutorial 🙂
small precision >> mysql -u root -p -h 127.0.0.1 -P 3337
-P = port
There is a better approach using mysqld_multi.
As described on the Percona blog.
Nice blog , Keep it up Chhetri
Ankit Agrawal says
Thank you friend , it was really some good work!
sharad chhetri says
Naveen Kumar Gupta says
Do you have any article like how to install multiple MySQL instances on rhel7/centos7?
I hope the method should be same but I have not chekcked. Only you have to install MySQL 5.6 on CentOS 7 or RHEL 7 . https://sharadchhetri.com/2014/07/31/how-to-install-mysql-server-5-6-on-centos-7-rhel-7/