<

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x

Quick Multi MySQL Server Installation with Master-Master Replication on Same Windows Box

Published on
13,171 Points
6,371 Views
3 Endorsements
Last Modified:
Approved
Community Pick
This article is a brief step-by-step tutorial on the subject task which is divided into the following main parts:
Installing two MySQL Server Instances
Setting up master-slave replication
Setting up slave-master replication
Let's begin covering those points...

Installing Two MySQL Server Instances on Windows:

1. Install MySQL:
 
Download MySQL Binaries from dev.mysql.com.
Install normal MSI Package of mysql. (I used: mysql-essential-5.0.83-win32).
* Following MSI Installation is quite easy, proceed and finish the installation.
This server will be installed on default 3306 port with all windows default settings.


2. Register/Install another MySQL instance:

To have two servers we make sure to have separate values of port, datadir etc. Please check the configuration file for according changes.

Create a duplicate of /etc/my.cnf as /etc/my2.cnf and make according changes.
 
mysqld-nt.exe --install "MySQL1" --defaults-file="C:\Program Files\MySQL\MySQL Server 5.0\my2.ini"

Open in new window


This will register the mysql1 service, and thus we'll end up having two mysql instances on windows.

3. Start Both MySQL Servers:
 
net start mysql
net start mysql1

Open in new window


4. Check connecting client from command prompt:
 
mysql -uroot -p --port=3306
mysql -uroot -p --port=3307

Open in new window

* It will prompt for password, on success you will end up logging in both mysql servers.

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.

For replication I have used database named 'master' having table named 'test' with following structure:
 
Create database master;
Use master;
CREATE TABLE `temp` (
  `id` int(10) NOT NULL auto_increment,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

Open in new window


Here we've two MySQL server instances, namely mysql and mysql1.

We will setup Master-Slave Replication from mysql1 to mysql. Later we will setup Master-Slave Replication from mysql to mysql1.
For your reference please check the appendix section for sample configuration files along with below mentioned instructions.


1. Setting Master Slave "mysql1 --> mysql":

Considering:

mysql1 @ port: 3307 as Master
mysql @ port: 3306 as Slave


- On mysql1(master): Add following line to my.ini:
 
binlog-do-db=master

Open in new window


- We require a 'Replication Slave' privileged user on Master for slave to connect:
 
mysql> Grant replication slave on *.* to 'replication1'@'localhost' identified by 'slave1';

Open in new window


On mysql(slave):
- Add following line to my.ini:

 
	#Conn. Pmtr for slave:
	master-host = localhost
	master-user = replication1
	master-password = slave1
	master-port = 3307

Open in new window


*It's not a good idea to add such parameters in configuration file; but this is just learning purpose. Use CHANGE MASTER& command.

- Start Slave Server:
 
net start mysql

Open in new window


- On Slave Client:
mysql>Start Slave;
** Check for working Master-Slave Replication.**
** Stop both MySQL Servers **



2. Setting Slave Master "mysql1 <-- mysql" :


Considering:

mysql1 @ port: 3307 as Slave
mysql @ port: 3306 as Master


- On mysql(master) Add following line to my.cnf:
 
binlog-do-db=master

Open in new window


- Start Master Server:
 
net start mysql1

Open in new window


- We require a 'Replication Slave' privileged user:
 
mysql> Grant replication slave on *.* to 'replication2'@'localhost' identified by 'slave2'; 

Open in new window



- On mysql1 (slave):
Add following line to my.cnf:

 
	#Conn. Pmtr for slave:
	master-host = localhost
	master-user = replication2
	master-password = slave2
	master-port = 3306

Open in new window

- Start Slave Server:
 
net start mysql

Open in new window


- On Slave client:
 
mysql>Start Slave;

Open in new window

** Check for working Master-Slave **
** Check for working Master-Master **



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.


Appendix: Configuration Files for master-master mysql setup:

Following is the my.ini [configuration] file for first MySQL Server Instance:
 
##my.ini for mysql (Regularly installed):
[client]
port=3306
[mysqld]
server-id=2
port=3306

# 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 = replication1
master-password = slave1
master-port = 3307

basedir="C:/Program Files/MySQL/MySQL Server 5.0/"
datadir="C:/Program Files/MySQL/MySQL Server 5.0/Data/"

auto_increment_increment=1
auto_increment_offset=1

Open in new window

Following is the my2.ini [configuration] file for second MySQL instance named "mysql1":
 
##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

Open in new window


References:
http://dev.mysql.com/doc/refman/5.0/en/replication-howto.html
http://dev.mysql.com/doc/refman/5.0/en/multiple-windows-servers.html
http://dev.mysql.com/doc/refman/5.0/en/change-master-to.html
http://dev.mysql.com/doc/refman/5.0/en/replication-options-master.html#sysvar_auto_increment_increment
3
Comment
Author:theGhost_k8
0 Comments

Featured Post

Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Join & Write a Comment

In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month