使用docker-compose在启动mysql容器时新建一个mysql用户并赋予其权限

使用docker-compose在启动mysql容器时新建一个mysql用户并赋予其权限

先来看看启动文件

# 原始的yml文件，只创建了一个wordpress_user用户
version: '3.2'
services:
    mysql-server:
      container_name: mysql
      ports:
        - "13306:3306" 
      environment:
        MYSQL_ROOT_PASSWORD: 12345
        MYSQL_DATABASE: wordpress
        MYSQL_USER: wordpress_user
        MYSQL_PASSWORD: secret
      image: mysql/mysql-server
    wordpress:
      image: wordpress:latest
      container_name: wordpress
      ports:
        - "20080:80"
      environment:
        WORDPRESS_DB_HOST: mysql-server:3306
        WORDPRESS_DB_USER: wordpress_user
        WORDPRESS_DB_PASSWORD: secret
      depends_on:
        - mysql-server

查询了一些资料后，知道了docker中mysql可以在启动时在/docker-entrypoint-initdb.d/目录运行初始化的sql文件，通过volume映射到主机的目录/root/mysql/init/下（当然这个目录你可以自己新建）。
初始化sql文件为init.sql

-- create a new user
create user 'user_test'@'%' identified by '12345';
-- grant all permission to all databases
GRANT ALL PRIVILEGES ON *.* TO 'user_test'@'%';
-- flush
flush privileges;

你也可以新增你想执行的sql语句，例如insert等等。
在yml文件中添加volume映射

version: '3.2'
services:
    mysql-server:
      container_name: mysql
      ports:
        - "13306:3306" 
      environment:
        MYSQL_ROOT_PASSWORD: 12345
        MYSQL_DATABASE: wordpress
        MYSQL_USER: wordpress_user
        MYSQL_PASSWORD: secret
      image: mysql/mysql-server
    volumes:
      - /root/mysql/init/:/docker-entrypoint-initdb.d/
    wordpress:
      image: wordpress:latest
      container_name: wordpress
      ports:
        - "20080:80"
      environment:
        WORDPRESS_DB_HOST: mysql-server:3306
        WORDPRESS_DB_USER: wordpress_user
        WORDPRESS_DB_PASSWORD: secret
      depends_on:
        - mysql-server

启动docker-compose文件

docker-compose up -d

进入容器内mysql，切换到mysql数据库

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 145
Server version: 8.0.27 MySQL Community Server - GPL

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

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> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed

查看用户权限

mysql> select user,host from user;
+------------------+-----------+
| user             | host      |
+------------------+-----------+
| user_test        | %         |
| wordpress_user   | %         |
| healthchecker    | localhost |
| mysql.infoschema | localhost |
| mysql.session    | localhost |
| mysql.sys        | localhost |
| root             | localhost |
+------------------+-----------+
7 rows in set (0.00 sec)

很清楚的看到我们新增的用户已经在user表里了，远程用工具尝试连接后也成功。