MyLinuxTips

In this tutorial, I have only shown the commands to configure Master-Slave replication as the details, pros/cons of the setup is available throughout the web.

Master Server Configuration

(i) Create mysql replication user

(ii) Grant permissions to replication user

mysql> GRANT REPLICATION SLAVE ON *.* To ‘replication_user’@’%.mydomain.com’ IDENTIFIED BY ‘password’;

(iii) Setting the replication Master Configuration directives in my.cnf

Edit the file my.cnf or my.ini For Replication you must enable binary logging on master.

[mysqld]

log-bin=/var/lib/mysql/mysql-bin

server-id=1

innodb_flush_log_at_trx_commit=1

sync-binlog=1

expire_logs_days = 10

max_binlog_size = 500M

# For Greatest Possible Durability & consistency in replication setup Use InnoDB with transitions

Ensure that skip-networking is not enabled on master. Disable if ndb-cluster configuration directives are present.

Replication Slave configuration

(i) Edit /etc/my.cnf OR my.ini file

[mysqld] server-id=2

Check Master Replication Information

(i) Check & note the details

mysql> FLUSH TABLES WITH READ LOCK;

mysql> SHOW MASTER STATUS;

Note down the file name, position, etc for future correspondance.

(ii) Create a data snapshot of Master using “mysqldump”

mysql> FLUSH TABLES WITH READ LOCK;

Take dump of all databases

mysql> mysqldump –all-databases –master-data > dbdump.db

(iii) Setting up replication with new master-slave

Ensure that master is properly configured (my.cnf) & mysqld is running on it. On master release the read locks:

mysql> UNLOCK TABLES;

Slave server final configuration

(i) On Slave make sure also that my.cnf is properly configured & mysqld is running. After that execute these statements on slave:

mysql> CHANGE MASTER TO MASTER_HOST=’master_host_name’, MASTER_USER=’master_replication_user_name’, MASTER_PASSWORD=’replication_user_password’, MASTER_LOG_FILE=’recorded_log_file_name’, MASTER_LOG_POS=recorded_log_position;

(ii) Setting up replication with existing data

Import the dumped database file from master onto slave & restore it.

shell> mysql < dbdump.db

Start the slave

mysql> START SLAVE;