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

   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;"
   

We also do above command with node3 (replicate node)

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                       |
   +---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+----------------------------+
   root@n3:/home/ubuntu#