Mysql

• 最好先建立共享网络

docker network create sqlNet

docker 中下载 mysql

docker pull mysql:8.0.20

启动mysql

docker run -p 15502:3306 -e MYSQL_ROOT_PASSWORD=mysql123456  --name mysql8.0.20 --net=sqlNet --network-alias  -d mysql:8.0.20

拷贝配置文件

mkdir /data/docker/mysql8.0.20
docker cp  mysql8.0.20:/etc/mysql /data/docker/mysql8.0.20

删除容器

docker stop mysql8.0.20
docker rm mysql8.0.20

启动脚本

cat>./mysql8.0.20.sh<<EOF
#!/bin/sh
docker run \
-p 15502:3306 \
--name mysql8.0.20 \
--privileged=true \
--restart unless-stopped \
-v /data/docker/mysql8.0.20/mysql:/etc/mysql \
-v /data/docker/mysql8.0.20/logs:/logs \
-v /data/docker/mysql8.0.20/data:/var/lib/mysql \
-v /etc/localtime:/etc/localtime \
-e MYSQL_ROOT_PASSWORD=123456 \
-d mysql:8.0.20
EOF

命令解释：
-p 端口映射
--privileged=true  挂载文件权限设置
--restart unless-stopped  设置 开机后自动重启容器
-v /data/docker/mysql8.0.20/mysql:/etc/mysql    挂载配置文件
-v /data/docker/mysql8.0.20/logs:/logs \      挂载日志
-v /data/docker/mysql8.0.20/data:/var/lib/mysql \  挂载数据文件 持久化到主机，
-v /etc/localtime:/etc/localtime    容器时间与宿主机同步
-e MYSQL_ROOT_PASSWORD=123456    设置密码
-d  mysql:8.0.20   后台启动,mysql

sh ./mysql8.0.20.sh

停止mysql

docker stop mysql8.0.20

启动 mysql

docker start mysql8.0.20

建立远程用户

docker exec -it mysql8.0.20 /bin/bash

mysql -uroot -p

# 1. 查询前，必须要修改密码，如新密码123456:
ALTER USER 'root'@'localhost' IDENTIFIED with mysql_native_password BY '你的新密码';
#刷新权限
flush privileges;
# 首次改密推荐使用本地密码插件 mysql_native_password
 
use mysql;
select user,host,plugin,authentication_string from user;

# 创建用户任意远程访问
CREATE user 'root'@'%';
# 修改密码
alter user 'root'@'%' identified with mysql_native_password by '123456';
 #给用户授权
grant all privileges on *.* to "root"@"%";
#刷新权限
flush privileges;

# 2. 更改具体用户远程访问
# 创建'root'@'127.0.0.1'用户
CREATE USER 'root'@'127.0.0.1' IDENTIFIED with mysql_native_password BY '123456'; 
#===> 记住刷新权限
flush privileges; 

select user,host,plugin,authentication_string from user;
 
===============5.7以后===================
update user set authentication_string=password("test") where user='root';
update mysql.user set host='你要指定的主机ip' where user='root';

============以下5.7以前======================
SET PASSWORD = PASSWORD('123456');
ALTER USER 'root'@'localhost' PASSWORD EXPIRE NEVER;
flush privileges;
select user,host,plugin,authentication_string from user;
============================================

# 退出mysql>
quit;或者exit;

#关闭mysql
shutdown;

# 修改MySQL用户密码
mysqladmin -u用户名 -p旧密码 password 新密码
 
# 或进入mysql命令行
SET PASSWORD FOR '用户名'@'主机' = PASSWORD('密码');
flush privileges;

添加帐户，分配特权和删除帐户

use mysql;

#创建新用户及密码
CREATE USER 'root'@'localhost' IDENTIFIED with mysql_native_password  BY '新密码';
FLUSH PRIVILEGES;
CREATE USER 'root'@'%' IDENTIFIED with mysql_native_password  BY '123456';
FLUSH PRIVILEGES;
CREATE USER 'mysql'@'%' IDENTIFIED with mysql_native_password  BY '123456';
CREATE USER 'test'@'%' IDENTIFIED with mysql_native_password  BY '123456';

#给用户授权
GRANT ALL ON *.* TO 'root'@'localhost' WITH GRANT OPTION;
GRANT ALL ON *.* TO 'root'@'%' WITH GRANT OPTION;
GRANT ALL ON *.* TO 'mysql'@'%' WITH GRANT OPTION;
GRANT ALL ON *.* TO 'test'@'%' WITH GRANT OPTION;
FLUSH PRIVILEGES;

select user,host,plugin,authentication_string from user;

#撤消test数据库级特权
REVOKE CREATE,DROP ON test.* FROM 'test'@'%';
FLUSH PRIVILEGES;

#删除帐户
DROP USER 'test'@'%';
FLUSH PRIVILEGES;

创建远程访问新用户并授权

use mysql;
# mysql8 以前：
grant all privileges on *.* to '新用户名'@'%' identified by '密码' with grant option;
grant all privileges on *.* to '新用户名'@'指定ip' identified by '密码' with grant option;
例如:以前使用
grant all privileges on *.* to 'root'@'%' identified by "123456" with grant option;

#mysql8==>不允许<==授权并创建用户了,要分开使用
mysql8分开操作为:
CREATE USER 'root'@'%'; #创建角色CREATE USER 'root'@'%' IDENTIFIED BY '123456';
ALTER USER 'root'@'%' IDENTIFIED with mysql_native_password by '123456'; #修改密码
grant all privileges on *.* to "root"@"%"; #给角色授权
flush privileges; #刷新权限
 
#另外修改主机
update mysql.user set host='具体要指定的主机ip' where user='root';

#重命名
RENAME USER 'jeffrey'@'localhost' TO 'jeff'@'127.0.0.1'; 

flush privileges; #刷新权限
select user,host,authentication_string from user;