Master-Slave Replication without downtime

First, we configure master’s /etc/mysql/my.cnf by including these lines in the [mysqld] section:

server-id=1

binlog-format = mixed

log-bin=mysql-bin

datadir=/var/lib/mysql

innodb_flush_log_at_trx_commit=1

sync_binlog=1

Restart the master mysql server and create a user with replication permission that your slave server will use to connect to the master:

CREATE USER repl@slave-server-ip;

GRANT REPLICATION SLAVE ON *.* TO rep@slave-server-ip IDENTIFIED BY 'password';

Next, create the dump file with the binlog position. It will affect the performance of  database, but won’t lock your tables:

mysqldump --skip-lock-tables --single-transaction --flush-logs --hex-blob --master-data=2 -A  > /dump.sql

Now, examine the head of the file and write down the values for MASTER_LOG_FILE and MASTER_LOG_POS. You will need them later:

head dump.sql -n80 | grep "MASTER_LOG_POS"

compress the file with gzip

gzip dump.sql

Next, you need to transfer the dump file to our slave server 

scp dump.sql.gz username@slave-server-ip:/directory/

Next, you should log into your slave server, and edit /etc/mysql/my.cnf file to add the following lines:

server-id     = 2

binlog-format = mixed

log_bin       = mysql-bin

relay-log     = mysql-relay-bin

log-slave-updates = 1

read-only         = 1

Restart the mysql slave, and then import your dump file:

gunzip dump.sql.gz

mysql -u root -p < /dump.sql

Log into your mysql prompt on your slave server and run the following commands to start replication:

CHANGE MASTER TO MASTER_HOST ='master_ip',MASTER_USER='repl',

MASTER_PASSWORD='password', MASTER_LOG_FILE='value from above', MASTER_LOG_POS=value from above>;

START SLAVE;

To check the progress of your slave:

SHOW SLAVE STATUS \G

If all is well, Last_Error will be blank, and Slave_IO_State will report “Waiting for master to send event”. Look for Seconds_Behind_Master which indicates how far behind it is. It took me a few hours to accomplish all of the above.

Sometimes errors occur in replication. For example, if you accidentally change a row of data on your slave. If this happens, fix the data, then run:

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