MySQL Master - Slave Replication Tutorial for Newbie
0

In this guide i’ll demonstrate steps that are practicalwith pictures) how to setup MySQL Replication in Master – Slave mode on either CentOS or Ubuntu server. I believe MySQL replication technique is not stuff that is expert-only also newbies can discover and make use of this system. In the event it.( if you didn’t know yet, let’s start with a short introduction about what is*******)

Meet MySQL Replication

MySQL Replication is a method to create backup that is synced of MySQL database. You’ll phone it backup that is hot a backup that’s always up-to-date and ready-to-use in case the main one crashes or down. Master – Slave replication is just one of available mode that is commonly used in many production system. So this tutorial is the simplest one (one master will send information to a slave( that is single) which newbies can follow and make use of it at their manufacturing servers. Better simply take back-up than maybe not!

What’s the purpose that is main of Replication? It is to create a redundant MySQL server setup which is also very useful in terms of Data Security, Fail-over Solution, Database Backup from Slave, Analytics (to analyze it without using the database( that is main), etc.

Ingredients:

exactly what you’ll require:

  • Two VPS servers: this is often in solitary node, exact same providers, or at split / various nodes (various providers / places). Require suggestion? See my selection of suggested providers and/or 20 low end cloud servers.
  • Either CentOS or Ubuntu operating on top from it
  • Note down Master and Slave IPs.
  • About 30 moments of your energy
  • A sit down elsewhere or tea

the things I use within this ( that is tutorial

  • A 512MB RAM cloud server from Atlantic.net as Master server.
  • A 512MB RAM droplet from Digital Ocean as Slave server.
  • Both running CentOS 6.x 32-bit
  • MySQL version I use is v5.1.73 at both Master and Slave.
  • A cup of Torabika coffee.

Preparation

First thing first, you have to install MySQL server on both Master and Slave server. In case it yet, you can follow my previous articles:

Do not forget to note down the password of MySQL root user.( if you haven’t installed*******)

FYI, you can have MySQL host set up via control interface computer software like Vesta CP at Master host while install MySQL that is ONLY server on Slave.

(I use these imaginary IPs which you can replace with your own IPs:

Master server: 111.111.111.111

Before we step up,*******)

Slave host: 222.222.222.222

Setup MySQL Master Node

Do these actions on Master Node:

Step 1 – Edit MySQL setup file utilizing your text that is favorite editor vi or Nano. Command below uses Nano editor:

## On Ubuntu
 nano /etc/mysql/my.cnf
 
 ## On CentOS
 nano /etc/my.cnf

You may firstly need to edit IP binding configuration (especially in Ubuntu). Change 127.0.0.1 at bind-address with the IP of your Master server

bind-address            = 127.0.0.1
 
 ## change 127.0.0.1 with IP address of your Master node
 bind-address            = 111.111.111.111

Step 2 – Now put these lines right after the [mysqld] section*******) that is:(***********************)server-id=1 binlog-do-db=databasename relay-log=/var/lib/mysql/mysql-relay-bin relay-log-index=/var/lib/mysql/mysql-relay-bin.index log-error=/var/lib/mysql/mysql.err master-info-file=/var/lib/mysql/mysql-master.info relay-log-info-file=/var/lib/mysql/mysql-relay-log.info log-bin=/var/lib/mysql/mysql-bin

Change databasename using the database title you wish to reproduce / copy it on Slave host. You could add one or more database by saying this relative line for all of the databases you will need. I use servermomdb as my database name for this example. Screenshot pic:

2015-06-15_211112

Once done, save changes and exit the editor. In Nano it is Control+O then Control+X.

Step 3 you defined :

mysql -u root -p

2015-06-14_234240

Step 5 – Here’s a little bit tricky part– you can now restart mysql service:

service mysql restart

2015-06-14_233853

Step 4 – Login to MySQL shell as root using password. Take notice closely every single demand is granted inside MySQL shell. What you are actually likely to do is: to generate the servant individual and grant privileges for replication task, filtering privileges and secure the database.

GRANT REPLICATION SLAVE ON *.* TO 'replicauser'@'222.222.222.222' IDENTIFIED with 'replicapass';
 FLUSH PRIVILEGES;
 FLUSH TABLES WITH BROWSE LOCK;

Do keep in mind to change replicauser, 222.222.222.222 and replicapass correctly.

  • replicauser = is MySQL that is new user replicating purpose.
  • 222.222.222.222 = is Slave IP
  • replicapass = is password for the new MySQL user.

Here’s an illustration of mine. I use servermom as user and a1b2c3d4 as password:

2015-06-15_205601

Step 6 – You’ll need to find out the name that is mysql-bin its Position then note it down (i merely start Notepad and note it here). Nevertheless in MySQL shell, usage demand below:

SHOW MASTER STATUS;

it should provide production such as this:

2015-06-15_210705

In my instance i need to note the mysql-bin.000002 and 106, yours changes.

Step 7 you wish to copy it to the Slave:

mysqldump -u root -p databasename > databasename.sql(– you can now exit MySQL shell: 
quit;

and continue by exporting the database file************************)

change databasename with yours.

2015-06-15_211417

Transfer The Database to Slave

It’s time to transfer / copy your database to the Slave server. You can do this using SCP.

This is the easiest method available. The command is:

scp -P 22 /root/databasename.sql [email protected]:/root/

Change 22 with specific SSH port ((it) of your Slave server., also change databasename.sql and 222.222.222.222 with yours**********)if you changed. Exemplory instance of my own:

2015-06-15_212438

Setup MySQL Slave Node

Leave the server that is firstMaster node) for a while and now login to your second server which is the Slave node as root. Issue all command in this section only on Slave node.

Step 1 – Edit MySQL configuration file using your text that is favorite editor vi or Nano. Command below makes use of Nano editor:

## On Ubuntu
 nano /etc/mysql/my.cnf
 
 ## On CentOS
 nano /etc/my.cnf

Step 2 – Now let’s configure MySQL setup file inside Slave host. Copy paste these setup immediately after the [mysqld] section*******) that is:(***********************)server-id=2 master-host=111.111.111.111 master-connect-retry=60 master-user=replicauser master-password=replicapass replicate-do-db=databasename relay-log = /var/lib/mysql/mysql-relay-bin relay-log-index =.index that are/var/lib/mysql/mysql-relay-bin**************************************************************************************************************************************************************************************************************************); log-error = /var/lib/mysql/mysql.err master-info-file = /var/lib/mysql/mysql-master.info relay-log-info-file = /var/lib/mysql/mysql-relay-log.info log-bin = /var/lib/mysql/mysql-bin

Change 111.111.111.111 with ip of Master host. Change replicauser, databasename and replicapass with yours. Example:

2015-06-15_214511

Save changes and exit text editor which in Nano it is Control+O then Control+X.

Step 3 – Login to MySQL shell as root:

mysql -u root -p

pic:

2015-06-15_213456

Step 4 – Create new database with same name then exit MySQL shell:

CREATE DATABASE databasename;
 quit;

replace databasename with your own database name that is real. Example:

2015-06-15_213659

Step 5 – improve database you’ve got moved formerly applying this demand:

mysql -u root -p databasename < databasename.sql

Step 6 – Restart MySQL host to utilize / load modifications*******) that is:(***********************)service mysqld restart

example:

2015-06-15_214956

Step 7 – Login back to MySQL shell as root to do some important tasks:

mysql -u root -p

First task is to stop the Slave so you can define where to look for Master log file.

slave stop;
 CHANGE MASTER TO MASTER_HOST='111.111.111.111', MASTER_USER='replicauser', MASTER_PASSWORD='replicapass', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=12345;

Change 111.111.111.111, replicauser, replicapass, mysql-bin.000001 and 12345 with yours. Example of mine:

2015-06-15_215900

Once done, now you can start Slave and show this command:

to its status

slave begin;
 show servant statusG

It will provide you with an output such as this:

2015-06-15_220403

If the thing is something such as above pic then you’re success. You will find couple of lines to cover more attention with: Last_Error is blank, and Slave_IO_State will report “Waiting for master to deliver occasion“, additionally the Seconds_Behind_Master will suggest what lengths behind it’s. Then congratulation you have configured MySQL slave replication.( if you see those*******)

You can now exit MySQL shell:

quit;

Finishing Up

Leave Slave server for a while and go back to Master server. Login back to MySQL shell at Master server:

mysql -u root -p

this is important, you can now safely unlock it:

UNLOCK TABLES;
 quit;

pic:

2015-06-15_221916

In production environment this is very important to make sure database between Master and Slave can be synced easily, but visitors / users of your websites will not able to make changes (post comment, add article, etc) during your time setting up MySQL replication above as you locked the tables of MySQL database in Master server. But nevertheless you’ll maybe not suffer any downtime through the procedure. That’s it.

Additional Note:

Once Master – Slave mode is enabled and succeed, the MySQL root password of Slave host will alter as a result of its Master.

(ontwitter to get notified quicker when a new article is posted******)If you accidentally change a row of data on your slave or if there is an issue in connecting, try issuing this command inside MySQL shell:

STOP SLAVE;
 SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;
 START SLAVE;

Do not forget to follow me. Have Some Fun!

 

Just how to Automate the Scaling of one’s Web Application on DigitalOcean Ubuntu 16.04 Droplets

Previous article

Means of Accessing Elements inside DOM File with JavaScript

Next article

You may also like

Comments

Leave a reply

Your email address will not be published. Required fields are marked *

More in advanced