Multiple mysql Instance creation in Linux

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/mysql3
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



chmod --reference /var/lib/mysql /var/lib/mysql3
chown --reference /var/lib/mysql /var/lib/mysql3
Step 3: Now create a new my3.cnf file and paste the below given contents. We will run the new mysql instance in port no. 3311.

Copy the my.cnf file and make it blank and add below configuration changes.
cp -p /etc/my.cnf /etc/my2.cnf echo -n "" > /etc/my2.cnf

[mysqld] datadir=/var/lib/mysql3 socket=/var/lib/mysql3/mysql3.sock user=mysql port=3311 server-id=3 slow-query-log=1 # optional slow_query_log_file="Mysql-slow_3311.log" # optional log-bin="binlog" # optional expire_logs_days=7 # optional log-error=/var/log/mysqld3.log pid-file=/var/lib/mysql3/mysqld3.pid max_connections=10000 # optional innodb_buffer_pool_size=500M # optional max_allowed_packet=128M # optional #print dead locks in errror log innodb_print_all_deadlocks=1 # optional lower_case_table_names = 1 sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

Step 4 : change the cnf files

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

mysqld --initialize --user=mysql --datadir=/var/lib/mysql3 >
Step 6:
create a log file 
  touch /var/log/mysqld3.log
chmod --reference /var/log/mysqld.log /var/log/mysqld3.log
chown --reference /var/log/mysqld.log /var/log/mysqld3.log

step 7 : mysqld  --defaults-file=/etc/my3.cnf &

step 8 : grep 'temporary password' /var/log/mysqld.log

step 9 :  mysql -u root -p --socket=/var/lib/mysql3/mysql3.sock

How to start and stop the instance:

Stop:

ps -ef|grep mysql

Kill PID

Warning:Beware to choose mysql pid value 

Start:
mysqld --defaults-file=/etc/my3.cnf --datadir=/var/lib/mysql3/ &