What is Database replication?
Database replication is one of most effective means of Backup. This is more effective when you need to handle a huge volume of data in a big organization. It is an online process means the master database is backed up instantaneously as and when there are any changes to the master database. The process of Replication may work in chained fashion. Replication protects against hardware failures on one of the replicated databases but not against user stupidity or maliciousness! If a user deletes a number of records, this process will then be replicated onto the other replicated servers, making replication useless as a reliable means of backup.
How to achieve this? ( I described about MySQL Database only)
Here both the servers are of same versions i.e. version: 4.1.15
The Master server is located in the location
/home/bijit/database/mysql
To start the services:
mysqld_multi start 3
mysqld_multi start 4
To log into mysql:
/home/bijit/database/mysql/bin/mysql -u root -proot --port=3307 --socket=/home/bijit/database/mysql/config/mysql.sock
/home/bijit/database2/mysql/bin/mysql -u root -p --port=3308 --socket=/home/bijit/database2/mysql/var/mysql.sock
The slave server is located in the path
/home/bijit/database2/mysql
Steps:
On the master do the following:
1.
use database mysql;
GRANT REPLICATION SLAVE ON *.* TO replicator@localhost IDENTIFIED BY 'replicator';
FLUSH PRIVILEGES;
2.
Make a copy of tables and data i.e. the entire database of the Master server. In this case copy the data directory located in /home/bijit/database/mysql/var
3.
In the my.cnf of Master server, add the following entries;
log-bin
server-id=1
4. Login to maser mysql server, and note the bin-log file and its position as
mysql> show master status;
+----------------------+----------+--------------+------------------+
File Position Binlog_Do_DB Binlog_Ignore_DB
+----------------------+----------+--------------+------------------+
localhost-bin.000002 79
+----------------------+----------+--------------+------------------+
1 row in set (0.05 sec)
On the slave do the following:
1.
In the file my.cnf, add the following entries (under the slave servers entry),
master-host = localhost
master-user = replicator
master-password = replicator
master-port = 3307
server-id = 2
2. Start the slave server, create a user called replicator as created in the master;
mysql> use mysql;
mysql> grant all privileges on *.* to replicator@localhost identified by 'replicator';
mysql>flush privileges;
mysql> stop slave;
mysql> CHANGE MASTER TO MASTER_HOST='localhost',
MASTER_USER='root',
MASTER_PASSWORD='root',
MASTER_PORT=3307,
MASTER_LOG_FILE='localhost-bin.000002'
MASTER_LOG_POS=79
2.
Copy the data taken from the master onto the slave.
3. Login to slave server as mysql -u root -proot [This is now same as master]
Start the slave server as
mysql> start slave;
mysql> LOAD DATA FROM MASTER;
Now check both master and slave and you will see both servers are in sync.
MySql replication has begun !!!
Database replication is one of most effective means of Backup. This is more effective when you need to handle a huge volume of data in a big organization. It is an online process means the master database is backed up instantaneously as and when there are any changes to the master database. The process of Replication may work in chained fashion. Replication protects against hardware failures on one of the replicated databases but not against user stupidity or maliciousness! If a user deletes a number of records, this process will then be replicated onto the other replicated servers, making replication useless as a reliable means of backup.
How to achieve this? ( I described about MySQL Database only)
Here both the servers are of same versions i.e. version: 4.1.15
The Master server is located in the location
/home/bijit/database/mysql
To start the services:
mysqld_multi start 3
mysqld_multi start 4
To log into mysql:
/home/bijit/database/mysql/bin/mysql -u root -proot --port=3307 --socket=/home/bijit/database/mysql/config/mysql.sock
/home/bijit/database2/mysql/bin/mysql -u root -p --port=3308 --socket=/home/bijit/database2/mysql/var/mysql.sock
The slave server is located in the path
/home/bijit/database2/mysql
Steps:
On the master do the following:
1.
use database mysql;
GRANT REPLICATION SLAVE ON *.* TO replicator@localhost IDENTIFIED BY 'replicator';
FLUSH PRIVILEGES;
2.
Make a copy of tables and data i.e. the entire database of the Master server. In this case copy the data directory located in /home/bijit/database/mysql/var
3.
In the my.cnf of Master server, add the following entries;
log-bin
server-id=1
4. Login to maser mysql server, and note the bin-log file and its position as
mysql> show master status;
+----------------------+----------+--------------+------------------+
File Position Binlog_Do_DB Binlog_Ignore_DB
+----------------------+----------+--------------+------------------+
localhost-bin.000002 79
+----------------------+----------+--------------+------------------+
1 row in set (0.05 sec)
On the slave do the following:
1.
In the file my.cnf, add the following entries (under the slave servers entry),
master-host = localhost
master-user = replicator
master-password = replicator
master-port = 3307
server-id = 2
2. Start the slave server, create a user called replicator as created in the master;
mysql> use mysql;
mysql> grant all privileges on *.* to replicator@localhost identified by 'replicator';
mysql>flush privileges;
mysql> stop slave;
mysql> CHANGE MASTER TO MASTER_HOST='localhost',
MASTER_USER='root',
MASTER_PASSWORD='root',
MASTER_PORT=3307,
MASTER_LOG_FILE='localhost-bin.000002'
MASTER_LOG_POS=79
2.
Copy the data taken from the master onto the slave.
3. Login to slave server as mysql -u root -proot [This is now same as master]
Start the slave server as
mysql> start slave;
mysql> LOAD DATA FROM MASTER;
Now check both master and slave and you will see both servers are in sync.
MySql replication has begun !!!
