{"id":218,"date":"2023-03-26T06:59:17","date_gmt":"2023-03-26T06:59:17","guid":{"rendered":"https:\/\/linux.atheesh.com\/?p=218"},"modified":"2023-04-02T15:08:55","modified_gmt":"2023-04-02T15:08:55","slug":"step-by-step-guide-to-setting-up-mysql-master-slave-replication","status":"publish","type":"post","link":"https:\/\/linux.atheesh.com\/index.php\/2023\/03\/26\/step-by-step-guide-to-setting-up-mysql-master-slave-replication\/","title":{"rendered":"Step-by-Step Guide to Setting up MySQL Master-Slave Replication"},"content":{"rendered":"\n

MySQL is one of the most widely used relational database management systems. It offers several features and options to manage data efficiently. One such feature is MySQL Master-Slave replication, which is used to copy data from a master database to one or more slave databases. In this blog, we will discuss how to configure MySQL Master-Slave replication with detailed explanations and commands.<\/p>\n\n\n\n

\"\"
Mysql Replication <\/figcaption><\/figure>\n\n\n\n

Master-Slave Replication<\/p>\n\n\n\n

Master-Slave replication is a method of copying data from a master MySQL database to one or more slave databases. It is used to improve the availability and scalability of MySQL databases. The master database is responsible for writing changes to the database, and the slave databases copy those changes. Slave databases can be used for backup, read-only queries, load balancing, or reporting.<\/p>\n\n\n\n

MySQL Master-Slave Replication Configuration<\/p>\n\n\n\n

To configure MySQL Master-Slave replication, we need to follow these steps:<\/p>\n\n\n\n

Step 1: Setting up the Master Database<\/p>\n\n\n\n

The first step is to set up the master database. To do this, we need to perform the following steps:<\/p>\n\n\n\n

    \n
  1. Install MySQL: Install MySQL on the server that will be the master database. We can use the following command to install MySQL on Ubuntu:<\/li>\n<\/ol>\n\n\n\n
    <\/circle><\/circle><\/circle><\/g><\/svg><\/span><\/path><\/path><\/svg><\/span>
    sudo<\/span> <\/span>apt<\/span>-<\/span>get<\/span> <\/span>install<\/span> <\/span>mysql<\/span>-<\/span>server<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
      \n
    1. Configure MySQL: Once MySQL is installed, we need to configure it by editing the MySQL configuration file. Open the MySQL configuration file with the following command:<\/li>\n<\/ol>\n\n\n\n
      <\/circle><\/circle><\/circle><\/g><\/svg><\/span><\/path><\/path><\/svg><\/span>
      sudo<\/span> <\/span>nano<\/span> <\/span>\/<\/span>etc<\/span>\/<\/span>mysql<\/span>\/<\/span>mysql<\/span>.<\/span>conf<\/span>.<\/span>d<\/span>\/<\/span>mysqld<\/span>.<\/span>cnf<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
      Add the following lines to the end of the file to enable binary logging:<\/p>\n\n\n\n
      <\/circle><\/circle><\/circle><\/g><\/svg><\/span><\/path><\/path><\/svg><\/span>
      log_bin<\/span> <\/span>=<\/span> <\/span>\/var<\/span>\/<\/span>log<\/span>\/mysql\/mysql-bin.log<\/span><\/span>\nserver_id<\/span> <\/span>=<\/span> <\/span>1<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
      Save and close the file.<\/p>\n\n\n\n
        \n
      1. Restart MySQL: After editing the MySQL configuration file, we need to restart MySQL with the following command:<\/li>\n<\/ol>\n\n\n\n
        <\/circle><\/circle><\/circle><\/g><\/svg><\/span><\/path><\/path><\/svg><\/span>
        sudo<\/span> <\/span>systemctl<\/span> <\/span>restart<\/span> <\/span>mysql<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
          \n
        1. Create a Replication User: We need to create a user that the slave database will use to connect to the master database. To create a replication user, run the following command:<\/li>\n<\/ol>\n\n\n\n
          <\/circle><\/circle><\/circle><\/g><\/svg><\/span><\/path><\/path><\/svg><\/span>
          GRANT<\/span> <\/span>REPLICATION<\/span> <\/span>SLAVE<\/span> <\/span>ON<\/span> <\/span>*<\/span>.<\/span>*<\/span> <\/span>TO<\/span> <\/span>'<\/span>repl_user<\/span>'<\/span>@<\/span>'<\/span>%<\/span>'<\/span> <\/span>IDENTIFIED<\/span> <\/span>BY<\/span> <\/span>'<\/span>password<\/span>'<\/span>;<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
          Note: Replace ‘repl_user’ and ‘password’ with the desired username and password.<\/p>\n\n\n\n
            \n
          1. Take a Snapshot: Finally, we need to take a snapshot of the master database to use as a starting point for the slave databases. To take a snapshot, use the following command:<\/li>\n<\/ol>\n\n\n\n
            <\/circle><\/circle><\/circle><\/g><\/svg><\/span><\/path><\/path><\/svg><\/span>
            mysqldump<\/span> <\/span>--<\/span>single<\/span>-<\/span>transaction<\/span> <\/span>--<\/span>master<\/span>-<\/span>data<\/span>=<\/span>1<\/span> <\/span>--<\/span>ignore<\/span>-<\/span>table<\/span>=<\/span>mysql<\/span>.<\/span>event<\/span> <\/span>dbname<\/span> <\/span>><\/span> <\/span>dbname<\/span>.<\/span>sql<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
            Note: Replace ‘dbname’ with the name of the database.<\/p>\n\n\n\n
            Step 2: Setting up the Slave Database<\/p>\n\n\n\n
            After setting up the master database, we need to set up the slave database. To do this, we need to perform the following steps:<\/p>\n\n\n\n
              \n
            1. Install MySQL: Install MySQL on the server that will be the slave database. We can use the following command to install MySQL on Ubuntu:<\/li>\n<\/ol>\n\n\n\n
              <\/circle><\/circle><\/circle><\/g><\/svg><\/span><\/path><\/path><\/svg><\/span>
              sudo<\/span> <\/span>apt<\/span>-<\/span>get<\/span> <\/span>install<\/span> <\/span>mysql<\/span>-<\/span>server<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
                \n
              1. Configure MySQL: Once MySQL is installed, we need to configure it by editing the MySQL configuration file. Open the MySQL configuration file with the following command:<\/li>\n<\/ol>\n\n\n\n
                <\/circle><\/circle><\/circle><\/g><\/svg><\/span><\/path><\/path><\/svg><\/span>
                sudo<\/span> <\/span>nano<\/span> <\/span>\/<\/span>etc<\/span>\/<\/span>mysql<\/span>\/<\/span>mysql<\/span>.<\/span>conf<\/span>.<\/span>d<\/span>\/<\/span>mysqld<\/span>.<\/span>cnf<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
                Add the following lines to the end of the file to enable replication:<\/p>\n\n\n\n
                <\/circle><\/circle><\/circle><\/g><\/svg><\/span><\/path><\/path><\/svg><\/span>
                server_id<\/span> <\/span>=<\/span> <\/span>2<\/span><\/span>\nrelay_log<\/span> <\/span>=<\/span> <\/span>\/var<\/span>\/<\/span>log<\/span>\/mysql\/mysql-relay-bin.log<\/span><\/span>\nrelay_log_index<\/span> <\/span>=<\/span> <\/span>\/var<\/span>\/<\/span>log<\/span>\/mysql\/mysql-relay-bin.index<\/span><\/span>\nlog_slave_updates<\/span> <\/span>=<\/span> <\/span>1<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
                Save and close the file.<\/p>\n\n\n\n
                  \n
                1. Restart MySQL: After editing the MySQL configuration file, we need to restart MySQL with the following command:<\/li>\n<\/ol>\n\n\n\n
                  <\/circle><\/circle><\/circle><\/g><\/svg><\/span><\/path><\/path><\/svg><\/span>
                  sudo<\/span> <\/span>systemctl<\/span> <\/span>restart<\/span> <\/span>mysql<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
                  Connect to the Master Database: We need to connect to the master database and get the binary log file name and position to use as a starting point for the slave database. To do this, run the following command on the master database:\n<\/pre>\n\n\n\n
                  <\/circle><\/circle><\/circle><\/g><\/svg><\/span><\/path><\/path><\/svg><\/span>
                  SHOW<\/span> <\/span>MASTER<\/span> <\/span>STATUS<\/span>;<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
                  Note the binary log file name and position.\n\nSet up Replication on the Slave Database: We need to set up replication on the slave database using the binary log file name and position from the master database. Run the following command on the slave database:\nmakefile\n<\/pre>\n\n\n\n
                  <\/circle><\/circle><\/circle><\/g><\/svg><\/span><\/path><\/path><\/svg><\/span>
                  CHANGE<\/span> <\/span>MASTER<\/span> <\/span>TO<\/span><\/span>\nMASTER_HOST<\/span>=<\/span>'<\/span>master_host_ip<\/span>'<\/span>,<\/span><\/span>\nMASTER_USER<\/span>=<\/span>'<\/span>repl_user<\/span>'<\/span>,<\/span><\/span>\nMASTER_PASSWORD<\/span>=<\/span>'<\/span>password<\/span>'<\/span>,<\/span><\/span>\nMASTER_LOG_FILE<\/span>=<\/span>'<\/span>mysql-bin.000001<\/span>'<\/span>,<\/span><\/span>\nMASTER_LOG_POS<\/span>=<\/span>501<\/span>;<\/span><\/span>\n<\/span><\/code><\/pre><\/div>\n\n\n\n
                  Note: Replace 'master_host_ip', 'repl_user', 'password', 'mysql-bin.000001', and '501' with the appropriate values.\n\nStart Replication on the Slave Database: Finally, we need to start replication on the slave database with the following command:\n<\/pre>\n\n\n\n
                  <\/circle><\/circle><\/circle><\/g><\/svg><\/span><\/path><\/path><\/svg><\/span>
                  SHOW<\/span> <\/span>SLAVE<\/span> <\/span>STATUS<\/span>\\<\/span>G<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
                  To check the replication status, run the following command:<\/p>\n\n\n\n
                  <\/circle><\/circle><\/circle><\/g><\/svg><\/span><\/path><\/path><\/svg><\/span>
                  SHOW<\/span> <\/span>SLAVE<\/span> <\/span>STATUS<\/span>\\<\/span>G<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
                  This will show the replication status and information on the slave database.<\/p>\n\n\n\n
                  Conclusion<\/p>\n\n\n\n
                  In this blog, we discussed how to configure MySQL Master-Slave replication with detailed explanations and commands. Master-Slave replication is a powerful feature of MySQL that can be used to improve the availability and scalability of MySQL databases. By following the steps outlined in this blog, you should be able to successfully set up MySQL Master-Slave replication.<\/p>\n","protected":false},"excerpt":{"rendered":"
                  MySQL is one of the most widely used relational database management systems. It offers several features and options to manage data efficiently. One such feature is MySQL Master-Slave replication, which is used to copy data from a master database to one or more slave databases. In this blog, we will discuss how to configure MySQL […]<\/p>\n","protected":false},"author":1,"featured_media":234,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[9],"tags":[],"_links":{"self":[{"href":"https:\/\/linux.atheesh.com\/index.php\/wp-json\/wp\/v2\/posts\/218"}],"collection":[{"href":"https:\/\/linux.atheesh.com\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/linux.atheesh.com\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/linux.atheesh.com\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/linux.atheesh.com\/index.php\/wp-json\/wp\/v2\/comments?post=218"}],"version-history":[{"count":3,"href":"https:\/\/linux.atheesh.com\/index.php\/wp-json\/wp\/v2\/posts\/218\/revisions"}],"predecessor-version":[{"id":236,"href":"https:\/\/linux.atheesh.com\/index.php\/wp-json\/wp\/v2\/posts\/218\/revisions\/236"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/linux.atheesh.com\/index.php\/wp-json\/wp\/v2\/media\/234"}],"wp:attachment":[{"href":"https:\/\/linux.atheesh.com\/index.php\/wp-json\/wp\/v2\/media?parent=218"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/linux.atheesh.com\/index.php\/wp-json\/wp\/v2\/categories?post=218"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/linux.atheesh.com\/index.php\/wp-json\/wp\/v2\/tags?post=218"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}