Steps for MySQL DB Replication

Posted by Manish Agrawal on Geeks with Blogs See other posts from Geeks with Blogs or by Manish Agrawal
Published on Tue, 27 Apr 2010 07:23:01 GMT Indexed on 2010/04/27 7:34 UTC
Read the original article Hit count: 585

Filed under:

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;

+---------------+----------+--------------+------------------+

+---------------+----------+--------------+------------------+

+---------------+----------+--------------+------------------+

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;