blank的编程之路


  • 首页

  • 归档

  • 搜索
consul atomikos mybatisplus druid nexus nas named bind mysqldump acme.sh Dockerfile rsync keepalived swarm kibana ubuntu linux scp bugfix https ssl certbot curl gogs adminer harbor yum portainer python kubernetes idea java springboot maven docker-compose redis nginx mysql brew git chocolatey jenkins elasticsearch docker haproxy rabbitmq centos

docker-compose 运行 mysql 8 + adminer

发表于 2019-08-16 | 分类于 运维 | 0 | 阅读次数 957

安装环境

  • docker v19.03
  • docker-compose 1.24.1

mysql 配置文件

mkdir -p /docker/mysql/conf

cat > /docker/mysql/conf/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

编辑 mysql docker-compose 文件

cat > /docker/mysql/docker-compose.yml << EOF
# Use root/example as user/password credentials
version: '3.7'

services:

  db:
    image: mysql:8
    container_name: mysql
    command: --default-authentication-plugin=mysql_native_password
    restart: always
    privileged: true
    environment:
      TZ: "Asia/Shanghai"
      MYSQL_ROOT_PASSWORD: 123456
      #MYSQL_USER: "test"
      #MYSQL_PASS: "test123"
    volumes:
      - "./data:/var/lib/mysql"
      - "./conf/my.cnf:/etc/my.cnf"
      - "./log:/var/log/mysql"
    ports:
      - 3306:3306

  adminer:
    image: adminer
    container_name: mysql-adminer
    restart: always
    ports:
      - 8080:8080
EOF

目录授权

chmod -R 777 /docker/mysql/data
chmod -R 777 /docker/mysql/log

启动服务

# 进入刚刚编写的 docker-compose.yml 的目录
cd /docker/mysql
# 以后台服务的方式启动 mysql 和 adminer
docker-compose up -d

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
[[email protected] mysql]# docker exec -it mysql bash
# 使用 root 账号登陆 mysql 密码就是前面docker-compose.yml中配置的
[email protected]:/# 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)

# 我们退出重新使用新创建的用户登陆测试
mysql> exit
Bye
[email protected]:/# 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

参见 mysql

  • 本文作者: blank
  • 本文链接: https://blankhang.com/2019/08/16/201908161621
  • 版权声明: 本博客所有文章除特别声明外,均采用CC BY-NC-SA 3.0 许可协议。转载请注明出处!
# consul # atomikos # mybatisplus # druid # nexus # nas # named # bind # mysqldump # acme.sh # Dockerfile # rsync # keepalived # swarm # kibana # ubuntu # linux # scp # bugfix # https # ssl # certbot # curl # gogs # adminer # harbor # yum # portainer # python # kubernetes # idea # java # springboot # maven # docker-compose # redis # nginx # mysql # brew # git # chocolatey # jenkins # elasticsearch # docker # haproxy # rabbitmq # centos
CentOS 升级内核到 5.X && 启用 bbr && 内核降级
CentOS7 安装 python36
© 2021 blank
Everything is energy and everything has a frequency