MySQL关系型数据库环境快速搭建部署

2022-03-24
部署运维
环境快速搭建

MySQL关系型数据库环境快速搭建部署

2022-03-24

[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.快速部署脚本命令。

点击阅读完整原文

[TOC]

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

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

命令方式

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

# 准备数据持久化目录
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中的部署情况。

# 容器
$ 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 等数据库。

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端口进行连接:

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

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

# 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

默认支持环境变量：

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 。

数据库备份、恢复

# 备份
$ 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 部署清单

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资源清单。

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.准备持久化目录与替换部署清单关键字。

# 注意,通常此目录为挂到各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的命令

# 名词空间
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.top-adminer-connect-k8s-deploy-mysql8.x

主从同步模式

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

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

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

# 温馨提示：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 文件，已下逻辑出主要修改点。

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中

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主从图表,以及显示安装情况

$ 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用户密码

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 连接到主服务中进行读写，然后验证从节点的是否正确可读。

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

-- 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;

从节点查询插入的数据

-- 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"

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

# 通过命令查看采集数据.
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主从实践完毕。

欢迎各位志同道合的朋友一起学习交流，如文章有误请在下方留下您宝贵的经验知识，个人邮箱地址【master#weiyigeek.top】或者个人公众号【WeiyiGeek】联系我。

更多文章来源于【WeiyiGeek Blog - 为了能到远方，脚下的每一步都不能少】, 个人首页地址( https://weiyigeek.top )

专栏书写不易，如果您觉得这个专栏还不错的，请给这篇专栏 【点个赞、投个币、收个藏、关个注、转个发、赞个助】，这将对我的肯定，我将持续整理发布更多优质原创文章！。

最后更新时间：2023-06-06 17:20:25
文章原始路径：_posts/系统运维/快速部署/MySQL.md
转载注明出处，原文地址：https://blog.weiyigeek.top/2022/3-24-687.html
本站文章内容遵循知识共享署名 - 非商业性 - 相同方式共享 4.0 国际协议

WeiyiGeeker