Thursday, November 12, 2009

Today I tried setting-up MySQL server replication for 5.0.84. Must say its kind of really easy to setup a simple replication between 2 MySQL server instances. Putting down some key points to be followed.

Note: Make sure that no processes are updating master server while we perform underlying operations.

Steps provided in this blog are applicable to both 32 bit and 64 bit version of MySQL.

Goal:
Setup a replication of a Database "nba_hiber_test" on Master server on other Slave Server with same DB name over there.

Pre-Requisties:

If you are making Master slave for a fresh DB, where there is nothing on the Master, just create DBs on both Master and Slave DB and directly go to Step 3.

Here 192.168.100.124 was the machine on which my existing Master database is located, and that DB's name is "nba_hiber_test". This will create a compressed bzip2 file of SQL file and store it at the present working directory.

#2. Import DB on the slave DB:
Transfer "nba_hiber_test12Nov09_with_data.sql.bz2" to the slave machine.
We need to put the exact copy of the Master database on the slave server before we can enable replication. To do that, import the database on the slave machine from "nba_hiber_test12Nov09_with_data.sql.bz2"

a. Decompress "nba_hiber_test12Nov09_with_data.sql.bz2" :

bzip2 -d nba_hiber_test12Nov09_with_data.sql.bz2

b. Login to the MySQL on slave box and Create DB with same name as on the Master server (here "nba_hiber_test"):

create database nba_hiber_test;

c. Assuming you started MySQL client from at the same location where u ran command 2.a, run that SQL fil against "nba_hiber_test" on slave box:

This import may take time depending upon the size of the database and the hardware on which you are running MySQL. Use that time to go thru this whole document rather than waiting and looking curiously on the black screen ;)

#3. What we did? :
At this point, we have identical copy of Master and Slave dbs on bothe machines.

#4. Configure Master server to generate bin logs that can be used pass replication info:
This will be needed only if you are starting using this server as Master in a replication environment fpr the first time
To confirm if server is acting as Master or not fir "SHOW MASTER STATUS;" command on mysql prompt. If no records returns, means you have to configure it for master.

# databases for which the replication info (log-bins) to be generated
replicate-do-db=nba_dev

#Change server id to some unique number in the range of 0 to 2^32 - 1
server-id = 1
Most of the time "my.cnf" is located in "/etc" folder. You will need root access to change the file and restart the server.

#7. Find out the log position on which Master server points to right now :
KEEP A NOTE OF THIS HADY to provide it to slave later:
SHOW MASTER STATUS

#8. Create replication user on the Master server to be used by slves to connect to do replication:
CREATE USER 'repl'@'%' IDENTIFIED BY 'passwd';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';

#9. Configure Slave server to give details of what all databases on this server are to be popullated with replication. Add following lines inside [mysqld] of "my.cnf" file.

skip-slave

#Change server id to some unique number in the range of 0 to 2^32 - 1
server-id = 2

#10. Restart Slave DB:
/etc/init.d/mysqld restart

#11. Stop any existing Slave threads on Slave Server:
STOP SLAVE

#12. Setup the Master info on Slave DB:
Note that we pass same values of "file" and "Position" in this command as found on step 7.
CHANGE MASTER TO
MASTER_HOST = '192.168.100.124',
MASTER_USER = 'repl',
MASTER_PASSWORD = 'passwd',
MASTER_LOG_FILE = 'bin-log.000001',
MASTER_LOG_POS = 98;
#13. Chkeck the Slave thread status to see if is able to connect to the Master DB or not:
SHOW SLAVE STATUS \G