Minimal MySQL master-slave replication
Today i was a bit interested in some mysql configurations when i stumbled across the mysql replication docs on MySQL . The docs seem to be interesting , so instantly i decided to give it a try . I went through the docs entirely and at the end found myself overly confused about the minimum changes required .After a couple of hours of my head storming with the configurations i found the minimum changes required for the replication .Here are the steps to do a minimal master slave replication for a MySQL server .
The mysql installation used for this article is mysql no install binary installation .The detailed steps for the replication are as follows :
1.Download the binary distribution for the mysql .
2.Extract the downloaded mysql binary zip files twice to two folders namely the “master” and “slave”.
3.Make a copy of “my-huge.ini” in the two folders and rename it as “my.ini”.
4.Make the configurations to the “my.ini” files in the master and slave folder as follows :
Section required in master my.ini
port=5000
log-error = c:/newmylog/mysql.err
log-bin = c:/newmylog/mysql-bin
Section required in slave my.ini
#log-bin=mysql-bin
server-id = 2
log-error = c:/newmylog/mysql.err
Execute the following code on the master server :
mysql -uroot
mysql>grant replication slave on *.* to slaveuser@'localhost' identified by 'secret';
mysql>\q
mysqldump -u root --all-databases --single-transaction --master-data=1 > masterdump.sql
Execute the following code on the slave server :
mysql -uroot < masterdump.sql
mysql -uroot
mysql> CHANGE MASTER TO MASTER_HOST='localhost',MASTER_PORT=5000, MASTER_USER='slaveuser', MASTER_PASSWORD='secret';
mysql>start slave;
mysql>show slave status\G
mysql>\q
After the last command on the slave for the status we get an output as shown below :
slave status command output
With no error displayed on the slave status you can see that any changes made on the master server databases are reflected on the slave immediately .
