{"id":60517,"date":"2018-09-26T11:00:44","date_gmt":"2018-09-26T08:00:44","guid":{"rendered":"http:\/\/examples.javacodegeeks.com\/?p=60517"},"modified":"2018-09-24T11:38:19","modified_gmt":"2018-09-24T08:38:19","slug":"mysql-replication-tutorial","status":"publish","type":"post","link":"https:\/\/examples.javacodegeeks.com\/java-development\/core-java\/sql\/mysql-replication-tutorial\/","title":{"rendered":"MySQL Replication Tutorial"},"content":{"rendered":"

1. Introduction<\/h2>\n

MySQL<\/a>\u00a0is one of the most used SQL databases in many of the world-enterprise applications due to its ability to manage transactions and the ACID-behaviour which is built into its core. One of the reasons MySQL is so popular is due to the easiness it provides for its usage and its related commands. In the open source web application framework LAMP (which consist of Linux, Apache, MySQL and PHP),\u00a0MySQL server is a central & important component. The MySQL Database server is written using C and C++ which internally uses\u00a0a lexical analyser to parse and understand the SQL queries.<\/p>\n

Today, when the systems have become distributed, scalable and highly fault tolerant, we cannot afford a fault in our databases like the DB servers going down and there is no way this is managed automatically. In this lesson, we will study about MySQL Replication, a concept through which we can make sure that even if the MySQL DB for a system goes down, it can shift to its replica and manage data from that as well, without letting users know what happened in the garage. This is one of the reasons that most of the systems today make use of\u00a0MySQL Replication.\u00a0MySQL Replication can be done for various reasons, including but not limited to:<\/p>\n

    \n
  1. To ensure we have a backup of the data directly from our database<\/li>\n
  2. To run analytics or check the data without disturbing the main database<\/li>\n
  3. Scaling out the DB for better performance<\/li>\n<\/ol>\n

    As\u00a0MySQL replication has so many uses, we must perform it in our production systems. If you want to find out what is MySQL Server and how to use it, read this<\/a> lesson. Let’s get started.<\/p>\n

    \n

    Table Of Contents<\/h3>\n
    \n
    1. Introduction<\/a><\/dt>\n
    2. MySQL Setup<\/a><\/dt>\n
    3. Master MySQL Server Configuration<\/a><\/dt>\n
    4. Slave MySQL Server Configuration<\/a><\/dt>\n
    5. Replication lag<\/a><\/dt>\n
    6. Advantages of Replications<\/a><\/dt>\n
    7. Disadvantages of Replication<\/a><\/dt>\n
    8. Limitations of Replication<\/a><\/dt>\n
    9. Types of MySQL Replication<\/a><\/dt>\n
    10. Effect on Performance<\/a><\/dt>\n
    11. MySQL Replication – Conclusion<\/a><\/dt>\n<\/dl>\n<\/div>\n

    2. MySQL Setup<\/h2>\n

    For this lesson, we have made two new servers with different IPs which we will be using as Master and Slave in our replica set. To proceed further, we need to setup MySQL Server and Client tools on both of them. We can do this be executing the following command:<\/p>\n

    Installing MySQL Server and Client<\/em><\/span><\/p>\n

    sudo apt-get install mysql-server mysql-client\r\n<\/pre>\n
    Once we run this command, the mentioned utilities will be installed on the server. We will do the same installation on both the servers we made. While installing, we also set a MySQL root password:<\/p>\n
    \"MySQL<\/a>
    Setting Root Password<\/figcaption><\/figure><\/p>\n
    Once the installation process is completed, we can confirm if the MySQL server is up and running with the following command:<\/p>\n
    Check MySQL Service status<\/em><\/span><\/p>\n
    sudo service mysql status\r\n<\/pre>\n
    We will see an output like this:<\/p>\n
    \"MySQL<\/a>
    Check MySQL Server Status<\/figcaption><\/figure><\/p>\n
    Now that we know that the MySQL server is up and running, we can connect to it using the username and password we supplied to it during the installation process. We can use the following command to do so:<\/p>\n
    Logging into MySQL<\/em><\/span><\/p>\n
    mysql -u root -p\r\n<\/pre>\n
    When we press enter, MySQL Server will silently wait for us to provide the password. When we type it, the password will not be echoed back to the terminal due to security reasons. We will see the following prompt once we have logged into the MySQL command line:<\/p>\n
    \"MySQL<\/a>
    MySQL Login<\/figcaption><\/figure><\/p>\n
    When we are inside the MySQL command prompt, we can use the given command to show the databases which are present in the system and to ensure that MySQL is running fine:<\/p>\n
    Show All databases<\/em><\/span><\/p>\n
    show databases;\r\n<\/pre>\n
    Here is what we get back with this command:<\/p>\n
    \"MySQL<\/a>
    Check MySQL Databases<\/figcaption><\/figure><\/p>\n
    In the output, MySQL just displays the list of MySQL default databases that are used for administration purpose. Once we can see the Active status on both the servers, we can move ahead with the configuration for Master and Slave databases.<\/p>\n
    3. Master MySQL Server Configuration<\/h2>\n
    Now that we have active MySQL installation, we can start with the configuration of the master database first. To do this, we need to add configuration in the master MySQL config file. To open it with the nano editor on Ubuntu, use the following command:<\/p>\n
    Edit config file<\/em><\/span><\/p>\n
    sudo nano \/etc\/mysql\/mysql.conf.d\/my.cnf\r\n<\/pre>\n
    This file contains many options with which we can modify and configure the behaviour of the MySQL server we are running on the system. Firstly, we need to find the bind-address<\/code> property in the file which will look like this:<\/p>\n
    Bind Address property<\/em><\/span><\/p>\n
    # Instead of skip-networking the default is now to listen only on\r\n# localhost which is more compatible and is not less secure.\r\nbind-address            = 127.0.0.1\r\n<\/pre>\n
    We will modify this IP to the current server IP itself so that it looks like:<\/p>\n
    Updated Bind Address property<\/em><\/span><\/p>\n
    bind-address            = <server_ip_here>\r\n<\/pre>\n
    The next property we need to find is server-id<\/code> in the file which will look like this:<\/p>\n
    Server ID property<\/em><\/span><\/p>\n
    # note: if you are setting up a replication slave, see README.Debian about\r\n#       other settings you may need to change.\r\n#server-id              = 1\r\n<\/pre>\n
    This will be a commented property which we will uncomment:<\/p>\n
    Updated Server ID property<\/em><\/span><\/p>\n
    server-id              = 1\r\n<\/pre>\n
    Next important property to look for is log_bin<\/code>. This property informs about the file where the replica set details are actually kept. This looks like:<\/p>\n
    Log Bin property<\/em><\/span><\/p>\n
    #log_bin                        = \/var\/log\/mysql\/mysql-bin.log\r\n<\/pre>\n
    This is the file where the slave logs about the changes it has accomodated in it from the master database. We will just uncomment the property for now. Finally, we will edit the binlog_do_db<\/code> property which informs the slave database server about which DB to replicate across the Slave database. We can include more than one database by repeating this line for all of the databases we will need:
     
    <\/div> <\/div><\/p>\n
    DB to backup<\/em><\/span><\/p>\n
    binlog_do_db            = jcg_database\r\n<\/pre>\n
    Here is a glimpse of the config file which shows some of the properties we updated:<\/p>\n
    \"MySQL<\/a>
    Updated config file<\/figcaption><\/figure><\/p>\n
    Once all of the properties are done, we can save the file and make sure that MySQL server is restarted so that these changes are reflected in the server. To restart MySQL server, run the following command:<\/p>\n
    Restart MySQL<\/em><\/span><\/p>\n
    sudo service mysql restart\r\n<\/pre>\n
    Once the MySQL Server restartes, the next changes we need to do is inside the MySQL shell itself. So log back in the MySQL command line again.<\/p>\n
    We need to grant some privileges to the Slave DB so that it can access and replicate the data from the DB we mentioned in the config file, jcg_database<\/code>. We will execute the following commands to grant the privileges to the Slave DB:<\/p>\n
    Grant Privileges<\/em><\/span><\/p>\n
    GRANT REPLICATION SLAVE ON *.* TO 'root'@'%' IDENTIFIED BY 'password';\r\n<\/pre>\n
    Now, flush the privileges with the command:<\/p>\n
    Flush Privileges<\/em><\/span><\/p>\n
    FLUSH PRIVILEGES;\r\n<\/pre>\n
    Now, switch to the database we want to replicate after creating it:<\/p>\n
    Use new DB<\/em><\/span><\/p>\n
    mysql> CREATE SCHEMA jcg_database;\r\nQuery OK, 1 row affected (0.00 sec)\r\n\r\nmysql> USE jcg_database;\r\nDatabase changed\r\n<\/pre>\n
    Now, lock the database to stop any new changes happening in it with the following command:<\/p>\n
    Apply Read Lock<\/em><\/span><\/p>\n
    FLUSH TABLES WITH READ LOCK;\r\n<\/pre>\n
    Please note that we made some new tables and inserted some data before we applied this lock. Now, type the following command:<\/p>\n
    Check Master Status<\/em><\/span><\/p>\n
    SHOW MASTER STATUS;\r\n<\/pre>\n
    Here is what we get back with this command:<\/p>\n
    \"MySQL<\/a>
    Master DB Status<\/figcaption><\/figure><\/p>\n
    Note the position as this is the place from where the slave DB will start replicating the database. Now, if we do any changes to DB, it will unlock automatically, so do not do any new changes in same window. The next part is a little tricky. Open a new terminal window or tab (without closing the current tab) and log into the MySQL server and execute the following command:<\/p>\n
    Dump MySQL<\/em><\/span><\/p>\n
    mysqldump -u root -p --opt jcg_database > jcg_database.sql\r\n<\/pre>\n
    Here is what we get back with this command:<\/p>\n
    \"MySQL<\/a>
    MySQL Dump<\/figcaption><\/figure><\/p>\n
    Now we we can exit the new tab we opened separately and return to older tab. On that tab, unlock the DB and exit from MySQL:<\/p>\n
    Unlock and Quit<\/em><\/span><\/p>\n
    UNLOCK TABLES;\r\nQUIT;\r\n<\/pre>\n
    With that, we are done with all the configuration needed to be done on master database.<\/p>\n
    4. Slave MySQL Server Configuration<\/h2>\n
    Now, we are ready to start configuring our Slave database which will replicate the data. We can log in to the Slave server and open MySQL command line in it. For now, we only need to create a DB with the same name which we want to replicate and exit from the MySQL terminal:<\/p>\n
    \"MySQL<\/a>
    MySQL Slave DB<\/figcaption><\/figure><\/p>\n
    Now, we need to import the original database into Slave MySQL server with the SQL file we made. make sure that you bring that file on this new server and run the following command to import it into slave MySQL DB:<\/p>\n
    Import DB<\/em><\/span><\/p>\n
    mysql -u root -p jcg_database < \/root\/jcg_database.sql\r\n<\/pre>\n
    Once you hit enter, the database content and metadata will be imported into the slave DB. Once that is done, we can configure the Slave MySQL DB as well:<\/p>\n
    Configure DB<\/em><\/span><\/p>\n
    nano \/etc\/mysql\/mysql.conf.d\/mysqld.cnf\r\n<\/pre>\n
    We need to make sure that some properties in this config file are setup as well. The firt property is server-id<\/code>. It is currently set to 1 which is the default value. Set it to some other value:<\/p>\n
    Server ID for Slave<\/em><\/span><\/p>\n
    server-id               = 2\r\n<\/pre>\n
    Here are some other properties which we need to modify as well:<\/p>\n
    Other properties for Slace<\/em><\/span><\/p>\n
    relay-log               = \/var\/log\/mysql\/mysql-relay-bin.log\r\n\r\nlog_bin                 = \/var\/log\/mysql\/mysql-bin.log\r\n\r\nbinlog_do_db            = jcg_database\r\n<\/pre>\n
    We will have to add relay-log<\/code> property as it is not present in the config file by default. Once this is done, we need to restart the Slave MySQL DB as well so that the configuration changes we did can come into effect. Execute the following command to do this:<\/p>\n
    Restart MySQL<\/em><\/span><\/p>\n
    sudo service mysql restart\r\n<\/pre>\n
    Once the MySQL Server restartes, the next changes we need to do is inside the MySQL shell itself. So log back in the MySQL command line again.<\/p>\n
    In the MySQL shell, execute the following command:<\/p>\n
    Enable Replication<\/em><\/span><\/p>\n
    CHANGE MASTER TO MASTER_HOST='<master-server-ip>',MASTER_USER='root', MASTER_PASSWORD='hello123', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=  1306;\r\n<\/pre>\n
    This command completes various steps at a single time, which are:<\/p>\n