背景
有了基础的虚拟化环境,打算从头搭建一个分布式应用环境,学习学习各种集群的搭建过程。在公司这些都是别的部门弄好了直接用的,包括高可用、灾备等特性都有值得我研究学习的地方。
集群方式选择
mysql的集群方法
传统方法是基于源节点二进制日志重放事件,并且要求日志文件和位置在源和副本之间是同步的
更新一点的方法是基于 global transaction identifiers (GTIDs),GTIDs是事务性的,不需要依赖日志文件和位置就能工作,这极大的简化了许多常见的副本任务.使用GTIDs的方法保证了源和副本之间的一致性,所有的事务只要在源上提交了就一定会被应用到副本上。
基于日志的方法
源节点个每个副本都应该被设置唯一的ID (using the server_id
system variable),顺带一提,每个副本必须设置源节点的host name, log file文件名,文件内的偏移量
这些细节可以通过mysql的session语句控制。MySQL 8.0.23开始是 CHANGE REPLICATION SOURCE TO
,之前是 CHANGE MASTER TO
. 这些细节被存储在副本连接的元数据存储中 (see Section 17.2.4, “Relay Log and Replication Metadata Repositories”).
这里有多种不同的方法可以设置副本, 使用某个具体的方法取决于你如何设置以及你是否在源节点已经有想要复制的数据
想要安装多实例的 MySQL, 你可以使用 InnoDB Cluster ,它可以让你使用 MySQL Shell 轻易的管理mysql服务器集群. InnoDB Cluster 用程序化的方式提供了环境包装了MySQL Group Replication,它可以让你轻易的部署一个高可用的Mysql集群。另外 InnoDB Cluster的接口可以无缝集成 MySQL Router, 它可以让你的程序无需编写降级逻辑的情况下连接集群. 对于不需要高可用的相似场景可以使用 InnoDB ReplicaSet. 这里是安装过程here.
这里是一些通用任务来改概括所有步骤:
1.在源节点上开启binlog并配置unique server ID Section 17.1.2.1, “Setting the Replication Source Configuration”.
可以考虑使用 –init-file 执行SET GLOBAL server_id = 2;
0 1 2 3 4 |
SERVER_ID=2 echo "SET GLOBAL server_id = $SERVER_ID;" > /etc/mysql/init.sql echo "init_file=/etc/mysql/init.sql" >> /etc/mysql/mysql.conf.d/mysqld.cnf service mysql restart |
查看这个值
0 1 |
show global variables like 'server_id'; |
2.在每个副本上配置a unique server ID. Section 17.1.2.2, “Setting the Replica Configuration”.
重复1
3.可选,为每个副本创建不同的用户用于鉴权 Section 17.1.2.3, “Creating a User for Replication”.
0 1 2 |
CREATE USER 'repl'@'192.168.2.%' IDENTIFIED BY 'password'; GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.2.%' |
4.在创建快照或重启副本进程之前,在记录源节点上的binlog当前位置. 当配置你副本节点时你需要这个信息 Section 17.1.2.4, “Obtaining the Replication Source Binary Log Coordinates
在源节点上执行
0 1 2 |
FLUSH TABLES WITH READ LOCK; SHOW MASTER STATUS; |
记录File | Position
5.如果你在源节点上已经有数据了并且想要同步到副本上,你需要创建数据的快照来复制数据到副本. 你所使用的存储引擎会决定你如何创建快照
当你使用 MyISAM
时,你必须在源上停止处理语句来获得read-lock, 在允许源节点继续执行语句之前获取当前binlog的坐标并导出数据。 Section 17.1.2.4, “Obtaining the Replication Source Binary Log Coordinates”.
当你使用 InnoDB
, 你不需要获取read-lock,一个够长的事务足够可以传输数据的快照(这么翻译感觉怪怪的,原文: you do not need a read-lock and a transaction that is long enough to transfer the data snapshot is sufficient.) Section 15.19, “InnoDB and MySQL Replication”.
6.在副本上设置连接的源节点,例如host name,、登录凭证、binlog文件名和位置 Section 17.1.2.7, “Setting the Source Configuration on the Replica”.
在副本节点上执行
0 1 2 3 4 5 6 |
CHANGE REPLICATION SOURCE TO SOURCE_HOST='source_host_name', SOURCE_USER='replication_user_name', SOURCE_PASSWORD='replication_password', SOURCE_LOG_FILE='recorded_log_file_name', SOURCE_LOG_POS=recorded_log_position; |
7.对源和副本实施特定的安全措施 Section 17.3, “Replication Security”.
基础配置完成后,选择你的场景:
- 添加一个新的副本并同步空的源节点 Section 17.1.2.6.1, “Setting Up Replication with New Source and Replicas”.
- 向一个已有的mysql服务器设置新的源Section 17.1.2.6.2, “Setting Up Replication with Existing Data”.
- 向已有的分布式集群添加副本Section 17.1.2.8, “Adding Replicas to a Replication Environment”.
在管理MySQL集群之前,阅读完整的章节并尝试这里所有的语句,同时熟悉副本的启动选项
Section 13.4.1, “SQL Statements for Controlling Source Servers,
Section 13.4.2, “SQL Statements for Controlling Replica Servers.
Section 17.1.6, “Replication and Binary Logging Options and Variables.
基于GTIDs的方法
TODO
MRG搭建
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 |
ssh-rsa AAAAB3NzaC1yc2EAAAADAQABAAABgQCk4ZxaNgw6+2u6uQrNik11NZ30FMd7eMB1xGAx2T5VB2651fM8/WVF22iwc1A4BwvWVIPv5lkDIGMlYZ41qsnYZY0pioYkZfq2PnE2WrL/R5XHG7Cnuvt61aqy7Zrdwt/p2WEE/dQHN7U4nYCwGTHLaFz6c0YGtUNHzTuRJqjgQ1yMzqQJj+0oWxyNvuhH3n5ejeJuyWc5cP+O2cYDfCStYpA0KlmDjyaAHaJ2M/n+GYOX9q+4s1mIDsjUoCTWJ5OpBflKiKTPUNCEFH3waFsew2N/WF53gyycYCIc11Bdib7XFiSoydLOGh4JP9NZVNeEl5YMSCoHFbZcWEMSKTpwoFFISnkJPnUnYg0V1NeuA8fhx3b58+/TbdPPMCZIO5TCruCBhJK0UD2D64z167wLm4ZEOh4PLwQIYL6n719XpHdlZBKVv6UOmt+Z3nFH4W5jUNAde/+5r6LC3xg6MJeXTmBEO/omO37HCDCYq+4Wq5xru5EsR1t4rXb0lnZ6zX0= hyl-jumpbox\hyl-jumpbox@HYL-JUMPBOX sudo apt install net-tools mysql-server -y ssh mysql1@192.168.2.190 ssh mysql2@192.168.2.169 ssh mysql3@192.168.2.216 sudo cat /etc/mysql/debian.cnf mysql1 192.168.2.190 user = debian-sys-maint password = UYbTJYUnTKzEnerQ mysql2 192.168.2.169 user = debian-sys-maint password = NQTc7vw0eAzqrABq mysql3 192.168.2.216 user = debian-sys-maint password = 0y2FH9aksKPTNqbu 192.168.2.190 mysql1 192.168.2.169 mysql2 192.168.2.216 mysql3 mysql -u debian-sys-maint -p CREATE USER 'mysql'@'localhost' IDENTIFIED BY 'password'; GRANT ALL ON *.* TO 'mysql'@'localhost'; CREATE USER 'mysql'@'192.168.2.%' IDENTIFIED BY 'password'; GRANT ALL ON *.* TO 'mysql'@'192.168.2.%'; flush privileges; mysql -u mysql -p password mysql -u mysql -h 192.168.2.190 -p mysql -u mysql -h 192.168.2.169 -p mysql -u mysql -h 192.168.2.216 -p password lisenport从127.0.0.1改为0.0.0.0监听所有网卡,或指定IP远程才可以连接3306 #setenforce=0 #打开/etc/selinux/config文件,修改SELINUX=DISABLED(需重启服务器才能生效) disabled_storage_engines="MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY" server_id=1 gtid_mode=ON enforce_gtid_consistency=ON plugin_load_add='group_replication.so' plugin_load_add='mysql_clone.so' group_replication_group_name="52d4b7bf-56db-11ed-b0fa-000c29409b22" group_replication_start_on_boot=off group_replication_local_address="192.168.2.190:33061" group_replication_group_seeds="192.168.2.190:33061,192.168.2.169:33061,192.168.2.216:33061" group_replication_bootstrap_group=off disabled_storage_engines="MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY" server_id=2 gtid_mode=ON enforce_gtid_consistency=ON plugin_load_add='group_replication.so' plugin_load_add='mysql_clone.so' group_replication_group_name="52d4b7bf-56db-11ed-b0fa-000c29409b22" group_replication_start_on_boot=off group_replication_local_address="192.168.2.169:33061" group_replication_group_seeds="192.168.2.190:33061,192.168.2.169:33061,192.168.2.216:33061" group_replication_bootstrap_group=off disabled_storage_engines="MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY" server_id=3 gtid_mode=ON enforce_gtid_consistency=ON plugin_load_add='group_replication.so' plugin_load_add='mysql_clone.so' group_replication_group_name="52d4b7bf-56db-11ed-b0fa-000c29409b22" group_replication_start_on_boot=off group_replication_local_address="192.168.2.216:33061" group_replication_group_seeds="192.168.2.190:33061,192.168.2.169:33061,192.168.2.216:33061" group_replication_bootstrap_group=off CREATE USER rpl_user@'%' IDENTIFIED WITH mysql_native_password BY 'password'; SET SQL_LOG_BIN=0; ALTER USER rpl_user@'%' IDENTIFIED WITH mysql_native_password BY 'password'; GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%'; GRANT CONNECTION_ADMIN ON *.* TO rpl_user@'%'; GRANT BACKUP_ADMIN ON *.* TO rpl_user@'%'; GRANT GROUP_REPLICATION_STREAM ON *.* TO rpl_user@'%'; FLUSH PRIVILEGES; SET SQL_LOG_BIN=1; SHOW PLUGINS; master上启动同步后会开始监听33061端口,注意如果有防火墙需要打开,网上的文章不靠谱,没检查监听就去关防火墙是高危操作! SET GLOBAL group_replication_bootstrap_group=ON; START GROUP_REPLICATION USER='rpl_user', PASSWORD='password'; SET GLOBAL group_replication_bootstrap_group=OFF; SELECT * FROM performance_schema.replication_group_members; CREATE DATABASE test; USE test; CREATE TABLE t1 (c1 INT PRIMARY KEY, c2 TEXT NOT NULL); INSERT INTO t1 VALUES (1, 'Luis'); INSERT INTO t1 VALUES (2, 'HylTest'); INSERT INTO t1 VALUES (3, 'HylTest3'); INSERT INTO t1 VALUES (4, 'HylTest4'); select * from test.t1; SHOW BINLOG EVENTS; truncate table test.t1; slave上加入group CHANGE REPLICATION SOURCE TO SOURCE_USER='rpl_user', SOURCE_PASSWORD='password' FOR CHANNEL 'group_replication_recovery'; START GROUP_REPLICATION USER='rpl_user', PASSWORD='password'; #停止同步 STOP GROUP_REPLICATION; |
注意,任意节点脱离group后需要手动执行start group_replication重新加入,执行前的数据保留在离开group时的状态
例如mysql2重新加入group变化如下
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
mysql> SELECT * FROM performance_schema.replication_group_members; +---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+----------------------------+ | CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION | MEMBER_COMMUNICATION_STACK | +---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+----------------------------+ | group_replication_applier | 5b1e6080-578f-11ed-bd92-000c2956aa71 | mysql1 | 3306 | ONLINE | PRIMARY | 8.0.31 | XCom | | group_replication_applier | 5c6b9aaa-578f-11ed-946f-000c2924d927 | mysql2 | 3306 | RECOVERING | SECONDARY | 8.0.31 | XCom | | group_replication_applier | 8153354e-578f-11ed-87f7-000c29409b22 | mysql3 | 3306 | ONLINE | SECONDARY | 8.0.31 | XCom | +---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+----------------------------+ 3 rows in set (0.00 sec) mysql> SELECT * FROM performance_schema.replication_group_members; +---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+----------------------------+ | CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION | MEMBER_COMMUNICATION_STACK | +---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+----------------------------+ | group_replication_applier | 5b1e6080-578f-11ed-bd92-000c2956aa71 | mysql1 | 3306 | ONLINE | PRIMARY | 8.0.31 | XCom | | group_replication_applier | 5c6b9aaa-578f-11ed-946f-000c2924d927 | mysql2 | 3306 | ONLINE | SECONDARY | 8.0.31 | XCom | | group_replication_applier | 8153354e-578f-11ed-87f7-000c29409b22 | mysql3 | 3306 | ONLINE | SECONDARY | 8.0.31 | XCom | +---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+----------------------------+ 3 rows in set (0.01 sec) |
尝试mysql shell自动配置
https://dev.mysql.com/doc/mysql-shell/8.0/en/mysql-innodb-cluster.html
参考文档
https://dev.mysql.com/doc/refman/8.0/en/replication.html
https://dev.mysql.com/doc/refman/8.0/en/group-replication-deploying-in-single-primary-mode.html
https://ethendev.github.io/2018/12/17/JPA-MySQL-read-write-separation/
https://dev.mysql.com/doc/mysql-shell/8.0/en/mysql-innodb-cluster.html
0 Comments