nativicat mysql触发器_【MySQL8.x】MySQL8.x 的docker化安装与使用

一、拉取MySQL8.0 官方镜像

# docker pull mysql:8.0

大版本为8，小版本需要具体看

# docker run -it --rm mysql:8.0 mysql -V

# docker run -it --rm mysql:8.0 cat /etc/passwd

二、初始化MySQL

# cat mysql8.x_init.sh

#######################################

#!/bin/bash

mysql_ver="8.0"

mysql_datadir="/opt/mysqldata"

mysql_root_pwd="MySQL@123"

registry_addr=""

docker run -itd  \

--name mysql \

-p 3306:3306 \

-e UMASK=0600 \

-e UMASK_DIR=0700 \

-e MYSQL_HISTFILE=/dev/null \

-v ${mysql_datadir}:/var/lib/mysql \

-v /opt/mysqlconfig/mysqld.cnf:/etc/mysql/conf.d//mysqld.cnf \

-e MYSQL_ROOT_PASSWORD="${mysql_root_pwd}" \

mysql:${mysql_ver}

sleep 30

if ss -tan | grep -w "3306" > /dev/null 2>&1; then

docker stop mysql

docker rm  mysql

echo "Mysql init successfuly!"

else

echo "Mysql init failed!"

fi

################################################

注意：库名表名是否大小写敏感(默认不区分大小写)，在MySQL初始化时需要通过配置文件声明

#  cat  /opt/mysqlconfig/mysqld.cnf

#####################################################

[mysqld]

pid-file                        = /var/run/mysqld/mysqld.pid

socket                          = /var/run/mysqld/mysqld.sock

datadir                        = /var/lib/mysql

symbolic-links                  = 0

max_connections                = 2000

user                            = mysql

skip_name_resolve

skip-host-cache

skip-log-bin

character-set-client-handshake  = FALSE

lower_case_table_names          = 1

sql-mode                        = "STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION"

character-set-server            = utf8

collation-server                = utf8_general_ci

init_connect                    = "SET NAMES 'utf8'"

default_authentication_plugin  = mysql_native_password

general_log      = on

general_log_file  = /var/lib/mysql/general.log

log_timestamps    = SYSTEM

require_secure_transport = ON

ssl-ca                  = /var/lib/mysql/ca.pem

ssl-cert                = /var/lib/mysql/server-cert.pem

ssl-key                  = /var/lib/mysql/server-key.pem

[mysql]

default-character-set          = utf8

[client]

default-character-set          = utf8

######################################################

三、启动MySQL

# cat /etc/systemd/system/mysqld.service

#####################################################

[Unit]

Description=MySQL Server

After=network-online.target docker.service

Requires=docker.service

[Service]

Type=simple

ExecStartPre=-/usr/bin/docker rm -f mysql

ExecStart=/usr/bin/docker run \

--name mysql \

-p 3306:3306 \

-e UMASK=0600 \

-e UMASK_DIR=0700 \

-e MYSQL_HISTFILE=/dev/null \

-v /opt/mysqldata:/var/lib/mysql \

-v /opt/mysqlconfig/mysqld.cnf:/etc/mysql/conf.d//mysqld.cnf \

-v /etc/localtime:/etc/localtime \

mysql:8.0

ExecStop=/usr/bin/docker stop mysql

LimitNOFILE=65535

Restart=on-failure

StartLimitBurst=3

StartLimitInterval=60s

[Install]

WantedBy=multi-user.target

######################################################

# systemctl  daemon-reload

# systemctl start mysqld

# systemctl enable mysqld

systemctl status mysqld

四、MySQL 连接测试

# docker exec -it mysql mysql -u root -p"MySQL@123"

select user,host from mysql.user;

alter user root@'%' require ssl;

flush privileges

MySQL8.x 要求先创建用户，然后给用户授权

create user root@'%' identified by "MySQL@123" require ssl;

grant all privileges on *.*  to  root@'%' with grant option;

flush privileges

select host,user,plugin from mysql.user;

客户端连接测试

注意：如果navicat版本过低，勾选SS了选项，依然会无法连接

五、参考

MySQL8.x 开启远程登录

https://www.cnblogs.com/xiaochina/p/8994394.html

MySQL 8.0 配置mysql_native_password身份验证插件的密码

https://blog.csdn.net/zhengbin9/article/details/82729861

mysql caching_sha2_password 和 mysql_native_password 说明

https://blog.csdn.net/shenhonglei1234/article/details/90603351

MySQL修改lower_case_table_names产生的问题

https://www.jianshu.com/p/5c7f1b149e0c

https://blog.csdn.net/wll_1017/article/details/55105180

MySQL 8.x报错：[ERROR] [MY-011087] [Server] Different lower_case_table_names settings forserver ('1') and data dictionary ('0').

https://blog.csdn.net/vkingnew/article/details/81267168

MySQL8.0的坑之lower_case_table_names

http://blog.itpub.net/20893244/viewspace-2565069

MySQL8.0 大小写敏感修改的问题

https://blog.csdn.net/qingcheng157/article/details/82182769

MySQL8.0新特性

https://www.cnblogs.com/xyabk/p/10882913.html

How to set sql_mode in my.cnf in MySQL 8?

https://stackoverflow.com/questions/54007824/how-to-set-sql-mode-in-my-cnf-in-mysql-8