Friday, March 20, 2015

Setting up multiple MySQL Instances in the same machine

  1. Download the MySQL server tarball
cd /mnt/softwares/

  1. Unzip and install into local system and create Master-Slave Configuration
gunzip mysql-5.6.23-linux-glibc2.5-x86_64.tar.gz
tar -xf mysql-5.6.23-linux-glibc2.5-x86_64.tar
groupadd mysql
useradd -r -g mysql mysql
ln -s mysql-5.6.23-linux-glibc2.5-x86_64 mysql
cd /mnt/softwares/mysql
chown -R mysql .
chgrp -R mysql .
scripts/mysql_install_db –user=mysql
chown -R root .
chown -R mysql data

vi /etc/my.cnf
 
#ADD BELOW LINES TO my.cnf >>>
 [mysqld]
 server-id=1
 bind-address=127.0.0.1
 character-set-server= utf8
 log-bin=mysql-bin
 log-slave-updates=1
 binlog-format=row
 gtid-mode=on
 enforce-gtid-consistency=1
 innodb-file-per-table=1
 replicate-ignore-db=mysql
 datadir=/var/lib/mysql3306
 socket=/var/lib/mysql/mysql3306.sock
 port=3306
 explicit_defaults_for_timestamp=1

 sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

 [mysqld_safe]
 log-error=/var/log/mysqld3306.log

 [client]
 default-character-set= utf8


scripts/mysql_install_db –user=mysql –dataDir=/var/lib/mysql3306
vi /etc/my2.cnf
 
#ADD BELOW LINES TO my2.cnf >>>
 [mysqld]
 server-id=2
 bind-address=127.0.0.1
 character-set-server= utf8
 log-bin=mysql-bin
 log-slave-updates=1
 binlog-format=row
 gtid-mode=on
 enforce-gtid-consistency=1
 innodb-file-per-table=1
 replicate-ignore-db=mysql
 datadir=/var/lib/mysql3307
 socket=/var/lib/mysql/mysql3307.sock
 port=3306
 explicit_defaults_for_timestamp=1

 sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

 [mysqld_safe]
 log-error=/var/log/mysqld3307.log

 [client]
 default-character-set= utf8
 

scripts/mysql_install_db –user=mysql –dataDir=/var/lib/mysql3307

  1. Start both MySQL instances
bin/mysqld_safe --defaults-file=/etc/my.cnf &
bin/mysqld_safe --defaults-file=/etc/my2.cnf &

And you are done.

No comments :