Overview of Azure Database for MySQL in Azure Government

MySQL Database Tutorial for Beginners to Advanced Part 1

MySQL Database Tutorial for Beginners to Advanced Part 1

Learn about MySQL Database from scratch. Go from zero to hero in MySQL Database with this complete course.

Learn about MySQL Database from scratch. Go from zero to hero in MySQL Database with this complete course.

In this course you will learn about MySQL Database from scratch. We will assume that you are a complete beginner and by the end of the course you will be at advanced level. In this complete course you shall get demonstration of every single topic that we have explained. This course is fully practical without neglecting the theories and basics.



How to Create Docker Image with MySQL Database

How to Create Docker Image with MySQL Database

In this tutorial, you will learn how to create Docker images and using bind mounts to customize MySQL database

In this tutorial, you will learn how to create Docker images and using bind mounts to customize MySQL database

Many **developers **use **Docker **to spin up a local database. This makes it easy to test their code and write data without installing and configuring a lot of tools. In this piece, I’ll explain how you can **customize **your Docker database. While I’ll be explaining the steps for MySQL, it’s the same for PostgreSQL!

Follow along and let’s create a custom MySQL which contains your desired tables and data.

Creating SQL scripts

We will create **SQL **scripts which contain the **SQL **statements we want to execute on our database. Create a directory in which we will work. Also, create a subdirectory where we will store our .sql scripts.

$ mkdir -p ~/my-mysql/sql-scripts
$ cd ~/my-mysql/sql-scripts

I want to customize my **database **with a table called employees. The table needs to contain one row with an employee (first name, last name, department, and email).

Write a CreateTable.sql. This file contains the SQL statement to create a table called employees. We will add four columns to our table

CREATE TABLE employees (
first_name varchar(25),
last_name  varchar(25),
department varchar(15),
email  varchar(50)
);

Write a InsertData.sql. This file contains our statement to insert data in the table ‘employees’.

INSERT INTO employees (first_name, last_name, department, email) 
VALUES ('Lorenz', 'Vanthillo', 'IT', '[email protected]');

Execute the tree command to verify that your two scripts exist and are saved in the right directory.

$ cd ~/my-mysql
$ tree
└── sql-scripts
    ├── CreateTable.sql
    └── InsertData.sql

Creating a Docker Image for Your Customized MySQL Database

Now that the scripts are ready, we can write a Dockerfile to create our own Docker image (based on the official image of MySQL).

$ cd ~/my-mysql/
$ vi Dockerfile

Content of Dockerfile:

# Derived from official mysql image (our base image)
FROM mysql
# Add a database
ENV MYSQL_DATABASE company
# Add the content of the sql-scripts/ directory to your image
# All scripts in docker-entrypoint-initdb.d/ are automatically
# executed during container startup
COPY ./sql-scripts/ /docker-entrypoint-initdb.d/

Create your Docker image:

$ cd ~/my-mysql/
$ docker build -t my-mysql .
Sending build context to Docker daemon  4.608kB
Step 1/2 : FROM mysql
latest: Pulling from library/mysql
Digest: sha256:691c55aabb3c4e3b89b953dd2f022f7ea845e5443954767d321d5f5fa394e28c
Status: Downloaded newer image for mysql:latest
 ---> 5195076672a7
Step 2/2 : ADD sql-scripts/ /docker-entrypoint-initdb.d/
 ---> 25065c3d93c0
Successfully built 25065c3d93c0
Successfully tagged my-mysql:latest

And start your **MySQL **container from the image:

$ docker run -d -p 3306:3306 --name my-mysql \
-e MYSQL_ROOT_PASSWORD=supersecret my-mysql

Now we can verify. We will exec inside the container:

$ docker exec -it my-mysql bash
[email protected]:/# mysql -uroot -p
Enter password: (supersecret)
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| company            |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)
mysql> use company;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables;
+-------------------+
| Tables_in_company |
+-------------------+
| employees         |
+-------------------+
1 row in set (0.00 sec)
mysql> show columns from employees;
+------------+-------------+------+-----+---------+-------+
| Field      | Type        | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| first_name | varchar(25) | YES  |     | NULL    |       |
| last_name  | varchar(25) | YES  |     | NULL    |       |
| department | varchar(15) | YES  |     | NULL    |       |
| email      | varchar(50) | YES  |     | NULL    |       |
+------------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
mysql> select * from employees;
+------------+-----------+------------+-------------------+
| first_name | last_name | department | email             |
+------------+-----------+------------+-------------------+
| Lorenz     | Vanthillo | IT         | [email protected] |
+------------+-----------+------------+-------------------+
1 row in set (0.01 sec)

It works! We have our customized MySQL database Docker image! This is a great solution for local development between multiple developers. By sharing the Docker image, every developer can use the **database **by just starting a container from the image.

It’s important to note, however, that this is not always the best solution:

  • If you insert a lot of data your image size will grow significantly
  • Need to build a new image when you want to update the data

That’s why there is another way to customize your Docker MySQL.

Use Bind Mounts to Customize Your MySQL Database in Docker

In this last section, we will simply mount the scripts inside the official MySQL Docker container.

$ docker run -d -p 3306:3306 --name my-mysql \
-v ~/my-mysql/sql-scripts:/docker-entrypoint-initdb.d/ \
-e MYSQL_ROOT_PASSWORD=supersecret \
-e MYSQL_DATABASE=company \
mysql

And we can verify again! Use the same steps as we did before: exec inside the container and check if the table and data exist!

This method is more flexible but it will be a little bit harder to distribute among the developers. They all need to store the scripts in a certain directory on their local machine and they need to point to that directory when they execute the docker run command.

Conclusion

In this piece, I’ve described how you can create your custom MySQL database in Docker. We’ve explored the two methods:

  • Custom MySQL Docker image
  • Bind mounts to mount scripts inside a container

We also discussed some of the advantages and disadvantages of each method.

We hope you enjoyed it!

Insert File Into MySQL Database In Java

Sometimes we have a requirement to insert file into database rather then storing it in the file system. For a developer, it is always hard to decide this. You can find good discussion in different forums on web over storing the file either in database or file system. I believe choosing the same depends on the project requirement rather than preferences. You can choose database over file system when file size is small and user’s file needs to be more tightly coupled, secured and confidential. Since this discussion is out of scope of this article, here is a good resource,&nbsp;<a href="https://habiletechnologies.com/blog/better-saving-files-database-file-system/" target="_blank">Which is Better ? Saving Files in Database or File System</a>&nbsp;for your reference.

Introduction

Sometimes we have a requirement to insert file into database rather then storing it in the file system. For a developer, it is always hard to decide this. You can find good discussion in different forums on web over storing the file either in database or file system. I believe choosing the same depends on the project requirement rather than preferences. You can choose database over file system when file size is small and user’s file needs to be more tightly coupled, secured and confidential. Since this discussion is out of scope of this article, here is a good resource, Which is Better ? Saving Files in Database or File System for your reference.

Well, today we will see how to insert image file into MySQL database using JDBC. We will use BLOB datatype of MySQL for storing files....

( Read More )