企業運維 | MySQL關係型資料庫在Docker與Kubernetes容器環境中快速搭建部署主從實踐
關注「 WeiyiGeek」公眾號 設為「
星標」每天帶你玩轉網路安全運維、應用開發、物聯網IOT學習！
希望各位看友【關注、點贊、評論、收藏、投幣】，助力每一個夢想。
本章目錄
首發地址: //mp.weixin.qq.com/s/7mmIsd83QPT65QnQd5CtFQ
溫馨提示：唯一極客技術部落格文章在線瀏覽【極客全棧修鍊】小程式上線了，涉及網路安全、系統運維、應用開發、物聯網實戰、全棧文章，希望和大家一起學習進步，歡迎瀏覽交流！（希望大家多多提提意見）
—
1.Docker 快速部署 MySQL 資料庫伺服器
MySQL 是一種廣泛使用的開源關係資料庫管理系統 (RDBMS)，其久經考驗的性能、可靠性和易用性，MySQL 已成為基於 Web 的應用程式的領先資料庫選擇。
MySQL 幫助文檔：//docs.oracle.com/en-us/iaas/mysql-database/doc/getting-started.html
鏡像倉庫地址：//hub.docker.com/_/mysql
鏡像問題：//github.com/docker-library/mysql/issues
溫馨提示：此處實踐環境是使用Docker,若你沒有安裝Docker環境或者不了解的Docker容器的朋友，可以參考部落客學習【Docker的系列筆記】匯總:
//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: //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
2.Kubernetes 快速部署 MySQL 資料庫伺服器
當前，許多企業開始構建自己的容器化架構，而 mysql 部署在 k8s 上的優勢主要有以下幾點：
- 資源隔離
- 動態彈性擴縮容
- 環境一致性
- 運維方便
溫馨提示：此處實踐環境是使用Kubernetes集群,若你沒有安裝Kubernetes集群環境或者不了解的Kubernetes容器的朋友，可以參考部落客學習【Kubernetes的系列筆記】匯總:
//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 //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 模板 ：//grafana.com/grafana/dashboards/7362
# 通過命令查看採集數據.
kubectl get --raw //10.66.35.76:9104/metrics
kubectl get --raw //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主從實踐完畢。
原文地址: //blog.weiyigeek.top/2022/3-24-687.html
本文至此完畢，更多技術文章，盡情期待下一章節！
【WeiyiGeek Blog 個人部落格 – 為了能到遠方，腳下的每一步都不能少 】
歡迎各位志同道合的朋友一起學習交流【點擊加入交流群】，如文章有誤請在下方留下您寶貴的經驗知識!
作者主頁: 【 //weiyigeek.top】
部落格地址: 【 //blog.weiyigeek.top 】
專欄書寫不易，如果您覺得這個專欄還不錯的，請給這篇專欄 【點個贊、投個幣、收個藏、關個注，轉個發，留個言】(人間六大情)，這將對我的肯定，謝謝！。
-
echo “【點個贊】，動動你那粗壯的拇指或者芊芊玉手，親！”
-
printf(“%s”, “【投個幣】，萬水千山總是情，投個硬幣行不行，親！”)
-
fmt.Printf(“【收個藏】，閱後即焚不吃灰，親！”)
-
console.info(“【轉個發】，讓更多的志同道合的朋友一起學習交流，親！”)
-
System.out.println(“【關個注】，後續瀏覽查看不迷路喲，親！”)
-
cout << “【留個言】，文章寫得好不好、有沒有錯誤，一定要留言喲，親! ” << endl;
更多網路安全、系統運維、應用開發、物聯網實踐、網路工程、全棧文章，盡在 //blog.weiyigeek.top 之中，謝謝各位看又支援！
