MySQL Master-Slave Configuration

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


# 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;

DISCLAIMER: The information provided on this website comes without warranty of any kind and is distributed AS IS. Every effort has been made to provide the information as accurate as possible, but no warranty or fitness is implied. The information may be incomplete, may contain errors or may have become out of date. The use of this information described herein is your responsibility, and to use it in your own environments do so at your own risk.

Copyright © 2012 LINUXHOWTO.IN