<>MGR concept
MGR（MySQL Group Replication）, yes MySQL stay 2016 year 12 A new high availability and high expansion solution launched in September
<> Why MGR
MySQL introduction MGR before , There are two traditional replication modes ,
* Asynchronous replication ;
* Semi synchronous replication
Asynchronous replication ：
* master Execute transactions , write in binlog journal , Then submit .
* slave receive binlog Log transactions and write transactions first relay journal , Then redo the transaction . When master Data inconsistency may occur during downtime .
Semi synchronous replication ：
* master Execute transactions , Write binary log , take binlog Transaction log delivered to slave,slave Received binlog After transaction log , Write it to relay
log, Then to master Return successful transmission ACK;
* master received ACK after , Then commit the transaction in the storage engine .（ Two submission methods , By parameter rpl_semi_sync_master_wait_point decision ）
Summarize the above points , The disadvantages are as follows ：
* Write operations are concentrated in MASTER On the server ;
* Data inconsistency may occur ;
* MASTER After downtime , You need to choose a new owner manually and give it to others again slave End execution change master.
<>MGR Functions provided
* synchronous copy ;
* Single master mode , Multi master mode ;
* Automatic switching ;
* Elastic expansion
<>MGR limit
* Only supported innodb engine ;
* All new tables must explicitly create primary keys ;
* The recommended isolation level is READ COMMITED, Multi master mode , I won't support it SERIALIZABLE;
* Multi master mode , Foreign keys are not recommended ;
* Need to open bin_log_format=row,log_slave_updates=ON;
* Need to open GTID;
* Need to install engine group_replication.so;
* MGR Maximum support 9 Nodes
<>MGR principle
Single master mode
Single master mode , Only one node can read and write , Other nodes provide read-only services . Single master mode , When the primary node goes down , Other nodes will automatically server_uuid Variables and group_replication_member_weight Variable value , Select next slave As master node ,group_replication_member_weight The member with the highest value of is selected as the new master node , This parameter defaults to 50, You can set different values on the node ; stay group_replication_member_weight With the same value ,group According to the data dictionary server_uuid sort , The top ranked node is selected as the primary node .
Multi master mode
Multi master mode , Pass in group replication Group Replication
Protocol Agreement and Paxos agreement , Overall high availability solution formed , At the same time increased certify Concept of , Be responsible for checking whether the transaction is allowed to be submitted , Whether there is conflict with other transactions ,Group
Replication It is a database cluster composed of multiple nodes , Each node can execute transactions independently , however read-write（RW） The operation of can only be performed after verification within the group commit,Read-only
(RO) Transactions can be executed immediately without verification , Before a transaction is committed on a node , Will automatically broadcast atomicity within the group , Tell other nodes what has changed / What transactions were performed , Then establish a global sort for the thing , final , This means that all servers receive the same set of transactions in the same order . therefore , All servers apply the same changeset in the same order , Therefore, they are consistent in the group .
In multi master mode , All members of this group are set to read-write mode , In multi master mode , I won't support it SERIALIZABLE Transaction isolation level , And cannot fully support cascading foreign key constraints .
<> Environmental preparation
1, be based on centos7 Virtual machine of the system （ Or ECS ）, Memory recommendations 4G above ;
2, Installed in advance docker environment , And start ;
<>docker build MRG step
1, Pull mysql8 image
docker pull mysql:8.0.20
2, establish docker private network
This is for fixing the containers at the back IP address , avoid IP Other problems caused by address changes
docker network create --subnet=172.72.0.0/24 mysql-network
3, Create a directory for storing data
mkdir -p /usr/local/mysql/lhrmgr15/conf.d mkdir -p
/usr/local/mysql/lhrmgr15/datamkdir -p /usr/local/mysql/lhrmgr16/conf.d mkdir
-p /usr/local/mysql/lhrmgr16/datamkdir -p /usr/local/mysql/lhrmgr17/conf.d mkdir
-p /usr/local/mysql/lhrmgr17/data
4, start-up 3 individual mysql container
Perform the following in sequence docker Command is enough , The directory name and container name can be changed in combination with their own conditions
First container ：
docker run -d --name mysql8020mgr33065 \ -h lhrmgr15 -p 33065:3306 --net=
mysql-network --ip172.72.0.15 \ -v
/usr/local/mysql/lhrmgr15/conf.d:/etc/mysql/conf.d -v
/usr/local/mysql/lhrmgr15/data:/var/lib/mysql/\ -e MYSQL_ROOT_PASSWORD=lhr \ -e
TZ=Asia/Shanghai \ mysql:8.0.20
Second container ：
docker run -d --name mysql8020mgr33066 \ -h lhrmgr16 -p 33066:3306 --net=
mysql-network --ip172.72.0.16 \ -v
/usr/local/mysql/lhrmgr16/conf.d:/etc/mysql/conf.d -v
/usr/local/mysql/lhrmgr16/data:/var/lib/mysql/\ -e MYSQL_ROOT_PASSWORD=lhr \ -e
TZ=Asia/Shanghai \ mysql:8.0.20
Third container ：
docker run -d --name mysql8020mgr33067 \ -h lhrmgr17 -p 33067:3306 --net=
mysql-network --ip172.72.0.17 \ -v
/usr/local/mysql/lhrmgr17/conf.d:/etc/mysql/conf.d -v
/usr/local/mysql/lhrmgr17/data:/var/lib/mysql/\ -e MYSQL_ROOT_PASSWORD=lhr \ -e
TZ=Asia/Shanghai \ mysql:8.0.20
4, View started containers
docker ps
5, Modify configuration parameters
Remember to use docker build mysql Process ? When we need to docker Container open binlog When logging , How is it done ? you 're right , Need one my.cnf file , next , Respectively on the top conf.d Under the directory , establish my.cnf file , Copy the following contents to their respective directories in turn my.cnf In the document ;
first my.cnf
[mysqld] user=mysql port=3306 character_set_server=utf8mb4 secure_file_priv=''
server-id= 802033065 default-time-zone = '+8:00' log_timestamps = SYSTEM log-bin
= binlog_format=row binlog_checksum=NONE log-slave-updates=1 skip-name-resolve
auto-increment-increment=2 auto-increment-offset=1 gtid-mode=ON
enforce-gtid-consistency=on default_authentication_plugin=mysql_native_password
max_allowed_packet= 500M master_info_repository=TABLE relay_log_info_repository=
TABLErelay_log=lhrmgr15-relay-bin-ip15 transaction_write_set_extraction=
XXHASH64 loose-group_replication_group_name=
"aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa" loose-group_replication_start_on_boot=
OFF loose-group_replication_local_address= "172.72.0.15:33061"
loose-group_replication_group_seeds=
"172.72.0.15:33061,172.72.0.16:33062,172.72.0.17:33063"
loose-group_replication_bootstrap_group=OFF loose-group_replication_ip_whitelist
="172.72.0.15,172.72.0.16,172.72.0.17" report_host=172.72.0.15 report_port=3306
the second my.cnf
[mysqld] user=mysql port=3306 character_set_server=utf8mb4 secure_file_priv=''
server-id= 802033066 default-time-zone = '+8:00' log_timestamps = SYSTEM log-bin
= binlog_format=row binlog_checksum=NONE log-slave-updates=1 gtid-mode=ON
enforce-gtid-consistency=ON skip_name_resolve default_authentication_plugin=
mysql_native_password max_allowed_packet= 500M master_info_repository=TABLE
relay_log_info_repository=TABLE relay_log=lhrmgr16-relay-bin-ip16
transaction_write_set_extraction=XXHASH64 loose-group_replication_group_name=
"aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa" loose-group_replication_start_on_boot=
OFF loose-group_replication_local_address= "172.72.0.16:33062"
loose-group_replication_group_seeds=
"172.72.0.15:33061,172.72.0.16:33062,172.72.0.17:33063"
loose-group_replication_bootstrap_group=OFF loose-group_replication_ip_whitelist
="172.72.0.15,172.72.0.16,172.72.0.17" report_host=172.72.0.16 report_port=3306
Third my.cnf
[mysqld] user=mysql port=3306 character_set_server=utf8mb4 secure_file_priv=''
server-id= 802033067 default-time-zone = '+8:00' log_timestamps = SYSTEM log-bin
= binlog_format=row binlog_checksum=NONE log-slave-updates=1 gtid-mode=ON
enforce-gtid-consistency=ON skip_name_resolve default_authentication_plugin=
mysql_native_password max_allowed_packet= 500M master_info_repository=TABLE
relay_log_info_repository=TABLE relay_log=lhrmgr16-relay-bin-ip16
transaction_write_set_extraction=XXHASH64 loose-group_replication_group_name=
"aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa" loose-group_replication_start_on_boot=
OFF loose-group_replication_local_address= "172.72.0.17:33063"
loose-group_replication_group_seeds=
"172.72.0.15:33061,172.72.0.16:33062,172.72.0.17:33063"
loose-group_replication_bootstrap_group=OFF loose-group_replication_ip_whitelist
="172.72.0.15,172.72.0.16,172.72.0.17" report_host=172.72.0.17 report_port=3306
6, restart 3 Containers
docker restart mysql8020mgr33065 mysql8020mgr33066 mysql8020mgr33067
7, Log in to each docker container , View the information of the next container
docker exec -it mysql8020mgr33065 /bin/bash mysql -uroot -plhr select
@@hostname,@@server_id,@@server_uuiddocker exec -it mysql8020mgr33066 /bin/bash
mysql -uroot -plhrselect @@hostname,@@server_id,@@server_uuid docker exec -it
mysql8020mgr33067 /bin/bash mysql -uroot -plhrselect
@@hostname,@@server_id,@@server_uuid
Take the first container as an example , The information queried is as follows ：
The above preparatory work is basically completed
<> install MGR plug-in unit （3 Execute in all containers ）
INSTALL PLUGIN group_replication SONAME 'group_replication.so';
View the plug-ins installed on the current container node , Description installation is complete
show plugins;
<> Set data replication account （3 Execute in all containers ）
Know through the principle explanation at the beginning , Single master mode , The data of each node is strongly consistent , So when data is written to a node , There must be copies of data between different nodes , Therefore, you need to create an account copied by the user , stay 3 Execute the following commands in turn in containers
SET SQL_LOG_BIN=0; CREATE USER repl@'%' IDENTIFIED BY 'lhr'; GRANT REPLICATION
SLAVE ON *.* TO repl@'%'; FLUSH PRIVILEGES; SET SQL_LOG_BIN=1; CHANGE MASTER TO
MASTER_USER='repl', MASTER_PASSWORD='lhr' FOR CHANNEL
'group_replication_recovery';
<> open MGR Single master mode
1, start-up MGR, In the main library (172.72.0.15) Execute on
SET GLOBAL group_replication_bootstrap_group=ON; START GROUP_REPLICATION; SET
GLOBALgroup_replication_bootstrap_group=OFF; -- see MGR Group information SELECT * FROM
performance_schema.replication_group_members;
2, The other two nodes join MGR
In slave Library （172.72.0.16,172.72.0.17） Execute on , completion of enforcement , Can view MGR Group information , Reflect the status of each node
START GROUP_REPLICATION; -- see MGR Group information SELECT * FROM
performance_schema.replication_group_members;
Can see ,3 Node status is online, And the primary node is 172.72.0.15, Only the master node can write data , Other nodes are read-only , Come here MGR The single master mode is set up successfully ;
Technology
Daily Recommendation