1- What? Replication?! What the hell?
Some people just scare away as they hear about replication in database field. There is nothing to worry about as far as you stick with us through this step by step guide.
Look at the picture below:
When you have a website with just one database then you just have the blue database (Master) as shown in the above picture. Your blue database will handle insertion, reading, sorting and nearly everything. If you have a high traffic website then your DB will suffer. Now imagine that you have another DB which is an exact copy of the first one. You can now shift the reading, sorting parts to the red one (slave). Phew.
Master DB now relaxes and takes a deep breathe. We’ve put some parts of the work on slave’s shoulder.
Data will be sent to the slave automatically.
2- Some real-world use cases for replication:
- Full and incremental backup
- Performance improvement (Scale out)
- Read scaling
3- meat and potatoes:
You need to have two separate servers. One is dedicated to the master and the other one to the slave.
Master database IP address: 184.108.40.2065
Slave database IP address: 220.127.116.117
Install MySQL if you haven’t already with: sudo apt-get install mysql-server mysql-client
3-1 Configuring master
After installation open MySQL configuration file with your desired text editor, I’ve used vim here:
sudo vim /etc/mysql/my.cnf
Search for a line which is something like below:
bind-address = 127.0.0.1
Change the IP address to master IP address that we defined before: 18.104.22.1685
bind-address = 22.214.171.1245
In the [mysqld] section you should find #server-id=1, remove the # to uncomment it.
server-id should be unique. It means your slave should have a different server-id.
Right below the server-id line in my.cnf we have:
#log_bin = /var/log/mysql/mysql-bin.log
Uncomment this line by removing the # sign. Slave will read this log file and replicate data to its database.
You need to specify database name that will be replicated on the slave. So put your database name in front of the below line and don’t forget to uncomment it:
#binlog_do_db = newdatabase
Our DB name is bugx. So this line would look like:
binlog_do_db = bugx
Viola! You’ve configured master. Congratulations!
Now we need to restart our MySQL server after saving changes to my.cnf:
sudo service mysql restart
Now slave should have right privileges to access our database. In your terminal login to MySQL as root user:
mysql -u root -p
It will prompt for password. Enter your root password. After successful login you should see the below screen:
Grant privileges to slave:
GRANT REPLICATION SLAVE ON *.* TO ‘slaveuser’@'%’ IDENTIFIED BY ‘slavepassword’;
Now reload privileges:
Now we need to select our database (bugx) and lock it:
FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;
Now you should see a table echoed in terminal with file, position, Binlog_Do_DB, Binlog_Ignore_DB columns. Jot down the position number. It’s very important. This is the position where our slave server will start replicating. My position number is 217.
Open new terminal window/tab. keep the first terminal open.
If you make any changes to the first terminal DB will be unlocked.That’s why we’ve opened a new terminal window.
In the 2nd terminal, newly opened one, backup your DB with MySQL dump:
mysqldump -u root -p –opt bugx > bugx.sql
Let’s get back to our first terminal that we locked our bugx database. Unlock your table:
You’ve come so far. Your master is up and kicking. Configuring slave is much easier. Take a break and come back.
3-2 Configuring slave
In slave server login to MySQL through terminal as we did before and create your bugx database:
CREATE DATABASE bugx;
As you may remember we exported our bugx database into bugx.sql file. Now its time to restore our data from that file into our slave server with mysql command:
mysql -u root -p bugx < /path/to/bugx.sql
Slave server needs some configuration similar to master server. So open my.cnf with vim text editor as below:
sudo nano /etc/mysql/my.cnf
Change server-id, log_bin and binlog_do_db:
server-id = 2
log_bin = /var/log/mysql/mysql-bin.log
binlog_do_db = bugx
Here we need to add a line for relay log so add the below line to your configuration file:
relay-log = /var/log/mysql/mysql-relay-bin.log
MySQL doc: The relay log, like the binary log, consists of a set of numbered files containing events that describe database changes, and an index file that contains the names of all used relay log files.
OK! restart slave server:
sudo service mysql restart
Now login to mysql via shell and type:
CHANGE MASTER TO MASTER_HOST=’126.96.36.1995′,MASTER_USER=’slaveuser’, MASTER_PASSWORD=’slavepassword’, MASTER_LOG_FILE=’mysql-bin.000001′, MASTER_LOG_POS= 217;
Log position that you previously wrote down is now useful. My log position was 217. 217 is the position that our slave will start replicating. In overall, this command says to slave to connect to master with above credentials.
Start slave server:
See what’s going on in the slave:
SHOW SLAVE STATUS\G
You can stop you slave:
Good to know that:
- We can have one master and many slaves.
- We can have master to master replication.
- We can have different storage engines for our tables in slave in contrast to the master.