Group Replicate

Ref ​here

We use docker swarm 3 nodes

1. pull image to 3 docker nodes

   $ docker pull mysql/mysql-server:8.0
   

2. Using docker-compose.yml file in atttached here and command docker-compose stack deploy -c docker-compose.yml db

   $ docker-compose stack deploy -c docker-compose.yml db
   
   Ignoring unsupported options: restart
   Ignoring deprecated options:
   container_name: Setting the container name is not supported.
   
   Creating network db_mysqlgr
   Creating service db_node2
   Creating service db_node3
   Creating service db_node1
   

3. Check with docker stack ps db

   root@n1:/home/ubuntu/GR# docker stack ps db
   ID             NAME         IMAGE                    NODE      DESIRED STATE   CURRENT STATE                ERROR     PORTS
   z8tarrgj0isq   db_node1.1   mysql/mysql-server:8.0   n3        Running         Running 50 seconds ago                 
   xt8vagtlz79c   db_node2.1   mysql/mysql-server:8.0   n2        Running         Running about a minute ago             
   ve5o54jtwc4v   db_node3.1   mysql/mysql-server:8.0   n1        Running         Running about a minute ago          
   

4. On master node1, in command pls change node1 to docker id ex. 26b55aaad407 which can be checked with docker ps on swarm node n3

   docker exec -it node1 mysql -uroot -pmypass \
     -e "SET @@GLOBAL.group_replication_bootstrap_group=1;" \
     -e "create user 'repl'@'%';" \
     -e "GRANT REPLICATION SLAVE ON *.* TO repl@'%';" \
     -e "flush privileges;" \
     -e "change master to master_user='repl' for channel 'group_replication_recovery';" \
     -e "START GROUP_REPLICATION;" \
     -e "SET @@GLOBAL.group_replication_bootstrap_group=0;" \
     -e "SELECT * FROM performance_schema.replication_group_members;"
   

5. On replicate node2, node3, pls change node2 to docker id ex. ac87f41b140c which can be checked with docker ps on swarm node n1, n2. We also do above command with node3 (replicate node)

   root@n1:/home/ubuntu/GR# docker exec -it ac87f41b140c mysql -uroot -pmypass \
      -e "change master to master_user='repl' for channel 'group_replication_recovery';" \
      -e "START GROUP_REPLICATION;"
   

6. Check all nodes status on node1

   root@n3:/home/ubuntu# docker exec -it 26 mysql -uroot -pmypass   -e "SELECT * FROM performance_schema.replication_group_members;"
   mysql: [Warning] Using a password on the command line interface can be insecure.
   +---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+----------------------------+
   | CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION | MEMBER_COMMUNICATION_STACK |
   +---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+----------------------------+
   | group_replication_applier | 852f597b-e92c-11ec-9238-02420a000b03 | node2       |        3306 | ONLINE       | SECONDARY   | 8.0.29         | XCom                       |
   | group_replication_applier | 89a10b8c-e92c-11ec-9362-02420a000b06 | node3       |        3306 | ONLINE       | SECONDARY   | 8.0.29         | XCom                       |
   | group_replication_applier | 8ef43b09-e92c-11ec-936c-02420a000b09 | node1       |        3306 | ONLINE       | PRIMARY     | 8.0.29         | XCom                       |
   +---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+----------------------------+
   

7. Create TEST database with table on node1

   root@n3:/home/ubuntu# docker exec -it 26 mysql -uroot -pmypass   -e "create database TEST; use TEST; CREATE TABLE t1 (id INT NOT NULL PRIMARY KEY) ENGINE=InnoDB; show tables;"
   mysql: [Warning] Using a password on the command line interface can be insecure.
   +----------------+
   | Tables_in_TEST |
   +----------------+
   | t1             |
   +----------------+
   

8. Check on node2, node3 to see that data had been replicated to node2, node3

   root@n2:/home/ubuntu# docker exec -it 39 mysql -uroot -pmypass -e "use TEST; show tables;"
   mysql: [Warning] Using a password on the command line interface can be insecure.
   +----------------+
   | Tables_in_TEST |
   +----------------+
   | t1             |
   +----------------+
   root@n2:/home/ubuntu#