SOURCECODEBD.NET

Live Experience

MySQL DB Replication
 
To set up replication between a new master and slave:

   1. Configure the MySQL master with the necessary configuration properties.
 # service mysqld stop
 # vi /etc/my.cnf
 [mysqld]

 log-bin=mysql-bin
 server-id=1
 #service mysqld start

   2. Set up a user for replication
 mysql> CREATE USER 'repl'@'%.mydomain.com' IDENTIFIED BY 'slavepass';
 mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%.mydomain.com'
   3. Obtain the master status information.
 mysql> FLUSH TABLES WITH READ LOCK;
 mysql > SHOW MASTER STATUS;
+----------------------+----------+------------------+-----------------------+
| File                     | Position | Binlog_Do_DB | Binlog_Ignore_DB  |
+----------------------+----------+------------------+-----------------------+
| mysql-bin.000003 | 73        | test                | manual,mysql        |
+----------------------+----------+------------------+-----------------------+

The File column shows the name of the log file and Position shows the position within the file. In this example, the binary log file is mysql-bin.000003 and the position is 73. Record these values. You need them later when you are setting up the slave.

   5. On the master, release the read lock:
       mysql> UNLOCK TABLES;

   6. On the slave, edit the MySQL configuration.
       # service mysqld stop
 # vi /etc/my.cnf
 [mysqld]
 server-id=2
 #service mysqld start
 
   8. Set the Master Configuration on the Slave

 mysql> CHANGE MASTER TO
     ->     MASTER_HOST='master_host_name',<br />     ->     MASTER_USER='replication_user_name',<br />     ->     MASTER_PASSWORD='replication_password',<br />     ->     MASTER_LOG_FILE='recorded_log_file_name',<br />     ->     MASTER_LOG_POS=recorded_log_position,<br />     ->    replicate-do-db=mydns,<br />     ->     replicate-wild-do-table=mydns.dns_%;<br /> mysql> START SLAVE;</p>'

You are here: Home / CentOS / MySQL DB Replication in CentOS