{"id":281,"date":"2016-08-07T02:55:24","date_gmt":"2016-08-06T23:55:24","guid":{"rendered":"https:\/\/community.virtono.com\/?p=281"},"modified":"2023-06-21T09:20:38","modified_gmt":"2023-06-21T06:20:38","slug":"mysql-replication-and-how-to-achieve-it","status":"publish","type":"post","link":"https:\/\/www.virtono.com\/community\/tutorial-how-to\/mysql-replication-and-how-to-achieve-it\/","title":{"rendered":"MySQL Replication and How to Achieve It"},"content":{"rendered":"

This article is a fraction of a Number of Articles on MySQL, to access them click here<\/a>.<\/h3>\n

Replication <\/strong><\/p>\n

Replication is the ability to duplicate data changes that occur on one server to another.<\/p>\n

Basic components<\/strong><\/p>\n\n\n\n\n\n\n
Servers<\/td>\nMaster, Slave, Relay Slave<\/td>\n<\/tr>\n
Configuration files<\/td>\n<\/td>\n<\/tr>\n
Threads<\/td>\nBinlog dump thread, I\/O Thread, SQL Thread<\/td>\n<\/tr>\n
Sequential files<\/td>\nBinary Log, Relay Log<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

Basic uses of Replication<\/strong><\/p>\n\n\n\n\n\n\n\n\n\n
Use<\/b><\/td>\nDescription<\/b><\/td>\n<\/tr>\n
Backup<\/td>\nRun backup operations that require (or not) taking the server offline<\/td>\n<\/tr>\n
Scale out<\/td>\nAdd more slaves to improve read throughput<\/td>\n<\/tr>\n
Hot Standby<\/td>\nProvide a replacement for the master to reduce downtime<\/td>\n<\/tr>\n
Data Analysis<\/td>\nPerform resource-intensive operations on a slave to avoid conflicts with other active applications<\/td>\n<\/tr>\n
Debugging<\/td>\nCarry diagnoses of complex queries, and refine database design without risking effects to the production databases.<\/td>\n<\/tr>\n
Development<\/td>\nProvide near-production-quality data for development of new applications that represent actual data values, ranges, and size<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

Replication \u2013 how it works?<\/strong><\/p>\n

    \n
  • Slave connects to Master and I\/O Thread<\/strong> asks for data<\/li>\n
  • Binlog dump thread<\/strong> sends contents to I\/O Thread<\/strong><\/li>\n
  • SQL Thread<\/strong> applies data<\/li>\n<\/ul>\n

    \"1\"<\/p>\n

    Basic rules<\/strong><\/p>\n

      \n
    • Slave can have only one Master<\/li>\n
    • Master can have many Slaves<\/li>\n
    • Each server must have a unique server_id<\/strong><\/li>\n
    • Slave can propagate changes from its Master and be Master for other Slaves (Relay Slave) \u2013 enable log_slave_updates<\/strong><\/li>\n<\/ul>\n

      Basic configuration – Master<\/strong><\/p>\n\n\n\n\n
      1. <\/b>Changes in .cnf file<\/strong><\/td>\n<\/tr>\n
      \n

      [mysqld]<\/p>\n

      server_id=N<\/p>\n

      log_bin=mysql-bin<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n\n\n\n\n
      2. <\/b>Set up a special user<\/strong><\/td>\n<\/tr>\n
      \n

      mysql> CREATE USER ‘slaveuser’@’%’ IDENTIFIED BY ‘haslo\u2019;<\/p>\n

      mysql> GRANT REPLICATION SLAVE ON *.* TO ‘slaveuser’@’%\u2019;<\/p>\n

      mysql> flush privileges;<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n\n\n\n\n
      3. <\/b>Get coordinates of binary log<\/strong><\/td>\n<\/tr>\n
      \n

      mysql> FLUSH TABLES WITH READ LOCK;<\/p>\n

      mysql> SHOW MASTER STATUS;<\/p>\n

      mysql> UNLOCK TABLES;<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

      Basic configuration – Slave<\/strong><\/p>\n\n\n\n\n
      1. <\/b>Changes in .cnf file<\/strong><\/td>\n<\/tr>\n
      \n

      [mysqld]<\/p>\n

      server_id=N<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n\n\n\n\n
      2. Connect to Master<\/strong><\/td>\n<\/tr>\n
      \n

      mysql> CHANGE MASTER TO<\/p>\n

      MASTER_HOST=’1.1.1.1\u2019,<\/p>\n

      MASTER_USER=’slaveuser\u2019,<\/p>\n

      MASTER_PASSWORD=\u2019password\u2019,<\/p>\n

      MASTER_LOG_FILE=’mysql-bin.000001\u2019,<\/p>\n

      MASTER_LOG_POS=107;<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n\n\n\n\n
      3. <\/b>Start the thread on the slave<\/strong><\/td>\n<\/tr>\n
      mysql> START SLAVE;<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

      Formats of Replication<\/strong><\/p>\n

        \n
      • SBR (statement-based replication)<\/li>\n
      • RBR (row-based replication)<\/li>\n
      • MFL (mixed format logging)<\/li>\n<\/ul>\n

        [myslqd]<\/p>\n

        binlog_format=\u2026<\/p>\n

        Master\/Slave \u2013 multiple slaves<\/strong><\/p>\n

        \"2\"<\/p>\n

          \n
        • Usefull with few writes and many reads<\/li>\n
        • Different slave for different roles (indexes, storage engines)<\/li>\n
        • Slave as standby master (with no traffic than replication)<\/li>\n
        • One of slaves backup, training, development<\/li>\n
        • Problems:<\/li>\n<\/ul>\n

          \u2013Does not protect from DROP TABLE operation<\/p>\n

          \u2013No guarantee that data from Master will be transferred correctly on Slave<\/p>\n

          Master\/Master<\/strong><\/p>\n

          \"3\"<\/p>\n

            \n
          • Each server is Master and Slave of the other<\/li>\n
          • e.g. 2 geographically separated offices. Each needs own locally writable copy of data<\/li>\n
          • Problems:<\/li>\n<\/ul>\n

            \u2013Simultaneously changes the same row<\/p>\n

            \u2013Insert at the same time into table with AUTO_INCREMENT column<\/p>\n","protected":false},"excerpt":{"rendered":"

            This article is a fraction of a Number of Articles on MySQL, to access them click here. Replication Replication is the ability to duplicate data changes that occur on one server to another. Basic components Servers Master, Slave, Relay Slave Configuration files Threads Binlog dump thread, I\/O Thread, SQL Thread<\/p>\n","protected":false},"author":3,"featured_media":285,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":true,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2},"jetpack_post_was_ever_published":false},"categories":[3,5],"tags":[],"class_list":["post-281","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-tutorial-how-to","category-knowledgebase"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"https:\/\/i0.wp.com\/www.virtono.com\/community\/wp-content\/uploads\/2016\/08\/MySQL_Replication1.png?fit=750%2C255&ssl=1","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p7ISfL-4x","jetpack_likes_enabled":true,"jetpack-related-posts":[{"id":338,"url":"https:\/\/www.virtono.com\/community\/knowledgebase\/articles-on-mysql\/","url_meta":{"origin":281,"position":0},"title":"Articles on MySQL","author":"Daniel Draga","date":"August 7, 2016","format":false,"excerpt":"MySQL:\u00a0 Introduction and How it Works MySQL Installation MySQL replication The Top MySQL 10 Design Tips MySQL Performance Tuning: The stages of Tuning MySQL Indexing How to create an index How MySQL and PHP Work Together How to handle MySQL errors Constraints in SQL and How to Specify Them","rel":"","context":"In "Knowledgebase"","block_context":{"text":"Knowledgebase","link":"https:\/\/www.virtono.com\/community\/category\/knowledgebase\/"},"img":{"alt_text":"","src":"https:\/\/i0.wp.com\/www.virtono.com\/community\/wp-content\/uploads\/2016\/08\/MySQL-Foreign-Key-EXAMPLE-2-1.png?fit=1200%2C675&ssl=1&resize=350%2C200","width":350,"height":200,"srcset":"https:\/\/i0.wp.com\/www.virtono.com\/community\/wp-content\/uploads\/2016\/08\/MySQL-Foreign-Key-EXAMPLE-2-1.png?fit=1200%2C675&ssl=1&resize=350%2C200 1x, https:\/\/i0.wp.com\/www.virtono.com\/community\/wp-content\/uploads\/2016\/08\/MySQL-Foreign-Key-EXAMPLE-2-1.png?fit=1200%2C675&ssl=1&resize=525%2C300 1.5x, https:\/\/i0.wp.com\/www.virtono.com\/community\/wp-content\/uploads\/2016\/08\/MySQL-Foreign-Key-EXAMPLE-2-1.png?fit=1200%2C675&ssl=1&resize=700%2C400 2x, https:\/\/i0.wp.com\/www.virtono.com\/community\/wp-content\/uploads\/2016\/08\/MySQL-Foreign-Key-EXAMPLE-2-1.png?fit=1200%2C675&ssl=1&resize=1050%2C600 3x"},"classes":[]},{"id":732,"url":"https:\/\/www.virtono.com\/community\/knowledgebase\/hardening-the-defense-of-database-server\/","url_meta":{"origin":281,"position":1},"title":"Hardening the Defense of Database Server","author":"Daniel Draga","date":"November 1, 2016","format":false,"excerpt":"Importance of Database Security: Databases often store sensitive data Incorrect data or loss of data could negatively affect business operations Databases can be used as bases to attack other systems from. \u00a0 Principles of Finding Holes Don't believe the documentation Implement your own client Debug the system to understand how\u2026","rel":"","context":"In "Knowledgebase"","block_context":{"text":"Knowledgebase","link":"https:\/\/www.virtono.com\/community\/category\/knowledgebase\/"},"img":{"alt_text":"","src":"https:\/\/i0.wp.com\/www.virtono.com\/community\/wp-content\/uploads\/2016\/11\/network-security1.jpg?fit=1200%2C556&ssl=1&resize=350%2C200","width":350,"height":200,"srcset":"https:\/\/i0.wp.com\/www.virtono.com\/community\/wp-content\/uploads\/2016\/11\/network-security1.jpg?fit=1200%2C556&ssl=1&resize=350%2C200 1x, https:\/\/i0.wp.com\/www.virtono.com\/community\/wp-content\/uploads\/2016\/11\/network-security1.jpg?fit=1200%2C556&ssl=1&resize=525%2C300 1.5x, https:\/\/i0.wp.com\/www.virtono.com\/community\/wp-content\/uploads\/2016\/11\/network-security1.jpg?fit=1200%2C556&ssl=1&resize=700%2C400 2x, https:\/\/i0.wp.com\/www.virtono.com\/community\/wp-content\/uploads\/2016\/11\/network-security1.jpg?fit=1200%2C556&ssl=1&resize=1050%2C600 3x"},"classes":[]},{"id":3496,"url":"https:\/\/www.virtono.com\/community\/tutorial-how-to\/how-to-tune-your-mysql-server-for-optimal-performance\/","url_meta":{"origin":281,"position":2},"title":"How to Tune Your MySQL Server for Optimal Performance","author":"George B.","date":"June 12, 2023","format":false,"excerpt":"In this step-by-step guide, we will walk you through the process of tuning your MySQL server, helping you enhance its efficiency and responsiveness. MySQL is one of the most popular open-source relational database management systems, widely used for various applications and websites. However, as your database grows in size and\u2026","rel":"","context":"In "Tutorials"","block_context":{"text":"Tutorials","link":"https:\/\/www.virtono.com\/community\/category\/tutorial-how-to\/"},"img":{"alt_text":"","src":"https:\/\/i0.wp.com\/www.virtono.com\/community\/wp-content\/uploads\/2023\/06\/1.png?fit=600%2C330&ssl=1&resize=350%2C200","width":350,"height":200,"srcset":"https:\/\/i0.wp.com\/www.virtono.com\/community\/wp-content\/uploads\/2023\/06\/1.png?fit=600%2C330&ssl=1&resize=350%2C200 1x, https:\/\/i0.wp.com\/www.virtono.com\/community\/wp-content\/uploads\/2023\/06\/1.png?fit=600%2C330&ssl=1&resize=525%2C300 1.5x"},"classes":[]},{"id":1030,"url":"https:\/\/www.virtono.com\/community\/tutorial-how-to\/how-to-install-and-use-mysql-on-debian-7\/","url_meta":{"origin":281,"position":3},"title":"How To Install and Use MySQL on Debian 7","author":"Daniel Draga","date":"September 24, 2017","format":false,"excerpt":"Introduction MYSQL is a software, with MySQL server at its core, and a lot of utility programs, that helps is managing and administration of database. For example, let's say you want to create a new database, you send a message to the MySQL server that says, for instance, \u201cCreate a\u2026","rel":"","context":"In "Tutorials"","block_context":{"text":"Tutorials","link":"https:\/\/www.virtono.com\/community\/category\/tutorial-how-to\/"},"img":{"alt_text":"","src":"https:\/\/i0.wp.com\/www.virtono.com\/community\/wp-content\/uploads\/2017\/09\/mysql-backup.png?fit=394%2C315&ssl=1&resize=350%2C200","width":350,"height":200},"classes":[]},{"id":1068,"url":"https:\/\/www.virtono.com\/community\/knowledgebase\/mongodb-and-nosql-databases\/","url_meta":{"origin":281,"position":4},"title":"MongoDB And NoSQL Databases","author":"Shreyash Sharma","date":"October 9, 2017","format":false,"excerpt":"Introduction MongoDB it is a document-oriented database. It is a free and open-source database. It does not rely on a traditional table-based relational database structure that\u2019s why it is classified as a NoSQL database. Instead it uses JSON-like documents with dynamic schemas. Before you add data to database MongoDB does\u2026","rel":"","context":"In "Knowledgebase"","block_context":{"text":"Knowledgebase","link":"https:\/\/www.virtono.com\/community\/category\/knowledgebase\/"},"img":{"alt_text":"","src":"https:\/\/i0.wp.com\/www.virtono.com\/community\/wp-content\/uploads\/2017\/10\/mongodb.jpeg?fit=1040%2C560&ssl=1&resize=350%2C200","width":350,"height":200,"srcset":"https:\/\/i0.wp.com\/www.virtono.com\/community\/wp-content\/uploads\/2017\/10\/mongodb.jpeg?fit=1040%2C560&ssl=1&resize=350%2C200 1x, https:\/\/i0.wp.com\/www.virtono.com\/community\/wp-content\/uploads\/2017\/10\/mongodb.jpeg?fit=1040%2C560&ssl=1&resize=525%2C300 1.5x, https:\/\/i0.wp.com\/www.virtono.com\/community\/wp-content\/uploads\/2017\/10\/mongodb.jpeg?fit=1040%2C560&ssl=1&resize=700%2C400 2x"},"classes":[]},{"id":294,"url":"https:\/\/www.virtono.com\/community\/knowledgebase\/mysql-performance-tuning-the-stages-of-tuning\/","url_meta":{"origin":281,"position":5},"title":"MySQL Performance Tuning: The stages of Tuning","author":"Daniel Draga","date":"August 7, 2016","format":false,"excerpt":"This article is a fraction of a Number of Articles on MySQL, to access them click here. Why Tune a Database? Cost-effectiveness A system that is tuned can minimize the need to buy additional hardware and other resources to meet the needs of the end users. Tuning may demonstrate that\u2026","rel":"","context":"In "Knowledgebase"","block_context":{"text":"Knowledgebase","link":"https:\/\/www.virtono.com\/community\/category\/knowledgebase\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]}],"_links":{"self":[{"href":"https:\/\/www.virtono.com\/community\/wp-json\/wp\/v2\/posts\/281","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.virtono.com\/community\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.virtono.com\/community\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.virtono.com\/community\/wp-json\/wp\/v2\/users\/3"}],"replies":[{"embeddable":true,"href":"https:\/\/www.virtono.com\/community\/wp-json\/wp\/v2\/comments?post=281"}],"version-history":[{"count":5,"href":"https:\/\/www.virtono.com\/community\/wp-json\/wp\/v2\/posts\/281\/revisions"}],"predecessor-version":[{"id":3534,"href":"https:\/\/www.virtono.com\/community\/wp-json\/wp\/v2\/posts\/281\/revisions\/3534"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.virtono.com\/community\/wp-json\/wp\/v2\/media\/285"}],"wp:attachment":[{"href":"https:\/\/www.virtono.com\/community\/wp-json\/wp\/v2\/media?parent=281"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.virtono.com\/community\/wp-json\/wp\/v2\/categories?post=281"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.virtono.com\/community\/wp-json\/wp\/v2\/tags?post=281"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}