Group Replicate using swarm network only

Ref ​here

1. build image from Dockerfile with tag

   $ docker build -t mysqlubuntu .
   

2. create docker swarm network for attachable

   $ docker network create --driver overlay --attachable group1
   

3. download mysql-8.0.11 from ​here select Linux - Generic (glibc 2.12) (x86, 64-bit), TAR. Then unzip and place in folder. In this test, we put in /home/ubuntu/GR_2/mysql-8.0.11. We start.sh script in this attach file.

   $ chmod -R 777 /home/ubuntu/GR_2/mysql-8.0.11
   $ ./start.sh
   

4. access to node1 with

   $ docker exec -it node1 ./bin/mysql -uroot --socket=/tmp/mysql.0.sock
   

5. set node1 as primary node

   create user 'repl'@'%';
   GRANT ALL  ON * . * TO repl@'%';
   flush privileges;
   SET @@GLOBAL.group_replication_group_name='aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee';
   SET @@GLOBAL.group_replication_local_address='node1:6606';
   SET @@GLOBAL.group_replication_group_seeds='node1:6606,node2:6606,node3:6606';
   SET @@GLOBAL.group_replication_bootstrap_group=1;
   change master to master_user='repl' for channel 'group_replication_recovery';
   START GROUP_REPLICATION;
   SET @@GLOBAL.group_replication_bootstrap_group=0;
   SELECT * FROM performance_schema.replication_group_members;
   

6. start node2 with ./start.sh, pls make change on --name node2, --hostname node2, -e DATADIR="d2", -e SERVERID=2. Then, exec on node2

   $ docker exec -it node2 ./bin/mysql -uroot --socket=/tmp/mysql.0.sock
   

7. set group replicate on node2

   SET @@GLOBAL.group_replication_group_name='aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee';
   SET @@GLOBAL.group_replication_local_address='node2:6606';
   SET @@GLOBAL.group_replication_group_seeds='node1:6606,node2:6606,node3:6606';
   SET @@GLOBAL.group_replication_bootstrap_group=0;
   SET @@global.group_replication_recovery_retry_count=5;
   change master to master_user='repl' for channel 'group_replication_recovery';
   START GROUP_REPLICATION;
   SELECT * FROM performance_schema.replication_group_members;
   

8. start node3 same as in step 6, 7, then set group replicate on node3

   SET @@GLOBAL.group_replication_group_name='aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee';
   SET @@GLOBAL.group_replication_local_address='node3:6606';
   SET @@GLOBAL.group_replication_group_seeds='node1:6606,node2:6606,node3:6606';
   SET @@GLOBAL.group_replication_bootstrap_group=0;
   SET @@global.group_replication_recovery_retry_count=5;
   change master to master_user='repl' for channel 'group_replication_recovery';
   START GROUP_REPLICATION;
   SELECT * FROM performance_schema.replication_group_members;
   

9. check status all node with mysql command.

   mysql> SELECT * FROM performance_schema.replication_group_members;
   

10. create database and table need ref ​here
    • InnoDB Storage Engine: data must be stored in the InnoDB transactional storage engine.
    • Primary Keys: every table that is to be replicated by the group must have an explicit primary key defined.

Now, if you know InnoDB, when there is no PK defined, InnoDB will use the first NOT NULL UNIQUE KEY as PK. How will Group Replication handle that ?

Let’s verify:

mysql> create table test_tbl_nopk_uniq_notnull (id int not null unique key, name varchar(10));
mysql> insert into test_tbl_nopk_uniq_notnull values (1,'lefred'); 
Query OK, 1 row affected (0.01 sec)

Or we can create auto increment key

mysql> CREATE TABLE products (
  product_id int(11) NOT NULL AUTO_INCREMENT,
  product_name varchar(150) NOT NULL,
  PRIMARY KEY (`product_id`)
);

mysql> INSERT INTO products(product_name) VALUES("Shoes"),("Shirt"),("Trouser");

11. mysqldump and restore, pls login to docker exec -it node1 bash to get in a bash shell, then mysqldump a database testdb to a file on upper directory that we mount.

    rpl_user@node1:/mysql/bin$ ./mysqldump -uroot --protocol=socket  --set-gtid-purged=OFF --socket=/tmp/mysql.0.sock testdb > ../testdb.sql
    

12. before we restore, create database testdb with mysql command first then, we can restore with following command.

    rpl_user@node1:/mysql/bin$ ./mysql -uroot --protocol=socket --socket=/tmp/mysql.0.sock testdb < ../testdb.sql