MENU


Setting Up

Step 1: Configure the Master
Enable binary logging, and assign a unique server ID to the master. These can be done by editing my.cnf or my.ini(in C:\ProgramData\MySQL\MySQL Server 5.6 for Windows).
[mysqld]
log-bin=mysql-bin
server-id=1
Restart the server after making the changes.

Step 2: Configure the Slaves
Assign a unique server ID to each slave, as in Step 1.
[mysqld]
server-id=2
Restart the server after making the changes.

Step 3: Create a User for Replication
There must be a user account on the master that the slave can use to connect.
CREATE USER 'repl'@'%.mydomain.com'
IDENTIFIED BY 'slavepass';
GRANT REPLICATION SLAVE ON *.*
TO 'repl'@'%.mydomain.com';


Step 4: Determine the Master Binary Log Coordinates
You must know the master’s current coordinates within its binary log, so that the slaves can start processing events from the binary log at the correct point. You may need to temporarily stop processing statements on the master first.

Start a session on the master, flush all tables, and block write statements:

FLUSH TABLES WITH READ LOCK;

In a different session on the master, obtain information about the current binary log file name and position:

SHOW MASTER STATUS;
File Position ……
mysql--bin.000033 14665  

If binary logging has not been previously enabled, you need to use the empty string ‘’ and 4 for the slave’s log file and position respectively.

Step 5: Copy a Data Snapshot
To copy all existing databases on the master to the slaves, run in the command line:
mysqldump --all-databases --master-data > dbdump.db
You may also choose to dump specific databases only. Once the dump is created, copy the dump file to each slave. If the database is large, you may choose to copy the raw data files instead.

On each slave, import the dump file in the command line:
mysql < dbdump.db


Step 6: Start the Replication
On the master, release the read lock:
UNLOCK TABLES;
On each slave, set the master replication server configuration. 

CHANGE MASTER TO
     MASTER_HOST='master_host_name',
     MASTER_USER='replication_user_name',
     MASTER_PASSWORD='replication_password',
     MASTER_LOG_FILE='recorded_log_file_name',
     MASTER_LOG_POS=recorded_log_position;