Mysql Database Backup Options

Last updated on May 5th, 2016 at 01:08 am

VN:F [1.9.22_1171]
Rating: 0.0/10 (0 votes cast)

Mysql Master Slave Replication, This tutorial will help you configure a MySQL Master Slave replication. Lets take an instance in which we have 2 MySQL servers

Server A (Master)
Server B (SLAVE)

Step 1 (Configuring MYSQL Master & Slave)

——-
On Master server, In your MySQL configuration file usually located at /etc/my.cnf in Linux / Unix systems add the below lines.

[mysqld]
server-id = 1
log-error = /var/lib/mysql/mysql.err
log-bin = /var/lib/mysql/mysql-bin

In the Slave server, Add below lines to the my.cnf file

[mysqld]
server-id = 2

Now restart both Master & Slave servers

service mysql restart

STEP 2 (Create USER and Grant Privileges)

——-
On Master run

mysql> CREATE USER 'replication'@'localhost' IDENTIFIED BY 'password';
Query OK, 0 rows affected (0.00 sec)
mysql> GRANT ALL PRIVILEGES ON * . * TO 'replication'@'localhost';
Query OK, 0 rows affected (0.00 sec)

On Slave run

GRANT REPLICATION SLAVE ON *.* TO 'replication'@'MASTER_HOST_NAME_OR_IP' IDENTIFIED BY 'password';

Then run the below command to check whether you are able to connect to Master from Slave using the new user credentials

mysql -ureplication -p -h MASTER_HOST_NAME_OR_IP

STEP 3 (SetUp Master And Slave)

——-
Now on the Master server run

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000002 |      120 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

Make a note of the POSITION column and FILE column, We are using these values later.
On slave server

mysql> STOP SLAVE;
Query OK, 0 rows affected (0.02 sec)
mysql> CHANGE MASTER TO master_host='MASTER_HOST_NAME_OR_IP', master_port=3306, master_user='replication', master_password='password', master_log_file='mysql-bin.000002', master_log_pos=120;
Query OK, 0 rows affected (0.07 sec)
mysql> START SLAVE;
Query OK, 0 rows affected (0.00 sec)

STEP 4 (Test Master and Slave Configuration By Running Some Mysql Queries Like CREATE, INSERT etc.,)

——-
In Master server mysql prompt follow below steps to create a MySQL table and database

mysql> CREATE DATABASE my_replica;
mysql> use my_replica;
Database changed
mysql> CREATE TABLE PERSON (ID INT NOT NULL AUTO_INCREMENT, FirstName VARCHAR(3),LastName VARCHAR(3),CITY VARCHAR(30),PRIMARY KEY(ID));
Query OK, 0 rows affected (0.02 sec)

Now go to your Slave server and check whether my_replica got created.

VN:F [1.9.22_1171]
Rating: 0.0/10 (0 votes cast)

Leave a Reply

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