Thursday, February 23, 2012

Master Slave, Master Master Replication

I just struggled 3 days for doing this. Just writing it down in case I forget it soon.
It will be helpful for other people also, who will get a good head start into this field.

These are the steps for creating Master Slave replication......

1. Make data base of Master and Slave identical by running the dump of Master on Slave.
2. Master configurations in my.ini
server-id = 1
auto_increment_offset=1
# total number of master servers
auto_increment_increment=2
# local slave replication options
log-bin=master1-bin
log-slave-updates
3. Slave Configuration in my.ini
server-id = 2
auto_increment_offset=2
# total number of master servers
auto_increment_increment=2
# local slave replication options
log-bin=master2-bin
log-slave-updates
5. Restart both severs.
6. Create a new user on Master and Grant him replication permissions.
CREATE USER 'mike'@'masterhostname' IDENTIFIED BY 'pass';
GRANT REPLICATION SLAVE ON *.* TO 'user1'@'%' IDENTIFIED BY 'pass';
7. Run this command on Master
show master status \G;
It will show something like this...
*************************** 1. row ***************************
File: master1-bin.000001
Position: 1739
Binlog_Do_DB:
Binlog_Ignore_DB:
Note down File name and Position of log file...
8. Run this query on Slave
CHANGE MASTER TO MASTER_HOST='masterhostname', MASTER_USER='user1', MASTER_PASSWORD='pass',
MASTER_PORT=3307, MASTER_LOG_FILE='master1-bin.000001', MASTER_LOG
_POS=1739; [remember to change port number accordingly]

9. Run this query on Slave
start slave;
10. Now run any query on Master and you will see that change on Slave.
11. Make sure network connections is stablished and all required ports are open between two
server.
12. Repeat the above process if you want to add more slaves.
13. Same process can be used for creating Master-Master replication also. You will have to run
CHANGE MASTER query, with right parameters on Master which is slave to another Master.

Installing Multiple Mysql on same Machine

It took me lot of time figuring out how to install multiple mysql instances on same machine.
So I thought of sharing Process for setting up multiple Mysql on same machine will be useful for somebody looking for such manual...

1. Install Mysql server on that machine
2. Create a folder called xyz anywhere.
3. Create 2-3 folders...say data1, data2 etc inside xyz folder...they will hold the data for respective tables of respective installation.
4. Create 3 files my1.ini, my2.ini, my3.ini inside xyz folder.
5. Find out my.ini of your original Mysql server installation. And copy the content into all 3 ini files.
6. Modify following configurations in all 3 ini files according to your convenience. Here is example..
[client]
# password = your_password
port = 3307
enable-named-pipe
socket = "C:/xampp/mysql/mypipe1"
[mysqld]
port= 3307
socket = "C:/xampp/mysql/mypipe1"
datadir="C:/mysql1/data1"
pid_file="mysql.pid1"
log_error="mysql_error.log1"
log-bin=master1-bin
server-id = 1
innodb_data_home_dir = "C:/mysql1/data1"
innodb_log_group_home_dir = "C:/mysql1/data1"

Each ini file will contain respective unique entry for port, datadir, socket, server-id etc..
7. Copy mySqlInstallationFolder/mysql/data/mysql folder into all 3 folders data1, data2 and data3.
8. Go to comand prompt and run these commands
Mysqlpath\mysql\bin\mysqld --install mysqld1 --defaults-file=yourPath\xyz\my1.ini [ for Windows...u can find equivalent Linux command for installing a new service ]
Mysqlpath\mysql\bin\mysqld --install mysqld2 --defaults-file=yourPath\xyz\my2.ini
Mysqlpath\mysql\bin\mysqld --install mysqld3 --defaults-file=yourPath\xyz\my3.ini

9. Start your respective service ie mysqld1,mysqld2,mysqld3 [these are all different instances of mysql].
10. Login to individual mysql server using following command
mysql -u root -h localhost -P 3307 [change your port name]
mysql -u root -h localhost -P 3307 -p [ if you have password for root ]