How can I get pre-existing data from a mysql database into a mysql docker container?

irishcomputing
July 11, 2024
210 views
0 votes
2 Answers

I am new to using Docker and I want to place an existing project in a set of Docker containers so I can run the project on other platforms/devices. I currently have a Flask app connected to a mysql database. The database lives locally on my computer. Within the Flask app I have scheduling portion, so I dont want to have to leave my computer on all the time hence why I thought to use Docker! I have Docker set up with 2 containers (one for the database and one named “server” for the python code).

My issues lie within my mysql database. I want to be able to make queries from information I already populated in the database, but I also want to be able to update the database. I am not sure if it makes sense to send all of the database information into the Docker container or if I can just use the container as a way to connect to the database and its contents on my computer. Right now, Docker can log into my mysql account but Docker tries to create a new database in the same name (“callassure”) instead of connecting to the existing database. (I also tried just connecting to my mysql account without a specific database but then docker can’t find my database either!)  When I look online for help, I am greeted by a lot of hyper specific Docker jargon I don’t understand. It seems like there are blind mounts for connecting to local data, volumes for uploading data to the Docker cloud and something called a network that might be useful.

If someone could help me with getting this set up, and also help me explain how the containers connect to one another and what the different Docker data storage solutions do I would appreciate it. I am more than happy to provide more info as needed.   My code is below…

here is my compose.yaml file:

services:
  server:
    build:
      context: .
    ports:
      - 8000:8000
    environment:
      - SQL_PASS=/run/secrets/db-password
      - MYSQL_ROOT_PASSWORD=/run/secrets/db-password
      - TWILIO_ACCOUNT_SID=/run/secrets/twilio-sid
      - TWILIO_AUTH_TOKEN=/run/secrets/twilio-token
    depends_on:
      db:
        condition: service_healthy
    secrets:
      - db-password
      - twilio-sid
      - twilio-token

  db:
    # We use a mariadb image which supports both amd64 & arm64 architecture
    #image: mariadb:10-focal
    # If you really want to use MySQL, uncomment the following line
    image: mysql:8
    restart: always
    healthcheck:
      test: ['CMD-SHELL', 'mysqladmin ping -h 127.0.0.1 --password="$$(cat /run/secrets/db-password)" --silent']
      interval: 3s
      retries: 5
      start_period: 30s
    secrets:
      - db-password
    volumes:
      - db-data:/usr/local/mysql/data/callassure
    environment:
      - MYSQL_DATABASE=callassure
      - MYSQL_USER=mysql
      - MYSQL_ROOT_PASSWORD=/run/secrets/db-password
      - MYSQL_PASSWORD=/run/secrets/db-password
    expose:
      - 3306
      - 33060

volumes:
  db-data:

secrets:
  db-password:
    file: db/password.txt
  twilio-sid:
    file: db/sid.txt
  twilio-token:
    file: db/auth.txt

and this is the flask to mysql connection if that helps:  

from flask import Flask
from flask_mysqldb import MySQL
import os

### FLASK SETUP ###
app = Flask(__name__)

### SQL SETUP ###
app.config['MYSQL_HOST'] = 'db'
app.config['MYSQL_USER'] = 'mysql'
app.config['MYSQL_PASSWORD'] = os.getenv('SQL_PASS')
app.config['MYSQL_DB'] = 'callassure'

mysql = MySQL(app)

### FUNCTIONS ###
def get_connection():
    return mysql

def get_app():
    return app

here is my error output: 

[+] Running 4/3
 ✔ Network callassure_default     Created                                                                                      0.0s 
 ✔ Volume "callassure_db-data"    Created                                                                                      0.0s 
 ✔ Container callassure-db-1      Created                                                                                      0.1s 
 ✔ Container callassure-server-1  Created                                                                                      0.0s 
Attaching to db-1, server-1
db-1      | 2024-07-10 22:01:28+00:00 [Note] [Entrypoint]: Entrypoint script for MySQL Server 8.4.1-1.el9 started.
db-1      | 2024-07-10 22:01:28+00:00 [Note] [Entrypoint]: Switching to dedicated user 'mysql'
db-1      | 2024-07-10 22:01:29+00:00 [Note] [Entrypoint]: Entrypoint script for MySQL Server 8.4.1-1.el9 started.
db-1      | 2024-07-10 22:01:29+00:00 [Note] [Entrypoint]: Initializing database files
db-1      | 2024-07-10T22:01:29.130148Z 0 [System] [MY-015017] [Server] MySQL Server Initialization - start.
db-1      | 2024-07-10T22:01:29.130853Z 0 [System] [MY-013169] [Server] /usr/sbin/mysqld (mysqld 8.4.1) initializing of server in progress as process 80
db-1      | 2024-07-10T22:01:29.134880Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
db-1      | 2024-07-10T22:01:29.287973Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
db-1      | 2024-07-10T22:01:30.268484Z 6 [Warning] [MY-010453] [Server] root@localhost is created with an empty password ! Please consider switching off the --initialize-insecure option.
db-1      | 2024-07-10T22:01:31.960997Z 0 [System] [MY-015018] [Server] MySQL Server Initialization - end.
db-1      | 2024-07-10 22:01:31+00:00 [Note] [Entrypoint]: Database files initialized
db-1      | 2024-07-10 22:01:31+00:00 [Note] [Entrypoint]: Starting temporary server
db-1      | 2024-07-10T22:01:31.991393Z 0 [System] [MY-015015] [Server] MySQL Server - start.
db-1      | 2024-07-10T22:01:32.144616Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.4.1) starting as process 128
db-1      | 2024-07-10T22:01:32.151372Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
db-1      | 2024-07-10T22:01:32.297599Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
db-1      | 2024-07-10T22:01:32.455855Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
db-1      | 2024-07-10T22:01:32.455881Z 0 [System] [MY-013602] [Server] Channel mysql_main configured to support TLS. Encrypted connections are now supported for this channel.
db-1      | 2024-07-10T22:01:32.458363Z 0 [Warning] [MY-011810] [Server] Insecure configuration for --pid-file: Location '/var/run/mysqld' in the path is accessible to all OS users. Consider choosing a different directory.
db-1      | 2024-07-10T22:01:32.468369Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Socket: /var/run/mysqld/mysqlx.sock
db-1      | 2024-07-10T22:01:32.468431Z 0 [System] [MY-010931] [Server] /usr/sbin/mysqld: ready for connections. Version: '8.4.1'  socket: '/var/run/mysqld/mysqld.sock'  port: 0  MySQL Community Server - GPL.
db-1      | 2024-07-10 22:01:32+00:00 [Note] [Entrypoint]: Temporary server started.
db-1      | '/var/lib/mysql/mysql.sock' -> '/var/run/mysqld/mysqld.sock'
db-1      | Warning: Unable to load '/usr/share/zoneinfo/iso3166.tab' as time zone. Skipping it.
db-1      | Warning: Unable to load '/usr/share/zoneinfo/leap-seconds.list' as time zone. Skipping it.
db-1      | Warning: Unable to load '/usr/share/zoneinfo/leapseconds' as time zone. Skipping it.
db-1      | Warning: Unable to load '/usr/share/zoneinfo/tzdata.zi' as time zone. Skipping it.
db-1      | Warning: Unable to load '/usr/share/zoneinfo/zone.tab' as time zone. Skipping it.
db-1      | Warning: Unable to load '/usr/share/zoneinfo/zone1970.tab' as time zone. Skipping it.
db-1      | 2024-07-10 22:01:33+00:00 [Note] [Entrypoint]: Creating database callassure
db-1      | 2024-07-10 22:01:33+00:00 [Note] [Entrypoint]: Creating user mysql
db-1      | 2024-07-10 22:01:33+00:00 [Note] [Entrypoint]: Giving user mysql access to schema callassure
db-1      | 
db-1      | 2024-07-10 22:01:33+00:00 [Note] [Entrypoint]: Stopping temporary server
db-1      | 2024-07-10T22:01:33.430010Z 13 [System] [MY-013172] [Server] Received SHUTDOWN from user root. Shutting down mysqld (Version: 8.4.1).
db-1      | 2024-07-10T22:01:34.908924Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.4.1)  MySQL Community Server - GPL.
db-1      | 2024-07-10T22:01:34.908981Z 0 [System] [MY-015016] [Server] MySQL Server - end.
db-1      | 2024-07-10 22:01:35+00:00 [Note] [Entrypoint]: Temporary server stopped
db-1      | 
db-1      | 2024-07-10 22:01:35+00:00 [Note] [Entrypoint]: MySQL init process done. Ready for start up.
db-1      | 
db-1      | 2024-07-10T22:01:35.463730Z 0 [System] [MY-015015] [Server] MySQL Server - start.
db-1      | 2024-07-10T22:01:35.608246Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.4.1) starting as process 1
db-1      | 2024-07-10T22:01:35.611361Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
db-1      | 2024-07-10T22:01:35.699591Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
db-1      | 2024-07-10T22:01:35.835753Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
db-1      | 2024-07-10T22:01:35.835776Z 0 [System] [MY-013602] [Server] Channel mysql_main configured to support TLS. Encrypted connections are now supported for this channel.
db-1      | 2024-07-10T22:01:35.838301Z 0 [Warning] [MY-011810] [Server] Insecure configuration for --pid-file: Location '/var/run/mysqld' in the path is accessible to all OS users. Consider choosing a different directory.
db-1      | 2024-07-10T22:01:35.849491Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: '::' port: 33060, socket: /var/run/mysqld/mysqlx.sock
db-1      | 2024-07-10T22:01:35.849532Z 0 [System] [MY-010931] [Server] /usr/sbin/mysqld: ready for connections. Version: '8.4.1'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306  MySQL Community Server - GPL.
server-1  | INFO:apscheduler.scheduler:Scheduler started
server-1  | Traceback (most recent call last):
server-1  |   File "/app/app.py", line 32, in <module>
server-1  |     schedule_checkins()
server-1  |   File "/app/schedule_verify.py", line 37, in schedule_checkins
server-1  |     times=fetch_times(today)
server-1  |   File "/app/schedule_verify.py", line 26, in fetch_times
server-1  |     cursor.execute(query, (today,))
server-1  |   File "/usr/local/lib/python3.9/site-packages/MySQLdb/cursors.py", line 179, in execute
server-1  |     res = self._query(mogrified_query)
server-1  |   File "/usr/local/lib/python3.9/site-packages/MySQLdb/cursors.py", line 330, in _query
server-1  |     db.query(q)
server-1  |   File "/usr/local/lib/python3.9/site-packages/MySQLdb/connections.py", line 261, in query
server-1  |     _mysql.connection.query(self, query)
server-1  | MySQLdb.ProgrammingError: (1146, "Table 'callassure.checkin_schedule' doesn't exist")
server-1 exited with code 1

Answers

- istepaniuk
- July 11, 2024 at 12:56 am
- 0 votes
0
From the documentation of the image you are using, you can see under "Initializing a fresh instance":

… will execute files with extensions .sh, .sql and .sql.gz that are
found in /docker-entrypoint-initdb.d. Files will be executed in
alphabetical order. You can easily populate your mysql services by
mounting a SQL dump into that directory⁠

It suggests that you use a bind mount to provide the initial SQL files to the container when it first starts.

You can easily accomplish that by adding a volume to your docker compose db service, for example (rest of the YAML removed for clarity):
```
  db:
    image: mysql:8
    ...
    volumes:
      - db-data:/usr/local/mysql/data/callassure
      - ./initial-data:/docker-entrypoint-initdb.d
    ...
```
Where initial-data is a local folder containing files with a .sql extension which initialize whatever data you want. (CREATE DATABASE callassure;, CREATE TABLE ..., INSERT INTO ..., etc.)

The container will do this only the first time it runs, i.e. when the db-data named volume does not exist yet. Further modification to the database will be preserved between restarts until you delete the db-data volume. The official documentation has good examples over volume types and options.

To understand how networking works in docker you can take a look at the official documentation, but in short, docker compose creates a docker network which allows each container (the docker compose services) to see each other in their own private network, and resolve each other’s IP address through their service name (e.g. db or server in your case).
Login or Signup to reply.

- Adedamola
- July 11, 2024 at 3:18 pm
- 0 votes
0
To use your existing MySQL database with Docker, first export your database using mysqldump. Use Docker volumes to persist your database data, avoiding direct mounting of your local data directory.

When configuring Docker Compose, it will automatically set up a network allowing your containers to communicate using service names.Finally, verify the setup by checking the logs and testing your app to ensure it connects to the database correctly.

Login or Signup to reply.

Please signup or login to give your own answer.

Click here to cancel reply.