毕业设计做网站用php好吗,某企业电子商务网站建设,数字广东网络建设有限公司介绍,长岛网站建设费用文章目录 前言1. 安装部署1.1 下载安装包1.2 MySQL Shell1.3 配置 MySQL 实例1.4 启动 ReplicaSet1.5 启动 8.2 Router 2. 测试路由总结 前言
MySQL 8.0 官方推出过一个高可用方案 ReplicaSet 主要由 Router、MySQL Shell、MySQL Server 三个组件组成。
MySQL Shell 负责管理… 文章目录 前言1. 安装部署1.1 下载安装包1.2 MySQL Shell1.3 配置 MySQL 实例1.4 启动 ReplicaSet1.5 启动 8.2 Router 2. 测试路由总结 前言
MySQL 8.0 官方推出过一个高可用方案 ReplicaSet 主要由 Router、MySQL Shell、MySQL Server 三个组件组成。
MySQL Shell 负责管理 ReplicaSet 包括部署、切换、节点加入等都可以通过内置 AdminAPI 自动化完成。
MySQL Router 是一款轻量级中间件可在应用程序和 ReplicaSet 之间提供透明路由和读写分离功能。 8.0 时期的 Router 读写需要配置两个端口应用如果想要使用读写分离的特性需要额外适配。
-- MySQL 经典协议
- Read/Write Connections: localhost:6446, /data/myrouter/mysql.sock
- Read/Only Connections: localhost:6447, /data/myrouter/mysqlro.sock-- MySQL X 协议
- Read/Write Connections: localhost:6448, /data/myrouter/mysqlx.sock
- Read/Only Connections: localhost:6449, /data/myrouter/mysqlxro.sock到了 MySQL 8.2 版本 Router 自动可以识别读取和写入按照配置规则分配到不同的实例无需使用额外端口。
1. 安装部署
让我们一起从 0 部署一套 MySQL 8.2 ReplicaSet 集群吧
系统版本CentOS Linux release 7.8.2003 (Core)MySQL Server8.0.32
IPhostnameRole172.16.104.56172-16-104-56Master172.16.104.57172-16-104-56Secondary
vi /etc/hosts
172.16.104.56 172-16-104-56
172.16.104.57 172-16-104-571.1 下载安装包 MySQL 官网下载地址https://downloads.mysql.com/archives/community 压缩包里面包含 MySQL Server 和 MySQL Router 组件。安装 MySQL Server 步骤参考下文 MySQL 部署MySQL 自动化安装部署 1.2 MySQL Shell
官方下载地址MySQL Shell download
mv mysql-shell-8.0.31-linux-glibc2.12-x86-64bit /usr/local/mysqlsh
export PATH/usr/local/mysqlsh/bin/:$PATH
echo PATH$PATH:/usr/local/mysqlsh/bin/ /etc/profile进入 MySQL Shell 使用 \py \js 可以切换命令模式本篇文章使用 python 模式演示
1.3 配置 MySQL 实例
部署好 MySQL 实例后需要创建一个用户提供给 MySQL Shell 使用这里演示创建的用户是rw_shell
GRANT CLONE_ADMIN, CONNECTION_ADMIN, CREATE USER, EXECUTE, FILE, GROUP_REPLICATION_ADMIN, PERSIST_RO_VARIABLES_ADMIN, PROCESS, RELOAD, REPLICATION CLIENT, REPLICATION SLAVE, REPLICATION_APPLIER, REPLICATION_SLAVE_ADMIN, ROLE_ADMIN, SELECT, SHUTDOWN, SYSTEM_VARIABLES_ADMIN ON *.* TO rw_shell% WITH GRANT OPTION;
GRANT DELETE, INSERT, UPDATE ON mysql.* TO rw_shell% WITH GRANT OPTION;
GRANT ALTER, ALTER ROUTINE, CREATE, CREATE ROUTINE, CREATE TEMPORARY TABLES, CREATE VIEW, DELETE, DROP, EVENT, EXECUTE, INDEX, INSERT, LOCK TABLES, REFERENCES, SHOW VIEW, TRIGGER, UPDATE ON mysql_innodb_cluster_metadata.* TO rw_shell% WITH GRANT OPTION;
GRANT ALTER, ALTER ROUTINE, CREATE, CREATE ROUTINE, CREATE TEMPORARY TABLES, CREATE VIEW, DELETE, DROP, EVENT, EXECUTE, INDEX, INSERT, LOCK TABLES, REFERENCES, SHOW VIEW, TRIGGER, UPDATE ON mysql_innodb_cluster_metadata_bkp.* TO rw_shell% WITH GRANT OPTION;
GRANT ALTER, ALTER ROUTINE, CREATE, CREATE ROUTINE, CREATE TEMPORARY TABLES, CREATE VIEW, DELETE, DROP, EVENT, EXECUTE, INDEX, INSERT, LOCK TABLES, REFERENCES, SHOW VIEW, TRIGGER, UPDATE ON mysql_innodb_cluster_metadata_previous.* TO rw_shell% WITH GRANT OPTION;配置 MySQL 实例检查是否符合创建 ReplicaSet 的条件如果发现需要修改的地方就会提示确认修改。 执行次命令会让用户输入密码保存后会进入检查阶段。
dba.configure_replica_set_instance(rw_shell172-16-104-55:3306, {clusterAdmin: rw_shell%})
dba.configure_replica_set_instance(rw_shell172-16-104-56:3306, {clusterAdmin: rw_shell%})MySQL Shell 会检查数据库参数是否满足创建 ReplicaSet 条件并提出修改建议 此阶段执行完成后表示实例都具备组成 ReplicaSet 条件可进入下一步。
1.4 启动 ReplicaSet
使用 MySQL Shell 连接到主实例否则测试中会报 session 错误。 An open session is required to perform this operation. \c rw_shell172-16-104-56:3306创建 ReplicaSet 默认当前进入的实例为主库实例
rs dba.create_replica_set(prd_op_service)添加复制节点
rs.add_instance(172-16-104-57:3306)至此ReplicaSet 已配置完成下图为拓扑信息
{replicaSet: {name: prd_op_service, primary: 172-16-104-56:3306, status: AVAILABLE, statusText: All instances available., topology: {172-16-104-56:3306: {address: 172-16-104-56:3306, instanceRole: PRIMARY, mode: R/W, status: ONLINE}, 172-16-104-57:3306: {address: 172-16-104-57:3306, instanceRole: SECONDARY, mode: R/O, replication: {applierStatus: APPLIED_ALL, applierThreadState: Waiting for an event from Coordinator, applierWorkerThreads: 4, receiverStatus: ON, receiverThreadState: Waiting for source to send event, replicationLag: null, replicationSsl: TLS_AES_128_GCM_SHA256 TLSv1.3, replicationSslMode: REQUIRED}, status: ONLINE}}, type: ASYNC}
}1.5 启动 8.2 Router
解压二进制包开箱即用。
xz -d mysql-router-8.2.0-linux-glibc2.12-x86_64.tar.xz
tar -xvf mysql-router-8.2.0-linux-glibc2.12-x86_64.tar创建 router 专用用户
rs.setup_router_account(op_router)更新 router 配置信息
./mysqlrouter \--bootstrap rw_shell172-16-104-56:3306 \--directory /data/myrouter \--conf-use-sockets \--account op_router \--userroot此时 Router 输出的配置信息
After this MySQL Router has been started with the generated configuration$ ./mysqlrouter -c /data/myrouter/mysqlrouter.confInnoDB ReplicaSet prd_op_service can be reached by connecting to:## MySQL Classic protocol- Read/Write Connections: localhost:6446, /data/myrouter/mysql.sock
- Read/Only Connections: localhost:6447, /data/myrouter/mysqlro.sock
- Read/Write Split Connections: localhost:6450, /data/myrouter/mysqlsplit.sock## MySQL X protocol- Read/Write Connections: localhost:6448, /data/myrouter/mysqlx.sock
- Read/Only Connections: localhost:6449, /data/myrouter/mysqlxro.sock从输出结果看相比于之前的版本多了一个 6450 端口。
启动 mysqlrouter 服务
./mysqlrouter -c /data/myrouter/mysqlrouter.conf 2. 测试路由
使用 6450 端口连接 MySQL
mysql -urw_shell -P6450 -h172.16.104.56 -prw_shellmysql 17:21: [(none)]select hostname;
---------------
| hostname |
---------------
| 172-16-104-57 |
---------------
1 row in set (0.01 sec)rw_shellmysql 17:22: [(none)]begin;
Query OK, 0 rows affected (0.01 sec)rw_shellmysql 17:22: [(none)]select hostname;
---------------
| hostname |
---------------
| 172-16-104-56 |
---------------
1 row in set (0.01 sec)rw_shellmysql 17:22: [(none)]commit;
Query OK, 0 rows affected (0.00 sec)rw_shellmysql 17:22: [(none)]
rw_shellmysql 17:22: [(none)]select hostname;
---------------
| hostname |
---------------
| 172-16-104-57 |
---------------
1 row in set (0.01 sec)从上面的实验我们可以看到默认情况下如果执行读取操作我们将到达复制节点但如果启动事务我们将到主节点而无需更改端口并使用相同的连接。
rw_shellmysql 17:25: [(none)]select hostname;
---------------
| hostname |
---------------
| 172-16-104-57 |
---------------
1 row in set (0.01 sec)rw_shellmysql 17:25: [(none)]start transaction read only;
Query OK, 0 rows affected (0.02 sec)rw_shellmysql 17:26: [(none)]select hostname;
---------------
| hostname |
---------------
| 172-16-104-57 |
---------------
1 row in set (0.01 sec)启动事务类型为只读事务的话也会直接到复制节点。
rw_shellmysql 17:32: [(none)]ROUTER SET access_moderead_only;
Query OK, 0 rows affected (0.00 sec)rw_shellmysql 17:32: [(none)]select hostname;
---------------
| hostname |
---------------
| 172-16-104-57 |
---------------
1 row in set (0.02 sec)rw_shellmysql 17:33: [(none)]ROUTER SET access_moderead_write;
Query OK, 0 rows affected (0.00 sec)rw_shellmysql 17:33: [(none)]select hostname;
---------------
| hostname |
---------------
| 172-16-104-56 |
---------------
1 row in set (0.02 sec)可以通过 ROUTER SET 语句定义接下来 SQL 访问类型。
总结
MySQL 8.2 版本的 router 读写分离支持使用一个端口无需业务适应更改是一项非常很有价值的功能可以优化数据库性能和可扩展性而无需对应用程序进行任何更改。 参考资料https://lefred.be/content/mysql-8-2-transparent-read-write-splitting/
