05.docker的mysql配置使用

mysql安装

建立一个保存mysql的文件路径

mkdir /home/data/mysql/data
mkdir /home/data/mysql/init
mkdir /home/data/mysql/conf

然后我们在/home/data/mysql文件夹下建立docker-compose.yml文件，由于我使用的是自建的网络，所以在生成容器前要建一个网络

# Use root/example as user/password credentials
version: '3.1'

services:
  db:
    container_name: mysql
    image: mysql
    # NOTE: use of "mysql_native_password" is not recommended: https://dev.mysql.com/doc/refman/8.0/en/upgrading-from-previous-series.html#upgrade-caching-sha2-password
    # (this is just an example, not intended to be a production configuration)
    command: 
        --max_connections=1000
        --character-set-server=utf8mb4
        --collation-server=utf8mb4_general_ci
        # --default-authentication-plugin=mysql_native_password
    restart: always
    environment:
      MYSQL_ROOT_PASSWORD: lqhlqh
      TZ: Asia/Shanghai
    volumes:
        - ./data:/var/lib/mysql 
        - ./init:/docker-entrypoint-initdb.d 
        - ./conf:/etc/mysql/conf.d 
    networks:
        - my_net
    ports:
        - 3306:3306

networks:
    my_net:
        name: lqh_net

然后在这个文件夹下执行命令，生成容器

docker-compose up -d

mysql简单使用

刚开始只有root用户，应该是不支持远程查看数据库的我们在本地进入以下命令

docker exec -it mysql /bin/bash
mysql -u root -p  #输密码直接回车
use mysql
select host,user from user;

果然，发现root用户的访问权限是localhost,需要修改host为%，输入命令：

update user set host='%' where user='root';
flush privileges;

再次查看后，就如下

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

mysql> select host,user,plugin,authentication_string from mysql.user;

发现如下

mysql> select host,user,plugin,authentication_string from mysql.user;
+-----------+------------------+-----------------------+------------------------------------------------------------------------+
| host      | user             | plugin                | authentication_string                                                  |
+-----------+------------------+-----------------------+------------------------------------------------------------------------+
| %         | root             | caching_sha2_password |                                                                        |
| localhost | mysql.infoschema | caching_sha2_password | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| localhost | mysql.session    | caching_sha2_password | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| localhost | mysql.sys        | caching_sha2_password | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
+-----------+------------------+-----------------------+------------------------------------------------------------------------+
4 rows in set (0.00 sec)

备注：host为 % 表示不限制ip localhost表示本机使用 plugin非mysql_native_password 则需要修改密码

mysql> use mysql;
mysql> alter user 'root'@'%' identified with mysql_native_password by 'lqhlqh';
mysql> flush privileges;
mysql> select host,user,plugin,authentication_string from mysql.user;

然后就是,下面这样

mysql> select host,user,plugin,authentication_string from mysql.user;
+-----------+------------------+-----------------------+------------------------------------------------------------------------+
| host      | user             | plugin                | authentication_string                                                  |
+-----------+------------------+-----------------------+------------------------------------------------------------------------+
| %         | root             | mysql_native_password | *592DE88F72C00A3F04E2E093E9098B474CB7074A                              |
| localhost | mysql.infoschema | caching_sha2_password | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| localhost | mysql.session    | caching_sha2_password | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| localhost | mysql.sys        | caching_sha2_password | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
+-----------+------------------+-----------------------+------------------------------------------------------------------------+
4 rows in set (0.00 sec)

使用mysql桌面应用，就可以远程登陆查看数据库了