设为「⭐️ 星标」每天带你玩转网络安全运维、应用开发、物联网IOT学习！
希望各位看友【关注、点赞、评论、收藏、投币】，助力每一个梦想。
本章目录
首发地址: https://mp.weixin.qq.com/s/7mmIsd83QPT65QnQd5CtFQ
温馨提示：唯一极客技术博客文章在线浏览【极客全栈修炼】小程序上线了，涉及网络安全、系统运维、应用开发、物联网实战、全栈文章，希望和大家一起学习进步，欢迎浏览交流！（希望大家多多提提意见）
设为「⭐️ 星标」每天带你玩转网络安全运维、应用开发、物联网IOT学习！
希望各位看友【关注、点赞、评论、收藏、投币】，助力每一个梦想。
本章目录
首发地址: https://mp.weixin.qq.com/s/7mmIsd83QPT65QnQd5CtFQ
温馨提示：唯一极客技术博客文章在线浏览【极客全栈修炼】小程序上线了，涉及网络安全、系统运维、应用开发、物联网实战、全栈文章，希望和大家一起学习进步，欢迎浏览交流！（希望大家多多提提意见）
---
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的系列笔记】汇总:
https://blog.weiyigeek.top/2018/1-1-1.html#Docker容器学习之路汇总
步骤 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认证密码
mkdir -vp /app/data echo "weiyigeek.top" > /app/my-secret-pw
步骤 03.使用docker或者docker-compose进行部署
docker stack deploy -c mysql.yml mysql docker-compose -f mysql.yml up 当前，许多企业开始构建自己的容器化架构，而 mysql 部署在 k8s 上的优势主要有以下几点：
温馨提示：此处实践环境是使用Kubernetes集群,若你没有安装Kubernetes集群环境或者不了解的Kubernetes容器的朋友，可以参考博主学习【Kubernetes的系列笔记】汇总:
https://blog.weiyigeek.top/2018/1-1-1.html#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 数据库，验证其服务。
此节，我们实践在K8S集群中搭建一个 MySQL 主从数据库，主（可读、可写），从只读，如下是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主从实践完毕。
原文地址: https://blog.weiyigeek.top/2022/3-24-687.html
本文至此完毕，更多技术文章，尽情期待下一章节！
【WeiyiGeek Blog 个人博客 - 为了能到远方，脚下的每一步都不能少 】
欢迎各位志同道合的朋友一起学习交流【点击加入交流群】，如文章有误请在下方留下您宝贵的经验知识!
作者主页: 【 https://weiyigeek.top】
博客地址: 【 https://blog.weiyigeek.top 】
专栏书写不易，如果您觉得这个专栏还不错的，请给这篇专栏 【点个赞、投个币、收个藏、关个注，转个发，留个言】(人间六大情)，这将对我的肯定，谢谢！。
echo "【点个赞】，动动你那粗壮的拇指或者芊芊玉手，亲！"
printf("%s", "【投个币】，万水千山总是情，投个硬币行不行，亲！")
fmt.Printf("【收个藏】，阅后即焚不吃灰，亲！")
console.info("【转个发】，让更多的志同道合的朋友一起学习交流，亲！")
System.out.println("【关个注】，后续浏览查看不迷路哟，亲！")
cout << "【留个言】，文章写得好不好、有没有错误，一定要留言哟，亲! " << endl;
更多网络安全、系统运维、应用开发、物联网实践、网络工程、全栈文章，尽在 https://blog.weiyigeek.top 之中，谢谢各位看又支持！
