Hi all,
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.
#1. Take Backup Dump of Master DB :
If Source (Master) database is already there on some DB which is still going to act as Master server in replication environment, please export Master database using command at shell prompt as below:
mysqldump --add-drop-table -h 192.168.100.124 -u zuber -p nba_hiber_test | bzip2 -c > nba_hiber_test12Nov09_with_data.sql.bz2
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:
# start using target slave DB
use nba_hiber_test;
# Run SQLs in "nba_hiber_test12Nov09_with_data.sql"
source nba_hiber_test12Nov09_with_data.sql;
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.
Add following lines inside [mysqld] of "my.cnf" file.
# folder path whre mysql will store its log-bin files
log-bin=/var/lib/mysqllogs/bin-log
# 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.
#5. Restart Master DB:
/etc/init.d/mysqld restart
#6. Confirm that bin logs are getting created:
Login to Master DB and fire:
SHOW MASTER STATUS;
Assume its running fine if you see something like:
mysql> show master status;
+---------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+---------------------+----------+--------------+------------------+
| bin-log.000001 | 98 | | |
+---------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
#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
This commend should give you something like this as result:
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.100.124
Master_User: repl
Master_Port: 3306
Connect_Retry: 10
Master_Log_File: bin-log.000003
Read_Master_Log_Pos: 41435
Relay_Log_File: mysqld-relay-bin.000006
Relay_Log_Pos: 41570
Relay_Master_Log_File: bin-log.000003
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: nba_hiber_test
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 41435
Relay_Log_Space: 41570
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
1 row in set (0.00 sec)
#14. Test:
Now try creating some records in the Master table, and check its getting reflected in the Slave server in some interval.
Wish you Happy Replication
No comments:
Post a Comment