MySQL Replication

MASTER

Create MySQL Replication User

GRANT REPLICATION SLAVE ON *.* TO 'replication'@'slavehost’ IDENTIFIED BY 'slavesecret';

Configure server ID and bin log on master

vim /etc/my.cnf

[mysqld]

server-id=1

log-bin=mysql-bin.log

Restart MySQL Server

/etc/init.d/mysqld restart

Flush and Lock all Tables to Stop Writes

FLUSH TABLES WITH READ LOCK;

Note the Master Bin Log Position Details for later on the Slave

SHOW MASTER STATUS;

+------------------+----------+--------------+------------------+

| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |

+------------------+----------+--------------+------------------+

| mysql-bin.000002 |       98 |              |                  |

+------------------+----------+--------------+------------------+

1 row in set (0.00 sec)

Dump the databases that will be replicated

If all will be replicated:

mysqldump –uuser –psecret --all-databases > mysql-all.sql

If one or a few will be replicated do the following for each:

mysqldump –uuser –psecret  repdb > mysql-repdb.sql

Unlock all the tables so writes can continue

UNLOCK TABLES;

SLAVE

Configure server ID and Master Details on the Slave

vim /etc/my.cnf

[mysqld]

server-id=2

master-host=masterhost

master-user=replication

master-password=slavesecret

master-connect-retry=60

Restart MySQL Server

/etc/init.d/mysqld restart

Configure the Master Bin Log Details on the Slave

CHANGE MASTER TO MASTER_HOST='masterhost', MASTER_USER='replication', MASTER_PASSWORD='slavesecret', MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=98;

Start the Slave IO Process

START SLAVE;

Check the Slave Status

SHOW SLAVE STATUS\G

Additional Replication Options

MASTER

Rather than all databases set the specific ones you want replicated

binlog-do-db=thisdb,anotherdb

OR per line

binlog-do-db=thisdb

binlog-do-db=anotherdb

Replicate all but the following databases

binlog-ignore-db=tempdb,testdb

OR per line

binlog-ignore-db=tempdb

binlog-ignore-db=testdb

SLAVE

Rather than all databases set the specific ones you want replicated

replicate-do-db=repthisdb,repthatdb

OR per line

replicate-do-db=repthisdb

replicate-do-db=repthatdb

Replicate all but the following databases

replicate-ignore-db=norepthisdb,norepthatdb

OR per line

replicate-ignore-db=norepthisdb

replicate-ignore-db=norepthatdb

Replication of specific tables

replicate-do-table=dbname.tablename

Preventing replication of specific tables

replicate-do-ignore=dbname.tablename

Rewrite the database name

replicate-rewrite-db=olddbname->newdbname

Incrementing the slave bin log position after fixing a replication problem

SET SQL_SLAVE_SKIP_COUNTER = 1;

SLAVE START;