Linux Red Hat Enterprise Server and CentOS
| MySQL Replication |
 |
 |
|
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;
|
