{"id":1185,"date":"2025-05-06T16:48:05","date_gmt":"2025-05-06T16:48:05","guid":{"rendered":"https:\/\/humtechno.com\/master\/?p=1185"},"modified":"2025-05-06T20:03:08","modified_gmt":"2025-05-06T20:03:08","slug":"mysql-group-replication-and-multi-master-nodes","status":"publish","type":"post","link":"https:\/\/humtechno.com\/master\/2025\/05\/06\/mysql-group-replication-and-multi-master-nodes\/","title":{"rendered":"MYSQL Group Replication and Multi Master Nodes"},"content":{"rendered":"\n
\nhttps:\/\/youtu.be\/C_K-qtUumPI\n<\/div><\/figure>\n\n\n\n\n\n\n\n

Download Free PDF<\/a><\/p>\n\n\n\n

Introduction<\/strong><\/h2>\n\n\n\n

MySQL Group Replication<\/strong> is a high-availability solution that enables multiple MySQL server instances (a group<\/em>) to synchronize data automatically, forming a fault-tolerant, distributed database system. It supports both single-primary<\/strong> and multi-primary<\/strong> modes.<\/strong><\/p>\n\n\n\n

Key Features<\/strong><\/p>\n\n\n\n

    \n
  • Synchronous replication:<\/strong> Transactions are replicated to all members before commit (using a group communication system).<\/li>\n\n\n\n
  • Automatic failover:<\/strong> In single-primary mode, if the current primary fails, a new one is elected.<\/li>\n\n\n\n
  • Conflict detection\/resolution:<\/strong> In multi-primary mode, it handles conflicts if two nodes try to update the same data.<\/li>\n<\/ul>\n\n\n\n

    Use Cases<\/h3>\n\n\n\n
      \n
    • High availability and disaster recovery.<\/li>\n\n\n\n
    • Multi-datacenter deployments (with care).<\/li>\n\n\n\n
    • Applications needing consistency and auto failover without external tools.<\/li>\n<\/ul>\n\n\n\n

      Architecture<\/h3>\n\n\n\n

      Each MySQL server (node) in the group:<\/p>\n\n\n\n

        \n
      • Maintains its own copy of the data.<\/li>\n\n\n\n
      • Communicates over the network with other members via group communication<\/strong>.<\/li>\n\n\n\n
      • Uses write sets<\/strong> and certification<\/strong> to ensure consistency.<\/li>\n<\/ul>\n\n\n\n

        Modes<\/h3>\n\n\n\n
          \n
        1. Single-primary mode (default)<\/strong>
          1. One node handles writes.<\/li><\/ol>\n
              \n
            1. Others are read-only unless the primary fails.<\/li>\n<\/ol>\n<\/li>\n\n\n\n
            2. Multi-primary mode<\/strong>
              1. All nodes can handle writes.<\/li><\/ol>\n
                  \n
                1. Conflict detection is necessary.<\/li>\n<\/ol>\n<\/li>\n<\/ol>\n\n\n\n

                  How to Configure<\/u><\/strong><\/p>\n\n\n\n

                  Steps for Configure Group Replication with Multi Master Nodes<\/strong><\/p>\n\n\n\n

                    \n
                  1. Install Operating System on All Nodes<\/li>\n\n\n\n
                  2. Define IP Address for Each Node<\/li>\n\n\n\n
                  3. Define Host Name of Each Nodes<\/li>\n\n\n\n
                  4. Enter Host Name of All Node in each Node etc\/hosts<\/li>\n\n\n\n
                  5. Download MYSQL RPM<\/li>\n\n\n\n
                  6. Install MYSQL RPM<\/li>\n\n\n\n
                  7. Define Table Case sensitive in mysql Configure File<\/li>\n\n\n\n
                  8. Start MYSQL services<\/li>\n\n\n\n
                  9. Secure MYSQL Database<\/li>\n\n\n\n
                  10. Create Users Required for Replication<\/li>\n\n\n\n
                  11. Define Configure Parameter on Each Node<\/li>\n\n\n\n
                  12. Stop Firewall<\/li>\n\n\n\n
                  13. Disable SELINUX<\/li>\n\n\n\n
                  14. Restart MYSQL Services<\/li>\n\n\n\n
                  15. Define Replication Source<\/li>\n\n\n\n
                  16. Start Group Replication on Node 1 with group_replication_bootstrap_group=ON;<\/li>\n\n\n\n
                  17. Start Group Replication on Node 2 and Node 3<\/li>\n\n\n\n
                  18. Enable Read Only Node on node 2 and Node 3<\/li>\n\n\n\n
                  19. Monitor Group Replication<\/li>\n<\/ol>\n\n\n\n

                    On Each Database Nodes<\/u><\/strong><\/p>\n\n\n\n

                    vi \/etc\/hosts<\/p>\n\n\n\n

                    127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4<\/p>\n\n\n\n

                    192.168.179.128 mysql1<\/p>\n\n\n\n

                    192.168.179.129 mysql2<\/p>\n\n\n\n

                    192.168.179.130 mysql3<\/p>\n\n\n\n

                    On Node1 Database<\/u><\/strong><\/p>\n\n\n\n

                    vi \/etc\/hostname<\/p>\n\n\n\n

                    mysql1<\/p>\n\n\n\n

                    On Node2 Database<\/u><\/strong><\/p>\n\n\n\n

                    vi \/etc\/hostname<\/p>\n\n\n\n

                    mysql2<\/p>\n\n\n\n

                    On Node3 Database<\/u><\/strong><\/p>\n\n\n\n

                    vi \/etc\/hostname<\/p>\n\n\n\n

                    mysql3<\/p>\n\n\n\n

                    On Each Database Nodes<\/u><\/strong><\/p>\n\n\n\n

                    mkdir -p \/software<\/p>\n\n\n\n

                    cd \/software<\/p>\n\n\n\n

                    wget https:\/\/repo.mysql.com\/mysql80-community-release-el8-3.noarch.rpm<\/p>\n\n\n\n

                    rpm -ivh mysql80-community-release-el8-3.noarch.rpm<\/p>\n\n\n\n

                    yum install mysql-server \u2013y<\/p>\n\n\n\n

                    Define Table Case sensitive in mysql Configure File on Each Database Nodes<\/u><\/strong><\/p>\n\n\n\n

                    vi \/etc\/my.cnf<\/p>\n\n\n\n

                    [mysqld]<\/p>\n\n\n\n

                    lower_case_table_names = 1<\/p>\n\n\n\n

                    systemctl start mysqld<\/p>\n\n\n\n

                    systemctl enable mysqld<\/p>\n\n\n\n

                    Secure MYSQL Database<\/u><\/strong><\/p>\n\n\n\n

                    mysql_secure_installation<\/p>\n\n\n\n

                    Create Users Required for Replication on Each Node<\/u><\/strong><\/p>\n\n\n\n

                    mysql -uroot -pOracle123<\/p>\n\n\n\n

                    CREATE USER ‘root’@’%’ IDENTIFIED BY ‘Oracle123’;<\/p>\n\n\n\n

                    GRANT ALL PRIVILEGES ON *.* TO ‘root’@’%’ WITH GRANT OPTION;<\/p>\n\n\n\n

                    FLUSH PRIVILEGES;<\/p>\n\n\n\n

                    CREATE USER ‘rpl_user’@’%’ IDENTIFIED WITH mysql_native_password BY ‘Oracle123’;<\/p>\n\n\n\n

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

                    GRANT CONNECTION_ADMIN ON *.* TO ‘rpl_user’@’%’;<\/p>\n\n\n\n

                    GRANT BACKUP_ADMIN ON *.* TO ‘rpl_user’@’%’;<\/p>\n\n\n\n

                    GRANT GROUP_REPLICATION_STREAM ON *.* TO ‘rpl_user’@’%’;<\/p>\n\n\n\n

                    GRANT ALL PRIVILEGES ON *.* TO ‘rpl_user’@’%’;<\/p>\n\n\n\n

                    GRANT REPLICATION SLAVE, BACKUP_ADMIN, CONNECTION_ADMIN ON *.* TO ‘rpl_user’@’%’;<\/p>\n\n\n\n

                    FLUSH PRIVILEGES;<\/p>\n\n\n\n

                    Define Configure Parameter on Each Node<\/u><\/strong><\/p>\n\n\n\n

                    vi \/etc\/my.cnf<\/p>\n\n\n\n

                    [mysqld]<\/p>\n\n\n\n

                    lower_case_table_names = 1<\/p>\n\n\n\n

                    disabled_storage_engines=”MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY”<\/p>\n\n\n\n

                    server_id=1 -\u2013 Change on Each Node<\/strong><\/p>\n\n\n\n

                    gtid_mode=ON<\/p>\n\n\n\n

                    enforce_gtid_consistency=ON<\/p>\n\n\n\n

                    plugin_load_add = “group_replication.so”<\/p>\n\n\n\n

                    group_replication_group_name=”aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa”<\/p>\n\n\n\n

                    group_replication_start_on_boot=OFF<\/p>\n\n\n\n

                    group_replication_local_address= “192.168.179.128:33061” -\u2013 Change on Each Node<\/strong><\/p>\n\n\n\n

                    group_replication_group_seeds= “192.168.179.128:33061,192.168.179.129:33061,192.168.179.130:33061”<\/p>\n\n\n\n

                    group_replication_bootstrap_group=OFF<\/p>\n\n\n\n

                    group_replication_ip_allowlist= “192.168.179.128,192.168.179.129,192.168.179.130”<\/p>\n\n\n\n

                    group_replication_single_primary_mode = OFF  — For Multi Master Nodes<\/p>\n\n\n\n

                    lower_case_table_names = 1<\/p>\n\n\n\n

                    Stop Firewall and Disable SELINUX<\/u><\/strong><\/p>\n\n\n\n

                    systemctl stop firewalld<\/p>\n\n\n\n

                    chkconfig firewalld off<\/p>\n\n\n\n

                    setenforce 0<\/p>\n\n\n\n

                    vi \/etc\/sysconfig\/selinux<\/p>\n\n\n\n

                    ELINUX=disabled<\/p>\n\n\n\n

                    systemctl restart mysqld<\/p>\n\n\n\n

                    Define Replication Source<\/u><\/strong><\/p>\n\n\n\n

                    mysql -uroot -pOracle123<\/p>\n\n\n\n

                    CHANGE REPLICATION SOURCE TO SOURCE_USER=’rpl_user’,  SOURCE_PASSWORD=’Oracle123′      FOR CHANNEL ‘group_replication_recovery’;<\/p>\n\n\n\n

                    Start Group Replication on Node 1<\/u><\/strong><\/p>\n\n\n\n

                    mysql -uroot -pOracle123<\/p>\n\n\n\n

                    SET GLOBAL group_replication_bootstrap_group=ON;<\/p>\n\n\n\n

                    START GROUP_REPLICATION;<\/p>\n\n\n\n

                    SET GLOBAL group_replication_bootstrap_group=OFF;<\/p>\n\n\n\n

                    Start Group Replication on Node 2 and Node 3<\/u><\/strong><\/p>\n\n\n\n

                    mysql -uroot -pOracle123<\/p>\n\n\n\n

                    START GROUP_REPLICATION;<\/p>\n\n\n\n

                    Enable Read Only on Node 2 and Node 3<\/u><\/strong><\/p>\n\n\n\n

                    SET GLOBAL super_read_only = ON;<\/p>\n\n\n\n

                    Monitor on Group Replication<\/u><\/strong><\/p>\n\n\n\n

                    SELECT * FROM performance_schema.replication_group_members;<\/p>\n\n\n","protected":false},"excerpt":{"rendered":"","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_siteseo_robots_primary_cat":"17","pagelayer_contact_templates":[],"_pagelayer_content":"","neve_meta_sidebar":"","neve_meta_container":"","neve_meta_enable_content_width":"","neve_meta_content_width":0,"neve_meta_title_alignment":"","neve_meta_author_avatar":"","neve_post_elements_order":"","neve_meta_disable_header":"","neve_meta_disable_footer":"","neve_meta_disable_title":"","_themeisle_gutenberg_block_has_review":false,"footnotes":""},"categories":[15],"tags":[30,31,32],"class_list":["post-1185","post","type-post","status-publish","format-standard","hentry","category-mysql","tag-mysql-cluster","tag-mysql-group-replication","tag-mysql-multi-master"],"_links":{"self":[{"href":"https:\/\/humtechno.com\/master\/wp-json\/wp\/v2\/posts\/1185","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/humtechno.com\/master\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/humtechno.com\/master\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/humtechno.com\/master\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/humtechno.com\/master\/wp-json\/wp\/v2\/comments?post=1185"}],"version-history":[{"count":3,"href":"https:\/\/humtechno.com\/master\/wp-json\/wp\/v2\/posts\/1185\/revisions"}],"predecessor-version":[{"id":1192,"href":"https:\/\/humtechno.com\/master\/wp-json\/wp\/v2\/posts\/1185\/revisions\/1192"}],"wp:attachment":[{"href":"https:\/\/humtechno.com\/master\/wp-json\/wp\/v2\/media?parent=1185"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/humtechno.com\/master\/wp-json\/wp\/v2\/categories?post=1185"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/humtechno.com\/master\/wp-json\/wp\/v2\/tags?post=1185"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}