Steps for MySQL DB Replication
- by Manish Agrawal
Following are the steps for MySQL Replication implementation on Linux machine:
Pre-implementation steps for DB Replication:
1. Identify the databases to be replicated
2. Identify the tables to be ignored during replication per database for example log tables
3. Carefully identify and replace the variables and paths(locations) mentioned (in bold) in the commands given below with appropriate values
4. Schedule the maintenance activity in odd hours as these activities will affect all the databases on Master database server
Implementation steps for DB Replication:
1. Configure the /etc/my.cnf file on Master database server to enable Binary logging, setting of server id and configuring of dbnames for which logging should be done.
[mysqld]
log-bin=mysql-bin
server-id=1
binlog-do-db = dbname
Note: You can specify multiple DB in binlog-do-db by using comma separated dbname values like: dbname1, dbname2, …, dbnameN
2. On Master database, Grant Replication Slave Privileges, by executing following command on mysql prompt
mysql> GRANT REPLICATION SLAVE ON *.* TO slaveuser@<hostname> identified by ‘slavepassword’;
3. Stop the Master & Slave database by giving the command
mysqladmin shutdown
4. Start the Master database by giving the command
/usr/local/mysql-5.0.22/bin/mysqld_safe --user=user&
5. mysql> FLUSH TABLES WITH READ LOCK;
Note: Leave the client (putty session) from which you issued the FLUSH TABLES statement running, so that the read lock remains in effect. If you exit the client, the lock is released.
6. mysql > SHOW MASTER STATUS;
+---------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+---------------+----------+--------------+------------------+
| mysql-bin.003 | 117 | dbname | |
+---------------+----------+--------------+------------------+
Note: Note this information as this will be required while starting of Slave and replication in later steps
7. Take MySQL dump by giving the following command, In another session window (putty window) run the following command:
mysqldump –u user --ignore-table=dbname.tbl_name -–ignore-table=dbname.tbl_name2 --master-data dbname > dbname_dump.db
Note: When choosing databases to include in the dump, remember that you will need to filter out databases on each slave that you do not want to include in the replication process.
8. Unlock the tables on Master by giving following command:
mysql> UNLOCK TABLES;
9. Copy the dump file to Slave DB server
10. Startup the Slave by using option --skip-slave
/usr/local/mysql-5.0.22/bin/mysqld_safe --user=user --skip-slave&
11. Restore the dump file on Slave DB server
mysql –u user dbname < dbname_dump.db
12. Stop the Slave database by giving the command
mysqladmin shutdown
13. Configure the /etc/my.cnf file on the Slave database server
[mysqld]
server-id=2
replicate-ignore-table = dbname.tablename
14. Start the Slave Mysql Server with 'replicate-do-db=DB name' option.
/usr/local/mysql-5.0.22/bin/mysqld_safe --user=user --replicate-do-db=dbname --skip-slave
15. Configure the settings at Slave server for Master host name, log filename and position within the log file as shown in Step 6 above
Use Change Master statement in the MySQL session
mysql> CHANGE MASTER TO MASTER_HOST='<master_host_name>', MASTER_USER='<replication_user_name>', MASTER_PASSWORD='<replication_password>', MASTER_LOG_FILE='<recorded_log_file_name>', MASTER_LOG_POS=<recorded_log_position>;
16. On Slave Servers mysql prompt give the following command:
a. mysql > START SLAVE;
b. mysql > SHOW SLAVE STATUS;
Note:
To stop slave for backup or any other activity you can use the following command on the Slave Servers mysql prompt:
mysql> STOP SLAVE
Refer following links for more information on MySQL DB Replication:
http://dev.mysql.com/doc/refman/5.0/en/replication-options.html
http://crazytoon.com/2008/04/21/mysql-replication-replicate-by-choice/
http://dev.mysql.com/doc/refman/5.0/en/mysqldump.html
