MySQL, the most popular Open Source SQL database management system, is developed, distributed, and supported by Oracle Corporation. MySQL is a database management system. MySQL databases are relational. MySQL software is Open Source. The MySQL Database Server is very fast, reliable, scalable, and easy to use. MySQL Server works in client/server or embedded systems. A large amount of contributed MySQL software is available. – by dev.mysql.com
The best way of handling the Data volume in docker container is, keep the data in a separate container and using by other containers which they call “Data Volume Container”.
PULL MYSQL DOCKER IMAGE
First step is, we need to pull the MySQL docker image from the docker hub repository. Execute the below command to pull the latest MySQL docker image from the docker hub repository.
docker pull mysql/mysql-server:latest
VIEW DOCKER IMAGE
Execute the below command, to view the list of docker images and to make sure the MySQL docker image has been downloaded or not.
docker images
create a data volume container
Execute the below command to create a new Data volume container for MySQL. The below command will create a new container to keep the data volume and it will acts a Data Volume Container.
I named the MySQL container “mysqldb” and the exposed the port number is -p 3306:3306. Also, I set the MySQL root user password as “root” by using docker environment variable itself.
After we run the MySQL container, we can access the database by executing the Docker Exec command and create a new database called “MY_FIRST_DATABASE”.
docker exec -it mysqldb bash
Then, execute the below command to login into the MySQL database.
mysql -uroot -p
Console will ask you the password, provide the root user password we setted by using Docker environment variable.
Now, execute the below SQL command to create a new database.
CREATE DATABASE MY_FIRST_DATABASE;
We can delete the “mysqldb” container without affecting our data in “mysqldata”.
To test the data volume, delete the “mysqldb” container. Then, run a new container “mysqldb1”.
MySQL, the most popular Open Source SQL database management system, is developed, distributed, and supported by Oracle Corporation. MySQL is a database management system. MySQL databases are relational. MySQL software is Open Source. The MySQL Database Server is very fast, reliable, scalable, and easy to use. MySQL Server works in client/server or embedded systems. A large amount of contributed MySQL software is available. – by dev.mysql.com
In Today’s world, most of the applications are depends on the databases. If you don’t want to setup a dedicated database server for your applications, then definitely you might go to the containerized platform.
For small and medium scale industries, running MySQL as a docker container is very specialized platform without spending money for any hardware based servers.
Before running MySQL as a docker container, the docker must be installed on your machine. If you want to know How to install Docker? Please refer the below link.
First step is, we need to pull the MySQL docker image from the docker hub repository. Execute the below command to pull the latest MySQL docker image from the docker hub repository.
Execute the below command, to view the list of docker images and to make sure the MySQL docker image has been downloaded or not.
docker images
root@instance-1:~# docker images
REPOSITORY TAG IMAGE ID CREATED SIZE
mysql/mysql-server latest 12a8d88596c0 8 days ago 294MB
run the mysql docker image as a container
Execute the below command to run the MySQL docker image as a container.
docker run -p 3306:3306 -d --name=mysql mysql/mysql-server:latest
root@instance-1:~# docker run -p 3307:3307 -d --name=mysql mysql/mysql-server:latest
de3bad95ad41de45e43f46da8faee8d6aaf6bb64bb099f6f9f8e62aee29bf073root@instance-1:~#
where,
-p 3306: 3306 – represents exposing the MySQL port number
–name=mysql – represents the name of the container
mysql/mysql-server:latest – represents the name of the MySQL docker image
view mysql docker logs
After run the MySQL image as docker container, execute the below command to view logs and get the default “root” user password to login into the MySQL database.
docker logs mysql
where,
mysql – name of the docker container
You can see the following logs on the console with default “root” user password. Copy the password and keep it safe to login into the database.
root@instance-1:~# docker logs mysql
[Entrypoint] MySQL Docker Image 8.0.17-1.1.12
[Entrypoint] No password option specified for new database.
[Entrypoint] A random onetime password will be generated.
[Entrypoint] Initializing database
2019-07-30T17:58:29.705963Z 0 [System] [MY-013169] [Server] /usr/sbin/mysqld (mysqld 8.0.17) initializing of server in progress as process 20
2019-07-30T17:58:33.701238Z 5 [Warning] [MY-010453] [Server] root@localhost is created with an empty password ! Please consider switching off the --initialize-insecure option.
2019-07-30T17:58:35.487233Z 0 [System] [MY-013170] [Server] /usr/sbin/mysqld (mysqld 8.0.17) initializing of server has completed
[Entrypoint] Database initialized
2019-07-30T17:58:36.967680Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.17) starting as process 67
2019-07-30T17:58:37.669577Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2019-07-30T17:58:37.698884Z 0 [System] [MY-010931] [Server] /usr/sbin/mysqld: ready for connections. Version: '8.0.17' socket: '/var/lib/mysql/mysql.sock' port: 0 MySQL Community Server - GPL.
2019-07-30T17:58:37.767916Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Socket: '/var/run/mysqld/mysqlx.sock'
Warning: Unable to load '/usr/share/zoneinfo/iso3166.tab' as time zone. Skipping it.
Warning: Unable to load '/usr/share/zoneinfo/leapseconds' as time zone. Skipping it.
Warning: Unable to load '/usr/share/zoneinfo/tzdata.zi' 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.
[Entrypoint] GENERATED ROOT PASSWORD: om4fK@h0P&3MetAbOdHEfh@xRes
[Entrypoint] ignoring /docker-entrypoint-initdb.d/*
2019-07-30T17:58:39.891983Z 10 [System] [MY-013172] [Server] Received SHUTDOWN from user root. Shutting down mysqld (Version: 8.0.17).
2019-07-30T17:58:41.714397Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.17) MySQL Community Server - GPL.
[Entrypoint] Server shut down
[Entrypoint] Setting root user as expired. Password will need to be changed before database can be used.
[Entrypoint] MySQL init process done. Ready for start up.
[Entrypoint] Starting MySQL 8.0.17-1.1.12
2019-07-30T17:58:42.287092Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.17) starting as process 1
2019-07-30T17:58:42.889227Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2019-07-30T17:58:42.922042Z 0 [System] [MY-010931] [Server] /usr/sbin/mysqld: ready for connections. Version: '8.0.17' socket: '/var/lib/mysql/mysql.sock' port: 3306 MySQL Community Server - GPL.
2019-07-30T17:58:43.064447Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Socket: '/var/run/mysqld/mysqlx.sock' bind-address: '::' port: 33060
docker exec
The docker exec command allows us to run the commands inside the docker container.
docker exec -it mysql bash
Then, execute the below command to login into the MySQL database.
mysql -uroot -p
Console will ask you the password, provide the default root user password took it from the container logs.
bash-4.2# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 16Server version: 8.0.17Copyright (c) 2000, 2019, Oracle and/or its affiliates.
All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates.
Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help.
Type '\c' to clear the current input statement.
mysql>
Now, execute the SQL commands to access the MySQL database as a Docker container.
alter root user password
Execute the below command to alter the root user password.
ALTER USER 'root'@'localhost' IDENTIFIED BY 'newpassword';
Then, execute the below command to view the list of databases.