At times you need to install multiple MySQL instances on same machine. This article is a brief step-by-step tutorial on the subject task which is divided into the following main parts:<\/p>\n\n\n\n
Let’s begin covering those points…<\/p>\n\n\n\n
Installing Two MySQL Server Instances on Windows: * Following MSI Installation is quite easy, proceed and finish the installation. 2. Register\/Install another MySQL instance:<\/p>\n\n\n\n To have two servers we make sure to have separate values of port, datadir etc. Please check the configuration file for according changes.<\/p>\n\n\n\n Create a duplicate of \/etc\/my.cnf as \/etc\/my2.cnf and make according changes.<\/p>\n\n\n\n This will register the mysql1 service, and thus we’ll end up having two mysql instances on windows.<\/p>\n\n\n\n 3. Start Both MySQL Servers:<\/p>\n\n\n\n 4. Check connecting client from command prompt:<\/p>\n\n\n\n * It will prompt for password, on success you will end up logging in both mysql servers.<\/em><\/p>\n\n\n\n Setting up Master-Master Replication on Windows – Single machine: Setting up Master-Master Replication mainly consists of getting both servers to perform Master and Slave Replication Roles.<\/p>\n\n\n\n For replication I have used database named ‘master’ having table named ‘test’ with following structure:<\/p>\n\n\n\n Here we’ve two MySQL server instances, namely mysql and mysql1.<\/p>\n\n\n\n We will setup Master-Slave Replication from mysql1 to mysql. Later we will setup Master-Slave Replication from mysql to mysql1. 1. Setting Master Slave “mysql1 –> mysql”:<\/span><\/strong> mysql1 @ port: 3307 as Master – On mysql1(master): Add following line to my.ini:<\/p>\n\n\n\n – We require a ‘Replication Slave’ privileged user on Master for slave to connect:<\/p>\n\n\n\n On mysql(slave): #Conn. Pmtr for slave:<\/p>\n\n\n\n *It’s not a good idea to add such parameters in configuration file; but this is just learning purpose. Use CHANGE MASTER& command.<\/p>\n\n\n\n – Start Slave Server:<\/p>\n\n\n\n – On Slave Client:<\/p>\n\n\n\n ** Check for working Master-Slave Replication.** 2. Setting Slave Master “mysql1 <– mysql” :<\/span><\/strong> mysql1 @ port: 3307 as Slave – On mysql(master) Add following line to my.cnf:<\/p>\n\n\n\n – Start Master Server:<\/p>\n\n\n\n – We require a ‘Replication Slave’ privileged user:<\/p>\n\n\n\n – On mysql1 (slave): #Conn. Pmtr for slave:<\/p>\n\n\n\n – Start Slave Server:<\/p>\n\n\n\n – On Slave client:<\/p>\n\n\n\n ** Check for working Master-Slave ** And that’s it. We now have two different instances of MySQL server running with a Master-Master replication scheme. Hopefully, you found this tutorial helpful. Please see examples of the final configuration files in the appendix below along with references for more information regarding this topic.<\/p>\n\n\n\n Appendix: Configuration Files for master-master mysql setup:<\/span><\/strong><\/p>\n\n\n\n Following is the my.ini [configuration] file for first MySQL Server Instance:<\/em><\/p>\n\n\n\n Following is the my2.ini [configuration] file for second MySQL instance named “mysql1”:<\/em><\/p>\n\n\n\n
<\/strong>
1. Install MySQL:<\/p>\n\n\n\n\n
This server will be installed on default 3306 port with all windows default settings.<\/em><\/p>\n\n\n\nmysqld-nt.exe --install \"MySQL1\" --defaults-file=\"C:\\Program Files\\MySQL\\MySQL Server 5.0\\my2.ini\"<\/code><\/pre>\n\n\n\nnet start mysql
net start mysql1<\/code><\/pre>\n\n\n\nmysql -uroot -p --port=3306
mysql -uroot -p --port=3307<\/code><\/pre>\n\n\n\n
<\/strong><\/p>\n\n\n\nCreate database master;
Use master;
CREATE TABLE `temp` (
`id` int(10) NOT NULL auto_increment,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;<\/code><\/pre>\n\n\n\n
For your reference please check the appendix section for sample configuration files along with below mentioned instructions.<\/em><\/p>\n\n\n\n
<\/span><\/strong>
Considering:<\/p>\n\n\n\n
mysql @ port: 3306 as Slave <\/em><\/p>\n\n\n\nbinlog-do-db=master<\/code><\/pre>\n\n\n\nmysql> Grant replication slave on *.* to 'replication1'@'localhost' identified by 'slave1';<\/code><\/pre>\n\n\n\n
– Add following line to my.ini:<\/p>\n\n\n\nmaster-host = localhost\nmaster-user = replication1\nmaster-password = slave1\nmaster-port = 3307<\/code><\/pre>\n\n\n\nnet start mysql<\/code><\/pre>\n\n\n\nmysql>Start Slave;<\/code><\/pre>\n\n\n\n
** Stop both MySQL Servers **<\/em><\/p>\n\n\n\n
<\/span>
<\/strong>
Considering:<\/p>\n\n\n\n
mysql @ port: 3306 as Master <\/em><\/p>\n\n\n\nbinlog-do-db=master<\/code><\/pre>\n\n\n\nnet start mysql1<\/code><\/pre>\n\n\n\nmysql> Grant replication slave on *.* to 'replication2'@'localhost' identified by 'slave2';<\/code><\/pre>\n\n\n\n
Add following line to my.cnf:<\/p>\n\n\n\nmaster-host = localhost
master-user = replication2
master-password = slave2
master-port = 3306<\/code><\/pre>\n\n\n\nnet start mysql<\/code><\/pre>\n\n\n\nmysql>Start Slave;<\/code><\/pre>\n\n\n\n
** Check for working Master-Master **<\/em><\/p>\n\n\n\n##my.ini for mysql (Regularly installed):\n[client]\nport=3306\n[mysqld]\nserver-id=2\nport=3306\n# Replication\nlog-bin=binlog\n\n\n#For this as master:\nbinlog-do-db=master\nbinlog-ignore-db=mysql\nbinlog-ignore-db=test\n\n#Conn. Pmtr for slave:\n\nmaster-host = localhost\nmaster-user = replication1\nmaster-password = slave1\nmaster-port = 3307\nbasedir=\"C:\/Program Files\/MySQL\/MySQL Server 5.0\/\"\ndatadir=\"C:\/Program Files\/MySQL\/MySQL Server 5.0\/Data\/\"\nauto_increment_increment=1\nauto_increment_offset=1<\/code><\/pre>\n\n\n\n##my2.ini for Mysql1:
[client]
port=3307
[mysqld]
server-id=1
port=3307
# Replication
log-bin=binlog
#For this as master:
binlog-do-db=master
binlog-ignore-db=mysql
binlog-ignore-db=test
#Conn. Pmtr for slave:
master-host = localhost
master-user = replication2
master-password = slave2
master-port = 3306
basedir=\"C:\/Program Files\/MySQL\/MySQL Server 5.0\/\"
datadir=\"C:\/Program Files\/MySQL\/MySQL Server 5.0\/Data1\/\"
auto_increment_increment=1
auto_increment_offset=2<\/code><\/pre>\n\n\n\n