当前位置： 首页 > news >正文

mysql部署模型_proxysql-单主模型MGR部署

news 来源：原创 2024/5/3 16:06:46

1.proxysql-单主模型MGR部署

1.1 环境描述

4dbd97678fce9fce6c9e3b1a1fc3409c.png

主机

IP地址

角色

vm3

192.168.225.130

proxysql

vm4

192.168.225.131

mysql主

vm5

192.168.225.134

mysql从

vm6

192.168.225.140

mysql从

关闭防火墙，SELINUX

1.2 部署mysql主从

1.2.1 安装mariadb

[root@vm4 ~]# yum -y install mariadb*

[root@vm5 ~]# yum -y install mariadb*

[root@vm6 ~]# yum -y install mariadb*

1.2.2 主库授权账户给从库

##授权从库

[root@vm4 local]# mysql

MariaDB [(none)]> grant replication slave on *.* to 'repli'@'192.168.225.134' identified by '123456';

Query OK, 0 rows affected (0.001 sec)

MariaDB [(none)]> grant replication slave on *.* to 'repli'@'192.168.225.140' identified by '123456';

Query OK, 0 rows affected (0.001 sec)

MariaDB [(none)]> select user,repl_slave_priv from mysql.user where user='repli';

+-------+-----------------+

| user | repl_slave_priv |

+-------+-----------------+

| repli | Y |

| repli | Y |

+-------+-----------------+

2 rows in set (0.001 sec)

MariaDB [(none)]> flush privileges;

1.2.3 配置主数据库

[root@vm4 ~]# cd /etc/my.cnf.d/

[root@vm4 my.cnf.d]# vi mariadb-server.cnf

................................

[mysqld]

datadir=/var/lib/mysql

socket=/var/lib/mysql/mysql.sock

log-error=/var/log/mariadb/mariadb.log

pid-file=/run/mariadb/mariadb.pid

server-id=10

log-bin=mysql-bin

...............................

[root@vm4 ~]# systemctl restart mariadb

[root@vm4 ~]# mysql

Welcome to the MariaDB monitor. Commands end with ; or \g.

Your MariaDB connection id is 9

Server version: 10.3.17-MariaDB-log MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> show master status;

+------------------+----------+--------------+------------------+

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |

+------------------+----------+--------------+------------------+

| mysql-bin.000001 | 328 | | |

+------------------+----------+--------------+------------------+

1 row in set (0.000 sec)

1.2.4 配置从库

[root@vm5 ~]# vi /etc/my.cnf.d/mariadb-server.cnf

...............................

[mysqld]

datadir=/var/lib/mysql

socket=/var/lib/mysql/mysql.sock

log-error=/var/log/mariadb/mariadb.log

pid-file=/run/mariadb/mariadb.pid

server-id=20

relay-log=mysql-relay-bin

.................................

[root@vm5 ~]# systemctl restart mariadb

[root@vm5 ~]# mysql

MariaDB [(none)]> change master to

-> master_host='192.168.225.131',

-> master_user='repli',

-> master_password='123456',

-> master_log_file='mysql-bin.000001',

-> master_log_pos=328;

Query OK, 0 rows affected (0.013 sec)

MariaDB [(none)]> start slave;

Query OK, 0 rows affected (0.003 sec)

MariaDB [(none)]> show slave status\G;

*************************** 1. row ***************************

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.225.131

Master_User: repli

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000001

Read_Master_Log_Pos: 328

Relay_Log_File: mysql-relay-bin.000002

Relay_Log_Pos: 555

Relay_Master_Log_File: mysql-bin.000001

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

..............................................................

[root@vm6 ~]# vi /etc/my.cnf

...............................

[mysqld]

datadir=/var/lib/mysql

socket=/var/lib/mysql/mysql.sock

server-id=21

relay-log=mysql-relay-bin

.................................

[root@vm6 ~]# systemctl restart mariadb

[root@vm6 ~]# mysql

MariaDB [(none)]> change master to

-> master_host='192.168.225.131',

-> master_user='repli',

-> master_password='123456',

-> master_log_file='mysql-bin.000001',

-> master_log_pos=328;

Query OK, 0 rows affected (0.013 sec)

MariaDB [(none)]> start slave;

Query OK, 0 rows affected (0.003 sec)

MariaDB [(none)]> show slave status\G;

*************************** 1. row ***************************

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.225.131

Master_User: repli

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000001

Read_Master_Log_Pos: 328

Relay_Log_File: mysql-relay-bin.000002

Relay_Log_Pos: 540

Relay_Master_Log_File: mysql-bin.000001

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

1.3 proxysql部署

1.3.1 proxysql安装

配置proxysql源，地址

[root@vm3 ~]# vim /etc/yum.repos.d/proxysql.repo

[proxysql_repo]

name=ProxySQL

baseurl=http://repo.proxysql.com/ProxySQL/proxysql-1.4.x/centos/7

gpgcheck=1

gpgkey=http://repo.proxysql.com/ProxySQL/repo_pub_key

[root@vm3 ~]# yum clean all

[root@vm3 ~]# yum makecache

CentOS-8 - AppStream 1.5 MB/s | 6.3 MB 00:04

CentOS-8 - Base 1.0 MB/s | 2.3 MB 00:02

CentOS-8 - Extras 5.6 kB/s | 8.6 kB 00:01

ProxySQL 2.8 kB/s | 4.4 kB 00:01

Last metadata expiration chec

安装proxysql

[root@vm3 ~]# yum -y install proxysql mariadb

1.3.2 配置proxysql

主库授权账号给proxysql

##授权一个账号给proxysql用于访问数据库

[root@vm4 ~]# mysql

MariaDB [(none)]> grant all on *.* to 'proxysql'@'192.168.225.130' identified by 'proxysql';

Query OK, 0 rows affected (0.001 sec)

##授权一个只读账号给proxysql用于健康检查

MariaDB [(none)]> grant select on *.* to 'monitor'@'192.168.225.130' identified by 'monitor';

Query OK, 0 rows affected (0.001 sec)

MariaDB [(none)]> flush privileges;

登录proxysql管理接口

[root@vm3 ~]# export MYSQL_PS1="(\u@\h:\p) [\d]> "

[root@vm3 ~]# mysql -uadmin -padmin -h127.0.0.1 -P6666

Welcome to the MariaDB monitor. Commands end with ; or \g.

Your MySQL connection id is 2

Server version: 5.5.30 (ProxySQL Admin Module)

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

(admin@127.0.0.1:6666) [(none)]> show tables from main;

+--------------------------------------------+

| tables |

+--------------------------------------------+

| global_variables |

| mysql_collations |

| mysql_group_replication_hostgroups |

| mysql_query_rules |

| mysql_query_rules_fast_routing |

| mysql_replication_hostgroups |

| mysql_servers |

| mysql_users |

| proxysql_servers |

| runtime_checksums_values |

| runtime_global_variables |

| runtime_mysql_group_replication_hostgroups |

| runtime_mysql_query_rules |

| runtime_mysql_query_rules_fast_routing |

| runtime_mysql_replication_hostgroups |

| runtime_mysql_servers |

| runtime_mysql_users |

| runtime_proxysql_servers |

| runtime_scheduler |

| scheduler |

+--------------------------------------------+

20 rows in set (0.001 sec)

在mysql_servers表中添加mysql主从主机

配置读组(vm5,vm6)、写组(vm4)

(admin@127.0.0.1:6666) [(none)]> insert into mysql_servers(hostgroup_id,hostname,port,weight,comment) values(10,'192.168.225.131',3306,1,'Write Group');

Query OK, 1 row affected (0.001 sec)

(admin@127.0.0.1:6666) [(none)]> insert into mysql_servers(hostgroup_id,hostname,port,weight,comment) values(20,'192.168.225.134',3306,1,'Read Group');

Query OK, 1 row affected (0.001 sec)

(admin@127.0.0.1:6666) [(none)]> insert into mysql_servers(hostgroup_id,hostname,port,weight,comment) values(20,'192.168.225.140',3306,1,'Read Group');

Query OK, 1 row affected (0.001 sec)

(admin@127.0.0.1:6666) [(none)]> select * from mysql_servers;

+--------------+-----------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+-------------+

| hostgroup_id | hostname | port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |

+--------------+-----------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+-------------+

| 10 | 192.168.225.131 | 3306 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | Write Group |

| 20 | 192.168.225.134 | 3306 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | Read Group |

| 20 | 192.168.225.140 | 3306 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | Read Group |

+--------------+-----------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+-------------+

3 rows in set (0.001 sec)

(admin@127.0.0.1:6666) [(none)]> load mysql servers to runtime;

Query OK, 0 rows affected (0.006 sec)

(admin@127.0.0.1:6666) [(none)]> save mysql servers to disk;

Query OK, 0 rows affected (0.035 sec)

在mysql_users添加主库上创建的proxysql账号

在 proxysql 主机的 mysql_users 表中添加刚才在 master 上创建的账号 proxysql，proxysql 客户端需要使用这个账号来访问数据库。

default_hostgroup默认组设置为写组，也就是10;当读写分离的路由规则不符合时，会访问默认组的数据库;

(admin@127.0.0.1:6666) [(none)]> insert into mysql_users(username,password,default_hostgroup,transaction_persistent) values('proxysql','proxysql',10,1);

Query OK, 1 row affected (0.001 sec)

(admin@127.0.0.1:6666) [(none)]> select * from mysql_users \G

*************************** 1. row ***************************

username: proxysql

password: proxysql

active: 1

use_ssl: 0

default_hostgroup: 10

default_schema: NULL

schema_locked: 0

transaction_persistent: 1

fast_forward: 0

backend: 1

frontend: 1

max_connections: 10000

1 row in set (0.001 sec)

(admin@127.0.0.1:6666) [(none)]> load mysql users to runtime;

Query OK, 0 rows affected (0.001 sec)

(admin@127.0.0.1:6666) [(none)]> save mysql users to disk;

Query OK, 0 rows affected (0.028 sec)

在proxysql端设置健康检查的账号

(admin@127.0.0.1:6666) [(none)]> set mysql-monitor_username='monitor';

Query OK, 1 row affected (0.001 sec)

(admin@127.0.0.1:6666) [(none)]> set mysql-monitor_password='monitor';

Query OK, 1 row affected (0.001 sec)

(admin@127.0.0.1:6666) [(none)]> load mysql variables to runtime;

Query OK, 0 rows affected (0.001 sec)

(admin@127.0.0.1:6666) [(none)]> save mysql variables to disk;

Query OK, 98 rows affected (0.006 sec)

添加读写分离的路由规则

注意:rule_id由小到大匹配，因此在设置rule_id时要预留足够的空间。

(admin@127.0.0.1:6666) [(none)]> insert into mysql_query_rules(rule_id,active,match_digest,destination_hostgroup,apply) values(10,1,'^SELECT.*FOR UPDATE$',10,1);

Query OK, 1 row affected (0.001 sec)

(admin@127.0.0.1:6666) [(none)]> insert into mysql_query_rules(rule_id,active,match_digest,destination_hostgroup,apply) values(20,1,'^SELECT',20,1);

Query OK, 1 row affected (0.001 sec)

(admin@127.0.0.1:6666) [(none)]> insert into mysql_query_rules(rule_id,active,match_digest,destination_hostgroup,apply) values(30,1,'^SHOW',20,1);

Query OK, 1 row affected (0.000 sec)

(admin@127.0.0.1:6666) [(none)]> select rule_id,active,match_digest,destination_hostgroup,apply from mysql_query_rules;

+---------+--------+----------------------+-----------------------+-------+

| rule_id | active | match_digest | destination_hostgroup | apply |

+---------+--------+----------------------+-----------------------+-------+

| 10 | 1 | ^SELECT.*FOR UPDATE$ | 10 | 1 |

| 20 | 1 | ^SELECT | 20 | 1 |

| 30 | 1 | ^SHOW | 20 | 1 |

+---------+--------+----------------------+-----------------------+-------+

3 rows in set (0.000 sec)

(admin@127.0.0.1:6666) [(none)]> load mysql query rules to runtime;

Query OK, 0 rows affected (0.001 sec)

(admin@127.0.0.1:6666) [(none)]> save mysql query rules to disk;

Query OK, 0 rows affected (0.037 sec)

1.4 验证读写分离

1.4.1 登录proxysql模拟读写操作

##模拟写操作

[root@vm3 ~]# mysql -uproxysql -pproxysql -h127.0.0.1 -P6033

Welcome to the MariaDB monitor. Commands end with ; or \g.

Your MySQL connection id is 2

Server version: 5.5.30 (ProxySQL)

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

(proxysql@127.0.0.1:6033) [(none)]> create database haha;

##模拟查询操作

(proxysql@127.0.0.1:6033) [(none)]> select * from mysql.user \G;

*************************** 1. row ***************************

Host: localhost

User: root

Password:

Select_priv: Y

Insert_priv: Y

Update_priv: Y

Delete_priv: Y

Create_priv: Y

Drop_priv: Y

Reload_priv: Y

.....................................

##模拟show操作

(proxysql@127.0.0.1:6033) [(none)]> show databases;

+--------------------+

| Database |

+--------------------+

| haha |

| information_schema |

| mysql |

| performance_schema |

| student |

+--------------------+

5 rows in set (0.004 sec)

1.4.2 验证读写分离是否成功

[root@vm3 ~]# mysql -uadmin -padmin -h127.0.0.1 -P6666

Welcome to the MariaDB monitor. Commands end with ; or \g.

Your MySQL connection id is 6

Server version: 5.5.30 (ProxySQL Admin Module)

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

(admin@127.0.0.1:6666) [(none)]> select * from stats_mysql_query_digest;

+-----------+--------------------+----------+--------------------+----------------------------------+------------+------------+------------+----------+----------+----------+

| hostgroup | schemaname | username | digest | digest_text | count_star | first_seen | last_seen | sum_time | min_time | max_time |

+-----------+--------------------+----------+--------------------+----------------------------------+------------+------------+------------+----------+----------+----------+

| 20 | information_schema | proxysql | 0x40B75DE8A4AD05EE | select * from mysql.user | 1 | 1609241562 | 1609241562 | 1740 | 1740 | 1740 |

| 20 | information_schema | proxysql | 0x65309B9B84E893C5 | select * from mysql.users | 2 | 1609241387 | 1609241558 | 31809 | 1660 | 30149 |

| 20 | information_schema | proxysql | 0x0F02B330C823D739 | select user,host from mysql.user | 1 | 1609240782 | 1609240782 | 35222 | 35222 | 35222 |

| 10 | information_schema | proxysql | 0xEB4549A4A010C504 | create database haha | 1 | 1609240749 | 1609240749 | 4299 | 4299 | 4299 |

| 20 | information_schema | proxysql | 0x02033E45904D3DF0 | show databases | 3 | 1609240723 | 1609241544 | 70697 | 3764 | 34361 |

| 10 | information_schema | proxysql | 0x594F2C744B698066 | select USER() | 2 | 1609240717 | 1609241353 | 0 | 0 | 0 |

| 10 | information_schema | proxysql | 0x226CD90D52A2BA0B | select @@version_comment limit ? | 2 | 1609240717 | 1609241353 | 0 | 0 | 0 |

+-----------+--------------------+----------+--------------------+----------------------------------+------------+------------+------------+----------+----------+----------+

7 rows in set (0.004 sec)

可知写操作被路由至10组(写组)，读操作被路由至20组(读组)

相关文章：

  • python 报表打印预览_python学习笔记之wxpython打印预览
  • python动态规划算法最大k乘积_动态规划最大K乘积问题
  • python实现语音播放失败_PyAudio alsa错误消息
  • python更新pip管理员权限_PIP升级权限被拒绝Windows 10
  • redis监听mysql数据库数据_天兔监控服务器和mysql，redis等监控
  • 第二部分 Mysql数据库管理_第二篇 数据库MySql
  • mysql 从库基于主库binlog恢复_MySQL利用binlog来恢复数据库
  • python可迭代对象相关的内建函数_第八章 Python可迭代对象、迭代器和生成器
  • mysql workbench连接jsp_JSP连接sql server2000数据库
  • 爱可生 mysql监控_数据库Prometheus 数据采集3-爱可生
  • java分割_Java中分割字符串的两种方法实例详解
  • java https 证书_JavaHTTPS客户端证书认证
  • java byte to hex_java 转换byte位hex字符串的工具代码
  • java泛型设计_使用java泛型设计通用方法
  • java线程 数据队列_java多线程从队列中取出数据执行
  • [译]Python中的类属性与实例属性的区别
  • Angular js 常用指令ng-if、ng-class、ng-option、ng-value、ng-click是如何使用的？
  • Angular4 模板式表单用法以及验证
  • bearychat的java client
  • CSS相对定位
  • css选择器
  • iOS帅气加载动画、通知视图、红包助手、引导页、导航栏、朋友圈、小游戏等效果源码...
  • Javascript设计模式学习之Observer(观察者)模式
  • jquery ajax学习笔记
  • JS基础篇--通过JS生成由字母与数字组合的随机字符串
  • PHP变量
  • Web设计流程优化：网页效果图设计新思路
  • Work@Alibaba 阿里巴巴的企业应用构建之路
  • 飞驰在Mesos的涡轮引擎上
  • 分享一个自己写的基于canvas的原生js图片爆炸插件
  • 记录一下第一次使用npm
  • 开放才能进步！Angular和Wijmo一起走过的日子
  • 删除表内多余的重复数据
  • 设计模式 开闭原则
  • 微信小程序开发问题汇总
  • Linux权限管理(week1_day5)--技术流ken
  • 扩展资源服务器解决oauth2 性能瓶颈
  • 如何通过报表单元格右键控制报表跳转到不同链接地址 ...
  • 新年再起“裁员潮”，“钢铁侠”马斯克要一举裁掉SpaceX 600余名员工 ...
  • ​iOS实时查看App运行日志
  • #Linux杂记--将Python3的源码编译为.so文件方法与Linux环境下的交叉编译方法
  • （33）STM32——485实验笔记
  • （4）STL算法之比较
  • (Java数据结构)ArrayList
  • （TOJ2804）Even? Odd?
  • （七）Java对象在Hibernate持久化层的状态
  • (切换多语言)vantUI+vue-i18n进行国际化配置及新增没有的语言包
  • （转）JVM内存分配 -Xms128m -Xmx512m -XX:PermSize=128m -XX:MaxPermSize=512m
  • (转)Linux下编译安装log4cxx
  • .java 9 找不到符号_java找不到符号
  • .net core控制台应用程序初识
  • .net mvc部分视图
  • .NET 材料检测系统崩溃分析
  • .NET处理HTTP请求
  • .net与java建立WebService再互相调用