Mysql one way DB replication

One way replication of mysql database:

Mysql replication help us in keeping the data replicated to one or more sites reliably with binary logs. Apart from good amount of advantages Mysql replication doesn’t help with data corruption, since the corrupted data is replicated in all slaves. It is good to have periodic backup of database apart from replication.

Replication Steps

– Create database with same name on all servers
> mysql -u db_user -p -e "CREATE DATABASE db_name"

– Create database user with replication privilege on master
> GRANT REPLICATION SLAVE ON *.* TO 'replication_user'@'replication_clients" IDENTIFIED BY 'replication_password'
This can be also supplied with particular database name with ;db_name.*’ instead of *.*

– Edit Mysql master configuration (my.cnf) to allow replication
[mysqld]
server-id = 1 # Important with replication
log-slave-updates
log-bin = /var/lib/mysql/mysql-bin
log-bin-index = /var/lib/mysql/mysql-bin.index
replicate-do-db = db_name # specify the dbs to replicate
log-warnings
innodb_flush_log_at_trx_commit=1
sync-binlog=1
innodb_safe_binlog

– Take dump of master db and put them on all replicas

use db_name;
FLUSH TABLES WITH READ LOCK;

$ mysqldump -u dbuser -p db_name > db_dump.sql
install on slaves
$ mysql -u dbuser -p db_name < db_dump.sql


use db_name;
UNLOCK TABLES;

– Edit mysql configuration on replicas with master credentials

[mysqld]
old_passwords=1
server-id=2
innodb_file_per_table
log-slave-updates
master-host = master_hostname
master-port = master_port
master-user = master_user
master-password = master_password
log-bin = /var/lib/mysql/mysql-bin
log-bin-index = /var/lib/mysql/mysql-bin.index

Restart the Mysql daemon on all servers and check the replication status:
Master: > show master status;
Replicas: > show slave status;