下载mysql镜像

  • https://hub.docker.com/_/mysql 从官网查看mysql镜像

  • 执行下载命令 : [docker pull mysql] 默认下载的是最新的版本

    1. # 下载最新版本
    2. docker pull mysql
    3. # 下载5.7.32
    4. docker pull mysql:5.7.32

    image.png

    运行mysql镜像

    1. # 运行一个5.7.32
    2. docker run --name mysql5.7 -p 3308:3306  -e MYSQL_ROOT_PASSWORD=123456 -d mysql:5.7.32
    3. # 运行8 将3306端口映射到宿主机的3307端口上
    4. docker run --name mysql8 -p 3307:3306  -e MYSQL_ROOT_PASSWORD=123456 -d mysql

    连接mysql 5.7

    image.png
    image.png

    连接mysql 8

  • 连接mysql 8 会遇到连接失败的问题。因为登录认证的客户端和服务端都有改变导致。

image.png
image.png

  • 修改办法 ```sql

    docker mysql -u root -p 登录系统

    切换到mysql数据库

    use mysql

    查看user信息

    select user, host,plugin, authentication_string from user;

    本地用户权限修改

    ALTER USER ‘root’@’localhost’ IDENTIFIED BY ‘password’ PASSWORD EXPIRE NEVER; ALTER USER ‘root’@’localhost’ IDENTIFIED WITH mysql_native_password BY ‘password’;

    非本地权限修改

    ALTER USER ‘root’@’%’ IDENTIFIED BY ‘password’ PASSWORD EXPIRE NEVER; alter user ‘root’@’%’ identified with mysql_native_password by ‘password’;

    刷新权限

    FLUSH PRIVILEGES;

连接到docker 的mysql

docker exec -it 容器ID /bin/bash 或者通过桌面工具直接打开

  1. ![image.png](https://cdn.nlark.com/yuque/0/2021/png/2565785/1610357424486-bc4a8f37-778e-490b-9a1e-4ede167a24d7.png#height=720&id=lzwTJ&margin=%5Bobject%20Object%5D&name=image.png&originHeight=1440&originWidth=2500&originalType=binary&ratio=1&size=464490&status=done&style=none&width=1250)
  3. - 修改后的权限列表如下
  5. ![image.png](https://cdn.nlark.com/yuque/0/2021/png/2565785/1610357276467-ce8dcdda-880c-44b4-8503-10d54018b999.png#height=103&id=DK6Z1&margin=%5Bobject%20Object%5D&name=image.png&originHeight=205&originWidth=1420&originalType=binary&ratio=1&size=87235&status=done&style=none&width=710)
  7. - 连接成功
  9. ![image.png](https://cdn.nlark.com/yuque/0/2021/png/2565785/1610357328105-56d0a078-fd12-4d54-930a-9e655b5e581c.png#height=129&id=KOyZ6&margin=%5Bobject%20Object%5D&name=image.png&originHeight=258&originWidth=840&originalType=binary&ratio=1&size=159323&status=done&style=none&width=420)
  11. - 查看运行日志 docker logs container_id
  14. ```shell
  15. # mysql 8 增加配置
  16. sudo docker run --name dqc-mysql8-dev -e MYSQL_ROOT_PASSWORD=123456 -e MYSQL_ROOT_HOST=%  -v /opt/aml/docker/mysql8-dev/conf:/etc/mysql/conf.d -v /opt/aml/docker/mysql8-dev/data:/var/lib/mysql -p 3307:3306  -d haakco/mysql80 --character-set-server=utf8mb4 --collation-server=utf8mb4_unicode_ci --lower_case_table_names=1
  17. # MYSQL_ROOT_PASSWORD root用户密码 
  18. # MYSQL_ROOT_HOST=% 不限制访问IP
  19. # -v /opt/aml/docker/mysql8-dev/conf:/etc/mysql/conf.d 配置文件目录
  20. # -v /opt/aml/docker/mysql8-dev/data:/var/lib/mysql 挂载目录
  21. # --lower_case_table_names 大小写敏感
  22. # --skip-name-resolve 掉过
  1. sudo docker run --name dqc-mysql8-test -e MYSQL_ROOT_PASSWORD=123456 -e MYSQL_ROOT_HOST=%  -v /home/dhcc/docker/mysql8-test/conf:/etc/mysql/conf.d -v /home/dhcc/docker/mysql8-test/data:/var/lib/mysql -p 3308:3306  -d haakco/mysql80 --character-set-server=utf8mb4 --collation-server=utf8mb4_unicode_ci --lower_case_table_names=1 --skip-name-resolve
  1. sudo docker run --name dqc-mysql8-dev -e MYSQL_ROOT_PASSWORD=123456 -e MYSQL_ROOT_HOST=%  -v /home/dhcc/docker/mysql8-dev/conf:/etc/mysql/conf.d -v /home/dhcc/docker/mysql8-dev/data:/var/lib/mysql -p 3307:3306  -d haakco/mysql80 --character-set-server=utf8mb4 --collation-server=utf8mb4_unicode_ci --lower_case_table_names=1 --skip-name-resolve

mysql8 因密码问题连接不上可执行

  1. ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY '123456';
  2. -- 刷新权限
  3. flush privileges;
  5. UPDATE user SET Password=PASSWORD('123456') where USER='root'；

本地配置文件

  1. [mysqld]
  2. local-infile=1
  3. secure-file-priv=NULL
  4. skip-grant-tables