MySQL Replication without downtime

This is a straight-forward implementation of doing a server-slave replication with minimal downtime on the master database server

On master_host modify /etc/mysql/my.cnf:

server-id		= 105
binlog-format   = mixed
log-bin			= mysql-bin
innodb_flush_log_at_trx_commit=1
sync_binlog=1

Restart MySQL

$ service mysql restart

Create replication user:

> GRANT REPLICATION SLAVE ON *.* to user@slave_host IDENTIFIED BY 'slave_password:less_than_32';
> FLUSH PRIVILEGES;

Create the backup sql, preferrably on the slave_host:

$ mysqldump -hmaster_host -uslave_user -p --skip-lock-tables --single-transaction --flush-logs --hex-blob --master-data=2 -A > masterdump.sql

Check the MASTER_LOG_FILE and MASTER_LOG_POS:

$ head masterdump.sql -n80 | grep "MASTER_LOG_POS"

Take note of the details like:

-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=107;

On slave_host modify /etc/mysql/my.cnf:

server-id               = 101
binlog-format       = mixed
log_bin                 = mysql-bin
relay-log               = mysql-relay-bin
log-slave-updates = 1
read-only               = 1

Restart MySQL

$ service mysql restart

Dump masterdump.sql on slave_host:

$ mysql -uroot -p -v < masterdump.sql

Set Replication Slave:

$ mysql -uroot -p
> CHANGE MASTER TO MASTER_HOST='master_host',
	MASTER_USER='slave_user',
	MASTER_PASSWORD='slave_password',
	MASTER_LOG_FILE='mysql-bin.000002',
	MASTER_LOG_POS=107;
> START SLAVE;

Check the progress of your slave:

> SHOW SLAVE STATUS\G

Skip to fix replication errors:

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