MySql Replication Configuration Tutorial

MySQL replication allows you to have an exact copy of a database from a master server on another server (slave), and all updates to the database on the master server are immediately replicated to the database on the slave server so that both databases are in sync.

There are two core types of replication format, Statement Based Replication (SBR), which replicates entire SQL statements, and Row Based Replication (RBR), which replicates only the changed rows. You may also use a third variety, Mixed Based Replication (MBR).

Replication Configuration
Replication between servers in MySQL is based on the binary logging mechanism. The MySQL instance operating as the master (the source of the database changes) writes updates and changes as “events” to the binary log.

The information in the binary log is stored in different logging formats according to the database changes being recorded. Slaves are configured to read the binary log from the master and to execute the events in the binary log on the slave's local database.

Both the master and each slave must be configured with a unique ID (using the server-id option). In addition, each slave must be configured with information about the master host name, log file name, and position within that file. These details can be controlled from within a MySQL session
using the CHANGE MASTER TO statement on the slave.

Steps to Set Up Replication:

Mysql Master Slave Replication Configuration

1) On the master, you must enable binary logging and configure a unique server ID. This might require a server restart.
server_id=1//default must be 1 fro master
log-bin=mysql-bin //bin log file
bind-address = //local ip address
binlog-do-db='sample' //database to be replicated 

we can also give the expiry days for binary log for more details visit the given link:-
Mysql Binary Logs

2) On each slave that you want to connect to the master, you must configure a unique server ID. This might require a server restart.
server_id=2 //it must be unique and different than master's server id
port=3307 // port no must be different than master
bind-address = //local ip address

3) You want to create a separate user that will be used by your slaves to authenticate with the master to read the binary log for replication.

mysql> CREATE USER 'repl'@'' IDENTIFIED BY 'slavepass';
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'';

4) Before creating a data snapshot or starting the replication process, you should record the position of the binary log on the master. You will need this information when configuring the slave so that the slave knows where within the binary log to start executing events.

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
| mysql-bin.000003 | 73 | test | manual,mysql |

5) If you already have data on your master and you want to use it to synchronize your slave, you will need to create a data snapshot. You can create a snapshot using mysqldump.

shell> mysqldump --all-databases --lock-all-tables >dbdump.db

After the dump is over unlock the tables it is very much important otherwise the data would not be entered into databases.


6) When are creating the replication between new master and new slave,You will need to configure the slave with settings for connecting to the master, such as the host name, login credentials, and binary log file name and position.

There are two methods for this,

1) by using CHANGE MASTER TO command.
-> MASTER_HOST='master_host_name',
-> MASTER_PORT = port_num,
-> MASTER_USER='replication_user_name',
-> MASTER_PASSWORD='replication_password',
-> MASTER_LOG_FILE='recorded_log_file_name',
-> MASTER_LOG_POS=recorded_log_position;
Before using change to master stop the slave using STOP SLAVE

2)by setting the variables in my.ini file of slave by adding host_name, port, user etc.
also replicate-do-db=exampledb// database to be replicated must be set

7) When you are setting up replication with existing data, you need to import the existing data in following ways:-

1)If you used mysqldump:
a. Start the slave, using the --skip-slave-start option so that replication
does not start.

b. Import the dump file:
shell> mysql < fulldb.dump

on windows use following:-
c:\mysql\bin\mysql -u root dbname < dumpfilename

2) If you created a snapshot using the raw data files:
a. Extract the data files into your slave data directory. For example:
shell> tar xvf dbdump.tar

The logging format also can be switched at runtime. To specify the format globally for all clients, set the global value of the binlog_format system variable:

mysql> SET GLOBAL binlog_format = 'STATEMENT';
mysql> SET GLOBAL binlog_format = 'ROW';
mysql> SET GLOBAL binlog_format = 'MIXED';