mysql mgr demo
docker compose 配置
services: db: container_name: mgr-db-0 image: mysql:8.0 restart: always environment: MYSQL_ROOT_PASSWORD: '123456' expose: # Opens port 3306 on the container - '3306' # Where our data will be persisted volumes: - /root/my.cnf:/etc/my.cnf hostname: s1 networks: mgr-cc: ipv4_address: 10.11.0.10 extra_hosts: - "s2:10.11.0.11" - "s3:10.11.0.12" db1: container_name: mgr-db-1 image: mysql:8.0 restart: always environment: MYSQL_ROOT_PASSWORD: '123456' expose: # Opens port 3306 on the container - '3306' # Where our data will be persisted volumes: - /root/my1.cnf:/etc/my.cnf hostname: s2 networks: mgr-cc: ipv4_address: 10.11.0.11 extra_hosts: - "s1:10.11.0.10" - "s3:10.11.0.12" db2: container_name: mgr-db-2 image: mysql:8.0 restart: always environment: MYSQL_ROOT_PASSWORD: '123456' expose: # Opens port 3306 on the container - '3306' # Where our data will be persisted volumes: - /root/my2.cnf:/etc/my.cnf hostname: s3 networks: mgr-cc: ipv4_address: 10.11.0.12 extra_hosts: - "s2:10.11.0.11" - "s1:10.11.0.10" # Names our volume volumes: my-db: networks: mgr-cc: ipam: driver: default config: - subnet: "10.11.0.0/16"
#my.cnf # For advice on how to change settings please see # http://dev.mysql.com/doc/refman/8.0/en/server-configuration-defaults.html [mysqld] # # Remove leading # and set to the amount of RAM for the most important data # cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%. # innodb_buffer_pool_size = 128M # # Remove leading # to turn on a very important data integrity option: logging # changes to the binary log between backups. # log_bin # # Remove leading # to set options mainly useful for reporting servers. # The server defaults are faster for transactions and fast SELECTs. # Adjust sizes as needed, experiment to find the optimal values. # join_buffer_size = 128M # sort_buffer_size = 2M # read_rnd_buffer_size = 2M # Remove leading # to revert to previous value for default_authentication_plugin, # this will increase compatibility with older clients. For background, see: # https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_default_authentication_plugin # default-authentication-plugin=mysql_native_password skip-host-cache skip-name-resolve datadir=/var/lib/mysql socket=/var/run/mysqld/mysqld.sock #secure-file-priv=/var/lib/mysql-files user=mysql server_id=1013306 pid-file=/var/run/mysqld/mysqld.pid gtid-mode=on enforce-gtid-consistency=true log_slave_updates=1 relay_log_info_repository=TABLE master_info_repository=TABLE relay_log_recovery=on default-time_zone ="+8:00" disabled_storage_engines="MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY" #plugin_load_add='group_replication.so' transaction_write_set_extraction=XXHASH64 loose-group_replication_group_name ="a876d35e-9110-11e6-a365-842b2b5909d6" loose-group_replication_start_on_boot =off loose-group_replication_local_address ="10.11.0.10:34901" loose-group_replication_group_seeds ="10.11.0.10:34901,10.11.0.11:34902,10.11.0.12:34903" loose-group_replication_bootstrap_group =off loose-group-replication-ip-whitelist="10.11.0.10,10.11.0.11,10.11.0.12" loose-group_replication_single_primary_mode=FALSE loose-group_replication_enforce_update_everywhere_checks=TRUE [client] socket=/var/run/mysqld/mysqld.sock !includedir /etc/mysql/conf.d/
# my1.cnf # For advice on how to change settings please see # http://dev.mysql.com/doc/refman/8.0/en/server-configuration-defaults.html [mysqld] # # Remove leading # and set to the amount of RAM for the most important data # cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%. # innodb_buffer_pool_size = 128M # # Remove leading # to turn on a very important data integrity option: logging # changes to the binary log between backups. # log_bin # # Remove leading # to set options mainly useful for reporting servers. # The server defaults are faster for transactions and fast SELECTs. # Adjust sizes as needed, experiment to find the optimal values. # join_buffer_size = 128M # sort_buffer_size = 2M # read_rnd_buffer_size = 2M # Remove leading # to revert to previous value for default_authentication_plugin, # this will increase compatibility with older clients. For background, see: # https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_default_authentication_plugin # default-authentication-plugin=mysql_native_password skip-host-cache skip-name-resolve datadir=/var/lib/mysql socket=/var/run/mysqld/mysqld.sock secure-file-priv=/var/lib/mysql-files user=mysql server_id=1023306 pid-file=/var/run/mysqld/mysqld.pid gtid-mode=on enforce-gtid-consistency=true log_slave_updates=1 relay_log_info_repository=TABLE master_info_repository=TABLE relay_log_recovery=on default-time_zone ="+8:00" disabled_storage_engines="MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY" #plugin_load_add='group_replication.so' transaction_write_set_extraction=XXHASH64 loose-group_replication_group_name ="a876d35e-9110-11e6-a365-842b2b5909d6" loose-group_replication_start_on_boot =off loose-group_replication_local_address ="10.11.0.11:34902" loose-group_replication_group_seeds ="10.11.0.10:34901,10.11.0.11:34902,10.11.0.12:34903" loose-group_replication_bootstrap_group =off loose-group-replication-ip-whitelist="10.11.0.10,10.11.0.11,10.11.0.12" loose-group_replication_single_primary_mode=FALSE loose-group_replication_enforce_update_everywhere_checks=TRUE [client] socket=/var/run/mysqld/mysqld.sock !includedir /etc/mysql/conf.d/
#my2.cnf # For advice on how to change settings please see # http://dev.mysql.com/doc/refman/8.0/en/server-configuration-defaults.html [mysqld] # # Remove leading # and set to the amount of RAM for the most important data # cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%. # innodb_buffer_pool_size = 128M # # Remove leading # to turn on a very important data integrity option: logging # changes to the binary log between backups. # log_bin # # Remove leading # to set options mainly useful for reporting servers. # The server defaults are faster for transactions and fast SELECTs. # Adjust sizes as needed, experiment to find the optimal values. # join_buffer_size = 128M # sort_buffer_size = 2M # read_rnd_buffer_size = 2M # Remove leading # to revert to previous value for default_authentication_plugin, # this will increase compatibility with older clients. For background, see: # https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_default_authentication_plugin # default-authentication-plugin=mysql_native_password skip-host-cache skip-name-resolve datadir=/var/lib/mysql socket=/var/run/mysqld/mysqld.sock secure-file-priv=/var/lib/mysql-files user=mysql server_id=1033306 pid-file=/var/run/mysqld/mysqld.pid gtid-mode=on enforce-gtid-consistency=true log_slave_updates=1 relay_log_info_repository=TABLE master_info_repository=TABLE relay_log_recovery=on default-time_zone ="+8:00" disabled_storage_engines="MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY" #plugin_load_add='group_replication.so' transaction_write_set_extraction=XXHASH64 loose-group_replication_group_name ="a876d35e-9110-11e6-a365-842b2b5909d6" loose-group_replication_start_on_boot =off loose-group_replication_local_address ="10.11.0.12:34903" loose-group_replication_group_seeds ="10.11.0.10:34901,10.11.0.11:34902,10.11.0.12:34903" loose-group_replication_bootstrap_group =off loose-group-replication-ip-whitelist="10.11.0.10,10.11.0.11,10.11.0.12" loose-group_replication_single_primary_mode=FALSE loose-group_replication_enforce_update_everywhere_checks=TRUE [client] socket=/var/run/mysqld/mysqld.sock !includedir /etc/mysql/conf.d/
操作
1、安装组复制插件 #每个机器上都要执行
INSTALL PLUGIN group_replication SONAME 'group_replication.so';
show plugins;
2、在db-0上创建复制用户 #最好每个机器上都执行 因为下次启动不一定会是同一个主机进行引导
SET SQL_LOG_BIN=0;
CREATE USER repl@'%' IDENTIFIED WITH mysql_native_password BY 'repl';
GRANT REPLICATION SLAVE ON *.* TO repl@'%';
GRANT BACKUP_ADMIN ON *.* TO repl@'%';
FLUSH PRIVILEGES;
SET SQL_LOG_BIN=1;
启动并引导组复制
在单主模式中我们需要默认的选择一个节点作为主节点，并且使这个节点成为引导节点。
在 mysql 中运行以下的命令
SET GLOBAL group_replication_bootstrap_group=ON;
START GROUP_REPLICATION;
SET GLOBAL group_replication_bootstrap_group=OFF;
3、依次在另外两个节点
CHANGE MASTER TO MASTER_USER='repl', MASTER_PASSWORD='repl' FOR CHANNEL 'group_replication_recovery'; #只需执行一次
START GROUP_REPLICATION;
4, 查看节点是否正常
select * from performance_schema.replication_group_members;
!!!!遇到的问题
1)用户安全验证错误
方案一:使用复制用户请求服务器公钥：(原因是mysql8 密码验证增强)
mysql -u repl -p123456 -hs1 --get-server-public-key
2) Plugin group_replication reported: 'This member has more executed transactions than those present in the group. Local transactions: e0c97c95-f571-11e9-933b-001b785baebb:1 > Group transactions: c2840554-f56b-11e9-8d9b-6cb3113192fc:1,
执行=> reset master;
