mysql数据目录挂载至外部_MySQL容器配置及数据挂载到本地路径

mkdir /data/docker/jiashunx-mysql

mkdir /data/docker/jiashunx-mysql/conf

mkdir /data/docker/jiashunx-mysql/data

vi /data/docker/jiashunx-mysql/conf/my.cnf

[mysqld]

user=mysql

character-set-server=utf8

default_authentication_plugin=mysql_native_password

[client]

default-character-set=utf8

[mysql]

default-character-set=utf8

启动mysql容器：

docker run -d -p 3306:3306 --privileged=true --name jiashunx-mysql -v /data/docker/jiashunx-mysql/conf/my.cnf:/etc/mysql/my.cnf -v /data/docker/jiashunx-mysql/data:/var/lib/mysql -e MYSQL_ROOT_PASSWORD=12335678 mysql:5.7.27

-d 表示使用守护进程运行，即服务挂在后台

-p 端口映射，容器内部(第二个参数)3306端口映射到本机端口(第一个)3306

-e MYSQL_ROOT_PASSWORD 初始化root用户密码

--name 容器名称

-privileged=true：容器内的root拥有真正root权限，否则容器内root只是外部普通用户权限

-v /data/docker/jiashunx-mysql/conf/my.cnf:/etc/mysql/my.cnf 映射mysql配置文件

-v /data/docker/jiashunx-mysql/data:/var/lib/mysql 映射数据存储目录

执行jiashunx-mysql容器命令

docker exec -it jiashunx-mysql bash

mysql -u root -p 12335678

create database solo default character set utf8mb4 collate utf8mb4_general_ci;

create user 'solo'@'%' identified by '1234.abcd';

grant all privileges on solo.* to solo;

flush privileges;

mysql -u solo -p solo (指定使用solo数据库)

MySQL workbench 连接后：

10:13:03 Error loading schema content Error Code: 1142 SELECT command denied to user 'solo'@'27.42.111.210' for table 'user_variables_by_thread'

没有select权限

UPDATE user SET select_priv='Y', alter_priv='Y', update_priv='Y', insert_priv='Y', delete_priv='Y', create_priv='Y', drop_priv='Y' WHERE User = 'solo';

说明xxxx用户没有ALTER权限。

到mysql中，

mysql> SELECT alter_priv FROM user WHERE User = 'solo';

发现alter_priv = 'N',果然没有权限。

修改权限：

mysql> UPDATE user SET alter_priv='Y' WHERE User = 'solo';

mysql> flush privileges;

另附上完整的MySQL赋权语句

UPDATE user SET select_priv='Y', alter_priv='Y', update_priv='Y', insert_priv='Y', delete_priv='Y', create_priv='Y', drop_priv='Y', execute_priv='Y', reload_priv='Y', index_priv='Y', show_db_priv='Y', create_tmp_table_priv='Y', lock_tables_priv='Y', repl_slave_priv='Y', repl_client_priv='Y', create_view_priv='Y', show_view_priv='Y', event_priv='Y', trigger_priv='Y' WHERE User = 'solo';