Set up MySQL slave

gistfile1.md

ON THE MASTER:

Ensure that ports are open on master (3306)

In my.cnf set

bind-address = xx.xx.xx.xx

or use

bind-address = 0.0.0.0

For all addresses

Add or uncomment

server-id               = 1
log_bin                 = /var/log/mysql/mysql-bin.log
binlog_do_db            = newdatabase
innodb_flush_log_at_trx_commit=1
sync_binlog=1

Increase the packet size

max_allowed_packet = 10G

Create replication user

GRANT REPLICATION SLAVE ON *.* TO 'slave_user'@'%' IDENTIFIED by 'password';

Reset binary logs

RESET MASTER;

Get a clean dump

FLUSH PRIVILEGES;

USE database;

FLUSH TABLES WITH READ LOCK;

SHOW MASTER STATUS;

Dump the db.

UNLOCK TABLES;

ON THE SLAVE:

server-id = 2

relay-log               = /var/log/mysql/mysql-relay-bin.log
relay-log-index         = /var/log/mysql/slave-relay-bin.index
log_bin                 = /var/log/mysql/mysql-bin.log
binlog_do_db            = newdatabase

Import the dump into stage

CHANGE MASTER TO MASTER_HOST='xx.xx.xx.xx',
MASTER_USER='slave_user', MASTER_PASSWORD='password', 
MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=  167688;

START SLAVE;

SHOW SLAVE STATUS\G;

If error starting:

SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1; SLAVE START;

mysqladmin stop-slave
mysqldump
mysqladmin start-slave