使用docker搭建服务方便又快捷，这里使用docker来搭建mysql
查询mysql镜像/下载镜像
[root@localhost-docker ~]#docker search mysql
NAME DESCRIPTION STARS OFFICIAL AUTOMATED
mysql MySQL is a widely used, open-source relation… 7474 [OK]
mariadb MariaDB is a community-developed fork of MyS… 2410 [OK]
mysql/mysql-server Optimized MySQL Server Docker images. Create… 557 [OK]
zabbix/zabbix-server-mysql Zabbix Server with MySQL database support 150 [OK]
hypriot/rpi-mysql RPi-compatible Docker Image with Mysql 102
zabbix/zabbix-web-nginx-mysql Zabbix frontend based on Nginx web-server wi… 80 [OK]
centurylink/mysql Image containing mysql. Optimized to be link… 59 [OK]
1and1internet/ubuntu-16-nginx-php-phpmyadmin-mysql-5 ubuntu-16-nginx-php-phpmyadmin-mysql-5 47 [OK]
centos/mysql-57-centos7 MySQL 5.7 SQL database server 43
mysql/mysql-cluster Experimental MySQL Cluster Docker images. Cr… 35
tutum/mysql Base docker image to run a MySQL database se… 31
bitnami/mysql Bitnami MySQL Docker Image 23 [OK]
schickling/mysql-backup-s3 Backup MySQL to S3 (supports periodic backup… 23 [OK]
linuxserver/mysql A Mysql container, brought to you by LinuxSe… 17
zabbix/zabbix-proxy-mysql Zabbix proxy with MySQL database support 17 [OK]
centos/mysql-56-centos7 MySQL 5.6 SQL database server 12
circleci/mysql MySQL is a widely used, open-source relation… 7
mysql/mysql-router MySQL Router provides transparent routing be… 6
openshift/mysql-55-centos7 DEPRECATED: A Centos7 based MySQL v5.5 image… 6
dsteinkopf/backup-all-mysql backup all DBs in a mysql server 5 [OK]
openzipkin/zipkin-mysql Mirror of https://quay.io/repository/openzip… 1
jelastic/mysql An image of the MySQL database server mainta… 1
cloudfoundry/cf-mysql-ci Image used in CI of cf-mysql-release 0
ansibleplaybookbundle/mysql-apb An APB which deploys RHSCL MySQL 0 [OK]
cloudposse/mysql Improved `mysql` service with support for `m… 0 [OK]
#这里可以选择不同的mysql版本，我们下载最新版的mysql（mysql8.0）
[root@localhost-docker ~]#docker pull mysql
Using default tag: latest
latest: Pulling from library/mysql
a5a6f2f73cd8: Pull complete
936836019e67: Pull complete
283fa4c95fb4: Pull complete
1f212fb371f9: Pull complete
e2ae0d063e89: Pull complete
5ed0ae805b65: Pull complete
0283dc49ef4e: Pull complete
a7e1170b4fdb: Pull complete
88918a9e4742: Pull complete
241282fa67c2: Pull complete
b0fecf619210: Pull complete
bebf9f901dcc: Pull complete
Digest: sha256:b7f7479f0a2e7a3f4ce008329572f3497075dc000d8b89bac3134b0fb0288de8
Status: Downloaded newer image for mysql:latest
#查看下载的镜像
[root@localhost-docker ~]#docker images
REPOSITORY TAG IMAGE ID CREATED SIZE
mysql latest f991c20cb508 2 weeks ago 486MB
hello-world latest 4ab4c602aa5e 2 months ago 1.84kB使用mysql镜像创建一个mysql服务容器
[root@localhost-docker ~]#docker run --name mysql8 -e MYSQL_ROOT_PASSWORD=123456 -p 3306:3306 -d mysql
77b8c375d7f50c21756badd1e9ec1e0aae5c62d42c01f096c08fd0ea42017547
#创建容器的参数含义：
run 运行一个容器
--name 给容器起个名称
-e 设置环境变量，此处是设置root密码
-p 3306:3306 容器中的3306端口映绑定到本机的3306端口
-d 表示使用守护进程运行，即服务挂在后台
最后参数一个为镜像的名称
#可选参数：
-v 主机目录映射容器目录（比如配置文件和数据库目录）
--dns 8.8.8.8 指定容器使用的DNS服务器，默认和宿主一致；
--dns-search example.com 指定容器DNS搜索域名，默认和宿主一致；
-h "mars" 指定容器的hostname；
-e username="ritchie" 设置环境变量；
--env-file=[] 从指定文件读入环境变量；
--cpuset="0-2" or --cpuset="0,1,2" 绑定容器到指定CPU运行；
-m 设置容器使用内存最大值；
--net="bridge" 指定容器的网络连接类型，支持 bridge/host/none/container: 四种类型；
--link=[] 添加链接到另一个容器；
--expose=[] 开放一个端口或一组端口；
#查看容器运行情况
[root@localhost-docker ~]#docker ps -a
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
77b8c375d7f5 mysql "docker-entrypoint.s…" About a minute ago Up About a minute 0.0.0.0:3306->3306/tcp, 33060/tcp mysql8
2b0d38b55f65 hello-world "/hello" 17 hours ago Exited (0) 17 hours ago condescending_bohr
进入mysql容器
[root@localhost-docker ~]#docker exec -it mysql8 /bin/bash
#参数含义：
-d :分离模式: 在后台运行
-i :即使没有附加也保持STDIN 打开
-t :分配一个伪终端
root@77b8c375d7f5:/# mysql -uroot -p123456
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 8.0.13 MySQL Community Server - GPL
Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
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> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.01 sec)
mysql>
或者 使用mysql镜像创建一个mysql服务容器并使用数据卷来挂载
#创建数据卷
[root@localhost-docker ~]#docker volume create mysql8
mysql8
#查看数据卷
[root@localhost-docker ~]#docker volume ls
DRIVER VOLUME NAME
local mysql8
#创建容器并且挂载数据卷到容器中
[root@localhost-docker ~]#docker run -dit --name mysql8 --mount src=mysql8,dst=/var/lib/mysql -e MYSQL_ROOT_PASSWORD=123456 -p 3306:3306 mysql
1f09192e6ec4631b07e25625ee94b3cc4dbc0491c194a9d2e4ce1806e3cb68bf
#查看创建的容器
[root@localhost-docker ~]#docker ps -a
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
1f09192e6ec4 mysql "docker-entrypoint.s…" 23 seconds ago Up 21 seconds 0.0.0.0:3306->3306/tcp, 33060/tcp mysql8
#进入容器中创建一个数据库，测试数据卷本地目录是否也会创建对应文件
[root@localhost-docker ~]#docker exec -it mysql8 bash
root@1f09192e6ec4:/# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.13 MySQL Community Server - GPL
Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
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> create database volume_test;
Query OK, 1 row affected (0.10 sec)
mysql>
#验证 [查看Mounts部分]
[root@localhost-docker ~]#docker inspect mysql8
#查看本地数据卷对应目录中的文件 [我这里修改过docker的默认数据目录默认为/var/lib/docker]
[root@localhost-docker ~]#cd /data/docker/volumes/mysql8/_data/
[root@localhost-docker /data/docker/volumes/mysql8/_data]#ll
total 179192
-rw-r----- 1 systemd-bus-proxy input 56 Apr 30 15:06 auto.cnf
-rw-r----- 1 systemd-bus-proxy input 3073835 Apr 30 15:06 binlog.000001
-rw-r----- 1 systemd-bus-proxy input 357 Apr 30 15:12 binlog.000002
-rw-r----- 1 systemd-bus-proxy input 32 Apr 30 15:06 binlog.index
-rw------- 1 systemd-bus-proxy input 1680 Apr 30 15:06 ca-key.pem
-rw-r--r-- 1 systemd-bus-proxy input 1112 Apr 30 15:06 ca.pem
-rw-r--r-- 1 systemd-bus-proxy input 1112 Apr 30 15:06 client-cert.pem
-rw------- 1 systemd-bus-proxy input 1676 Apr 30 15:06 client-key.pem
-rw-r----- 1 systemd-bus-proxy input 5949 Apr 30 15:06 ib_buffer_pool
-rw-r----- 1 systemd-bus-proxy input 12582912 Apr 30 15:12 ibdata1
-rw-r----- 1 systemd-bus-proxy input 50331648 Apr 30 15:12 ib_logfile0
-rw-r----- 1 systemd-bus-proxy input 50331648 Apr 30 15:06 ib_logfile1
-rw-r----- 1 systemd-bus-proxy input 12582912 Apr 30 15:06 ibtmp1
drwxr-x--- 2 systemd-bus-proxy input 187 Apr 30 15:06 #innodb_temp
drwxr-x--- 2 systemd-bus-proxy input 143 Apr 30 15:06 mysql
-rw-r----- 1 systemd-bus-proxy input 31457280 Apr 30 15:12 mysql.ibd
drwxr-x--- 2 systemd-bus-proxy input 4096 Apr 30 15:06 performance_schema
-rw------- 1 systemd-bus-proxy input 1680 Apr 30 15:06 private_key.pem
-rw-r--r-- 1 systemd-bus-proxy input 452 Apr 30 15:06 public_key.pem
-rw-r--r-- 1 systemd-bus-proxy input 1112 Apr 30 15:06 server-cert.pem
-rw------- 1 systemd-bus-proxy input 1676 Apr 30 15:06 server-key.pem
drwxr-x--- 2 systemd-bus-proxy input 28 Apr 30 15:06 sys
-rw-r----- 1 systemd-bus-proxy input 12582912 Apr 30 15:06 undo_001
-rw-r----- 1 systemd-bus-proxy input 10485760 Apr 30 15:12 undo_002
drwxr-x--- 2 systemd-bus-proxy input 6 Apr 30 15:12 volume_test
授权用户远程连接
#mysql8.0 之前授权语句是不能用的，8.0后要先创建用户，然后再授权。
#mysql8.0 之前的版本默认加密认证规则是mysql_native_password, mysql8.0之后默认加密认证规则是caching_sha2_password
#注意：默认使用远程工具是连接不上的，在设置用户密码时候需要加上 with mysql_native_password 参数才可以。
#查看user表
mysql> select host, user, authentication_string, plugin from user;
+-----------+------------------+------------------------------------------------------------------------+-----------------------+
| host | user | authentication_string | plugin |
+-----------+------------------+------------------------------------------------------------------------+-----------------------+
| % | root | $A$005$whW=Qp{Q jf:c0Of8AWSmczxg3VgzdjLisc2AUeUxQLH0WG66jIuRHFAS. | caching_sha2_password |
| localhost | mysql.infoschema | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password |
| localhost | mysql.session | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password |
| localhost | mysql.sys | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password |
| localhost | root | $A$005$LS z7#CE~`
6]pHq/0tshTFB1aWQuSuiiQ2w6LFt4Kpi0UTKdRpYBmvLZL/ | caching_sha2_password |
+-----------+------------------+------------------------------------------------------------------------+-----------------------+
5 rows in set (0.00 sec)
#看到有一个root@%的用户了，但是密码加密认证方式是caching_sha2_password，我们需要修改下
mysql> ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY '666666';
Query OK, 0 rows affected (0.04 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
远程连接测试
[root@localhost-docker ~]#ip a
1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN qlen 1
link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
inet 127.0.0.1/8 scope host lo
valid_lft forever preferred_lft forever
inet6 ::1/128 scope host
valid_lft forever preferred_lft forever
2: ens192: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc mq state UP qlen 1000
link/ether 00:0c:29:b9:c6:90 brd ff:ff:ff:ff:ff:ff
inet 192.168.16.187/22 brd 192.168.19.255 scope global ens192
valid_lft forever preferred_lft forever
inet6 fe80::e6e5:a42b:e32e:85c0/64 scope link
valid_lft forever preferred_lft forever
3: docker0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc noqueue state UP
link/ether 02:42:ab:23:5b:49 brd ff:ff:ff:ff:ff:ff
inet 172.17.0.1/16 brd 172.17.255.255 scope global docker0
valid_lft forever preferred_lft forever
inet6 fe80::42:abff:fe23:5b49/64 scope link
valid_lft forever preferred_lft forever
7: veth8f5b2bd@if6: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc noqueue master docker0 state UP
link/ether aa:9f:6a:b7:71:4c brd ff:ff:ff:ff:ff:ff link-netnsid 0
inet6 fe80::a89f:6aff:feb7:714c/64 scope link
valid_lft forever preferred_lft forever
[root@localhost-docker ~]#netstat -lntp
Active Internet connections (only servers)
Proto Recv-Q Send-Q Local Address Foreign Address State PID/Program name
tcp 0 0 0.0.0.0:22 0.0.0.0:* LISTEN 1229/sshd
tcp 0 0 127.0.0.1:25 0.0.0.0:* LISTEN 1789/master
tcp6 0 0 :::3306 :::* LISTEN 9194/docker-proxy
tcp6 0 0 :::22 :::* LISTEN 1229/sshd
tcp6 0 0 ::1:25 :::* LISTEN 1789/master
#使用另一台机器连接测试(如果没有更改密码的加密认证方式会报这样的错误)
[root@localhost ~]# mysql -uroot -p -h192.168.16.187
Enter password:
ERROR 2059 (HY000): Authentication plugin 'caching_sha2_password' cannot be loaded: /usr/local/mysql/lib/plugin/caching_sha2_password.so: cannot open shared object file: No such file or directory
#改完加密认证方式后再次连接，连接成功
[root@localhost emucoo-service]# mysql -uroot -p666666 -h192.168.16.187
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 14
Server version: 8.0.13 MySQL Community Server - GPL
Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.
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>
本文最后记录时间 2024-03-31
文章链接地址：https://wojc.cn/archives/335.html
本站文章除注明[转载|引用|来源],均为本站原创内容,转载前请注明出处
文章链接地址：https://wojc.cn/archives/335.html
本站文章除注明[转载|引用|来源],均为本站原创内容,转载前请注明出处
