![(please configure the [header_logo] section in trac.ini)](/Utils/chrome/site/your_project_logo.png){kind=logo}
| Version 17 (modified by , 2 years ago) (diff) |
|---|
GROUP Replicate master-master
All node can write (primary)
On nodedb1, just start docker with, pls download mysql-8.0.11 source
docker run -d --name=nodedb1 --net=group1 --hostname=nodedb1 -p 3306:3306 \
-v $PWD/mysql-8.0.11/d1:/var/lib/mysql \
-e MYSQL_ROOT_PASSWORD=mypass mysql/mysql-server:8.0.11 \
--server-id=1 \
--log-bin='mysql-bin-1.log' \
--enforce-gtid-consistency='ON' \
--log-slave-updates='ON' \
--gtid-mode='ON' \
--transaction-write-set-extraction='XXHASH64' \
--binlog-checksum='NONE' \
--master-info-repository='TABLE' \
--relay-log-info-repository='TABLE' \
--plugin-load='group_replication.so' \
--relay-log-recovery='ON' \
--group-replication-start-on-boot='OFF' \
--group-replication-group-name='aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee' \
--group-replication-local-address="nodedb1:33061" \
--group-replication-group-seeds='nodedb1:33061,nodedb2:33061,nodedb3:33061' \
--loose-group-replication-single-primary-mode='OFF' \
--loose-group-replication-enforce-update-everywhere-checks='ON'
Then start mysql with docker exec -it nodedb1 -uroot -pmypass
mysql> 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='nodedb1:33061';
SET @@GLOBAL.group_replication_group_seeds='nodedb1:33061,nodedb2:33061,nodedb3:33061';
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;
On nodedb1, we need to set the group_replication_bootstrap_group variable tells a member that it shouldn’t expect to receive information from peers and should instead establish a new group and elect itself the primary member. You can turn this variable on with the following command:
mysql> SET GLOBAL group_replication_bootstrap_group=ON;
Then you can start replication for the initial group member:
mysql> START GROUP_REPLICATION;
Following that, you can set the group_replication_bootstrap_group variable back to OFF, since the only situation where this is appropriate is when there are no existing group members:
mysql> SET GLOBAL group_replication_bootstrap_group=OFF;
The group will be started with this server as the only member. Verify this by checking the entries within the replication_group_members table in the performance_schema database:
mysql> SELECT * FROM performance_schema.replication_group_members;
On nodedb2, start docker same as nodedb1 but change --name=nodedb2, --hostname=nodedb2, --server-id=2, -v $PWD/mysql-8.0.11/d2:/var/lib/mysql, --group-replication-local-address="nodedb2:33061"
mysql> SET @@GLOBAL.group_replication_group_name='aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee';
SET @@GLOBAL.group_replication_local_address='nodedb2:33061';
SET @@GLOBAL.group_replication_group_seeds='nodedb1:33061,nodedb2:33061,nodedb3:33061';
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;
On nodedb3
mysql> SET @@GLOBAL.group_replication_group_name='aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee';
SET @@GLOBAL.group_replication_local_address='nodedb3:33061';
SET @@GLOBAL.group_replication_group_seeds='nodedb1:33061,nodedb2:33061,nodedb3:33061';
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;
Add user and password without authenticates with caching_sha2_plugin. Just use WITH mysql_native_password
mysql> CREATE USER 'user1'@'%' IDENTIFIED WITH mysql_native_password BY 'password'; mysql> FLUSH PRIVILEGES;
Multiple hosts on Docker Container
At nodedb1 192.168.81.36 file start.sh where the network share with host ( --net=host )
node=1
docker run -d --name=nodedb$node --hostname=nodedb$node --net=host \
-v $PWD/mysql-8.0.11/d$node:/var/lib/mysql \
-e MYSQL_ROOT_PASSWORD=mypass mysql/mysql-server:8.0.11 \
--server-id=$node \
--bind-address="0.0.0.0" \
--report_host="192.168.81.36" \
--loose-group_replication_local_address="192.168.81.36:33061" \
--log-bin='mysql-bin-1.log' \
--enforce-gtid-consistency='ON' \
--log-slave-updates='ON' \
--gtid-mode='ON' \
--transaction-write-set-extraction='XXHASH64' \
--binlog-checksum='NONE' \
--master-info-repository='TABLE' \
--relay-log-info-repository='TABLE' \
--plugin-load='group_replication.so' \
--relay-log-recovery='ON' \
--group-replication-start-on-boot='OFF' \
--group-replication-group-name='aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee' \
--group-replication-local-address="192.168.81.36:33061" \
--group-replication-group-seeds='192.168.81.36:33061,192.168.81.11:33061,192.168.81.10:33061,192.168.81.12:33061' \
--loose-group-replication-single-primary-mode='OFF' \
--loose-group-replication-enforce-update-everywhere-checks='ON'
Then, on nodedb1 use mysql cmd
mysql> 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='192.168.81.36:33061';
SET @@GLOBAL.group_replication_group_seeds='192.168.81.36:33061,192.168.81.11:33061,192.168.81.10:33061,192.168.81.12:33061';
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;
On nodedb2 192.168.81.11, use mysql cmd to do
mysql> SET @@GLOBAL.group_replication_group_name='aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee';
SET @@GLOBAL.group_replication_local_address="192.168.81.11:33061";
SET @@GLOBAL.group_replication_group_seeds="192.168.81.36:33061,192.168.81.11:33061,192.168.81.10:33061,192.168.81.12:33061";
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;
On nodedb3 192.168.81.10, use mysql cmd to do
mysql> SET @@GLOBAL.group_replication_group_name='aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee';
SET @@GLOBAL.group_replication_local_address="192.168.81.10:33061";
SET @@GLOBAL.group_replication_group_seeds="192.168.81.36:33061,192.168.81.11:33061,192.168.81.10:33061,192.168.81.12:33061";
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;
On nodedb3 192.168.81.12, use mysql cmd to do
mysql> SET @@GLOBAL.group_replication_group_name='aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee';
SET @@GLOBAL.group_replication_local_address="192.168.81.12:33061";
SET @@GLOBAL.group_replication_group_seeds="192.168.81.36:33061,192.168.81.11:33061,192.168.81.10:33061,192.168.81.12:33061";
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;
