docker-compose.yml

  1. version: "3"
  3. services:
  4.   mysql:
  5.     container_name: mysql8
  6.     image: mysql/mysql-server:8.0.18-1.1.13
  7.     command: --default-authentication-plugin=mysql_native_password
  8.     ports:
  9.       - 3306:3306
  10.       - 33060:33060
  11.     environment:
  12.       MYSQL_ROOT_PASSWORD: 123456
  13.       MYSQL_USER: hxy
  14.       MYSQL_PASSWORD: hxy
  15.     volumes:
  16.       - ./conf/my.cnf:/etc/mysql/my.cnf
  17.       - ./conf/conf.d:/etc/mysql/conf.d
  18.       - ./data:/var/lib/mysql
  19.       - ./log:/var/log/mysql

MYSQL_USER 、 MYSQL_PASSWORD 配置 mysql 新用户账号密码

在./conf目录下新建my.cnf，配置以下内容

  1. !includedir /etc/mysql/conf.d

这样 /etc/mysql/conf.d 下的 .cnf 配置文件就会被加载

在 ./conf/conf.d 新建文件 mysql.cnf，内容如下：

  1. [mysqld]
  2. # -----binlog 相关配置 start---------
  3. # 打开 binlog 需要指定 server-id
  4. server-id=1
  5. # binlog 地址
  6. log_bin=/var/log/mysql/mysql-bin.log
  7. # binlog 过期数据
  8. expire_logs_days=10
  9. # binlog 文件最大大小
  10. max_binlog_size=100M
  11. # binlog 格式，有 row/statement/mixed 三种格式
  12. binlog_format=row
  13. # -----binlog 相关配置 end---------
  15. bind-address=0.0.0.0

如果不打开 binlog，可以不用配置 binlog 相关配置 参考：https://snapshooter.com/learn/mysql/enable-and-use-binary-log-mysql

启动：

  1. docker-compose up -d

客户端连接：

image.png

mysql+mysqld-exporter

  1. version: "3"
  3. services:
  4.   mysql:
  5.     container_name: mysql8
  6.     image: mysql/mysql-server:8.0.18-1.1.13
  7.     command: --default-authentication-plugin=mysql_native_password
  8.     ports:
  9.       - 3306:3306
  10.       - 33060:33060
  11.     environment:
  12.       MYSQL_ROOT_PASSWORD: 123456
  13.       MYSQL_USER: hxy
  14.       MYSQL_PASSWORD: hxy
  15.     volumes:
  16.       - ./conf/my.cnf:/etc/mysql/my.cnf
  17.       - ./conf/conf.d:/etc/mysql/conf.d
  18.       - ./data:/var/lib/mysql
  19.       - ./log:/var/log/mysql
  20.     networks:
  21.       - mysql-network
  23.   mysqld-exporter:
  24.     container_name: mysqld-exporter
  25.     image: prom/mysqld-exporter:v0.12.1
  26.     ports:
  27.       - 9104:9104
  28.     environment:
  29.       DATA_SOURCE_NAME: "hxy:hxy@(mysql:3306)/"
  30.     networks:
  31.       - mysql-network
  32.     depends_on:
  33.       - mysql
  35. networks:
  36.   mysql-network:

浏览器访问 http://localhost:9104 ，出现 mysql 监控参数，证明配置成功

  1. ...
  2. mysql_global_status_commands_total{command="create_function"} 0
  3. mysql_global_status_commands_total{command="create_index"} 0
  4. mysql_global_status_commands_total{command="create_procedure"} 0
  5. mysql_global_status_commands_total{command="create_resource_group"} 0
  6. mysql_global_status_commands_total{command="create_role"} 0
  7. mysql_global_status_commands_total{command="create_server"} 0
  8. mysql_global_status_commands_total{command="create_spatial_reference_system"} 0
  9. mysql_global_status_commands_total{command="create_table"} 34
  10. mysql_global_status_commands_total{command="create_trigger"} 0
  11. mysql_global_status_commands_total{command="create_udf"} 0
  12. mysql_global_status_commands_total{command="create_user"} 0
  13. mysql_global_status_commands_total{command="create_view"} 0
  14. mysql_global_status_commands_total{command="dealloc_sql"} 0
  15. mysql_global_status_commands_total{command="delete"} 0
  16. ...

链接：https://registry.hub.docker.com/r/prom/mysqld-exporter

常用操作

进入 mysql 容器

  1. $ mysql -uroot -p
  2. Enter password:

查看用户权限

语法：show grants for '{user}'@'{host}'

  1. $ show grants for 'hxy'@'%';
  2. +-------------------------------------------------------------------+
  3. | Grants for hxy@%                                                  |
  4. +-------------------------------------------------------------------+
  5. | GRANT USAGE ON *.* TO `hxy`@`%`                                   |
  6. | GRANT ALL PRIVILEGES ON `testdb`.* TO `hxy`@`%` WITH GRANT OPTION |
  7. +-------------------------------------------------------------------+

用户授权

给用户授予某个库的所有权限
语法：grant all privileges on{db_name}.{table_name} to {user}@'{host}' [WITH GRANT OPTION] 

  1. $ grant all privileges on `testdb`.* to hxy@'%';
  2. Query OK, 0 rows affected (0.00 sec)

WITH GRANT OPTION 表示该用户可以将该权限授予其他用户

撤销权限

语法：revoke all on{db_name}.{table_name} from {user}@'{host}';

  1. $ revoke all on `testdb`.* from hxy@'%';
  2. Query OK, 0 rows affected (0.01 sec)

踩坑经历

  1. 如果不挂载 my.cnf 文件，并配置!includedir，mysql 并不会加载 /etc/mysql/conf.d 下的配置文件，这点在 dockerhub 中没讲

image.png
https://hub.docker.com/_/mysql?tab=description

进入 mysql 容器，使用 mysql --help | grep my.cnf -C 1

  1. # mysql --help | grep /my.cnf -C 1
  2. Default options are read from the following files in the given order:
  3. /etc/my.cnf /etc/mysql/my.cnf /usr/etc/my.cnf ~/.my.cnf
  4. The following groups are read: mysql client

可以看到 mysql 读取配置文件的优先级是/etc/my.cnf /etc/mysql/my.cnf /usr/etc/my.cnf ~/.my.cnf，然后在容器中只看到/etc/my.cnf这一个配置文件，该配置文件的内容如下

  1. # For advice on how to change settings please see
  2. # http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html
  4. [mysqld]
  5. #
  6. # Remove leading # and set to the amount of RAM for the most important data
  7. # cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
  8. # innodb_buffer_pool_size = 128M
  9. #
  10. # Remove leading # to turn on a very important data integrity option: logging
  11. # changes to the binary log between backups.
  12. # log_bin
  13. #
  14. # Remove leading # to set options mainly useful for reporting servers.
  15. # The server defaults are faster for transactions and fast SELECTs.
  16. # Adjust sizes as needed, experiment to find the optimal values.
  17. # join_buffer_size = 128M
  18. # sort_buffer_size = 2M
  19. # read_rnd_buffer_size = 2M
  20. skip-host-cache
  21. skip-name-resolve
  22. datadir=/var/lib/mysql
  23. socket=/var/lib/mysql/mysql.sock
  24. secure-file-priv=/var/lib/mysql-files
  25. user=mysql
  27. # Disabling symbolic-links is recommended to prevent assorted security risks
  28. symbolic-links=0
  30. #log-error=/var/log/mysqld.log
  31. pid-file=/var/run/mysqld/mysqld.pid

可以看到，my.cnf 并没有使用 !includedir 加载 /etc/mysql/conf.d 目录下的配置文件

  1. 即使加了!includedir配置后，启动的时候，mysql 还是不会读取 /etc/mysql/conf.d 目录下的配置文件

docker-compose.yml 中挂载了 ./conf/my.cnf 到 /etc/mysql/conf.d/my.cnf，并且在 ./conf/my.cnf 下加了 !includedir 配置，指定加载 /etc/mysql/conf.d 下的配置文件

  1. !includedir /etc/mysql/conf.d

在 /etc/mysql/conf.d 下新建 mysql.cnf 配置文件，配置如下

  1. [mysqld]
  2. server-id=1
  3. log_bin=/var/log/mysql/mysql-bin.log
  4. expire_logs_days=10
  5. max_binlog_size=100M
  6. binlog_format=row

然后启动 mysql 容器，进入到容器中发现 binlog 还是处于 off 状态

  1. $ docker exec -it mysql5.7 /bin/bash
  2. bash-4.2# mysql -uroot -p123456 -e "show variables like '%log_bin%';"
  3. mysql: [Warning] Using a password on the command line interface can be insecure.
  4. +---------------------------------+--------------------------------+
  5. | Variable_name                   | Value                          |
  6. +---------------------------------+--------------------------------+
  7. | log_bin                         | OFF                            |
  8. | log_bin_basename                |                                |
  9. | log_bin_index                   |                                |
  10. | log_bin_trust_function_creators | OFF                            |
  11. | log_bin_use_v1_row_events       | OFF                            |
  12. | sql_log_bin                     | ON                             |
  13. +---------------------------------+--------------------------------+

log_bin = OFF 证明没打开 binlog

证明 mysql.cnf 配置不起作用/mysql 没加载到该配置，接着还是使用 mysql —help 查看是什么情况，执行命令发现有一行 warning 如下

  1. # mysql --help
  2. Warning: World-writable config file '/etc/mysql/conf.d' is ignored

该 warning 的意思是 /etc/mysql/conf.d 的权限太大，mysql 认为所有人都可以修改该目录下的文件，不安全，所以忽略该配置文件，因为我之前将这个目录的权限修改为 777，所以才会出现这种情况，将权限修改为 644 就可以了

  1. chmod 644 /etc/mysql/conf.d

这里，删除容器重新build容器也是可以的

  1. 镜像为什么使用 mysql/mysql-server:8.0.18-1.1.13

这里有点忘了，因为 dockerhub 里，mysql 的镜像并不是这个，不知道为什么要用这个，后面有空再研究
dockerhub - mysql: https://hub.docker.com/_/mysql?tab=tags