This write up was originally created by Nick Taylor so he has a full credit and right for this write up, and my deep thanks as well. I’ve placed it here only not to loose it in the pile of daily emails.

On the Master my.cnf, add this and restart:

expire_logs_days        = 10
max_binlog_size         = 100M
server-id=1
innodb_flush_log_at_trx_commit=1
sync_binlog=1
log-bin = /var/log/mysql/mysql-bin.log

On the Slave my.cnf, add this and restart:

expire_logs_days        = 10
max_binlog_size         = 100M
server-id=2
master-host=master_hostname
master-user=repl_user
master-password=some_password
master-connect-retry=60
replicate-do-db=db_name_to_replicate

On the Master, create the repl_user and grant “REPLICATION”

GRANT REPLICATION SLAVE ON *.* TO 'repl_user'@'master_hostname' IDENTIFIED BY 'some_password';
FLUSH PRIVILEGES;

do a DB dump

Include master_data = 2 (which creates a comment inside the dump file with a CHANGE MASTER statement). You can only use—single-transaction if your tables are InnoDB, otherwise you need to lock the tables (which will cause an outage for the duration of the dump).

mysqldump -ublah -pblah --all-databases --master_data=2 --single-transaction

import this dump into the slave DB

mysql -u root -p<password> mydb < /path/to/mydb.sql

Execute the CHANGE MASTER statement from inside the dump file

 CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000010', MASTER_LOG_POS=6620791;

Start the Slave (do this on the slave)

START SLAVE;

Check to see that it’s replicating.

ls -la /var/lib/mysql/ (location of the relay logs, files should be growing)

inside MySQL:

SHOW SLAVE STATUS;

Should have this:

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

And no errors. Also these:

Exec_Master_Log_Pos: 3264652
Relay_Log_Pos: 3264789

Should be roughly equal. Exec_master_log_pos might be behind slightly because executing lags behind the relay log.

What to do if you’ve done alter on slave first and then on master?

SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1; 
START SLAVE;