This is an automated archive.
The original was posted on /r/mysql by /u/JustAServerNewbie on 2023-11-30 23:46:17+00:00.
So i have been trying to achieve high availability with mysql for sometime but i am having trouble with the proxy (currently using proxysql but also tried mysql-router with no luck) .
the goal is to have 3 mysql server containers running (all as their own deployment) and a proxysql/mysql-router (with multiple replications if possible?) to manage traffic. so far the mysql servers seem to work by themselfs (since i can access them using mysql workspace) but when trying to use the proxy i get prompted with (in the proxysql container)
proxysql:
MySQL_Session.cpp:5087:handler___status_CONNECTING_CLIENT___STATE_SERVER_HANDSHAKE(): [ERROR] ProxySQL Error: Access denied for user 'root'@'10.42.2.0' (using password: NO)
MySQL_Session.cpp:5087:handler___status_CONNECTING_CLIENT___STATE_SERVER_HANDSHAKE(): [ERROR] ProxySQL Error: Access denied for user 'root'@'10.42.2.0' (using password: YES)
and mysql-router cant seem to get a connection at all and than just restarts.
(even though other containers can see the mysql servers when using their dns name)
here is the yaml for the proxysql (currently i only have one mysql host written down)
# ProxySQL Deployment
apiVersion: apps/v1
kind: Deployment
metadata:
name: proxysql
spec:
replicas: 1
selector:
matchLabels:
app: proxysql
template:
metadata:
labels:
app: proxysql
spec:
containers:
- name: proxysql
image: proxysql/proxysql:2.1.1
ports:
- containerPort: 6033
- containerPort: 6032
- containerPort: 3306
env:
- name: MYSQL_HOST
value: "mysql-cluster-0"
- name: MYSQL_PORT
value: "3306"
- name: MYSQL_USER
value: root
- name: MYSQL_PASSWORD
value: examplepassword
- name: ADMIN_USER
value: admin_user
- name: ADMIN_PASSWORD
value: admin_password
# ProxySQL Service
apiVersion: v1
kind: Service
metadata:
name: proxysql-service
spec:
selector:
app: proxysql
ports:
- protocol: TCP
port: 3306
targetPort: 6033
type: LoadBalancer
and here is the mysql-router yaml
# mysql-router.yaml
apiVersion: apps/v1
kind: Deployment
metadata:
name: mysql-router
spec:
replicas: 1
selector:
matchLabels:
app: mysql-router
template:
metadata:
labels:
app: mysql-router
spec:
containers:
- name: mysql-router
image: mysql/mysql-router:latest
env:
- name: MYSQL_HOST
value: "mysql-cluster-0"
- name: MYSQL_PORT
value: "3306"
- name: MYSQL_USER
value: root
- name: MYSQL_PASSWORD
value: examplepassword
- name: MYSQL_INNODB_CLUSTER_MEMBERS
value: "mysql-cluster-0:3306,mysql-cluster-1:3306,mysql-cluster-2:3306"
# Service to expose the deployment with a LoadBalancer
apiVersion: v1
kind: Service
metadata:
name: mysql-router
spec:
selector:
app: mysql-router # Update this to match the labels on the MySQL Router pod
ports:
- protocol: TCP
port: 3306
targetPort: 3306
type: LoadBalancer
and here is the master mysql server
# mysql-deployment.yaml
apiVersion: apps/v1
kind: StatefulSet
metadata:
name: mysql-cluster-0
spec:
replicas: 1
selector:
matchLabels:
app: mysql
template:
metadata:
labels:
app: mysql
spec:
containers:
- name: mysql
image: mysql:8.0
env:
- name: MYSQL_ROOT_PASSWORD
value: examplepassword
- name: MYSQL_REPLICATION_MODE
value: master
- name: MYSQL_REPLICATION_GROUP_NAME
value: mygroup
- name: MYSQL_REPLICATION_USER
value: repl_user
- name: MYSQL_REPLICATION_PASSWORD
value: repl_password
- name: MYSQL_LOG_BIN
value: mysql-bin
- name: MYSQL_LOG_SLAVE_UPDATES
value: "ON"
- name: MYSQL_SERVER_ID
valueFrom:
fieldRef:
fieldPath: metadata.uid
- name: MYSQL_APPLICATION_USER
value: router_user
- name: MYSQL_APPLICATION_PASSWORD
value: router_password
# Service to expose the deployment with a LoadBalancer
apiVersion: v1
kind: Service
metadata:
name: mysql-cluster-0
spec:
selector:
app: mysql
ports:
- protocol: TCP
port: 3306
targetPort: 3306
type: LoadBalancer
(currently set to use a loadbalancer to expose it outside the cluster to see if they work by themselves)
Than you for your time