Database Replication in MySQL

MASTER CONFIGURATION

 (a) my.ini

1. Enable Networking.

    eg. # skip-networking

2. Set unique Server ID.

    eg. server-id=1

3. Enable binary logging.

    eg. log-bin=mysql-bin

4. Specify database to be replicated.(OPTIONAL)

    eg. binlog-do-db=exampledb

5. Databases to be ignored.(OPTIONAL)

   eg. replicate-ignore-db = mysql

6. Restart MySQL.

(b) MySQL console

1. Create slave replication account.

    Syntax: CREATE USER 'slave_user'@'slave_host';

    eg. CREATE USER 'slave_user'@'10.177.15.106';

2. Grant replication to slave.

    Syntax: GRANT REPLICATION SLAVE > ON *.* TO 'slave_user'@'slave_host' -> IDENTIFIED    

    BY 'slave_password';

    eg. GRANT REPLICATION SLAVE -> ON *.* TO 'slave_user'@'10.177.15.106' ->  

    IDENTIFIED BY 'slave_password'; 3. FLUSH PRIVILEGES; 4. USE exampledb; 5. FLUSH 

    TABLES WITH  READ LOCK; 

6. SHOW MASTER STATUS;

3. FLUSH PRIVILEGES;

4. USE exampledb;

5. FLUSH TABLES WITH READ LOCK;

6. SHOW MASTER STATUS;

7. Leave the MySQL shell.

    eg. quit;

SLAVE CONFIGURATION:

 (a) my.ini

1. Enable Networking.

     eg. # skip-networking

2. Set unique Server ID.(Other than Master server-id)

     eg. server-id=2

3. Specify database on master to be replicated.(OPTIONAL)

     eg. replicate-do-db=exampledb

4. Restart MySQL.

(b) MySQL console

1. Tell MySQL on the slave that it is the slave, that the master is 10.177.15.81, and that the master database   

    to watch is exampledb.

    Syntax: CHANGE MASTER TO MASTER_HOST='10.177.15.81',MASTER_PORT=3306 ->

    MASTER_USER='slave_user',MASTER_PASSWORD='slave_password', -> 

    MASTER_LOG_FILE='mysql-bin.000017',MASTER_LOG_POS=107;

2. Start the slave.

    eg. START SLAVE;

3. Leave the MySQL shell.

    eg. quit;

The master and slave database servers do not need to be in sync entirely, with all databases and all tables from the master replicated onto the slave. By default, the slave will replicate everything, but this behavior can be changed with change in configuration options:


// replicate this database
replicate-do-db=database_name

// don't replicate this database
replicate-ignore-db=database_name

// replicate this database.table
replicate-do-table=database_name.table_name

// don't replicate this table
replicate-ignore-table=database_name.table_name
// allows wildcards, use % as the wildcard character
// e.g db% would be all databases beginning with db
replicate--wild-do-table=database_name.table_name

// ignore all specified tables, with wildcards
replicate-wild-ignore-table=db_name.table_name

These options can all be used multiple times in a single configuration.
A couple of other useful options:

// allows you to use map databases that use different
// database names on each server

replicate-rewrite-db=master_db->slave_database

// writes replicated statements to the slaves binary logs
log-slave-update