How to create multiple mysql instance in CentOS 6.4 and Red Hat 6.4
from:http://sharadchhetri.com/2013/12/02/create-multiple-mysql-instance-centos-6-4-red-hat-6-4/
December 2, 2013 by sharad chhetri 6 Comments
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.
vi /etc/my2.cnf
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
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 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 --port 3337 //这里原来是-p 3337,正确是--port 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