Create unique directories for each instance
mkdir c:\insta
mkdir c:\insta\data
mkdir c:\insta\logs
mkdir c:\instb
mkdir c:\instb\data
mkdir c:\instb\logs
mkdir c:\instc
mkdir c:\instc\data
mkdir c:\instc\logs
Now, create “my.ini” or “my.cnf” files for each instance
create a txt file “c:\insta\mya.ini”
[mysqld]
port=3311
server-id=1
basedir=”C:/Program Files/MySQL/MySQL Server 5.5/”
datadir=c:/insta/data
log-bin=c:/insta/logs/insta
skip-grant-tables
create a txt file “c:\instb\myb.ini”
[mysqld]
port=3322
server-id=2
basedir=”C:/Program Files/MySQL/MySQL Server 5.5/”
datadir=c:/instb/data
####log-bin=c:/insta/logs/instb
skip-grant-tables
create a txt file “c:\instc\myc.ini”
[mysqld]
port=3333
server-id=3
basedir=”C:/Program Files/MySQL/MySQL Server 5.5/”
datadir=c:/instc/data
#####log-bin=c:/insta/logs/instc
skip-grant-tables
Start instances
open 3 command prompts
in each prompt, type
“mysqld –defaults-file=c:\insta\mya.ini”
“mysqld –defaults-file=c:\instb\myb.ini”
“mysqld –defaults-file=c:\instc\myc.ini”
Do NOT close the prompts… this is a “Windows” daemon.. =)
Create tables for “mysql” database by running scripts provided by mysql
mysql –port=3311
mysql -P 3311
C:\Program Files\MySQL\MySQL Server 5.5\share
create database mysql;
use mysql
source c:\mysql_system_tables.sql
source c:\mysql_system_tables_data.sql
source c:\fill_help_tables.sql
Repeat for all 3 instances
Shutdown all 3 instances cleanly
mysqladmin shutdown -P 3311
mysqladmin shutdown -P 3322
mysqladmin shutdown -P 3333
Remove “skip-grant-tables” from all 3 ini files
edit c:\insta\mya.ini
edit c:\insta\myb.ini
edit c:\insta\myc.ini
Start up all 3 daemons again, this time relying on security
“mysqld –defaults-file=c:\insta\mya.ini”
“mysqld –defaults-file=c:\instb\myb.ini”
“mysqld –defaults-file=c:\instc\myc.ini”
Configure root password on 3 instances
mysqladmin -P 3311 -u root password toor
mysqladmin -P 3322 -u root password toor
mysqladmin -P 3333 -u root password toor
Drop any users not required (optional, for “security”, necessary to ensure proper user is found)
Hint: select user, host from mysql.user; drop user ‘user’@’host’;
We now have 3 standalone instances created from scratch, with 3 separate databases with no connection/relationship.
mysql> drop user ”@’localhost’;
Query OK, 0 rows affected (0.00 sec)
mysql> drop user ”@’student-4-192′;
Query OK, 0 rows affected (0.00 sec)
I recommend creating 3 command prompts each connected to one of the 3 instances:
mysql -u root -p -P 3311
Put some data into master (insta)
insta> create database world;
insta> use world
insta> source c:\world.sql
Determine what log position we are at (called coordinates):
insta> show master status;
take note of the filename and the “position” #
Notice that this is exactly the same # as the # of bytes in the log file at present
Copy insta to instb
mysqldump –all-databases –master-data -u root -p -P 3311 > c:\insta.sql
mysql -u root -p -P 3322 < c:\insta.sql
Now, instb looks like insta as of the point in time we took the dump
Create a user on Master for slave to use
insta> create user repman@’%’ identified by ’reppass’;
insta> grant replication slave on *.* to repman@’%';
Configure Slave to point to Master to copy binlogs and start playing (help change master to)
instb> CHANGE MASTER TO MASTER_HOST=’localhost’,
MASTER_USER=’repman’, MASTER_PASSWORD=’reppass’,
MASTER_PORT=3311,
MASTER_LOG_FILE=’??? replace with coordinates’,
MASTER_LOG_POS=???;
instb> SHOW SLAVE STATUS;
instb> START SLAVE;
To configure A -> B -> C
Add to myb.ini
# Now Instance B can be a master too
log-bin=c:/insta/logs/instb
# This option will cause SQL thread of B as a slave, to also
# write all replicated statements to our own bin logs.
log-slave-updates
# disable the execution of our own statements, based on “server-id”
# DEFAULT anyhow… this variable is NOT necessary for the safe
# default
replicate-same-server-id = off
From A -> B, B -> A (Multi Master)
We do not need any special parameters technically…
A LocalExec -> A Bin
A Bin -> B Relay
B Relay -> B SQL Execution
(if log-slave-updates is on, then B SQL Exec -> B Bin)
B LocalExec -> B Bin
B Bin -> A Relay
A Relay -> A SQL Execution