1594786786
If you’re a database administrator, data scientist, or developer, you may have to use MySQL to store and manipulate data. Since Docker has become a popular DevOps tool in recent years, you will eventually need to know how to run MySQL inside a Docker container.
Running your application on a different machine can be nerve-wracking because of environment management. Docker solves this problem by using its containerization technology, which uses Docker images. If you are new to Docker or want to brush your skills, read the following post where I’ve covered some basics.
Once you’ve finished this tutorial, you’ll be on your way to testing any MySQL query using Docker. This method will save you time and effort since you don’t have to install or configure MySQL.
#mysql #data-science #docker #devops
1595905879
HTML to Markdown
MySQL is the all-time number one open source database in the world, and a staple in RDBMS space. DigitalOcean is quickly building its reputation as the developers cloud by providing an affordable, flexible and easy to use cloud platform for developers to work with. MySQL on DigitalOcean is a natural fit, but what’s the best way to deploy your cloud database? In this post, we are going to compare the top two providers, DigitalOcean Managed Databases for MySQL vs. ScaleGrid MySQL hosting on DigitalOcean.
At a glance – TLDR
ScaleGrid Blog - At a glance overview - 1st pointCompare Throughput
ScaleGrid averages almost 40% higher throughput over DigitalOcean for MySQL, with up to 46% higher throughput in write-intensive workloads. Read now
ScaleGrid Blog - At a glance overview - 2nd pointCompare Latency
On average, ScaleGrid achieves almost 30% lower latency over DigitalOcean for the same deployment configurations. Read now
ScaleGrid Blog - At a glance overview - 3rd pointCompare Pricing
ScaleGrid provides 30% more storage on average vs. DigitalOcean for MySQL at the same affordable price. Read now
MySQL DigitalOcean Performance Benchmark
In this benchmark, we compare equivalent plan sizes between ScaleGrid MySQL on DigitalOcean and DigitalOcean Managed Databases for MySQL. We are going to use a common, popular plan size using the below configurations for this performance benchmark:
Comparison Overview
ScaleGridDigitalOceanInstance TypeMedium: 4 vCPUsMedium: 4 vCPUsMySQL Version8.0.208.0.20RAM8GB8GBSSD140GB115GBDeployment TypeStandaloneStandaloneRegionSF03SF03SupportIncludedBusiness-level support included with account sizes over $500/monthMonthly Price$120$120
As you can see above, ScaleGrid and DigitalOcean offer the same plan configurations across this plan size, apart from SSD where ScaleGrid provides over 20% more storage for the same price.
To ensure the most accurate results in our performance tests, we run the benchmark four times for each comparison to find the average performance across throughput and latency over read-intensive workloads, balanced workloads, and write-intensive workloads.
Throughput
In this benchmark, we measure MySQL throughput in terms of queries per second (QPS) to measure our query efficiency. To quickly summarize the results, we display read-intensive, write-intensive and balanced workload averages below for 150 threads for ScaleGrid vs. DigitalOcean MySQL:
ScaleGrid MySQL vs DigitalOcean Managed Databases - Throughput Performance Graph
For the common 150 thread comparison, ScaleGrid averages almost 40% higher throughput over DigitalOcean for MySQL, with up to 46% higher throughput in write-intensive workloads.
#cloud #database #developer #digital ocean #mysql #performance #scalegrid #95th percentile latency #balanced workloads #developers cloud #digitalocean droplet #digitalocean managed databases #digitalocean performance #digitalocean pricing #higher throughput #latency benchmark #lower latency #mysql benchmark setup #mysql client threads #mysql configuration #mysql digitalocean #mysql latency #mysql on digitalocean #mysql throughput #performance benchmark #queries per second #read-intensive #scalegrid mysql #scalegrid vs. digitalocean #throughput benchmark #write-intensive
1650506892
In this tutorial, we will detail how to use MySQL with Docker and docker-compose step-by-step keeping things easy to comprehend.
For this beginners’ guide, we will use the official MySQL Docker image from DockerHub. The official MySQL Docker image does not have an Alpine Linux version, still, the Debian version is also 147 MB which is not too big for a docker image.
To run the MySQL 8.0 container using the official image, simply run the following command:
mkdir /tmp/mysql-data
docker run --name basic-mysql --rm -v /tmp/mysql-data:/var/lib/mysql -e MYSQL_ROOT_PASSWORD=ANSKk08aPEDbFjDO -e MYSQL_DATABASE=testing -p 3306:3306 -it mysql:8.0
Let’s analyze the command we just ran to run MySQL with docker. First, we created a directory called mysql-data
in /tmp
to store the data of MySQL. Then we ran a docker run
command with:
--name
to name the container basic-mysql
--rn
to remove the container when it is stopped-v /tmp/mysql-data:/var/lib/mysql
is added to retain the data when the container restarts, it will vanish when the host machine restarts as it is in /tmp
-e MYSQL_ROOT_PASSWORD=ANSKk08aPEDbFjDO -e MYSQL_DATABASE=testing
for setting the root user’s password and initializing a database named testing
-p 3306:3306
maps host port 3306 to container port 3306, the port 3306 is MySQL’s default port.-it mysql:8.0
- -it will show all logs and we are using the official MySQL image version 8.0 which will run the Debian flavor.It will show and output simialr to below:
2022-02-19 10:31:54+00:00 [Note] [Entrypoint]: Entrypoint script for MySQL Server 8.0.27-1debian10 started.
2022-02-19 10:31:54+00:00 [Note] [Entrypoint]: Switching to dedicated user 'mysql'
2022-02-19 10:31:54+00:00 [Note] [Entrypoint]: Entrypoint script for MySQL Server 8.0.27-1debian10 started.
2022-02-19 10:31:54+00:00 [Note] [Entrypoint]: Initializing database files
2022-02-19T10:31:54.932929Z 0 [System] [MY-013169] [Server] /usr/sbin/mysqld (mysqld 8.0.27) initializing of server in progress as process 44
2022-02-19T10:31:54.942400Z 0 [Warning] [MY-010159] [Server] Setting lower_case_table_names=2 because file system for /var/lib/mysql/ is case insensitive
2022-02-19T10:31:54.952407Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2022-02-19T10:31:57.114688Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2022-02-19T10:31:58.994205Z 0 [Warning] [MY-013746] [Server] A deprecated TLS version TLSv1 is enabled for channel mysql_main
2022-02-19T10:31:58.994241Z 0 [Warning] [MY-013746] [Server] A deprecated TLS version TLSv1.1 is enabled for channel mysql_main
2022-02-19T10:31:59.204723Z 6 [Warning] [MY-010453] [Server] root@localhost is created with an empty password ! Please consider switching off the --initialize-insecure option.
2022-02-19 10:32:03+00:00 [Note] [Entrypoint]: Database files initialized
2022-02-19 10:32:03+00:00 [Note] [Entrypoint]: Starting temporary server
mysqld will log errors to /var/lib/mysql/568272f57c6b.err
mysqld is running as pid 95
2022-02-19 10:32:04+00:00 [Note] [Entrypoint]: Temporary server started.
Warning: Unable to load '/usr/share/zoneinfo/iso3166.tab' as time zone. Skipping it.
Warning: Unable to load '/usr/share/zoneinfo/leap-seconds.list' as time zone. Skipping it.
Warning: Unable to load '/usr/share/zoneinfo/zone.tab' as time zone. Skipping it.
Warning: Unable to load '/usr/share/zoneinfo/zone1970.tab' as time zone. Skipping it.
2022-02-19 10:32:08+00:00 [Note] [Entrypoint]: Creating database testing
2022-02-19 10:32:08+00:00 [Note] [Entrypoint]: Stopping temporary server
2022-02-19 10:32:10+00:00 [Note] [Entrypoint]: Temporary server stopped
2022-02-19 10:32:10+00:00 [Note] [Entrypoint]: MySQL init process done. Ready for start up.
2022-02-19T10:32:10.353185Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.27) starting as process 1
2022-02-19T10:32:10.361054Z 0 [Warning] [MY-010159] [Server] Setting lower_case_table_names=2 because file system for /var/lib/mysql/ is case insensitive
2022-02-19T10:32:10.379917Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2022-02-19T10:32:11.007492Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2022-02-19T10:32:11.362057Z 0 [Warning] [MY-013746] [Server] A deprecated TLS version TLSv1 is enabled for channel mysql_main
2022-02-19T10:32:11.362123Z 0 [Warning] [MY-013746] [Server] A deprecated TLS version TLSv1.1 is enabled for channel mysql_main
2022-02-19T10:32:11.366535Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2022-02-19T10:32:11.366644Z 0 [System] [MY-013602] [Server] Channel mysql_main configured to support TLS. Encrypted connections are now supported for this channel.
2022-02-19T10:32:11.372769Z 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.
2022-02-19T10:32:11.435797Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: '::' port: 33060, socket: /var/run/mysqld/mysqlx.sock
2022-02-19T10:32:11.435995Z 0 [System] [MY-010931] [Server] /usr/sbin/mysqld: ready for connections. Version: '8.0.27' socket: '/var/run/mysqld/mysqld.sock' port: 3306 MySQL Community Server - GPL.
As the MySQL server is running we can execute the MySQL command inside the container with:
docker exec -it basic-mysql /bin/bash
#once inside the container
mysql -uroot -p
#put/paste the password, and once inside MySQL CLI run
show databases;
It will look like the below:
We can stop the container with docker stop basic-mysql
.
Even though that wasn’t too hard I would not consider it easy as well as the command’s parameters were not simple to remember. Another aspect is we ran MySQL in isolation, there is no link between the Quotes API Node.js application and the MySQL container. This is where the declarative nature of docker-compose comes in very handy as we will see in the next section.
To run the same MySQL 8.0 with docker-compose we will create a new docker-compose-mysql-only.yml file, with the following contents:
version: '3.8'
services:
db:
image: mysql:8.0
cap_add:
- SYS_NICE
restart: always
environment:
- MYSQL_DATABASE=quotes
- MYSQL_ROOT_PASSWORD=mauFJcuf5dhRMQrjj
ports:
- '3306:3306'
volumes:
- db:/var/lib/mysql
- ./db/init.sql:/docker-entrypoint-initdb.d/init.sql
volumes:
db:
driver: local
The above docker-compose file has the following things to comprehend:
db
as a service, each service will be equivalent to a new docker run commandcap_add
set to SYS_NICE
suppresses some not useful error messages.3306
to the container port 3306
as the MySQL server is running on container port 3306
. Depending on the preference the host port may be changed.db
. Which basically tells docker and docker-compose to manage the volume for us. Next, we add an init.sql
script which will initialize our quotes
database with the given SQL file.We can start the MySQL container this time with docker-compose using:
docker-compose -f docker-compose-mysql-only.yml up
It will show an output like below:
Starting nodejs-mysql_db_1 ... done
Attaching to nodejs-mysql_db_1
db_1 | 2022-02-19 10:55:55+00:00 [Note] [Entrypoint]: Entrypoint script for MySQL Server 8.0.27-1debian10 started.
db_1 | 2022-02-19 10:55:56+00:00 [Note] [Entrypoint]: Switching to dedicated user 'mysql'
db_1 | 2022-02-19 10:55:56+00:00 [Note] [Entrypoint]: Entrypoint script for MySQL Server 8.0.27-1debian10 started.
db_1 | 2022-02-19T10:55:56.381394Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.27) starting as process 1
db_1 | 2022-02-19T10:55:56.392419Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
db_1 | 2022-02-19T10:55:56.878693Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
db_1 | 2022-02-19T10:55:57.258522Z 0 [System] [MY-010229] [Server] Starting XA crash recovery...
db_1 | 2022-02-19T10:55:57.268530Z 0 [System] [MY-010232] [Server] XA crash recovery finished.
db_1 | 2022-02-19T10:55:57.305749Z 0 [Warning] [MY-013746] [Server] A deprecated TLS version TLSv1 is enabled for channel mysql_main
db_1 | 2022-02-19T10:55:57.305945Z 0 [Warning] [MY-013746] [Server] A deprecated TLS version TLSv1.1 is enabled for channel mysql_main
db_1 | 2022-02-19T10:55:57.309232Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
db_1 | 2022-02-19T10:55:57.309330Z 0 [System] [MY-013602] [Server] Channel mysql_main configured to support TLS. Encrypted connections are now supported for this channel.
db_1 | 2022-02-19T10:55:57.313177Z 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 | 2022-02-19T10:55:57.374334Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: '::' port: 33060, socket: /var/run/mysqld/mysqlx.sock
db_1 | 2022-02-19T10:55:57.374405Z 0 [System] [MY-010931] [Server] /usr/sbin/mysqld: ready for connections. Version: '8.0.27' socket: '/var/run/mysqld/mysqld.sock' port: 3306 MySQL Community Server - GPL.
The output is similar but this time being declarative with docker-compose, running MySQL inside a docker container was a much easier command.
For your reference, the PR to add this file is also available. Next up we will link up the MySQL container with our existing Node.js app.
At this point, we have run MySQL with just docker and then with docker-compose which was much easier than the previous long command. Still, one piece of the puzzle is missing, the piece to link the MySQL container with an application. In our case, it will be the Node.js MySQL API for Quotes.
For this beginner’s tutorial we will add a new docker-compose file
with the following contents:
version: '3.8'
services:
db:
image: mysql:8.0
cap_add:
- SYS_NICE
restart: always
environment:
- MYSQL_DATABASE=quotes
- MYSQL_ROOT_PASSWORD=mauFJcuf5dhRMQrjj
ports:
- '3306:3306'
volumes:
- db:/var/lib/mysql
- ./db/init.sql:/docker-entrypoint-initdb.d/init.sql
api:
container_name: quotes-api
build:
context: ./
target: production
image: quotes-api
depends_on:
- db
ports:
- 3000:3000
environment:
NODE_ENV: production
DB_HOST: db
DB_PORT: 3306
DB_USER: root
DB_PASSWORD: mauFJcuf5dhRMQrjj
DB_NAME: quotes
links:
- db
volumes:
- ./:/src
volumes:
db:
driver: local
This docker-compose.yml file looks like a longer version of the above file. The main differences include:
Adding the API service which uses the Dockerfile
included in the project. We will build the API
service with the target production. As per your interest, you can read more about docker multi-stage builds which explains the use of build target.
Next up, we indicate to docker-compose that the API depends on the DB container. After that, we map the host port 3000
to the container port 3000
as the Express.js app with Node.js runs on port 3000
.
Subsequently, we set all the needed environment variables to that the Node.js app connects to to the MySQL database running inside the container without problems. After that, we map the local file in ./
to ./src
in the container so that all the file changes are reflected inside the container. Next, we link both containers so that the API container can communicate with the DB container.
This docker-compose.yml
file is available as a pull request for your reference.
When we run the application with docker-compose up
we will see output like the below:
Starting nodejs-mysql_db_1 ... done
Starting quotes-api ... done
Attaching to nodejs-mysql_db_1, quotes-api
db_1 | 2022-02-19 11:08:36+00:00 [Note] [Entrypoint]: Entrypoint script for MySQL Server 8.0.27-1debian10 started.
db_1 | 2022-02-19 11:08:36+00:00 [Note] [Entrypoint]: Switching to dedicated user 'mysql'
db_1 | 2022-02-19 11:08:36+00:00 [Note] [Entrypoint]: Entrypoint script for MySQL Server 8.0.27-1debian10 started.
db_1 | 2022-02-19T11:08:36.469670Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.27) starting as process 1
db_1 | 2022-02-19T11:08:36.478201Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
db_1 | 2022-02-19T11:08:36.830802Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
db_1 | 2022-02-19T11:08:37.004513Z 0 [System] [MY-010229] [Server] Starting XA crash recovery...
db_1 | 2022-02-19T11:08:37.015831Z 0 [System] [MY-010232] [Server] XA crash recovery finished.
db_1 | 2022-02-19T11:08:37.063455Z 0 [Warning] [MY-013746] [Server] A deprecated TLS version TLSv1 is enabled for channel mysql_main
db_1 | 2022-02-19T11:08:37.063521Z 0 [Warning] [MY-013746] [Server] A deprecated TLS version TLSv1.1 is enabled for channel mysql_main
db_1 | 2022-02-19T11:08:37.064770Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
db_1 | 2022-02-19T11:08:37.064845Z 0 [System] [MY-013602] [Server] Channel mysql_main configured to support TLS. Encrypted connections are now supported for this channel.
db_1 | 2022-02-19T11:08:37.068935Z 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 | 2022-02-19T11:08:37.109788Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: '::' port: 33060, socket: /var/run/mysqld/mysqlx.sock
db_1 | 2022-02-19T11:08:37.109836Z 0 [System] [MY-010931] [Server] /usr/sbin/mysqld: ready for connections. Version: '8.0.27' socket: '/var/run/mysqld/mysqld.sock' port: 3306 MySQL Community Server - GPL.
quotes-api |
quotes-api | > nodejs-mysql@0.0.0 start /src
quotes-api | > node ./bin/www
quotes-api |
If we hit the browser with http://localhost:3000/quotes
we can see something like the following:
Great! Our Node.js Express Quotes API is communicating properly with the local MySQL running inside the docker container and connected with docker-compose. As the data was put from the init.sql
script the quotes are available in the API response.
In this guide, we saw how to run MySQL with docker run, then docker-compose, and finally linked up with a Node.js application step-by-step.
This video is a on mysql tutorial and how to run mysql in docker. You can easily spin up my sql docker containers and start working on the databases. Check out the whole commands listed below.
docker container run -d --name mysqldb -p 3306:3306 -e MYSQL_ROOT_PASSWORD=password mysql:latest
docker exec -it mysqldb bash
mysql -u root -ppassword
show databases;
create database company;
drop database company;
use company;
create table team ( id int not null, name text, primary key (id) );
show tables;
insert into team (id , name) values (1, 'Thetips4you');
insert into team (id , name) values (2, 'King');
insert into team (id , name) values (3, 'Queen');
select * from team;
1595249460
Following the second video about Docker basics, in this video, I explain Docker architecture and explain the different building blocks of the docker engine; docker client, API, Docker Daemon. I also explain what a docker registry is and I finish the video with a demo explaining and illustrating how to use Docker hub
In this video lesson you will learn:
#docker #docker hub #docker host #docker engine #docker architecture #api
1598259180
We can create a container and run it using Dockerfile. We can even run multiple container in separate ports using two Dockefiles in multiple terminals.
But when you want to create more than one container for your application, you have to create several Docker files. This adds on the load of maintaining them and is also quite time-consuming.
This blog is continuation with my previous blogwhere i showed** How to Install WordPress on Docker using MySQL Backend**
This problem is solved by **Docker Compose. **Docker compose is a tool which is used for multi-container applications in a single host.
For example in my previous blog I have to run two container : first wordpress container and second mysql container as backend.
We can run multi containers as services in the single host with the help of docker-compose.yaml.
Docker Swarm extends the concept of manage multiple containers deployed across multiple node docker cluster.
curl -L "https://github.com/docker/compose/releases/download/1.26.2/docker-compose-$(uname -s)-$(uname -m)" -o /usr/local/bin/docker-compose
#docker-compose #containers #docker #mysql #wordpress
1626863940
In this video you will learn how to Dockerize spring boot with MySQL application and Dockerize a Spring Boot with MySQL using docker-compose in Ubuntu machine.
Git Hub Link: https://github.com/shameed1910/springboot-mysql-docker.git
Previous video link: https://youtu.be/EZolJ4lNiYc
#mysql #docker #docker compose #spring boot