Slides

Setting up a MySQL database with Podman

These instructions will allow you to create a simple MySQL database using Podman. This is a simple example and should not be used in a production environment but will work for learning purposes.

First we need to pull down the latest MySQL image.

podman pull mysql

create a new volume and database only need to do this once

Now we are going to generate a basic server which has a persistent volume for the database. This will allow us to stop and start the container without losing the data.

podman run -d \
    --replace \
    --security-opt label=disable \
    --name mysql_container \
    -e MYSQL_ROOT_PASSWORD=rootpass \
    -e MYSQL_DATABASE=mydb \
    -e MYSQL_USER=jmacey \
    -e MYSQL_PASSWORD=jmacey \
    -p 3306:3306 \
    -v mysql_data:/transfer/mysql \
    mysql:latest

Note the replace flag. This will remove the container if it already exists so we can start fresh. --security-opt label=disable stop SELinux from blocking the container (which will happe in the labs)

The MSQL flags are container specific and setup some basic information for the database you can change the username and password as needed.

The -v mysql_data:/transfer/mysql flag creates a volume called mysql_data which is mounted in the container at /transfer/mysql. This is where the database will be stored.

The -p 3306:3306 flag maps the container port 3306 to the host port 3306. This is the default MySQL port and is used to connect to the database and is part of the free ports we have available.

Initial setup

We can now start the container and connect to the database.

This start the server, we can then connect to the database using the mysql command line tool.

podman start mysql_container 

podman exec -it mysql_container mysql -u root -p

You will be prompted for the root password which is rootpass for this example.

We are going to let jmacey user create databases and have full (dangerous) access to all databases. But this is for learning purposes only (use you own username if you changed above) This example is based on the exercise here.

GRANT ALL ON *.* TO 'jmacey'@'%';
FLUSH PRIVILEGES;

Create a database

We can now logout and log back in as the normal user

podman exec -it mysql_container mysql -u jmacey -p

create database computer_store;
use computer_store;

CREATE TABLE Manufacturers (
                            Code INTEGER PRIMARY KEY NOT NULL,
                            Name TEXT NOT NULL
                            );
CREATE TABLE Products ( Code INTEGER PRIMARY KEY NOT NULL,         
                        Name TEXT NOT NULL ,         
                        Price REAL NOT NULL ,         
                        Manufacturer INTEGER NOT NULL                
                        );

Now insert some toy data

INSERT INTO Manufacturers(Code,Name) VALUES(1,'Sony');
INSERT INTO Manufacturers(Code,Name) VALUES(2,'Creative Labs');
INSERT INTO Manufacturers(Code,Name) VALUES(3,'Hewlett-Packard');
INSERT INTO Manufacturers(Code,Name) VALUES(4,'Iomega');
INSERT INTO Manufacturers(Code,Name) VALUES(5,'Fujitsu');
INSERT INTO Manufacturers(Code,Name) VALUES(6,'Winchester');

INSERT INTO Products(Code,Name,Price,Manufacturer) VALUES(1,'Hard drive',240,5);
INSERT INTO Products(Code,Name,Price,Manufacturer) VALUES(2,'Memory',120,6);
INSERT INTO Products(Code,Name,Price,Manufacturer) VALUES(3,'ZIP drive',150,4);
INSERT INTO Products(Code,Name,Price,Manufacturer) VALUES(4,'Floppy disk',5,6);
INSERT INTO Products(Code,Name,Price,Manufacturer) VALUES(5,'Monitor',240,1);
INSERT INTO Products(Code,Name,Price,Manufacturer) VALUES(6,'DVD drive',180,2);
INSERT INTO Products(Code,Name,Price,Manufacturer) VALUES(7,'CD drive',90,2);
INSERT INTO Products(Code,Name,Price,Manufacturer) VALUES(8,'Printer',270,3);
INSERT INTO Products(Code,Name,Price,Manufacturer) VALUES(9,'Toner cartridge',66,3);
INSERT INTO Products(Code,Name,Price,Manufacturer) VALUES(10,'DVD burner',180,2);

The rest of the example are in the lecture notes.

Once finished you can stop the container

podman stop mysql_container