Mysql Master Slave Replication Configuration
Mysql Master Slave Replication Configuration:
MySQL replication allows you to have an exact copy of a database from a master server on another server (slave), and all updates to the database on the master server are immediately replicated to the database on the slave server so that both databases are in sync.
1. Configure The Master with following Steps:-
1) Open the my.cnf or my.ini file (depending on whether you are running Linux or Windows resp).
2) We want to replicate the database exampledb, so we put the following lines into /etc/mysql/my.cnf or my.ini under [mysqld] section:
[mysqld]
port=3306
server-id=1 //default is 1 for master
log-bin = /var/log/mysql/mysql-bin.log //Binary log file name and path to
store
binlog-do-db=exampledb // database name to replicate
expire_logs_days = 7 // bin_log must expire after 7 days
binlog_format=row // it may be row or statement or mixed .
3) Then we restart MySQL.
4) Then we log into the MySQL database as root and create a user with
replication privileges:
mysql> CREATE USER 'repl'@'%' IDENTIFIED BY 'slavepass';
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
5) To get the Binary logging Co-ordinates use following:-
mysql> FLUSH TABLES WITH READ LOCK;
mysql > SHOW MASTER STATUS;
+------------------+----------+--------------+---------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+---------------------------+
| mysql-bin.006 | 183 |exampledb | |
+------------------+----------+--------------+---------------------------+
6) To use the existing data, Dump the data using following command:-
mysqldump -u root -p<password> --opt exampledb > exampledb.sql (Replace
<password> with the real password for the MySQL user root! Important: There
is no space between -p and <password>!)
7) After the dump is over unlock the tables it is very much important
otherwise the data would not be entered into databases.
mysql> UNLOCK TABLES;
2. Configure The Slave with following Steps:-
1) On the slave we first have to create the database exampledb:
CREATE DATABASE exampledb;
use following:-
default-collation=latin1_general_ci
default-character-set=latin1
2) Import the SQL dump into our newly created exampledb on the slave:
mysql -u root -p<password> exampledb < /path/to/exampledb.sql (Replace
<password> with the real password for the MySQL user root! Important: There
is no space between -p and <password>!)
3) we add the following lines to /etc/mysql/my.cnf of Slave under [mysqld]
section:
[mysqld]
server-id=2
master-host= '192.168.0.100'
master-user= 'slave_user'
master-password= 'secret'
master-connect-retry= 60
replicate-do-db= 'exampledb'
4) Then we restart MySQL.
5) We change the master to bin_log position which is noted during show
master status.
SLAVE STOP;
CHANGE MASTER TO MASTER_HOST='192.168.0.100', MASTER_USER='slave_user',
MASTER_PASSWORD='<some_password>', MASTER_LOG_FILE='mysql-bin.006',
MASTER_LOG_POS=183;
6) START SLAVE;
That's it! Now whenever exampledb is updated on the master, all changes will be replicated to exampledb on the slave.
MySQL replication allows you to have an exact copy of a database from a master server on another server (slave), and all updates to the database on the master server are immediately replicated to the database on the slave server so that both databases are in sync.
1. Configure The Master with following Steps:-
1) Open the my.cnf or my.ini file (depending on whether you are running Linux or Windows resp).
2) We want to replicate the database exampledb, so we put the following lines into /etc/mysql/my.cnf or my.ini under [mysqld] section:
[mysqld]
port=3306
server-id=1 //default is 1 for master
log-bin = /var/log/mysql/mysql-bin.log //Binary log file name and path to
store
binlog-do-db=exampledb // database name to replicate
expire_logs_days = 7 // bin_log must expire after 7 days
binlog_format=row // it may be row or statement or mixed .
3) Then we restart MySQL.
4) Then we log into the MySQL database as root and create a user with
replication privileges:
mysql> CREATE USER 'repl'@'%' IDENTIFIED BY 'slavepass';
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
5) To get the Binary logging Co-ordinates use following:-
mysql> FLUSH TABLES WITH READ LOCK;
mysql > SHOW MASTER STATUS;
+------------------+----------+--------------+---------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+---------------------------+
| mysql-bin.006 | 183 |exampledb | |
+------------------+----------+--------------+---------------------------+
6) To use the existing data, Dump the data using following command:-
mysqldump -u root -p<password> --opt exampledb > exampledb.sql (Replace
<password> with the real password for the MySQL user root! Important: There
is no space between -p and <password>!)
7) After the dump is over unlock the tables it is very much important
otherwise the data would not be entered into databases.
mysql> UNLOCK TABLES;
2. Configure The Slave with following Steps:-
1) On the slave we first have to create the database exampledb:
CREATE DATABASE exampledb;
use following:-
default-collation=latin1_general_ci
default-character-set=latin1
2) Import the SQL dump into our newly created exampledb on the slave:
mysql -u root -p<password> exampledb < /path/to/exampledb.sql (Replace
<password> with the real password for the MySQL user root! Important: There
is no space between -p and <password>!)
3) we add the following lines to /etc/mysql/my.cnf of Slave under [mysqld]
section:
[mysqld]
server-id=2
master-host= '192.168.0.100'
master-user= 'slave_user'
master-password= 'secret'
master-connect-retry= 60
replicate-do-db= 'exampledb'
4) Then we restart MySQL.
5) We change the master to bin_log position which is noted during show
master status.
SLAVE STOP;
CHANGE MASTER TO MASTER_HOST='192.168.0.100', MASTER_USER='slave_user',
MASTER_PASSWORD='<some_password>', MASTER_LOG_FILE='mysql-bin.006',
MASTER_LOG_POS=183;
6) START SLAVE;
That's it! Now whenever exampledb is updated on the master, all changes will be replicated to exampledb on the slave.
