【Docker学习笔记】通过Docker部署MariaDB并连接到Python程序

Docker网络任务目标：

• 用Docker部署Mariadb到临时服务器 10.80.28.13
  • 把服务器的3306端口映射到容器的3306端口
  • 提供一个可以访问数据库的账户
  • 把数据和配置文件通过Docker卷映射到本机(10.80.28.13)，Docker容器可以多次重启而不会数据丢失
  • 任意新建一个数据库和表
• 编写一段Python程序
  • 容器化
  • 连接该数据库
  • 定时打印全表数据
  • 可通过docker logs查看打印出来的日志
• 利用Docker compose service 把上面的数据库和py_mariadb做成两个服务，通过 docker compose up -d启动

---

上一篇：【Docker学习笔记】docker

---

一、在Ubuntu上通过docker部署MariaDB

1.拉取MariaDB镜像

docker pull mariadb

2.运行容器并把数据和配置文件通过Docker卷映射到本机
配置了MariaDB的根密码、数据库用户和相关密码。然后在本机3307端口映射容器3306端口运行MariaDB，关于支持的环境变量的更广泛的列表，参考Docker的MariaDB的文档。

默认安装时，mariadb会在/etc/mysql/conf.d目录下存放数据库配置，数据库表格存储目录默认在/var/lib/mysql下。

datalab@datalabvm12:/$ docker run -d --name mymariadb -p 3307:3306 -e TZ="Asia/Shanghai" \
> -e MYSQL_ROOT_PASSWORD=123456 \
> -e MARIADB_USER=datalab \
> -e MARIADB_PASSWORD=123456 \
> -e MARIADB_DATABASE=testdb \
> -v /home/datalab/mariadb_vol/mariadb_data/:/var/lib/mysql \
> -v /home/datalab/mariadb_vol/mariadb_conf/:/etc/mysql/conf.d \
> mariadb
dafb5d1bbbfd4471e0c878e28c0a99a69a756876a80cae25cb0800d26772be87
datalab@datalabvm12:/$ docker container ls
CONTAINER ID   IMAGE     COMMAND                  CREATED          STATUS          PORTS                                       NAMES
dafb5d1bbbfd   mariadb   "docker-entrypoint.s…"   59 seconds ago   Up 58 seconds   0.0.0.0:3307->3306/tcp, :::3307->3306/tcp   mymariadb
#-e TZ="Asia/Shanghai" 设置容器使用的时区为UTC+8
#-e MYSQL_ROOT_PASSWORD=123456 设置root用户登录密码
#-e MARIADB_USER=datalab 设置用户
#-e MARIADB_PASSWORD=123456 设置用户密码
#-e MARIADB_DATABASE=testdb 设置数据库
#-v /home/datalab/mariadb_vol/mariadb_data/:/var/lib/mysql 挂载数据文件
#-v /home/datalab/mariadb_vol/mariadb_conf/:/etc/mysql/conf.d 挂载配置文件
#-v /xxx/log:/var/log/mysql 挂载日志文件

【解决报错】docker部署MariaDB容器执行后停止docker mariadb container is not running（已解决）
docker logs -f <container id> 查看报错日志

3.连接启动应用

datalab@datalabvm12:/$ docker exec -it dafb5d1bbbfd /bin/bash
#连接到容器内部
root@dafb5d1bbbfd:/# mariadb -u root -p
#登录root账号
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 12
Server version: 11.0.3-MariaDB-1:11.0.3+maria~ubu2204 mariadb.org binary distribution
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

二、MariaDB使用

1.数据库使用

(1)create database创建一个MariaDB数据库

MariaDB [(none)]> CREATE DATABASE  test01;
Query OK, 1 row affected (0.000 sec)

或指定字符集

MariaDB [(none)]> CREATE DATABASE test02 CHARACTER SET = utf8 COLLATE = utf8_general_ci;
Query OK, 1 row affected (0.000 sec)

查看系统上的所有可用字符集，字符集相关设置可以参考：mysql设置utf8mb4字符编码

MariaDB [(none)]> SHOW CHARACTER SET;
+----------+-----------------------------+---------------------+--------+
| Charset  | Description                 | Default collation   | Maxlen |
+----------+-----------------------------+---------------------+--------+
| big5     | Big5 Traditional Chinese    | big5_chinese_ci     |      2 |
| dec8     | DEC West European           | dec8_swedish_ci     |      1 |
| cp850    | DOS West European           | cp850_general_ci    |      1 |
| hp8      | HP West European            | hp8_english_ci      |      1 |
| koi8r    | KOI8-R Relcom Russian       | koi8r_general_ci    |      1 |
| latin1   | cp1252 West European        | latin1_swedish_ci   |      1 |
...
40 rows in set (0.000 sec)

在创建数据库中指定字符集时，此信息将存储在该特定数据库的db.opt文件中，位于/var/lib/mysql/test02目录下

MariaDB [(none)]> Ctrl-C -- exit!
Aborted
root@dafb5d1bbbfd:/# cat /var/lib/mysql/test02/db.opt
default-character-set=utf8mb3
default-collation=utf8mb3_general_ci

(2)drop database删除现有的数据库

MariaDB [(none)]> DROP DATABASE test01;
Query OK, 0 rows affected (0.023 sec)

(3)show databases查看现有数据库

MariaDB [(none)]> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| testdb             |
+--------------------+
5 rows in set (0.000 sec)

(4)alter database更改db.opt的数据库特性

MariaDB [(none)]> ALTER DATABASE test02 CHARACTER SET = utf8 COLLATE = utf8_general_ci;
Query OK, 1 row affected (0.00 sec)

(5)use进入数据库

MariaDB [(none)]> USE testdb
Database changed

2.数据表使用

(1)create table创建数据表

MariaDB [testdb]> CREATE TABLE info(name varchar(50),sex int,age int,class int,score int) default character set utf8;
Query OK, 0 rows affected (0.089 sec)
#default character set utf8 可以写入中文的数据表

(2)desc查看表结构

MariaDB [testdb]> DESC info;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| name  | varchar(50) | YES  |     | NULL    |       |
| sex   | int(11)     | YES  |     | NULL    |       |
| age   | int(11)     | YES  |     | NULL    |       |
| class | int(11)     | YES  |     | NULL    |       |
| score | int(11)     | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
5 rows in set (0.001 sec)

(3)insert into插入数据表信息

MariaDB [testdb]> INSERT INTO info(name,sex,age,class,score) value('tom',1,20,1,85);
Query OK, 1 row affected (0.022 sec)
MariaDB [testdb]> INSERT INTO info(name,sex,age,class,score) value('苹果',1,20,1,85);
Query OK, 1 row affected (0.004 sec)

(5)select * from info查询一下数据表

MariaDB [testdb]> SELECT * FROM info;
+--------+------+------+-------+-------+
| name   | sex  | age  | class | score |
+--------+------+------+-------+-------+
| tom    |    1 |   20 |     1 |    85 |
| 苹果   |    1 |   20 |     1 |    85 |
+--------+------+------+-------+-------+
2 rows in set (0.000 sec)

三、python程序容器化

1.新建项目文件夹pyDemo，编写Dockerfile

datalab@datalabvm12:~$ cd pyDemo
datalab@datalabvm12:~/pyDemo$ vim hello.py
datalab@datalabvm12:~/pyDemo$ vim Dcokerfile
datalab@datalabvm12:~/pyDemo$ cat hello.py
print('hello python')
datalab@datalabvm12:~/pyDemo$ cat Dockerfile
FROM python:3
WORKDIR /user/src/app
COPY hello.py /user/src/app/
VOLUME /user/src/app
ENTRYPOINT ["python"]
CMD ["hello.py"]

2.构建容器

datalab@datalabvm12:~/pyDemo$ docker build -t pydocker .
[+] Building 39.5s (9/9) FINISHED 

3.运行容器

datalab@datalabvm12:~/pyDemo$ docker run -v /home/datalab/py_vol:/usr/src/app pydocker
hello python

如用到了input需要输入内容，运行容器的时候需要用到-it参数，否则会报EOFError:EOF when reaeding a line的错误。

四、python连接mariaDB数据库

重新编写py_mariadb.py 、requirements.txt 、Dockerfile文件

datalab@datalabvm12:~/pyDemo$ vim py_mariadb.py
datalab@datalabvm12:~/pyDemo$ vim requirements.txt
datalab@datalabvm12:~/pyDemo$ vim Dockerfile

py_mariadb.py文件

datalab@datalabvm12:~/pyDemo$ cat py_mariadb.py
import os,sys
import pymysql
import mariadb
from threading import Timer
import time
import datetime
def prtime(): #打印当前时间
    now = datetime.datetime.now()
    print(now.strftime('%Y-%m-%d %H:%M:%S'))
prtime() #打印起始时间
#打开数据库连接
db = mariadb.connect(host="10.80.28.13",port=3307,user="root",passwd="123456",database="testdb")
#使用 cursor() 方法创建一个游标对象
cursor = db.cursor()
#使用 execute() 方法执行 SQL 查询
cursor.execute("SELECT * FROM info")
#使用 fetchall() 方法获取后所有行数据
data = cursor.fetchall()
def Mon(): #打印数据表并显示时间
    for one in data:
        print(one)
    prtime()
Timer(3,Mon).start() #延时3s执行Mon()
#关闭数据库连接
cursor.close()
db.close()

requirements.txt文件

pymysql
cryptography
mariadb
Timer
datetime

Dockerfile文件

datalab@datalabvm12:~/pyDemo$ cat Dockerfile
FROM python:3
WORKDIR /user/src/app
COPY requirements.txt ./
RUN pip install --no-cache-dir -i https://pypi.tuna.tsinghua.edu.cn/simple -r requirements.txt
COPY py_mariadb.py /user/src/app/
VOLUME /user/src/app
ENTRYPOINT ["python"]
CMD ["py_mariadb.py"]

重新构建运行，打印数据库成功

datalab@datalabvm12:~/pyDemo$ docker rm 13100d1bb984
13100d1bb984
datalab@datalabvm12:~/pyDemo$ docker build -t pydocker .
[+] Building 3.1s (10/10) FINISHED
datalab@datalabvm12:~/pyDemo$ docker run -v /home/datalab/py_vol/py_logs:/var/log/mysql pydocker
2023-08-17 01:44:09
('tom', 1, 20, 1, 85)
('苹果', 1, 20, 1, 85)
2023-08-17 01:44:12

五、docker logs查看打印出来的日志

docker logs查看打印出来的日志

datalab@datalabvm12:~/pyDemo$ docker container ls -a
CONTAINER ID   IMAGE                                COMMAND                  CREATED         STATUS                      PORTS                                       NAMES
c8f603e9c55b   pydocker                             "python py_mariadb.py"   7 minutes ago   Exited (0) 7 minutes ago                                                zen_wilbur

datalab@datalabvm12:~/pyDemo$ docker logs -f c8f603e9c55b
2023-08-17 01:44:09
('tom', 1, 20, 1, 85)
('苹果', 1, 20, 1, 85)
2023-08-17 01:44:12

六、利用Docker compose 两个service一起启动

1.新建docker-compose.yml文件配置服务

docker-compose.yml文件，利用环境变量传递数据库信息

datalab@datalabvm12:~/pyDemo$ cat docker-compose.yml
version: "3"
services:
        mymariadb:
                image: mariadb
                container_name: mymariadb
                restart: always
                ports:
                        - 3307:3306
                volumes:
                        - /home/datalab/mariadb_vol/mariadb_data/:/var/lib/mysql
                        - /home/datalab/mariadb_vol/mariadb_conf/:/etc/mysql/conf.d
                environment:
                        MYSQL_ROOT_PASSWORD: 123456
                        MARIADB_USER: datalab
                        MARIADB_PASSWORD: 123456
                        MARIADB_DATABASE: testdb
                networks:
                        - nw
        pydocker:
                image: pydocker
                container_name: pydocker
                networks:
                        - nw
                depends_on:
                        - mymariadb
                command: ["sh","wait-for","mymariadb:3306","--","python","py_mariadb.py"]
                environment: 
                        MARIADB_USER: root
                        MARIADB_DATABASE: testdb    
                        MARIADB_PASSWORD: 123456
                        MARIADB_PORT: 3307
                        MARIADB_HOST: 10.80.28.13       
                
networks:
        nw:
                driver:bridge

py_mariadb.py文件，利用os.environ.get()获取环境变量信息

datalab@datalabvm12:~/pyDemo$ cat py_mariadb.py
#!/usr/bin/python3
#-*-coding:utf-8-*-

import os,sys
import pymysql
import mariadb
from threading import Timer
import time
import datetime

mysql_host = os.environ.get("MARIADB_HOST")
mysql_post = int(os.environ.get("MARIADB_PORT"))
mysql_user = os.environ.get("MARIADB_USER")
mysql_pwd = os.environ.get("MARIADB_PASSWORD")
mysql_database = os.environ.get("MARIADB_DATABASE")

def prtime():
    now = datetime.datetime.now()
    print(now.strftime('%Y-%m-%d %H:%M:%S'))
prtime()
#打开数据库连接
#db = mariadb.connect(host="10.80.28.13",port=3307,user="root",passwd="123456",database="testdb")
db = mariadb.connect(mysql_host,mysql_post,mysql_user,mysql_pwd,mysql_database)
#使用 cursor() 方法创建一个游标对象
cursor = db.cursor()
#使用 execute() 方法执行 SQL 查询
cursor.execute("SELECT * FROM info")
#使用 fetchone() 方法获取单条数据
data = cursor.fetchall()
def Mon():
    for one in data:
        print(one)
    prtime()
Timer(3,Mon).start()
#关闭数据库连接
cursor.close()
db.close()

2.为了解决启动顺序问题重新编写Dockerfile引入wait-for

使用depends_on进行容器排序时并不能完美的解决容器之间的依赖问题，因为 depends_on只能保证容器进入到运行状态而不是完全状态，引入wait-for解决此问题。
Dockerfile文件

datalab@datalabvm12:~/pyDemo$ cat Dockerfile
FROM python:3
WORKDIR /user/src/app

COPY requirements.txt ./
RUN pip install --no-cache-dir -i https://pypi.tuna.tsinghua.edu.cn/simple -r requirements.txt

COPY py_mariadb.py /user/src/app/
VOLUME /user/src/app

RUN apt-get -q update
RUN apt install netcat-traditional -y

COPY wait-for .

3.启动结果

datalab@datalabvm12:~/pyDemo$ docker-compose up &
[1] 1626936
Creating mymariadb ... done
Creating pydocker  ... done
Attaching to mymariadb, pydocker
mymariadb    | 2023-08-18 08:30:31+00:00 [Note] [Entrypoint]: Entrypoint script for MariaDB Server 1:11.0.3+maria~ubu2204 started.
mymariadb    | 2023-08-18 08:30:31+00:00 [Note] [Entrypoint]: Switching to dedicated user 'mysql'
...
mymariadb    | 2023-08-18  8:30:32 3 [Warning] Aborted connection 3 to db: 'unconnected' user: 'unauthenticated' host: '172.20.0.3' (This connection closed normally without authentication)
pydocker     | 2023-08-18 08:30:32
pydocker     | ('tom', 1, 20, 1, 85)
pydocker     | ('苹果', 1, 20, 1, 85)
pydocker     | 2023-08-18 08:30:35
pydocker exited with code 0

【报错】datalab@datalabvm12:~/pyDemo$ ERROR: Version in “./docker-compose.yml” is unsupported. You might be seeing this error because you’re using the wrong Compose file version. Either specify a supported version (e.g “2.2” or “3.3”) and place your service definitions under the services key, or omit the version key and place your service definitions at the root of the file to use version 1.
解决方式：重新下载docker-compose ，修改version=“3”

#删除
sudo rm /usr/local/bin/docker-compose
#下载
$ sudo curl -L "https://github.com/docker/compose/releases/download/v2.2.2/docker-compose-$(uname -s)-$(uname -m)" -o /usr/local/bin/docker-compose
#授权
sudo chmod +x /usr/local/bin/docker-compose
#查看版本
docker-compose version

学习参考

【官网github】mariadb/README.md
Docker学习-Mariabd安装部署
如何在 Ubuntu 20.04 上安装 MariaDB
MariaDB创建，更改，删除数据库命令
Mariadb数据库创建数据表
mysql设置utf8mb4字符编码
【官网】mariadb DOCKER OFFICIAL IMAGE

Ubuntu环境的docker实践——python
Python连接MariaDB数据库 CSDN
Python连接Mariadb数据库 腾讯云
Docker 基本觀念與使用教學：自行建立 Docker 影像檔 requirements.txt的使用
【Python】fetchone()和fetchall()
彻底搞懂Python 中的 import 与 from import
Python3-定时任务四种实现方式

Compose file versions and upgrading
解决Docker-compose中的depends_on顺序问题
wait-for源码
docker-compose.yml.mariadb.example
How to Connect Docker Compose Services To Each Other
Python Env Vars – How to Get an Environment Variable in Python
Compose 中的网络