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;