[TOC]

1.Docker 快速部署 MySQL 数据库服务器

MySQL 是一种广泛使用的开源关系数据库管理系统 (RDBMS),其久经考验的性能、可靠性和易用性,MySQL 已成为基于 Web 的应用程序的领先数据库选择。

MySQL 帮助文档:https://docs.oracle.com/en-us/iaas/mysql-database/doc/getting-started.html
镜像仓库地址:https://hub.docker.com/_/mysql
镜像问题:https://github.com/docker-library/mysql/issues

温馨提示:此处实践环境是使用Docker,若你没有安装Docker环境或者不了解的Docker容器的朋友,可以参考博主学习【Docker的系列笔记】汇总,关注 WeiyiGeek 公众号回复【Docker容器学习之路汇总】即可获得学习资料:
https://www.weiyigeek.top/wechat.html?key=Kubernetes学习之路汇总


命令方式

步骤 01.快速部署脚本命令。

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
# 准备数据持久化目录
mkdir -vp /app/data

# 准备mysql8.x仓库镜像
docker pull mysql:8.0.30

# 准备root密码不采用环境变量直接显示密码
echo "weiyigeek.top" > /app/my-secret-pw

# 一条命令创建运行mysql数据库容器
docker run -d --name mysql8.0 --restart=always \
-v "/app/data":/var/lib/mysql \
-v "/app/my-secret-pw":/app/my-secret-pw \
-e MYSQL_ROOT_PASSWORD_FILE=/app/my-secret-pw \
-e MYSQL_DATABASE=app \
-e MYSQL_USER=weiyigeek \
-e MYSQL_PASSWORD=password \
-p 3306:3306 \
mysql:8.0.30 \
--default-authentication-plugin=mysql_native_password
# 144e883af1a99901913a986d540382c8aefe3e5bd96730ad76a019b2567159bb

# 可以为 mysqld 使用特定的 UID/GID , 例如此处的 1000 用户。
--user 1000:1000

# 可以为 mysqld 指定命令行参数。
--character-set-server=utf8mb4
--collation-server=utf8mb4_unicode_ci


步骤 02.查看验证在Docker中的部署情况。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
# 容器
$ docker ps
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
05c5a0e23e39 mysql:8.0.30 "docker-entrypoint.s…" About a minute ago Up About a minute 0.0.0.0:3306->3306/tcp, 33060/tcp mysql8.0

# 日志
$ docker logs mysql8.0
2022-09-27 14:19:03+00:00 [Note] [Entrypoint]: Entrypoint script for MySQL Server 8.0.30-1.el8 started.
2022-09-27 14:19:03+00:00 [Note] [Entrypoint]: Switching to dedicated user 'mysql'
2022-09-27 14:19:03+00:00 [Note] [Entrypoint]: Entrypoint script for MySQL Server 8.0.30-1.el8 started.
2022-09-27 14:19:03+00:00 [Note] [Entrypoint]: Initializing database files

# 连接测试
$ docker exec -it mysql8.0 sh -c 'mysql -u root -p"weiyigeek.top"'
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql> select @@version;
+-----------+
| @@version |
+-----------+
| 8.0.30 |
+-----------+
1 row in set (0.00 sec)


步骤 03.部署 Adminer 进行管理连接 MySQL 数据库, Adminer 是一个类似于 phpMyAdmin 的 MySQL 管理客户端。
Adminer 可用于连接 MySQL, PostgreSQL, SQLite, MSSQL, Oracle, Firebird, SimpleDB, Elasticsearch and MongoDB 等数据库。

1
2
3
4
5
6
7
docker pull adminer:latest

# Standalone
docker run -d --restart unless-stopped --name db_adminer -p 8080:8080 adminer:latest

# FastCGI
docker run -d --name db_admine_fastcgi --link some_database:db -p 9000:9000 adminer:fastcgi

随后使用浏览器访问宿主机的8080端口进行连接:

WeiyiGeek.db_adminer

WeiyiGeek.db_adminer

温馨提示:MySQL的默认配置可以在 /etc/mysql/my.cnf,或可以自定义配置文件/etc/mysql/conf.d/my.cnf

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
tee my.cnf <<'EOF'
[mysqld]
# 执行用户
user=mysql

# 开放监听服务端口
port=3306
bind-address=*
socket=/var/run/mysqld/mysqld.sock

# 数据目录
datadir=/var/lib/mysql

# 进程 pid 文件
pid-file=/var/run/mysqld/mysqld.pid

# 插件默认路径
plugin-dir=/usr/lib64/mysql/plugin/

# 安全文件路径
secure-file-priv=/var/lib/mysql-files

# 启用日志与路径设置
general-log=on
general-log-file=/var/lib/mysql/mysql8x.log

# 服务器字符集设置
character-set-server=utf8mb4
collation-server=utf8mb4_unicode_ci

# 数据库容灾binlog启用配置
log-bin=binlog
log-bin-index=binlog.index

# 认证密码策略, 默认 aching_sha2_password , 针对于old链接认证方式为 mysql_native_password
default-authentication-plugin=mysql_native_password

# 跳过某些操作
skip-host-cache
skip-name-resolve

[client]
socket=/var/run/mysqld/mysqld.sock
EOF

温馨提示:如果您想查看 mysqld 可用选项的完整列表,只需运行

$ docker run -it –rm mysql:8.0.30 –verbose –help

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
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
# my.cnf 可用配置
Variables (--variable-name=value)
and boolean options {FALSE|TRUE} Value (after reading options)
------------------------------------------------------------ -------------
abort-slave-event-count 0
activate-all-roles-on-login FALSE
admin-address (No default value)
admin-port 33062
admin-ssl TRUE
admin-ssl-ca (No default value)
admin-ssl-capath (No default value)
admin-ssl-cert (No default value)
admin-ssl-cipher (No default value)
admin-ssl-crl (No default value)
admin-ssl-crlpath (No default value)
admin-ssl-key (No default value)
admin-tls-ciphersuites (No default value)
admin-tls-version TLSv1.2,TLSv1.3
allow-suspicious-udfs FALSE
archive ON
authentication-policy *,,
auto-generate-certs TRUE
auto-increment-increment 1
auto-increment-offset 1
autocommit TRUE
automatic-sp-privileges TRUE
avoid-temporal-upgrade FALSE
back-log 151
basedir /usr/
big-tables FALSE
bind-address *
binlog-cache-size 32768
binlog-checksum CRC32
binlog-direct-non-transactional-updates FALSE
binlog-encryption FALSE
binlog-error-action ABORT_SERVER
binlog-expire-logs-auto-purge TRUE
binlog-expire-logs-seconds 2592000
binlog-format ROW
binlog-group-commit-sync-delay 0
binlog-group-commit-sync-no-delay-count 0
binlog-gtid-simple-recovery TRUE
binlog-max-flush-queue-time 0
binlog-order-commits TRUE
binlog-rotate-encryption-master-key-at-startup FALSE
binlog-row-event-max-size 8192
binlog-row-image FULL
binlog-row-metadata MINIMAL
binlog-row-value-options
binlog-rows-query-log-events FALSE
binlog-stmt-cache-size 32768
binlog-transaction-compression FALSE
binlog-transaction-compression-level-zstd 3
binlog-transaction-dependency-history-size 25000
binlog-transaction-dependency-tracking COMMIT_ORDER
blackhole ON
block-encryption-mode aes-128-ecb
bulk-insert-buffer-size 8388608
caching-sha2-password-auto-generate-rsa-keys TRUE
caching-sha2-password-digest-rounds 5000
caching-sha2-password-private-key-path private_key.pem
caching-sha2-password-public-key-path public_key.pem
character-set-client-handshake TRUE
character-set-filesystem binary
character-set-server utf8mb4
character-sets-dir /usr/share/mysql-8.0/charsets/
check-proxy-users FALSE
chroot (No default value)
collation-server utf8mb4_0900_ai_ci
completion-type NO_CHAIN
concurrent-insert AUTO
connect-timeout 10
connection-memory-chunk-size 8912
connection-memory-limit 18446744073709551615
console FALSE
create-admin-listener-thread FALSE
cte-max-recursion-depth 1000
daemonize FALSE
datadir /var/lib/mysql/
default-authentication-plugin caching_sha2_password
default-password-lifetime 0
default-storage-engine InnoDB
default-table-encryption FALSE
default-time-zone (No default value)
default-tmp-storage-engine InnoDB
default-week-format 0
delay-key-write ON
delayed-insert-limit 100
delayed-insert-timeout 300
delayed-queue-size 1000
disabled-storage-engines
disconnect-on-expired-password TRUE
disconnect-slave-event-count 0
div-precision-increment 4
end-markers-in-json FALSE
enforce-gtid-consistency FALSE
eq-range-index-dive-limit 200
event-scheduler ON
expire-logs-days 0
explicit-defaults-for-timestamp TRUE
external-locking FALSE
federated OFF
flush FALSE
flush-time 0
ft-boolean-syntax + -><()~*:""&|
ft-max-word-len 84
ft-min-word-len 4
ft-query-expansion-limit 20
ft-stopword-file (No default value)
gdb FALSE
general-log FALSE
general-log-file /var/lib/mysql/a29706ab34c6.log
generated-random-password-length 20
global-connection-memory-limit 18446744073709551615
global-connection-memory-tracking FALSE
group-concat-max-len 1024
group-replication-consistency EVENTUAL
gtid-executed-compression-period 0
gtid-mode OFF
help TRUE
histogram-generation-max-mem-size 20000000
host-cache-size 279
information-schema-stats-expiry 86400
init-connect
init-file (No default value)
init-replica
init-slave
initialize FALSE
initialize-insecure FALSE
innodb-adaptive-flushing TRUE
innodb-adaptive-flushing-lwm 10
innodb-adaptive-hash-index TRUE
innodb-adaptive-hash-index-parts 8
innodb-adaptive-max-sleep-delay 150000
innodb-api-bk-commit-interval 5
innodb-api-disable-rowlock FALSE
innodb-api-enable-binlog FALSE
innodb-api-enable-mdl FALSE
innodb-api-trx-level 0
innodb-autoextend-increment 64
innodb-autoinc-lock-mode 2
innodb-buffer-pool-chunk-size 134217728
innodb-buffer-pool-dump-at-shutdown TRUE
innodb-buffer-pool-dump-now FALSE
innodb-buffer-pool-dump-pct 25
innodb-buffer-pool-filename ib_buffer_pool
innodb-buffer-pool-in-core-file TRUE
innodb-buffer-pool-instances 0
innodb-buffer-pool-load-abort FALSE
innodb-buffer-pool-load-at-startup TRUE
innodb-buffer-pool-load-now FALSE
innodb-buffer-pool-size 134217728
innodb-change-buffer-max-size 25
innodb-change-buffering all
innodb-checksum-algorithm crc32
innodb-cmp-per-index-enabled FALSE
innodb-commit-concurrency 0
innodb-compression-failure-threshold-pct 5
innodb-compression-level 6
innodb-compression-pad-pct-max 50
innodb-concurrency-tickets 5000
innodb-data-file-path ibdata1:12M:autoextend
innodb-data-home-dir (No default value)
innodb-ddl-buffer-size 1048576
innodb-ddl-threads 4
innodb-deadlock-detect TRUE
innodb-dedicated-server FALSE
innodb-default-row-format dynamic
innodb-directories (No default value)
innodb-disable-sort-file-cache FALSE
innodb-doublewrite ON
innodb-doublewrite-batch-size 0
innodb-doublewrite-dir (No default value)
innodb-doublewrite-files 0
innodb-doublewrite-pages 0
innodb-extend-and-initialize TRUE
innodb-fast-shutdown 1
innodb-file-per-table TRUE
innodb-fill-factor 100
innodb-flush-log-at-timeout 1
innodb-flush-log-at-trx-commit 1
innodb-flush-method fsync
innodb-flush-neighbors 0
innodb-flush-sync TRUE
innodb-flushing-avg-loops 30
innodb-force-load-corrupted FALSE
innodb-force-recovery 0
innodb-fsync-threshold 0
innodb-ft-aux-table (No default value)
innodb-ft-cache-size 8000000
innodb-ft-enable-diag-print FALSE
innodb-ft-enable-stopword TRUE
innodb-ft-max-token-size 84
innodb-ft-min-token-size 3
innodb-ft-num-word-optimize 2000
innodb-ft-result-cache-limit 2000000000
innodb-ft-server-stopword-table (No default value)
innodb-ft-sort-pll-degree 2
innodb-ft-total-cache-size 640000000
innodb-ft-user-stopword-table (No default value)
innodb-idle-flush-pct 100
innodb-io-capacity 200
innodb-io-capacity-max 4294967295
innodb-lock-wait-timeout 50
innodb-log-buffer-size 16777216
innodb-log-checksums TRUE
innodb-log-compressed-pages TRUE
innodb-log-file-size 50331648
innodb-log-files-in-group 2
innodb-log-group-home-dir (No default value)
innodb-log-spin-cpu-abs-lwm 80
innodb-log-spin-cpu-pct-hwm 50
innodb-log-wait-for-flush-spin-hwm 400
innodb-log-write-ahead-size 8192
innodb-log-writer-threads TRUE
innodb-lru-scan-depth 1024
innodb-max-dirty-pages-pct 90
innodb-max-dirty-pages-pct-lwm 10
innodb-max-purge-lag 0
innodb-max-purge-lag-delay 0
innodb-max-undo-log-size 1073741824
innodb-monitor-disable (No default value)
innodb-monitor-enable (No default value)
innodb-monitor-reset (No default value)
innodb-monitor-reset-all (No default value)
innodb-old-blocks-pct 37
innodb-old-blocks-time 1000
innodb-online-alter-log-max-size 134217728
innodb-open-files 0
innodb-optimize-fulltext-only FALSE
innodb-page-cleaners 4
innodb-page-size 16384
innodb-parallel-read-threads 4
innodb-print-all-deadlocks FALSE
innodb-print-ddl-logs FALSE
innodb-purge-batch-size 300
innodb-purge-rseg-truncate-frequency 128
innodb-purge-threads 4
innodb-random-read-ahead FALSE
innodb-read-ahead-threshold 56
innodb-read-io-threads 4
innodb-read-only FALSE
innodb-redo-log-archive-dirs (No default value)
innodb-redo-log-capacity 104857600
innodb-redo-log-encrypt FALSE
innodb-replication-delay 0
innodb-rollback-on-timeout FALSE
innodb-rollback-segments 128
innodb-segment-reserve-factor 12.5
innodb-sort-buffer-size 1048576
innodb-spin-wait-delay 6
innodb-spin-wait-pause-multiplier 50
innodb-stats-auto-recalc TRUE
innodb-stats-include-delete-marked FALSE
innodb-stats-method nulls_equal
innodb-stats-on-metadata FALSE
innodb-stats-persistent TRUE
innodb-stats-persistent-sample-pages 20
innodb-stats-transient-sample-pages 8
innodb-status-file FALSE
innodb-status-output FALSE
innodb-status-output-locks FALSE
innodb-strict-mode TRUE
innodb-sync-array-size 1
innodb-sync-spin-loops 30
innodb-table-locks TRUE
innodb-temp-data-file-path ibtmp1:12M:autoextend
innodb-temp-tablespaces-dir (No default value)
innodb-thread-concurrency 0
innodb-thread-sleep-delay 10000
innodb-tmpdir (No default value)
innodb-undo-directory (No default value)
innodb-undo-log-encrypt FALSE
innodb-undo-log-truncate TRUE
innodb-undo-tablespaces 2
innodb-use-fdatasync FALSE
innodb-use-native-aio TRUE
innodb-validate-tablespace-paths TRUE
innodb-write-io-threads 4
interactive-timeout 28800
internal-tmp-mem-storage-engine TempTable
join-buffer-size 262144
keep-files-on-create FALSE
key-buffer-size 8388608
key-cache-age-threshold 300
key-cache-block-size 1024
key-cache-division-limit 100
keyring-migration-destination (No default value)
keyring-migration-host (No default value)
keyring-migration-port 0
keyring-migration-socket (No default value)
keyring-migration-source (No default value)
keyring-migration-to-component FALSE
keyring-migration-user (No default value)
language /usr/share/mysql-8.0/
large-pages FALSE
lc-messages en_US
lc-messages-dir /usr/share/mysql-8.0/
lc-time-names en_US
local-infile FALSE
lock-wait-timeout 31536000
log-bin binlog
log-bin-index binlog.index
log-bin-trust-function-creators FALSE
log-bin-use-v1-row-events FALSE
log-error stderr
log-error-services log_filter_internal; log_sink_internal
log-error-suppression-list
log-error-verbosity 1
log-isam myisam.log
log-output FILE
log-queries-not-using-indexes FALSE
log-raw FALSE
log-replica-updates TRUE
log-short-format FALSE
log-slave-updates TRUE
log-slow-admin-statements FALSE
log-slow-extra FALSE
log-slow-replica-statements FALSE
log-slow-slave-statements FALSE
log-statements-unsafe-for-binlog TRUE
log-tc tc.log
log-tc-size 24576
log-throttle-queries-not-using-indexes 0
log-timestamps UTC
long-query-time 10
low-priority-updates FALSE
lower-case-table-names 0
mandatory-roles
master-info-file master.info
master-info-repository TABLE
master-retry-count 86400
master-verify-checksum FALSE
max-allowed-packet 67108864
max-binlog-cache-size 18446744073709547520
max-binlog-dump-events 0
max-binlog-size 1073741824
max-binlog-stmt-cache-size 18446744073709547520
max-connect-errors 100
max-connections 151
max-delayed-threads 20
max-digest-length 1024
max-error-count 1024
max-execution-time 0
max-heap-table-size 16777216
max-join-size 18446744073709551615
max-length-for-sort-data 4096
max-points-in-geometry 65536
max-prepared-stmt-count 16382
max-relay-log-size 0
max-seeks-for-key 18446744073709551615
max-sort-length 1024
max-sp-recursion-depth 0
max-user-connections 0
max-write-lock-count 18446744073709551615
memlock FALSE
min-examined-row-limit 0
myisam-block-size 1024
myisam-data-pointer-size 6
myisam-max-sort-file-size 9223372036853727232
myisam-mmap-size 18446744073709551615
myisam-recover-options OFF
myisam-sort-buffer-size 8388608
myisam-stats-method nulls_unequal
myisam-use-mmap FALSE
mysql-native-password-proxy-users FALSE
mysqlx ON
mysqlx-bind-address *
mysqlx-cache-cleaner ON
mysqlx-compression-algorithms DEFLATE_STREAM,LZ4_MESSAGE,ZSTD_STREAM
mysqlx-connect-timeout 30
mysqlx-deflate-default-compression-level 3
mysqlx-deflate-max-client-compression-level 5
mysqlx-document-id-unique-prefix 0
mysqlx-enable-hello-notice TRUE
mysqlx-idle-worker-thread-timeout 60
mysqlx-interactive-timeout 28800
mysqlx-lz4-default-compression-level 2
mysqlx-lz4-max-client-compression-level 8
mysqlx-max-allowed-packet 67108864
mysqlx-max-connections 100
mysqlx-min-worker-threads 2
mysqlx-port 33060
mysqlx-port-open-timeout 0
mysqlx-read-timeout 30
mysqlx-socket (No default value)
mysqlx-ssl-ca (No default value)
mysqlx-ssl-capath (No default value)
mysqlx-ssl-cert (No default value)
mysqlx-ssl-cipher (No default value)
mysqlx-ssl-crl (No default value)
mysqlx-ssl-crlpath (No default value)
mysqlx-ssl-key (No default value)
mysqlx-wait-timeout 28800
mysqlx-write-timeout 60
mysqlx-zstd-default-compression-level 3
mysqlx-zstd-max-client-compression-level 11
net-buffer-length 16384
net-read-timeout 30
net-retry-count 10
net-write-timeout 60
new FALSE
ngram ON
ngram-token-size 2
no-dd-upgrade FALSE
offline-mode FALSE
old FALSE
old-alter-table FALSE
old-style-user-limits FALSE
open-files-limit 1048576
optimizer-max-subgraph-pairs 100000
optimizer-prune-level 1
optimizer-search-depth 62
optimizer-switch index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,duplicateweedout=on,subquery_materialization_cost_based=on,use_index_extensions=on,condition_fanout_filter=on,derived_merge=on,use_invisible_indexes=off,skip_scan=on,hash_join=on,subquery_to_derived=off,prefer_ordering_index=on,hypergraph_optimizer=off,derived_condition_pushdown=on
optimizer-trace
optimizer-trace-features greedy_search=on,range_optimizer=on,dynamic_range=on,repeated_subselect=on
optimizer-trace-limit 1
optimizer-trace-max-mem-size 1048576
optimizer-trace-offset -1
parser-max-mem-size 18446744073709551615
partial-revokes FALSE
password-history 0
password-require-current FALSE
password-reuse-interval 0
performance-schema TRUE
performance-schema-accounts-size -1
performance-schema-consumer-events-stages-current FALSE
performance-schema-consumer-events-stages-history FALSE
performance-schema-consumer-events-stages-history-long FALSE
performance-schema-consumer-events-statements-cpu FALSE
performance-schema-consumer-events-statements-current TRUE
performance-schema-consumer-events-statements-history TRUE
performance-schema-consumer-events-statements-history-long FALSE
performance-schema-consumer-events-transactions-current TRUE
performance-schema-consumer-events-transactions-history TRUE
performance-schema-consumer-events-transactions-history-long FALSE
performance-schema-consumer-events-waits-current FALSE
performance-schema-consumer-events-waits-history FALSE
performance-schema-consumer-events-waits-history-long FALSE
performance-schema-consumer-global-instrumentation TRUE
performance-schema-consumer-statements-digest TRUE
performance-schema-consumer-thread-instrumentation TRUE
performance-schema-digests-size -1
performance-schema-error-size 5153
performance-schema-events-stages-history-long-size -1
performance-schema-events-stages-history-size -1
performance-schema-events-statements-history-long-size -1
performance-schema-events-statements-history-size -1
performance-schema-events-transactions-history-long-size -1
performance-schema-events-transactions-history-size -1
performance-schema-events-waits-history-long-size -1
performance-schema-events-waits-history-size -1
performance-schema-hosts-size -1
performance-schema-instrument
performance-schema-max-cond-classes 150
performance-schema-max-cond-instances -1
performance-schema-max-digest-length 1024
performance-schema-max-digest-sample-age 60
performance-schema-max-file-classes 80
performance-schema-max-file-handles 32768
performance-schema-max-file-instances -1
performance-schema-max-index-stat -1
performance-schema-max-memory-classes 450
performance-schema-max-metadata-locks -1
performance-schema-max-mutex-classes 350
performance-schema-max-mutex-instances -1
performance-schema-max-prepared-statements-instances -1
performance-schema-max-program-instances -1
performance-schema-max-rwlock-classes 60
performance-schema-max-rwlock-instances -1
performance-schema-max-socket-classes 10
performance-schema-max-socket-instances -1
performance-schema-max-sql-text-length 1024
performance-schema-max-stage-classes 175
performance-schema-max-statement-classes 219
performance-schema-max-statement-stack 10
performance-schema-max-table-handles -1
performance-schema-max-table-instances -1
performance-schema-max-table-lock-stat -1
performance-schema-max-thread-classes 100
performance-schema-max-thread-instances -1
performance-schema-session-connect-attrs-size -1
performance-schema-setup-actors-size -1
performance-schema-setup-objects-size -1
performance-schema-show-processlist FALSE
performance-schema-users-size -1
persist-only-admin-x509-subject
persist-sensitive-variables-in-plaintext TRUE
persisted-globals-load TRUE
pid-file /var/run/mysqld/mysqld.pid
plugin-dir /usr/lib64/mysql/plugin/
port 3306
port-open-timeout 0
preload-buffer-size 32768
print-identified-with-as-hex FALSE
profiling-history-size 15
protocol-compression-algorithms zlib,zstd,uncompressed
query-alloc-block-size 8192
query-prealloc-size 8192
range-alloc-block-size 4096
range-optimizer-max-mem-size 8388608
read-buffer-size 131072
read-only FALSE
read-rnd-buffer-size 262144
regexp-stack-limit 8000000
regexp-time-limit 32
relay-log a29706ab34c6-relay-bin
relay-log-index a29706ab34c6-relay-bin.index
relay-log-info-file relay-log.info
relay-log-info-repository TABLE
relay-log-purge TRUE
relay-log-recovery FALSE
relay-log-space-limit 0
replica-allow-batching TRUE
replica-checkpoint-group 512
replica-checkpoint-period 300
replica-compressed-protocol FALSE
replica-exec-mode STRICT
replica-load-tmpdir /tmp
replica-max-allowed-packet 1073741824
replica-net-timeout 60
replica-parallel-type LOGICAL_CLOCK
replica-parallel-workers 4
replica-pending-jobs-size-max 134217728
replica-preserve-commit-order TRUE
replica-skip-errors (No default value)
replica-sql-verify-checksum TRUE
replica-transaction-retries 10
replica-type-conversions
replicate-same-server-id FALSE
replication-optimize-for-static-plugin-config FALSE
replication-sender-observe-commit-only FALSE
report-host (No default value)
report-password (No default value)
report-port 0
report-user (No default value)
require-secure-transport FALSE
rpl-read-size 8192
rpl-stop-replica-timeout 31536000
rpl-stop-slave-timeout 31536000
safe-user-create FALSE
schema-definition-cache 256
secondary-engine-cost-threshold 100000
secure-file-priv /var/lib/mysql-files
select-into-buffer-size 131072
select-into-disk-sync FALSE
select-into-disk-sync-delay 0
server-id 1
server-id-bits 32
session-track-gtids OFF
session-track-schema TRUE
session-track-state-change FALSE
session-track-system-variables time_zone,autocommit,character_set_client,character_set_results,character_set_connection
session-track-transaction-info OFF
sha256-password-auto-generate-rsa-keys TRUE
sha256-password-private-key-path private_key.pem
sha256-password-proxy-users FALSE
sha256-password-public-key-path public_key.pem
show-create-table-verbosity FALSE
show-gipk-in-create-table-and-information-schema TRUE
show-old-temporals FALSE
show-replica-auth-info FALSE
show-slave-auth-info FALSE
skip-grant-tables FALSE
skip-name-resolve TRUE
skip-networking FALSE
skip-replica-start FALSE
skip-show-database FALSE
skip-slave-start FALSE
slave-allow-batching TRUE
slave-checkpoint-group 512
slave-checkpoint-period 300
slave-compressed-protocol FALSE
slave-exec-mode STRICT
slave-load-tmpdir /tmp
slave-max-allowed-packet 1073741824
slave-net-timeout 60
slave-parallel-type LOGICAL_CLOCK
slave-parallel-workers 4
slave-pending-jobs-size-max 134217728
slave-preserve-commit-order TRUE
slave-rows-search-algorithms INDEX_SCAN,HASH_SCAN
slave-skip-errors (No default value)
slave-sql-verify-checksum TRUE
slave-transaction-retries 10
slave-type-conversions
slow-launch-time 2
slow-query-log FALSE
slow-query-log-file /var/lib/mysql/a29706ab34c6-slow.log
socket /var/run/mysqld/mysqld.sock
sort-buffer-size 262144
source-verify-checksum FALSE
sporadic-binlog-dump-fail FALSE
sql-generate-invisible-primary-key FALSE
sql-mode ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
sql-require-primary-key FALSE
ssl TRUE
ssl-ca (No default value)
ssl-capath (No default value)
ssl-cert (No default value)
ssl-cipher (No default value)
ssl-crl (No default value)
ssl-crlpath (No default value)
ssl-fips-mode OFF
ssl-key (No default value)
ssl-session-cache-mode TRUE
ssl-session-cache-timeout 300
stored-program-cache 256
stored-program-definition-cache 256
super-large-pages FALSE
super-read-only FALSE
symbolic-links FALSE
sync-binlog 1
sync-master-info 10000
sync-relay-log 10000
sync-relay-log-info 10000
sync-source-info 10000
sysdate-is-now FALSE
table-definition-cache 2000
table-encryption-privilege-check FALSE
table-open-cache 4000
table-open-cache-instances 16
tablespace-definition-cache 256
tc-heuristic-recover OFF
temptable-max-mmap 1073741824
temptable-max-ram 1073741824
temptable-use-mmap TRUE
terminology-use-previous NONE
thread-cache-size 9
thread-handling one-thread-per-connection
thread-stack 1048576
tls-ciphersuites (No default value)
tls-version TLSv1.2,TLSv1.3
tmp-table-size 16777216
tmpdir /tmp
transaction-alloc-block-size 8192
transaction-isolation REPEATABLE-READ
transaction-prealloc-size 4096
transaction-read-only FALSE
transaction-write-set-extraction XXHASH64
updatable-views-with-limit YES
upgrade AUTO
validate-config FALSE
validate-user-plugins TRUE
verbose TRUE
wait-timeout 28800
windowing-use-high-precision TRUE
xa-detach-on-prepare TRUE


默认支持环境变量

1
2
3
4
5
6
7
8
MYSQL_DATABASE=数据库名称
MYSQL_USER=应用用户
MYSQL_PASSWORD=应用账号密码
MYSQL_ROOT_PASSWORD=ROOT账户密码
MYSQL_RANDOM_ROOT_PASSWORD=yes # 允许为为 root 用户生成一个随机初始密码并将其打印到stdout
MYSQL_ALLOW_EMPTY_PASSWORD=yes # 以允许使用根用户的空白密码启动容器,非常不建议在实践环境中使用该变量
# MYSQL_ONETIME_PASSWORD # 通常不适用,此功能仅在 MySQL 5.6+ 上受支持。 在 MySQL 5.5 上使用此选项将在初始化期间引发适当的错误。
# MYSQL_INITDB_SKIP_TZINFO # 默认情况下,入口点脚本会自动加载所需的时区数据 CONVERT_TZ()功能。 如果不需要,任何非空值都会禁用时区加载。

温馨提示: 为了替代环境变量传递敏感信息 , 我们可在 MYSQL_ROOT_PASSWORD_FILE=/run/secrets/mysql-root, 可在如下变量中添加 _FILE= 目前仅支持 ,MYSQL_ROOT_PASSWORD, MYSQL_ROOT_HOST, MYSQL_DATABASE, MYSQL_USER 和 MYSQL_PASSWORD


数据库备份、恢复

1
2
3
4
5
# 备份
$ docker exec mysql8.0 sh -c 'exec mysqldump --all-databases -uroot -p"$MYSQL_ROOT_PASSWORD"' > /some/path/on/your/host/all-databases.sql

# 恢复
$ docker exec -i mysql8.0 sh -c 'exec mysql -uroot -p"$MYSQL_ROOT_PASSWORD"' < /some/path/on/your/host/all-databases.sql


配置清单

描述:我们可以使用类似于配置清单文件,使用 docker 的 stack 子命令或者 docker-compose 名来部署 stack.yml

步骤 01.准备 mysql.yaml 部署清单

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
version: '3.1'
services:
db:
image: mysql:8.0.30
container_name: mysql8.x
# NOTE: use of "mysql_native_password" is not recommended: https://dev.mysql.com/doc/refman/8.0/en/upgrading-from-previous-series.html#upgrade-caching-sha2-password
# (this is just an example, not intended to be a production configuration)
command: --default-authentication-plugin=mysql_native_password
restart: always
environment:
# MYSQL_ROOT_PASSWORD: example
MYSQL_ROOT_PASSWORD_FILE: /app/my-secret-pw
MYSQL_DATABASE: app
MYSQL_USER: weiyigeek
MYSQL_PASSWORD: password
volumes:
- "/app/data:/var/lib/mysql"
- "/app/my-secret-pw:/app/my-secret-pw"
ports:
- 3306:3306
# 部署adminer 进行图形化管理 mysql 数据库
adminer:
image: adminer
restart: always
ports:
- 8080:8080


步骤 02.准备本地持久化数据库以及root认证密码

1
2
mkdir -vp /app/data
echo "weiyigeek.top" > /app/my-secret-pw


步骤 03.使用docker或者docker-compose进行部署

1
2
docker stack deploy -c mysql.yml mysql 
docker-compose -f mysql.yml up

2.Kubernetes 快速部署 MySQL 数据库服务器

当前,许多企业开始构建自己的容器化架构,而 mysql 部署在 k8s 上的优势主要有以下几点:

  • 资源隔离
  • 动态弹性扩缩容
  • 环境一致性
  • 运维方便

温馨提示:此处实践环境是使用Kubernetes集群,若你没有安装Kubernetes集群环境或者不了解的Kubernetes容器的朋友,可以参考博主学习【Kubernetes的系列笔记】汇总,关注 WeiyiGeek 公众号回复【Kubernetes学习之路汇总】即可获得学习资料:
https://www.weiyigeek.top/wechat.html?key=Kubernetes学习之路汇总

单实例模式

步骤 01.准备mysql部署资源清单,此处使用StatefulSet与Service资源清单。

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
tee K8s-Standalone-MySQL.yaml <<'EOF'
kind: Service
apiVersion: v1
metadata:
name: {APP_NAME}
namespace: {NAMESPACE}
labels:
app: {APP_NAME}
type: standalone
spec:
type: NodePort
ports:
- name: server
port: 3306
protocol: TCP
targetPort: 3306
nodePort: {NODEPORT}
selector:
app: {APP_NAME}
type: standalone
---
apiVersion: apps/v1
kind: StatefulSet
metadata:
name: {APP_NAME}
namespace: {NAMESPACE}
labels:
app: {APP_NAME}
type: standalone
annotations:
version: {APP_VERSION}
spec:
replicas: 1
selector:
matchLabels:
app: {APP_NAME}
type: standalone
serviceName: {APP_NAME}
template:
metadata:
labels:
app: {APP_NAME}
type: standalone
spec:
# 运行节点标签选择
# nodeSelector:
# app: database
containers:
- name: {APP_NAME}
image: mysql:{APP_VERSION}
imagePullPolicy: IfNotPresent
ports:
- name: server
containerPort: 3306
env:
- name: MYSQL_ROOT_PASSWORD
value: "{MYSQL_ROOT_PASSWORD}"
- name: MYSQL_DATABASE
value: "{MYSQL_DATABASE}"
- name: MYSQL_USER
value: "{MYSQL_USER}"
- name: MYSQL_PASSWORD
value: "{MYSQL_PASSWORD}"
volumeMounts:
- name: data
mountPath: /var/lib/mysql
# - name: mysql-conf
# mountPath: /etc/mysql/my.cnf
# subPath: my.cnf
# - name: log
# mountPath: /var/log/mysqld.log
resources:
limits:
memory: "4Gi"
cpu: "2"
requests:
memory: "512Mi"
cpu: "1"
volumes:
# 方式1,持久化 hostPath
- name: mysql-persistent-storage
hostPath:
path: {HOSTPATH}
type: DirectoryOrCreate
# - name: mysql-conf
# configMap:
# name: mysql-conf
# items:
# - key: my.cnf
# path: my.cnf
# 方式2,持久化nfs存储卷
volumeClaimTemplates:
- metadata:
name: data
labels:
app: {APP_NAME}
type: standalone
spec:
accessModes:
- ReadWriteOnce
storageClassName: {storageClassName}
resources:
requests:
storage: 5Gi
EOF


步骤 02.准备持久化目录与替换部署清单关键字。

1
2
3
4
5
6
7
8
9
10
11
# 注意,通常此目录为挂到各k8s节点上的nfs服务存储
mkdir -vp /app/data

# 替换关键配置
sed -i -e "s#{APP_NAME}#mysql-weiyigeek#g" -e "s#{NAMESPACE}#database#g" -e "s#{NODEPORT}#31001#g" -e "s#{APP_VERSION}#8.0.30#g" \
-e "s#{MYSQL_ROOT_PASSWORD}#weiyigeek.top#g" -e "s#{MYSQL_DATABASE}#app#g" -e "s#{MYSQL_USER}#weiyigeek#g" -e "s#{MYSQL_PASSWORD}#password#g" \
-e "s#{HOSTPATH}#/app/data#g" -e "s#{storageClassName}#nfs-dev#g" \
K8s-Standalone-MySQL.yaml

# 例如,可以将 my.cnf 使用 configmap 控制器进行存储,此外我采用镜像缺省的没有使用如下方式。
kubectl create configmap mysql-conf --from-file=my.cnf --namespace database


步骤 03.在K8S中执行部署mysql的命令

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
# 名词空间
kubectl create namespace database

# 部署mysql资源清单
kubectl apply -f K8s-Standalone-MySQL.yaml
# service/mysql-weiyigeek created
# statefulset.apps/mysql-weiyigeek created

# 查看部署情况
kubectl get sts,svc,pod -n database
# NAME READY AGE
# statefulset.apps/mysql-weiyigeek 1/1 77s

# NAME TYPE CLUSTER-IP EXTERNAL-IP PORT(S) AGE
# service/mysql-weiyigeek NodePort 10.108.74.113 <none> 3306:31001/TCP 77s

# NAME READY STATUS RESTARTS AGE
# pod/mysql-weiyigeek-0 1/1 Running 0 77s

# 日志查看
kubectl logs -f --tail 50 -n database pod/mysql-weiyigeek-0

# 持久化数据查看
kubectl get pvc -n database
# NAME STATUS VOLUME CAPACITY ACCESS MODES STORAGECLASS AGE
# data-mysql-weiyigeek-0 Bound pvc-37390e64-9401-4b66-8b4f-216d91e2a7fd 5Gi RWO nfs-dev 4m55s

cd /storage/dev/pvc/local/database-data-mysql-weiyigeek-0-pvc-37390e64-9401-4b66-8b4f-216d91e2a7fd
ls
# app binlog.000002 ca.pem '#ib_16384_0.dblwr' ibdata1 '#innodb_temp' mysql.sock public_key.pem sys
# auto.cnf binlog.index client-cert.pem '#ib_16384_1.dblwr' ibtmp1 mysql performance_schema server-cert.pem undo_001
# binlog.000001 ca-key.pem client-key.pem ib_buffer_pool '#innodb_redo' mysql.ibd private_key.pem server-key.pem undo_002


步骤 04.使用 adminer 连接 k8s 部署的 MySQL 数据库,验证其服务。

WeiyiGeek.adminer-connect-k8s-deploy-mysql8.x

WeiyiGeek.adminer-connect-k8s-deploy-mysql8.x


主从同步模式

此节,我们实践在K8S集群中搭建一个 MySQL 主从数据库,主(可读、可写),从只读,如下是MySQL主从原理图以及MySQL主从模式在K8S集群中的部署架构。

WeiyiGeek.MySQL主从原理图以及MySQL主从模式在K8S集群中的部署架构图

WeiyiGeek.MySQL主从原理图以及MySQL主从模式在K8S集群中的部署架构图

步骤 01.添加 helm 源并在源中下载 mysql 部署清单到本地,此处我的helm版本为v3.9.0。

1
2
3
4
5
6
7
8
9
10
# 温馨提示:master节点上需要安装 helm 然后进行拉取部署的相关资源部署清单图表
helm3 repo add bitnami https://charts.bitnami.com/bitnami
helm3 search repo bitnami/mysql -l
# NAME CHART VERSION APP VERSION DESCRIPTION
# bitnami/mysql 9.3.4 8.0.30 MySQL is a fast, reliable, scalable, and easy t....

# 拉取到本地以及其部署清单图表
$ helm3 pull bitnami/mysql --version 9.3.4 --untar
$ ls mysql/
Chart.lock charts Chart.yaml README.md templates values.schema.json values.yaml


步骤 02.修改该 Chart 图表 values.yaml 文件,已下逻辑出主要修改点。

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
vim mysql/values.yaml
....
# 修改1.使用内部仓库镜像地址(后续会将其同步到内部harbor中此处先更改)
image:
registry: harbor.weiyigeek.top
repository: library/mysql
tag: 8.0.30-debian-11-r15
...
# 修改2.MySQL部署模式 (`standalone` or `replication`)此处为主从复制。
architecture: replication

# 修改3.数据库认证账号(root、普通用户、replication用户)相关密码以及创建的数据库设置,密码留空则会自动生成
auth:
rootPassword: ""
createDatabase: true
database: "app"
username: "app"
password: ""
replicationUser: replicator
replicationPassword: ""

# 修改4.MySQL Primary 服务相关参数配置
primary:
name: primary
# 资源限制 : 此处 1000m 表示使用1个CPU的资源,内存最大4G。
resources:
limits:
cpu: 1000m
memory: 4Gi
# 修改5.主资源持久化配置,此处我已经搭建了动态逻辑卷。
persistence:
enabled: true
storageClass: "nfs-local"
accessModes:
- ReadWriteOnce
size: 10Gi
# 修改6.主服务持久化配置,注意此处与secondary服务节点配置不同
service:
type: NodePort
ports:
mysql: 3306
nodePorts:
mysql: "31006"

# 修改7.MySQL Secondary 服务相关参数配置
secondary:
name: secondary
replicaCount: 2
resources:
limits:
cpu: 1000m
memory: 2048Mi
# 修改6.从(节点)资源持久化配置,此处我已经搭建了动态逻辑卷。
persistence:
enabled: true
storageClass: "nfs-local"
accessModes:
- ReadWriteOnce
size: 10Gi
# 修改8.从(节点)持久化配置,注意此处与primary服务节点配置不同
service:
type: NodePort
ports:
mysql: 3306
nodePorts:
mysql: "31008"

# 修改点9.启用 Promethues 的 mysqld-exporter
metrics:
enabled: true
image:
registry: harbor.weiyigeek.top
repository: library/mysqld-exporter
tag: 0.14.0-debian-11-r33


步骤 03.为了加快拉取速度,此处将镜像拉取上传到内部harbor中

1
2
3
4
5
6
7
docker pull bitnami/mysqld-exporter:0.14.0-debian-11-r33
docker tag bitnami/mysqld-exporter:0.14.0-debian-11-r33 harbor.weiyigeek.top/library/mysqld-exporter:0.14.0-debian-11-r33
docker push harbor.weiyigeek.top/library/mysqld-exporter:0.14.0-debian-11-r33

docker pull bitnami/mysql:8.0.30-debian-11-r15
docker tag bitnami/mysql:8.0.30-debian-11-r15 harbor.weiyigeek.top/library/mysql:8.0.30-debian-11-r15
docker push harbor.weiyigeek.top/library/mysql:8.0.30-debian-11-r15


步骤 04.使用helm3安装我们修改后的MySQL主从图表,以及显示安装情况

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
$ helm3 install mysql ./mysql --namespace database --create-namespace
# NAME: mysql
# LAST DEPLOYED: Wed Sep 28 16:33:23 2022
# NAMESPACE: database
# STATUS: deployed
# REVISION: 1
# TEST SUITE: None
# NOTES:
# CHART NAME: mysql
# CHART VERSION: 9.3.4
# APP VERSION: 8.0.30

$ helm3 list -n database
# NAME NAMESPACE REVISION UPDATED STATUS CHART APP VERSION
# mysql database 1 2022-09-28 16:33:23.01465975 +0800 CST deployed mysql-9.3.4 8.0.30

$ kubectl get sts,pod -n database -l app.kubernetes.io/name=mysql
# NAME READY AGE
# statefulset.apps/mysql-primary 1/1 2m37s
# statefulset.apps/mysql-secondary 2/2 2m37s

# NAME READY STATUS RESTARTS AGE
# pod/mysql-primary-0 2/2 Running 0 2m37s
# pod/mysql-secondary-0 2/2 Running 0 2m37s
# pod/mysql-secondary-1 2/2 Running 0 95s


步骤 05.获取自动生成的MySQL root、app以及replication用户密码

1
2
3
4
5
6
7
8
echo -n "MYSQL_ROOT_PASSWORD=";kubectl get secret --namespace database mysql -o jsonpath="{.data.mysql-root-password}" | base64 -d;echo
# MYSQL_ROOT_PASSWORD=oX7112Avng

echo -n "MYSQL_PASSWORD=";kubectl get secret --namespace database mysql -o jsonpath="{.data.mysql-password}" | base64 -d;echo
# MYSQL_PASSWORD=pdtsixSpV28

echo -n "MYSQL_REPLICATION_PASSWORD=";kubectl get secret --namespace database mysql -o jsonpath="{.data.mysql-replication-password}" | base64 -d;echo
# MYSQL_REPLICATION_PASSWORD=FJRspMupePE


步骤 06.使用 Adminer 连接到主服务中进行读写,然后验证从节点的是否正确可读。

主节点服务中创建表并插入数据

1
2
3
4
5
6
7
8
9
10
11
12
13
-- To connect to primary service (read/write):
CREATE TABLE replication (
id int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(255) NOT NULL,
INDEX name_in (name)
);
INSERT INTO app.replication(name) VALUES('WeiyiGeek')

-- 创建数据库、用户、并将创建的数据库所有权赋予给创建用户
CREATE DATABASE dev;
CREATE USER 'dev'@'%' IDENTIFIED BY 'dev.weiyigeek.top';
GRANT ALL ON dev.* TO 'dev'@"%";
FLUSH PRIVILEGES;

从节点查询插入的数据

1
2
3
4
-- To connect to secondary service (read):
kubectl run mysql-client --rm --tty -i --restart='Never' --image harbor.weiyigeek.top/library/mysql:8.0.30-debian-11-r15 --namespace database --env MYSQL_ROOT_PASSWORD=oX7xxIovng --command -- bash
--# mysql -h mysql-primary.database.svc -u app -p"$MYSQL_PASSWORD"
mysql -h mysql-secondary.database.svc -u app -p"$MYSQL_PASSWORD"

WeiyiGeek.主从验证实践图

WeiyiGeek.主从验证实践图


步骤 07.查看exporter监控数据, 此处就不演示在Grafana在集群中MySQL资源监控, 如果想卸载安装的MySQL主从。
基于 mysqld-exporter 的 Grafana 模板 :https://grafana.com/grafana/dashboards/7362

1
2
3
4
5
6
7
8
9
10
11
# 通过命令查看采集数据.
kubectl get --raw http://10.66.35.76:9104/metrics
kubectl get --raw http://10.66.53.95:9104/metrics

# 通过helm3卸载安装的mysql主从.
helm3 uninstall mysql --namespace database
# kubectl delete pod -n database `kubectl get pod -n database | awk 'NR>1{print $1}'` --force

# 删除创建的名称空间, 注意删除名词空间时, 若有其他资源请谨慎执行如下命令。
kubectl patch ns database -p '{"metadata":{"finalizers":null}}'
kubectl delete ns database --force

至此,在容器化环境中安装MySQL主从实践完毕。