Docker快速部署方案 ---- MySQL8.0+

Docker快速部署方案 ---- MySQL8.0+

Docker快速部署方案 ---- MySQL8，Docker 启动 MySQL8.0+

1. 拉取镜像

docker pull mysql:8.0.32

2. 创建挂载目录

如果无法确定挂载的文件目录，可以启动并进入容器查看其文件目录树。

mkdir -p /opt/docker/mysql/{conf,logs,data}

3. 创建配置my.cnf

vim /opt/docker/mysql/conf/my.cnf

# 把下面的内容复制到my.cnf文件中
[mysqld]
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
datadir         = /var/lib/mysql
secure-file-priv= NULL
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

# Custom config should go here
!includedir /etc/mysql/conf.d/
log-error=/var/log/mysql/error.log
#log=/var/log/mysql/mysql.log
slow_query_log =ON
slow_query_log_file=/var/log/mysql/slowquery.log
long_query_time=3
log-queries-not-using-indexes=/var/log/mysql/nouseindex.log
lower_case_table_names=1
default_authentication_plugin=mysql_native_password
init_connect='SET collation_connection = utf8_unicode_ci'
init_connect='SET NAMES utf8'
character-set-server=utf8
collation-server=utf8_unicode_ci
skip-character-set-client-handshake

[client]
default-character-set=utf8
 
[mysql]
default-character-set=utf8

完整的my.cnf配置参数参考

[mysqld]
####: for global
user =mysql
federated
basedir =/usr/local/mysql
datadir =/data/mysql/data
server_id = 1250
port =3306
mysqlx_port = 33070
character_set_server =utf8mb4
collation_server=utf8mb4_bin
log_timestamps =system
default_time_zone ='+8:00'
socket =/data/mysql/mysql.sock
read_only = 0
super_read_only = 0
skip_name_resolve =on
lower_case_table_names =1
#secure_file_priv =/data/mysql/data
open_files_limit =65536
max_connections =2000
thread_cache_size =256
table_open_cache =4096 # 2000
table_definition_cache =4096
table_open_cache_instances =64
wait_timeout =28800
interactive_timeout =28800
event_scheduler = 1
max_allowed_packet =1024M
init_connect ='SET AUTOCOMMIT=1'
transaction_isolation =READ-COMMITTED
lock_wait_timeout=3600
default_authentication_plugin=mysql_native_password

###: cache
key_buffer_size = 64M
sort_buffer_size  = 4M
join_buffer_size  = 4M

####: for binlog
binlog_format =row
log_bin =/data/mysql/logs/mysql-bin
binlog_rows_query_log_events =off
log_slave_updates =on
#expire_logs_days =7
binlog_expire_logs_seconds=604800
binlog_cache_size =1G
max_binlog_size =1G
binlog_checksum =none
sync_binlog =1
slave-preserve-commit-order =ON
log_bin_trust_function_creators=on  



####:for error-log
log_error =/data/mysql/logs/mysql.err
general_log =off
general_log_file =/data/mysql/logs/general.log

####: for slow query log
slow_query_log =on
slow_query_log_file =/data/mysql/logs/slowquery.log
long_query_time =1.000000
log_queries_not_using_indexes =0

####: for gtid
#gtid_executed_compression_period =1000
gtid_mode =on
enforce_gtid_consistency =on

####: for replication
relay-log-index = /data/mysql/logs/relay-bin.index
relay-log-info-file = /data/mysql/logs/relay-log.info
relay-log = /data/mysql/logs/relay-bin
log-slave-updates =1
skip_slave_start =1
master_info_repository =table
relay_log_info_repository =table
slave_parallel_type =logical_clock
slave_parallel_workers =2

####: for performance_schema
performance_schema = 1
performance_schema_instrument = '%memory%=on'
performance_schema_instrument = '%lock%=on'
performance_schema_digests_size =100000

####: for innodb
innodb_data_file_path =ibdata1:1024M:autoextend
innodb_temp_data_file_path =ibtmp1:12M:autoextend
innodb_buffer_pool_filename =ib_buffer_pool # ib_buffer_pool
innodb_log_files_in_group =3
innodb_log_file_size =256M # 50331648(48M)
innodb_max_undo_log_size =256M
innodb_undo_log_truncate =1
innodb_undo_tablespaces =2
innodb_file_per_table =on
innodb_open_files =65535
innodb_thread_concurrency =0 # 0
innodb_read_io_threads =2
innodb_write_io_threads =2
innodb_purge_threads =1
innodb_page_cleaners =1
innodb_print_all_deadlocks =off
innodb_lock_wait_timeout =20
innodb_autoinc_lock_mode =2
innodb_io_capacity =500
innodb_io_capacity_max =1000

#########################
innodb_flush_method =O_DIRECT
innodb_log_buffer_size =128M
innodb_flush_log_at_trx_commit =1
innodb_buffer_pool_size = 128M
innodb_buffer_pool_instances = 2 
#innodb_numa_interleave=on
#####################################
#######################
[client]
socket=/data/mysql/mysql.sock

4. 启动容器

docker run \
--privileged=true \
--restart=always \
-v /opt/docker/mysql/conf/my.cnf:/etc/mysql/my.cnf \
-v /opt/docker/mysql/data:/var/lib/mysql \
-v /opt/docker/mysql/logs:/var/log/mysql \
-p 3306:3306 \
--name mysql \
-e MYSQL_ROOT_PASSWORD=123456 \
-d mysql:8.0.32 \
--default_authentication_plugin=mysql_native_password \
--character-set-server=utf8 \
--collation-server=utf8_general_ci

参数解释

--privileged=true                                         -> 使container内的root拥有真正的root权限。否则只是外部的一个普通用户权限
--restart=always                                          -> 开机启动容器,容器异常自动重启
-d                                                        -> 以后台方式启动容器
-v /opt/docker/mysql/conf/my.cnf:/etc/mysql/my.cnf        -> 映射配置文件
-v /opt/docker/mysql/logs:/var/log/mysql                  -> 映射日志
-v /opt/docker/mysql/data:/var/lib/mysql                  -> 映射数据
-p 3306:3306                                              -> 绑定宿主机端口
--name mysql                                              -> 指定容器名称
-e MYSQL_ROOT_PASSWORD=123456                             -> 写入配置root密码
--character-set-server=utf8                               -> 设置数据库编码为uft8
--collation-server=utf8_general_ci                        -> 设置默认排序规则为utf8_unicode_ci
-default_authentication_plugin=mysql_native_password      -> 设置密码加密方式为mysql_native_password

5. 连接客户端

选择自己已安装的数据库客户端连接即可！