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;

 

 

Share this blog post on social media:

Social Links

Disclaimer

All advice, installation/configuration how to guides, troubleshooting and other information on this website are provided as-is with no warranty or guarantee. Whilst the information provided is correct to the best of my knowledge, I am not reponsible for any issues that may arise using this information, and you do so at your own risk. As always before performing anything; check, double check, test and always ensure you have a backup.

Copyright ©2016 Andy Barnes - Please do not copy any content including images without prior consent!

Designed and Hosted by Andy Barnes