Grid Guides

Explore How System On Grid Can Work For You

Grid Guide Topics

How to Setup Master-Slave Replication in MySQL

Table Of Contents


    ## Table of Contents

    – [Introduction](#introduction
    – [Prerequisites](#prerequisites
    – [How To Setup Master-Slave Replication in MySQL](#how-to-setup-master-slave-replication-in-mysql
    – [Configuring Master Database](#configuring-master-database
    – [Configuring Slave Database](#configuring-slave-database
    – [Conclusion](#conclusion

    ## Introduction

    MySQL replication is a process of enabling data from one database server (master to be copied to one or more database servers (slaves automatically. It is commonly used to get read access to multiple database servers for flexibility. It is also used to analyze data on the slave servers to overload the master server.

    In this guide, you are going to learn how to set up master-slave replication in MySQL. We use the following IP address to explain you the procedure.

    Master Database – 192.168.32.17

    Slave Database – 192.168.32.18

    ## Prerequisites

    You should have access to an Ubuntu 18.04 server and created a non-root user account with sudo privileges by following our guide, [Initial server setup with Ubuntu 18.04](https://systemongrid.com/support/guides/how-to-do-initial-server-setup-with-ubuntu-18.04.

    You should have installed MySQL by following our guide, Install MySQL.

    ## How To Setup Master-Slave Replication in MySQL

    ## Configuring Master Database

    Open the MySQL configuration file in a text editor on your master server.

    “`
    $ sudo vi /etc/mysql/mysql.conf.d/mysqld.cnf
    “`

    You need to make a few changes within the configuration file.
    Find the following line within the file.

    “`
    bind-address = 127.0.0.1
    “`

    Replace the default IP address with your server IP address.

    “`
    bind-address = 192.168.32.17
    “`

    Now, you need to uncomment the server-id line located in the mysqld section. You can choose any number for your server-id but make sure that it won’t match any other server-id in your replication group. We are fine with its default number 1.

    “`
    server-id = 1
    “`

    Now, you need to uncomment the log_bin line where the actual details of the replication are kept. The slave copies all the changes that are registered in the log.

    “`
    log_bin = /var/log/mysql/mysql-bin.log
    “`

    Now, its time to designate the database that will be replicated on the slave server. You can include any number of databases just by repeating the following line for each of your databases.

    “`
    binlog_do_db = newdatabase
    “`

    Then, save and exit the configuration file.

    Refresh the MySQL.

    “`
    $ sudo service mysql restart
    “`

    Now, open the MySQL shell.

    “`
    $ sudo mysql
    “`

    Grant privileges to the slave using the below command.

    “`
    mysql> GRANT REPLICATION SLAVE ON *.* TO ‘slave_user’@’%’ IDENTIFIED BY ‘password’;
    “`

    Apply the changes with the command:

    “`
    mysql> FLUSH PRIVILEGES;
    “`

    Switch to the “newdatabase” using the command:

    “`
    mysql> USE newdatabase;
    “`

    Lock the database to prevent new changes.

    “`
    mysql> FLUSH TABLES WITH READ LOCK;
    “`

    Check the status of your master server.

    “`
    mysql> SHOW MASTER STATUS;
    “`

    Output

    ![showmaster](https://grid.media/assets/images/show-master-02132019.png

    The slave database will start replication from this position. Note down the numbers as you need to use them later.

    Don’t make new changes in the same tab/window because it can unlock the database automatically. So, open a new tab/window and run the following commands.

    Export your database in the new tab/window using the below command.

    “`
    $ sudo mysqldump -u root -p –opt newdatabase > newdatabase.sql
    “`

    Then, return to the previous tab/window, unlock the databases and exit the MySQL shell.

    “`
    mysql> UNLOCK TABLES;

    mysql> QUIT;
    “`

    ## Configuring Slave Database

    To configure the slave database, you need to log into your slave server. Then, open the MySQL shell using the below command.

    “`
    $ sudo mysql
    “`

    Create the new database that you want to replicate from the master database and exit the MySQL shell.

    “`
    mysql> CREATE DATABASE newdatabase;

    mysql> EXIT;
    “`

    Now, import the database from the master database.

    “`
    $ sudo mysql -u root -p newdatabase < /path/to/newdatabase.sql ``` Configure the slave configuration as you did in the master configuration step. ``` $ sudo vi /etc/mysql/mysql.conf.d/mysqld.cnf ``` Now, you need to uncomment the server-id line located in the mysqld section. As we already mentioned that the server-id should be unique, change its default number to something different. ``` server-id = 2 ``` Now, you need to add the relay-log line, uncomment the log_bin line and change the binlog_do_db value to newdatabase. ``` relay-log = /var/log/mysql/mysql-relay-bin.log log_bin = /var/log/mysql/mysql-bin.log binlog_do_db = newdatabase ``` Then, save and exit the configuration file. Restart the MySQL once again ``` $ sudo service mysql restart ``` Now, you need to enable the replication within the MySQL shell. To do this, open the MySQL shell, run the below command by replacing the values with your values. ``` $ sudo mysql ``` ``` mysql> CHANGE MASTER TO MASTER_HOST=’192.168.32.18′,MASTER_USER=’slave_user’, MASTER_PASSWORD=’password’, MASTER_LOG_FILE=’mysql-bin.000001′, MASTER_LOG_POS= 775;
    “`

    Then, exit the MySQL shell.

    “`
    mysql> EXIT;
    “`

    ## Conclusion

    Now, you have an idea about how to set up master-slave replication in MySQL. The MySQL replication has tremendous options, and we explained it briefly.