Why i have to do master-slave replication using MySQL?
1. One of the biggest advantages to have master-slave set up in MySQL is to be able to use master for all of the inserts, update and delete queries and slave for select queries. This will most probably speed up your application without having to diving into optimizing all the queries or buying more hardware.
2. Do backups from slave: One of the advantages people overlook is that you can use MySQL slave to do backups from. That way site is not affected at all when doing backups.
Goal: Master DB – Use db only for insert,delete and updated queries
Slave DB – Use db only for select queries
There are several Master-Slave replication available in MySQL. Below is the one of the best Master-Slave replication.
1. Below configuration is only for a single database replication.
2. Do the step 1 and step 2 in your master server and step 3 and step 4 slave servers.
3. Before doing all steps, you must create database and repective tables in your master and slave server.
Step 1: (Activate binary(bin) log in MySQL)
log-bin = mysql-bin
binlog-do-db = databasename // give your database name
server-id = 1 // unique id depend upon your servers
Step 2: (Creating a new user in Master)
In mysql… login as root user.
CREATE USER username;
GRANT replication SLAVE on *.* TO ‘username’@’%’ IDENTIFIED BY ‘password’;
server-id = 2
master-host = master ip address
master-user = username
master-password = password
master-connect-retry = 60
replicate-do-db = databasename
In mysql console…
show slave status;
Hope this will help you!!!