Wednesday, July 6, 2011

How to set up MySql master slave replication

On master server you must enter in your configuration:

# replication master 
server-id=1
log-bin=www0-bin
expire_logs_days = 30
sync_binlog=1
# end replication

On slave server you must enter in you configuration:

# replication slave 
relay-log=/data/database/mysql/mysqld-relay-bin
server-id=2
# end replication

Be careful to set the unique id for the server-id (must be numeric)!

On master dump the DB in single transaction with current master position (on top of the dump file):

mysqldump -pyourpass --single-transaction --quick --master-data=2 schema_name > db_dump.sql

Using master-data=2 option will add change master to statement on the top of your dump file (commented).

On slave server if replication is running, we must first clean slave state. Log in to mysql console and run:

STOP SLAVE;
RESET SLAVE;

Now we can import the database on slave:

mysql -uroot -p db_name <  db_dump.sql

Get the master position from the top of db_dump.sql:

head -50 db_dump.sql | grep CHANGE

Add some additional parameters to CHANGE MASTER command (master_user, ...):

CHANGE MASTER TO MASTER_LOG_FILE='www0-bin.001519', MASTER_LOG_POS=518850262, master_user='replslave';

Start the slave and check the status:

START SLAVE;
show slave status \G;

If by any chance there are some duplicates, do the following:

STOP SLAVE; SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1; START SLAVE;

Post a Comment