blank
blank
发布于 2019-08-16 / 1475 阅读 / 0 评论 / 0 点赞

docker-compose 运行 mysql 8 + adminer

安装环境

  • 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

mysql-docker-compose-up

浏览器访问 http://运行机ip:8080 到 Adminer 登陆页面

  • 服务器 mysql
  • 用户名 root
  • 密码 123456 (MYSQL_ROOT_PASSWORD配置的密码)

adminer-login

输入完成 点击登陆后进入管理界面

adminer-home

当然 mysql 也能跟普通安装的 mysql 一样通过 3306 端口号来访问

mysql-tool-connection

创建测试数据库 test

create database test;

mysql-tool-new-database-test-created

创建测试表 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)
)

mysql-tool-new-table-test-created

插入测试数据到表 test

insert into test.test values (1,'blank',28,now());

mysql-tool-table-insert-test

查询刚刚插入到表 test 的数据

select * from test.test;

mysql-tool-table-select-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;

参见 hub.docker.com/_/mysql


评论