Eleo Nona

Eleo Nona

1594786786

How to Run MySQL using Docker

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

What is GEEK

Buddha Community

How to Run MySQL using Docker
Joe  Hoppe

Joe Hoppe

1595905879

Best MySQL DigitalOcean Performance – ScaleGrid vs. DigitalOcean Managed Databases

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

Dylan North

Dylan North

1650506892

Run MySQL with Docker | How to Use MySQL with Docker & Docker-Compose

How to Use MySQL with Docker & Docker-Compose

In this tutorial, we will detail how to use MySQL with Docker and docker-compose step-by-step keeping things easy to comprehend. 

Run MySQL with Docker

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:

Use MySQL with Docker going inside the container

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.

Running MySQL with docker-compose

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:

  1. It uses the docker-compose file version 3.8, one of the latest ones.
  2. Consequently, we definedb as a service, each service will be equivalent to a new docker run command
  3. Subsequently, we point docker-compose to use the official MySQL 8.0 image for this DB service.
  4. The cap_add set to SYS_NICE suppresses some not useful error messages.
  5. Next up we ask docker-compose to always restart this container if it fails.
  6. After that we add 2 environment variables for the MySQL database and the root user’s password. As per need, we can add another user to give it less privilege with other env vars.
  7. Then, we map the host port 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.
  8. Then we add 2 volumes, first one is the DB volume mapped to the below volumes 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.

Adding MySQL to and existing Node.js app using docker-compose

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:

Use MySQL with Docker with an existing Node.js application

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.

MySQL Docker Tutorial | MySQL Docker Setup | How To Run MySQL In Docker Container with Video Tutorial


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;

#docker #mysql 

Iliana  Welch

Iliana Welch

1595249460

Docker Explained: Docker Architecture | Docker Registries

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:

  • What is Docker Host
  • What is Docker Engine
  • Learn about Docker Architecture
  • Learn about Docker client and Docker Daemon
  • Docker Hub and Registries
  • Simple demo to understand using images from registries

#docker #docker hub #docker host #docker engine #docker architecture #api

Docker Compose: How to Install WordPress on Docker using MySQL Backend

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.

Installation

  1. Run this command to download the current stable release of Docker Compose in linux :
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

Deploy and Run Spring boot with MySQL Application in Docker and Docker Compose

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