A blog by a system administrator and programmer.

Simple MySQL Replication HOWTO 
Thursday, November 1, 2007, 06:10 PM - System
Posted by Freddy Chu

Requirements:



* Slave server should be newer version than the master server. It is adviced that both servers are in same version
* Network port 3306 of both server should be avaliable
* log-bin is enabled


Procedure



1. edit my.cnf to add log-bin and server-id, server-id should be unique and from 1 to 2^32 - 1.
[mysqld]
server-id = 1
log-bin


2. restart the mysql server both server if you have do many modification in step 1

3. create replicate user, if you are using MySQL before 4.0.2 replace "REPLICATION SLAVE" by "FILE"
GRANT REPLICATION SLAVE ON *.* TO 'Username'@'Host' IDENTIFIED BY 'password';


4. lock all tables on master server

mysql> FLUSH TABLES WITH READ LOCK;


5. Copy all mysql files to the slave server

6. At master server run show master status, remember the file and position info

mysql> SHOW MASTER STATUS;
+--------------+----------+--------------+------------------+
| File | Position | Binlog_do_db | Binlog_ignore_db |
+--------------+----------+--------------+------------------+
| log-bin.002 | 189 | | |
+--------------+----------+--------------+------------------+


7. At slave server run change master to use the account create in step 3 and info in step 6
CHANGE MASTER TO
MASTER_HOST='master_server_ip',
MASTER_USER='slave_user_name',
MASTER_PASSWORD='slave_password',
MASTER_LOG_FILE='file_in_step_7',MASTER_LOG_POS=position_in_step_7;


8. run Slave start

9. unlock tables on master server

mysql>UNLOCK TABLES;



Now you will have a replicate MySQL server, just test to modify any records. :D
9 comments ( 91 views )   |  permalink   |   ( 3 / 2425 )

<<First <Back | 1 | 2 | Next> Last>>