I'm following Kubernetes's MySQL as a StatefulSet from here. What the tutorial did not cover is how do I specify other environmental variables, like MYSQL_ROOT_PASSWORD, MYSQL_USER and such. I tried doing it myself, but didn't work. Here's the code:
apiVersion: apps/v1
kind: StatefulSet
metadata:
name: mysql
spec:
selector:
matchLabels:
app: mysql
serviceName: mysql
replicas: 3
template:
metadata:
labels:
app: mysql
spec:
initContainers:
- name: init-mysql
image: mysql:5.7
env:
- name: MYSQL_ROOT_PASSWORD
valueFrom:
secretKeyRef:
name: mysql-secret
key: mysql-root-password
command:
- bash
- "-c"
- |
set -ex
# Generate mysql server-id from pod ordinal index.
[[ `hostname` =~ -([0-9] )$ ]] || exit 1
ordinal=${BASH_REMATCH[1]}
echo [mysqld] > /mnt/conf.d/server-id.cnf
# Add an offset to avoid reserved server-id=0 value.
echo server-id=$((100 $ordinal)) >> /mnt/conf.d/server-id.cnf
# Copy appropriate conf.d files from config-map to emptyDir.
if [[ $ordinal -eq 0 ]]; then
cp /mnt/config-map/primary.cnf /mnt/conf.d/
else
cp /mnt/config-map/replica.cnf /mnt/conf.d/
fi
volumeMounts:
- name: conf
mountPath: /mnt/conf.d
- name: config-map
mountPath: /mnt/config-map
- name: clone-mysql
image: gcr.io/google-samples/xtrabackup:1.0
command:
- bash
- "-c"
- |
set -ex
# Skip the clone if data already exists.
[[ -d /var/lib/mysql/mysql ]] && exit 0
# Skip the clone on primary (ordinal index 0).
[[ `hostname` =~ -([0-9] )$ ]] || exit 1
ordinal=${BASH_REMATCH[1]}
[[ $ordinal -eq 0 ]] && exit 0
# Clone data from previous peer.
ncat --recv-only mysql-$(($ordinal-1)).mysql 3307 | xbstream -x -C /var/lib/mysql
# Prepare the backup.
xtrabackup --prepare --target-dir=/var/lib/mysql
volumeMounts:
- name: data
mountPath: /var/lib/mysql
subPath: mysql
- name: conf
mountPath: /etc/mysql/conf.d
containers:
- name: mysql
image: mysql:5.7
env:
- name: MYSQL_ROOT_PASSWORD
valueFrom:
secretKeyRef:
name: mysql-secret
key: mysql-root-password
ports:
- name: mysql
containerPort: 3306
volumeMounts:
- name: data
mountPath: /var/lib/mysql
subPath: mysql
- name: conf
mountPath: /etc/mysql/conf.d
resources:
requests:
cpu: 100m
memory: 500Mi
- name: xtrabackup
image: gcr.io/google-samples/xtrabackup:1.0
env:
- name: MYSQL_ROOT_PASSWORD
valueFrom:
secretKeyRef:
name: mysql-secret
key: mysql-root-password
ports:
- name: xtrabackup
containerPort: 3307
command:
- bash
- "-c"
- |
set -ex
cd /var/lib/mysql
# Determine binlog position of cloned data, if any.
if [[ -f xtrabackup_slave_info && "x$(<xtrabackup_slave_info)" != "x" ]]; then
# XtraBackup already generated a partial "CHANGE MASTER TO" query
# because we're cloning from an existing replica. (Need to remove the tailing semicolon!)
cat xtrabackup_slave_info | sed -E 's/;$//g' > change_master_to.sql.in
# Ignore xtrabackup_binlog_info in this case (it's useless).
rm -f xtrabackup_slave_info xtrabackup_binlog_info
elif [[ -f xtrabackup_binlog_info ]]; then
# We're cloning directly from primary. Parse binlog position.
[[ `cat xtrabackup_binlog_info` =~ ^(.*?)[[:space:]] (.*?)$ ]] || exit 1
rm -f xtrabackup_binlog_info xtrabackup_slave_info
echo "CHANGE MASTER TO MASTER_LOG_FILE='${BASH_REMATCH[1]}',\
MASTER_LOG_POS=${BASH_REMATCH[2]}" > change_master_to.sql.in
fi
# Check if we need to complete a clone by starting replication.
if [[ -f change_master_to.sql.in ]]; then
echo "Waiting for mysqld to be ready (accepting connections)"
until mysql -h 127.0.0.1 -uroot -p$MYSQL_ROOT_PASSWORD -e "SELECT 1"; do sleep 1; done
echo "Initializing replication from clone position"
mysql -h 127.0.0.1 \
-e "$(<change_master_to.sql.in), \
MASTER_HOST='mysql-0.mysql', \
MASTER_USER='root', \
MASTER_PASSWORD=$MYSQL_ROOT_PASSWORD, \
MASTER_CONNECT_RETRY=10; \
START SLAVE USER='root' PASSWORD=$MYSQL_ROOT_PASSWORD;" || exit 1
# In case of container restart, attempt this at-most-once.
mv change_master_to.sql.in change_master_to.sql.orig
fi
# Start a server to send backups when requested by peers.
exec ncat --listen --keep-open --send-only --max-conns=1 3307 -c \
"xtrabackup --backup --slave-info --stream=xbstream --host=127.0.0.1 --user=root --password=$MYSQL_ROOT_PASSWORD"
volumeMounts:
- name: data
mountPath: /var/lib/mysql
subPath: mysql
- name: conf
mountPath: /etc/mysql/conf.d
resources:
requests:
cpu: 50m
memory: 50Mi
volumes:
- name: conf
emptyDir: {}
- name: config-map
configMap:
name: mysql
volumeClaimTemplates:
- metadata:
name: data
spec:
accessModes: ["ReadWriteOnce"]
resources:
requests:
storage: 10Gi
and here is my kubectl get all,secret,configmap's result:
NAME READY STATUS RESTARTS AGE
pod/mysql-0 2/2 Running 0 11m
pod/mysql-1 1/2 Error 7 11m
pod/mysql-2 0/2 Init:CrashLoopBackOff 6 10m
NAME TYPE CLUSTER-IP EXTERNAL-IP PORT(S) AGE
service/kubernetes ClusterIP 10.245.0.1 <none> 443/TCP 2d23h
service/mysql ClusterIP None <none> 3306/TCP 43h
service/mysql-read ClusterIP 10.245.81.173 <none> 3306/TCP 43h
NAME READY UP-TO-DATE AVAILABLE AGE
deployment.apps/phpmyadmin 1/1 1 1 2d18h
NAME DESIRED CURRENT READY AGE
replicaset.apps/phpmyadmin-6c57fd98bf 1 1 1 2d18h
NAME READY AGE
statefulset.apps/mysql 1/3 11m
NAME TYPE DATA AGE
secret/mysql-pass Opaque 1 46h
secret/mysql-secret Opaque 3 2d18h
NAME DATA AGE
configmap/mysql 4 2d18h
When I tried to describe the mysql-1 pod, it said that:
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
But when I tried logging in using my defined root password on mysql-0 pod, it works. I have also tried looking a duplicated question here, but the past's tutorial code has changed (FYI, I also tried it too despite the change), so it did not really help. Any help will do! Thanks in advance!
UPDATE I tried adding this:
- name: MYSQL_ROOT_HOST
value: "%"
on my mysql and xtrabackup containers, also didn't work, here's the get pods --watch return:
NAME READY STATUS RESTARTS AGE
mysql-0 0/2 Init:0/2 0 7s
mysql-0 0/2 Init:1/2 0 18s
mysql-0 0/2 PodInitializing 0 19s
mysql-0 2/2 Running 0 20s
mysql-1 0/2 Pending 0 0s
mysql-1 0/2 Pending 0 0s
mysql-1 0/2 Pending 0 8s
mysql-1 0/2 Init:0/2 0 8s
mysql-1 0/2 Init:1/2 0 26s
mysql-1 0/2 Init:1/2 0 27s
mysql-1 0/2 PodInitializing 0 34s
mysql-1 2/2 Running 0 35s
mysql-2 0/2 Pending 0 0s
mysql-2 0/2 Pending 0 0s
mysql-1 1/2 Error 0 37s
mysql-1 1/2 Error 1 38s
mysql-2 0/2 Pending 0 3s
mysql-2 0/2 Init:0/2 0 3s
mysql-1 1/2 CrashLoopBackOff 1 53s
mysql-1 1/2 Error 2 54s
mysql-2 0/2 Init:1/2 0 21s
mysql-2 0/2 Init:Error 0 22s
mysql-2 0/2 Init:Error 1 23s
mysql-2 0/2 Init:CrashLoopBackOff 1 24s
mysql-1 1/2 CrashLoopBackOff 2 64s
mysql-2 0/2 Init:Error 2 38s
mysql-1 1/2 Error 3 79s
mysql-2 0/2 Init:CrashLoopBackOff 2 50s
mysql-1 1/2 CrashLoopBackOff 3 92s
mysql-2 0/2 Init:Error 3 62s
mysql-2 0/2 Init:CrashLoopBackOff 3 74s
mysql-1 1/2 Error 4 2m1s
mysql-1 1/2 CrashLoopBackOff 4 2m11s
mysql-2 0/2 Init:Error 4 113s
mysql-2 0/2 Init:CrashLoopBackOff 4 2m6s
mysql-1 1/2 Error 5 3m29s
mysql-1 1/2 CrashLoopBackOff 5 3m41s
mysql-2 0/2 Init:Error 5 3m23s
mysql-2 0/2 Init:CrashLoopBackOff 5 3m34s
For some reason mysql-1 ran for a while, then stopped.
Logs from mysql-1 pod :
2021-10-25 03:16:36 00:00 [Note] [Entrypoint]: Entrypoint script for MySQL Server 5.7.36-1debian10 started.
2021-10-25 03:16:36 00:00 [Note] [Entrypoint]: Switching to dedicated user 'mysql'
2021-10-25 03:16:36 00:00 [Note] [Entrypoint]: Entrypoint script for MySQL Server 5.7.36-1debian10 started.
2021-10-25T03:16:37.231906Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2021-10-25T03:16:37.234015Z 0 [Note] mysqld (mysqld 5.7.36) starting as process 1 ...
2021-10-25T03:16:37.237044Z 0 [Note] InnoDB: PUNCH HOLE support available
2021-10-25T03:16:37.237196Z 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2021-10-25T03:16:37.237263Z 0 [Note] InnoDB: Uses event mutexes
2021-10-25T03:16:37.237345Z 0 [Note] InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier
2021-10-25T03:16:37.237416Z 0 [Note] InnoDB: Compressed tables use zlib 1.2.11
2021-10-25T03:16:37.237489Z 0 [Note] InnoDB: Using Linux native AIO
2021-10-25T03:16:37.237792Z 0 [Note] InnoDB: Number of pools: 1
2021-10-25T03:16:37.238102Z 0 [Note] InnoDB: Using CPU crc32 instructions
2021-10-25T03:16:37.240179Z 0 [Note] InnoDB: Initializing buffer pool, total size = 128M, instances = 1, chunk size = 128M
2021-10-25T03:16:37.248111Z 0 [Note] InnoDB: Completed initialization of buffer pool
2021-10-25T03:16:37.251017Z 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority().
2021-10-25T03:16:37.263519Z 0 [Note] InnoDB: Highest supported file format is Barracuda.
2021-10-25T03:16:37.307064Z 0 [Note] InnoDB: Removed temporary tablespace data file: "ibtmp1"
2021-10-25T03:16:37.307319Z 0 [Note] InnoDB: Creating shared tablespace for temporary tables
2021-10-25T03:16:37.307466Z 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
2021-10-25T03:16:37.353707Z 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB.
2021-10-25T03:16:37.354244Z 0 [Note] InnoDB: 96 redo rollback segment(s) found. 96 redo rollback segment(s) are active.
2021-10-25T03:16:37.354256Z 0 [Note] InnoDB: 32 non-redo rollback segment(s) are active.
2021-10-25T03:16:37.355048Z 0 [Note] InnoDB: Waiting for purge to start
2021-10-25T03:16:37.406335Z 0 [Note] InnoDB: 5.7.36 started; log sequence number 12660776
2021-10-25T03:16:37.406850Z 0 [Note] Plugin 'FEDERATED' is disabled.
2021-10-25T03:16:37.413880Z 0 [Note] InnoDB: Loading buffer pool(s) from /var/lib/mysql/ib_buffer_pool
2021-10-25T03:16:37.426491Z 0 [Note] InnoDB: Buffer pool(s) load completed at 211025 3:16:37
2021-10-25T03:16:37.427210Z 0 [Note] Salting uuid generator variables, current_pid: 1, server_start_time: 1635131797, bytes_sent: 0,
2021-10-25T03:16:37.427438Z 0 [Note] Generated uuid: 'f6f89137-3541-11ec-84bc-f29710c93c86', server_start_time: 281476611842454, bytes_sent: 94355589700272
2021-10-25T03:16:37.427554Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: f6f89137-3541-11ec-84bc-f29710c93c86.
2021-10-25T03:16:38.109098Z 0 [Note] Auto generated SSL certificates are placed in data directory.
2021-10-25T03:16:38.109327Z 0 [Warning] A deprecated TLS version TLSv1 is enabled. Please use TLSv1.2 or higher.
2021-10-25T03:16:38.109395Z 0 [Warning] A deprecated TLS version TLSv1.1 is enabled. Please use TLSv1.2 or higher.
2021-10-25T03:16:38.109907Z 0 [Warning] CA certificate ca.pem is self signed.
2021-10-25T03:16:38.354105Z 0 [Note] Auto generated RSA key files are placed in data directory.
2021-10-25T03:16:38.354795Z 0 [Note] Server hostname (bind-address): '*'; port: 3306
2021-10-25T03:16:38.354968Z 0 [Note] IPv6 is available.
2021-10-25T03:16:38.355039Z 0 [Note] - '::' resolves to '::';
2021-10-25T03:16:38.355125Z 0 [Note] Server socket created on IP: '::'.
2021-10-25T03:16:38.357907Z 0 [Warning] Insecure configuration for --pid-file: Location '/var/run/mysqld' in the path is accessible to all OS users. Consider choosing a different directory.
2021-10-25T03:16:38.361720Z 0 [Note] Failed to start slave threads for channel ''
2021-10-25T03:16:38.366691Z 0 [Note] Event Scheduler: Loaded 0 events
2021-10-25T03:16:38.367135Z 0 [Note] mysqld: ready for connections.
Version: '5.7.36' socket: '/var/run/mysqld/mysqld.sock' port: 3306 MySQL Community Server (GPL)
2021-10-25T03:16:39.011694Z 3 [Note] Access denied for user 'root'@'localhost' (using password: NO)
2021-10-25T03:16:39.759020Z 5 [Note] Access denied for user 'root'@'localhost' (using password: NO)
2021-10-25T03:16:56.006754Z 7 [Note] Access denied for user 'root'@'localhost' (using password: NO)
2021-10-25T03:17:21.016988Z 9 [Note] Access denied for user 'root'@'localhost' (using password: NO)
2021-10-25T03:18:03.010443Z 11 [Note] Access denied for user 'root'@'localhost' (using password: NO)
2021-10-25T03:19:30.984929Z 13 [Note] Access denied for user 'root'@'localhost' (using password: NO)
2021-10-25T03:22:25.011291Z 15 [Note] Access denied for user 'root'@'localhost' (using password: NO)
UPDATE
What I have tried again is stripping down a lot of optional code(I think...), and here is the result:
apiVersion: apps/v1
kind: StatefulSet
metadata:
name: mysql
spec:
selector:
matchLabels:
app: mysql
serviceName: mysql
replicas: 3
template:
metadata:
labels:
app: mysql
spec:
initContainers:
- name: init-mysql
image: mysql:5.7
env:
- name: MYSQL_ROOT_PASSWORD
value: admin123
command:
- bash
- "-c"
- |
set -ex
# Generate mysql server-id from pod ordinal index.
[[ `hostname` =~ -([0-9] )$ ]] || exit 1
ordinal=${BASH_REMATCH[1]}
echo [mysqld] > /mnt/conf.d/server-id.cnf
# Add an offset to avoid reserved server-id=0 value.
echo server-id=$((100 $ordinal)) >> /mnt/conf.d/server-id.cnf
# Copy appropriate conf.d files from config-map to emptyDir.
if [[ $ordinal -eq 0 ]]; then
cp /mnt/config-map/primary.cnf /mnt/conf.d/
else
cp /mnt/config-map/replica.cnf /mnt/conf.d/
fi
volumeMounts:
- name: conf
mountPath: /mnt/conf.d
- name: config-map
mountPath: /mnt/config-map
containers:
- name: mysql
image: mysql:5.7
env:
- name: MYSQL_ROOT_PASSWORD
value: admin123
ports:
- name: mysql
containerPort: 3306
volumeMounts:
- name: data
mountPath: /var/lib/mysql
subPath: mysql
- name: conf
mountPath: /etc/mysql/conf.d
resources:
requests:
cpu: 100m
memory: 500Mi
volumes:
- name: conf
emptyDir: {}
- name: config-map
configMap:
name: mysql
volumeClaimTemplates:
- metadata:
name: data
spec:
accessModes: ["ReadWriteOnce"]
resources:
requests:
storage: 10Gi
From the yaml above, the master pod is running without a problem, but for the slave pods, the process is pretty normal until it ran for a while, then they encountered an error. After that, the state went back to running again. I checked the error from the logs and turned out that they could not authenticate mysql using the user root and the root password that I've set, so they tried authenticating mysql using the user root with no password specified and succeeded. That's weird because I've set the env variables MYSQL_ROOT_PASSWORD already inside the initContainers and the containers themselves, so I tried to exec each of the slave pods. I ran echo $MYSQL_ROOT_PASSWORD and the result is the same as I have specified. So the problem now from the yaml above is the env variable that I've set on the yaml file did not penetrate through the mysql application, but only through the container's environmental system.
CodePudding user response：
I have solved the problem. Turns out I tweaked some of my first uploaded code because there was a syntax error and typos:
$MYSQL_ROOT_PASSWORD ==> $(MYSQL_ROOT_PASSWORD)
#when calling env var inside command, you have to add the brackets
-uroot -p$MYQSL_ROOT_PASSWORD ==> -u root --password=$(MYSQL_ROOT_PASSWORD)
START SLAVE USER='root' PASSWORD=$(MYSQL_ROOT_PASSWORD);" || exit 1 ==> START SLAVE;" || exit 1
and
MASTER_PASSWORD=$(MYSQL_ROOT_PASSWORD), \ ==> MASTER_PASSWORD='$(MYSQL_ROOT_PASSWORD)', \
# have to put ' before and after getting the env value
Also, I noticed that the code from mine only works if you removed the livenessProbe and readinessProbe. Somehow, both of them is called before the env var gets loaded, that's why when readinessProbe executes this command:
command: ["mysql", "-h", "127.0.0.1", "-u", "root", "--password=$(MYSQL_ROOT_PASSWORD)", "-e", "SELECT 1"]
it failed because $(MYSQL_ROOT_PASSWORD)'s value was not found, therefore, resulting this error:
2021-10-25T03:16:39.011694Z 3 [Note] Access denied for user 'root'@'localhost' (using password: NO)
If someone has any additional info between livenessProbe, readinessProbe, and env var, please let me know, because my theory might be wrong. CMIIW. :)