安装环境
- docker v19.03
- docker-compose 1.24.1
编辑 mysql 配置及 docker-compose 文件
#创建mysql主目录
mkdir -p /docker/mysql/{conf.d,data,log}
#修改数据目录所属为docker中运行的用户
chown 999:999 /docker/mysql/{conf.d,data,log}
#mysql 配置文件
cat > /docker/mysql/conf.d/my.cnf << EOF
[client]
default-character-set = utf8mb4
[mysql_safe]
default-character-set = utf8mb4
[mysql]
default-character-set = utf8mb4
[mysqldump]
quick
quote-names
max_allowed_packet = 16M
[mysqld]
# MySQL 服务的唯一编号 几个 MySQL 服务 ID 需唯一(集群中)
server-id = 1
skip-host-cache
skip-name-resolve
# 修改时区为东8区
default-time-zone = '+8:00'
#pid-file = /var/run/mysqld/mysqld.pid
#socket = /var/run/mysqld/mysqld.sock
# 数据库的数据存放目录
datadir = /var/lib/mysql
# 错误日志
log-error = /var/log/mysql/error.log
# 跳过密码登陆(取消注释重置 root 密码)
#skip-grant-tables
# 设置默认排序规则
collation-server = utf8mb4_unicode_ci
# 客户端跟服务器字符编码不一致时拒绝连接
character-set-client-handshake = TRUE
# 指定初始化连接时的字符编码
init_connect = 'SET NAMES utf8mb4'
init_connect = 'SET collation_connection = utf8mb4_unicode_ci'
# 时间格式的 0000 不报错处理
sql_mode = NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO
# 创建新表时将使用的默认存储引擎
default-storage-engine = INNODB
# 锁定等待时间由默认的50s 修改为500s
innodb_lock_wait_timeout = 500
# 事务隔离级别，默认为可重复读，mysql默认可重复读级别（此级别下可能参数很多间隙锁，影响性能）
transaction_isolation = READ-COMMITTED
# 表名不分区大小写
lower_case_table_names=1
# 设置client连接mysql时的字符集,防止乱码
init_connect='SET NAMES utf8mb4'
# 最大连接数
max_connections = 400
# 最大错误连接数
max_connect_errors = 1000
# TIMESTAMP如果没有显示声明NOT NULL，允许NULL值
explicit_defaults_for_timestamp = true
# SQL数据包发送的大小，如果有BLOB对象建议修改成1G
max_allowed_packet = 128M
# 代表受信任的函数创建者，也就是使MySQL不对函数做出限制
log_bin_trust_function_creators = 1
# 使用旧密码插件 兼容性更好
default-authentication-plugin = mysql_native_password
# MySQL连接闲置超过一定时间后(单位：秒)将会被强行关闭
# MySQL默认的wait_timeout 值为8个小时, interactive_timeout参数需要同时配置才能生效
interactive_timeout = 1800
wait_timeout = 1800
# 内部内存临时表的最大值 ，设置成128M。
# 比如大数据量的group by ,order by时可能用到临时表，
# 超过了这个值将写入磁盘，系统IO压力增大
tmp_table_size = 134217728
max_heap_table_size = 134217728
# 慢查询sql日志设置
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
# 检查未使用到索引的sql
log_queries_not_using_indexes = 1
# 针对log_queries_not_using_indexes开启后，记录慢sql的频次、每分钟记录的条数
log_throttle_queries_not_using_indexes = 5
# 作为从库时生效,从库复制中如何有慢sql也将被记录
log_slow_slave_statements = 1
# 慢查询执行的秒数，必须达到此值可被记录
long_query_time = 8
# 检索的行数必须达到此值才可被记为慢查询
min_examined_row_limit = 100
log-bin = /var/lib/mysql/log.bin
# mysql binlog日志文件保存的过期时间，过期后自动删除 7 天 = 7 * 24 * 60 * 60
binlog_expire_logs_seconds = 604800
EOF
#创建docker-compose.yml
cat > /docker/mysql/docker-compose.yml << EOF
# Use root/123456 as user/password credentials
version: '3.7'
services:
db:
image: mysql:8
container_name: mysql
command: --default-authentication-plugin=mysql_native_password
restart: always
user: "999:999"
environment:
TZ: "Asia/Shanghai"
MYSQL_ROOT_PASSWORD: 123456
#MYSQL_USER: "test"
#MYSQL_PASS: "test123"#部分工具并不支持caching_sha2_password 密码 必须改回旧的 mysql_native_password 否则会报连接错误/验证错误
volumes:
- "./data:/var/lib/mysql"
- "./conf.d:/etc/mysql/conf.d"
- "./log:/var/log/mysql"
ports:
- 3306:3306
adminer:
image: adminer
container_name: mysql-adminer
restart: always
ports:
- 8080:8080
EOF
#启动运行mysql8
cd /docker/mysql && docker-compose up -d
#检查运行状态 正常则可通过ip:3306 root:123456 连接
docker-compose logs -f
浏览器访问 http://运行机ip:8080 到 Adminer 登陆页面
- 服务器 mysql
- 用户名 root
- 密码 123456 (MYSQL_ROOT_PASSWORD配置的密码)
输入完成 点击登陆后进入管理界面
当然 mysql 也能跟普通安装的 mysql 一样通过 3306 端口号来访问
创建测试数据库 test
create database test;
创建测试表 test
create table test.test
(
id int auto_increment,
name varchar(100) null,
age int(3) null,
created_time timestamp default CURRENT_TIMESTAMP null,
constraint test_pk
primary key (id)
)
插入测试数据到表 test
insert into test.test values (1,'blank',28,now());
查询刚刚插入到表 test 的数据
select * from test.test;
命令行连接 创建用户 授权 建表 插入数据 查询数据 测试
# 进入 docker 中的 mysql
[root@node51 mysql]# docker exec -it mysql bash
# 使用 root 账号登陆 mysql 密码就是前面docker-compose.yml中配置的
root@e337d97aab13:/# mysql -uroot -p123456
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 8.0.18 MySQL Community Server - GPL
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
# 创建用户
mysql> CREATE USER 'blank'@'%' IDENTIFIED BY '123456';
Query OK, 0 rows affected (0.00 sec)
# 用户授权
mysql> GRANT ALL ON *.* TO 'blank'@'%' WITH GRANT OPTION;
Query OK, 0 rows affected (0.00 sec)
# 刷新权限
FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)
# 我们退出重新使用新创建的用户登陆测试
mysql> exit
Bye
root@e337d97aab13:/# mysql -ublank -p123456
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 8.0.18 MySQL Community Server - GPL
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> create database test;
Query OK, 1 row affected (0.00 sec)
mysql> create table test.test
-> (
-> id int auto_increment,
-> name varchar(100) null,
-> age int(3) null,
-> created_time timestamp default CURRENT_TIMESTAMP null,
-> constraint test_pk
-> primary key (id)
-> );
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql> insert into test.test values (1,'blank',28,now());
Query OK, 1 row affected (0.00 sec)
mysql> select * from test.test;
+----+-------+------+---------------------+
| id | name | age | created_time |
+----+-------+------+---------------------+
| 1 | blank | 28 | 2019-10-17 16:06:12 |
+----+-------+------+---------------------+
1 row in set (0.00 sec)
Done
ps
mysql 8 原生踩坑
如果是直接用 apt/yum install 的 mysql
没修改配置的情况下 默认密码插件为新的caching_sha2_password
部分工具并不支持caching_sha2_password 密码 必须改回旧的 mysql_native_password 否则会报连接错误/验证错误
mysql -hlocalhost -uroot -p 旧密码
USE mysql
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '123456';
FLUSH PRIVILEGES;