Setting up MySQL replication using mysqldump

1. Set up new replication user on master database
GRANT REPLICATION SLAVE ON *.* TO ‘repuser’@'%’ IDENTIFIED BY 'passwd';
2. Ensure binary logging is turned on in the master server. Also check for server-id value in my.cnf:
Log-bin=/some-directory/
Server-id=1
3. Install mysql binaries on new slave server (Please see my article on installing new MySQL server)
4. Set up my.cnf on slave server. Ensure relay related parameter are added in slave’s my.cnf
master-info-file=/Some-directory/master.info
relay-log=/Some-directory/JWIKIPROD-relay-bin
relay-log-index=/Some-directory/JWIKIPROD-relay-bin.index
relay-log-info-file=/Some-directory/relay-log.info
server-id=2
5. Take backup of Master database using mysqldump:
mysqldump --all-databases --master-data --lock-all-tables >db_name_timestamp.dmp
6. Copy backup dump to slave server
9. start the slave:
cd $MYSQL_HOME
bin/mysqld_safe --user=mysql --skip-slave
Check logfile to ensure there are no issues while starting the database
10. Import the data in slave server:
mysql -u root < db_name_timestamp.dmp
11. Connect to mysql database as root mysql -u root-p (password is null for new installation)
CHANGE MASTER TO
MASTER_HOST=master_hostname,
MASTER_PORT=3306,
MASTER_USER='repluser',
MASTER_PASSWORD='passwd',
MASTER_LOG_FILE='DB_arch.455, -- look in dump file created in step5 MASTER_LOG_POS=23343; -- -- look in dump file created in step 5
12. start slave server process
Mysql> start slave;
13 Ensure slave server is catching up properly
Mysql> show slave status/G
14. Using this mechanism, none of the user privileges are copied over to slave server. You need to export users and privileges separately (if required)
15 set up monitors to ensure replication is working properly

Setting up MySQL replication without any production downtime (only for InnoDB based database)

Setting up MySQL replication without any production downtime (only for InnoDB based database)
This method requires purchasing ibbackup hotbackup tool. Also, please note that setting up replication without any downtime is only possible only if all underlying tables are based on InnoDB engine. Please see my separate article for setting up replication for MyISAM based databases
1. Set up new replication user on master database
GRANT REPLICATION SLAVE ON *.* TO 'repluser'@'%’ IDENTIFIED BY 'passwd';
2. Ensure binary logging is turned on in the master server. Also check for server-id value in my.cnf:
Log-bin=/some-directory/
Server-id=1
3. Install mysql binaries on new slave server (Please see my article on installing new MySQL server)
4. Set up my.cnf on slave server. Ensure relay related parameter are added in slave’s my.cnf
master-info-file=/Some-directory/master.info
relay-log=/Some-directory/JWIKIPROD-relay-bin
relay-log-index=/Some-directory/JWIKIPROD-relay-bin.index
relay-log-info-file=/Some-directory/relay-log.info
server-id=2
5. Take snapshot of Master database using ibbackup tool:
Please read instructions in http://www.innodb.com/support/documentation/innodb-hot-backup-manual/#options for how to take hot backup using ibbackup tool
6. Copy backed up files to slave server
7. Apply logs to backup to make it a consistent copy
Please read instructions in http://www.innodb.com/support/documentation/innodb-hot-backup-manual/#options for how to apply logs to make a consistent backup copy
8. Put the restored backup and the log files at appropriate place for the new slave. Check my.cnf in production to figure out appropriate location for innodb files
9. start the slave:
cd $MYSQL_HOME
bin/mysqld_safe --user=mysql --skip-slave
Check logfile to ensure there are no issues while starting the database
10. connect to mysql database as root mysql -u root-p (password is null for new installation)
CHANGE MASTER TO
MASTER_HOST=master_hostname,
MASTER_PORT=3306,
MASTER_USER='repluser',
MASTER_PASSWORD='passwd',
MASTER_LOG_FILE='DB_arch.12345', -- whatever you find in log file of slave server while applying backup
MASTER_LOG_POS=1312184; -- -- whatever you find in log file of slave server while applying backup
9. start slave server process
Mysql> start slave;
10 Ensure slave server is catching up properly
Mysql> show slave status/G
11. Using this mechanism, none of the user privileges are copied over to slave server. You need to export users and privileges separately (if required)15 set up monitors to ensure replication is working properly
12. Setup up replication monitoring scripts