Using MySQL Database
MySQL is the most commonly used open source relational database. MySQL is similar to Oracle Database in some regards such as users are kept in grant tables by the database. But MySQL is different from Oracle Database in some regards too:
- MySQL does not have roles and privileges have to be granted individually to users.
- Database and table names are case-insensitive in Oracle but are case sensitive if the underlying OS is case-sensitive.
- MySQL provides a default value for columns that do not allow a NULL value and a value is not provided explicitly in the INSERT statement, if the strict mode is not enabled. Oracle database does not generate a default value for columns with the NOT NULL constraint.
- MySQL database supports AUTO_INCREMENT for a column while a Sequence is used in Oracle Database.
- Some of the data types in MySQL are different. For example, MySQL does not support the VARCHAR2 data type.
In this chapter we shall run MySQL database in a Docker container. This chapter has the following sections.
- Setting the Environment
- Starting MySQL CLI Shell
- Setting the Database to Use
- Creating a Database Table
- Adding Table Data
- Querying a Table
- Listing Databases and Tables
- Exiting TTY Terminal
- Starting Another MySQL Server Instance
- Listing Docker Container Log
Setting the Environment
The following software is required for this chapter.
- -Docker Engine (version 1.8 used)
- -Docker image for MySQL Database
Login to an Amazon EC2 instance using the public IP address of the instance.
ssh -i "docker.pem" [email protected]
Start the Docker service.
sudo service docker start
Verify that the Docker service is running.
sudo service docker status
The output from the docker start command should be OK and the output from the docker status command should be active (running) for the Active field as shown in Figure 4-1.
Figure 4-1. Starting Docker Service and verifying Status
Docker Hub provides an official Docker image. Download the Docker image with the following command.
sudo docker pull mysql
The latest Docker image mysql:latest gets downloaded as shown in Figure 4-2.
Figure 4-2. Downloading Docker Image for MySQL Database
List the Docker images with the following command.
sudo docker images
The mysql image gets listed as shown in Figure 4-3.
Figure 4-3. Listing Docker Image for MySQL Database
Starting MySQL Server
In this section we shall run MySQL database in a Docker container. MySQL database uses the /var/lib/mysql directory by default for storing data, but another directory may also be used. We shall use the /mysql/data directory for storing MySQL data. Create the /mysql/data directory and set its permissions to global (777).
sudo mkdir -p /mysql/data
sudo chmod -R 777 /mysql/data
The /mysql/data directory gets created as shown in Figure 4-4.
Figure 4-4. Creating the Data Directory
When the docker run command is run to start MySQL in a Docker container, certain environment variables may be specified as discussed in the following table.
Env Variable | Description | Required |
|---|---|---|
MYSQL_ROOT_PASSWORD | Password for the “root” user. | Yes |
MYSQL_DATABASE | Creates a database | No |
MYSQL_USER, MYSQL_PASSWORD | Specify the username and password to create a new user. The user is granted superuser privileges on the database specified in the MYSQL_DATABASE variable. Both the user name and password must be set if either is set. | No |
MYSQL_ALLOW_EMPTY_PASSWORD | Specifies whether the “root” user is permitted to have an empty password. | No |
Other than the MYSQL_ROOT_PASSWORD environment variable, all the other variables are optional, but we shall run a MySQL instance container using all the environment variables. We shall run the docker run command using the following command parameters.
Command Parameter | Value |
|---|---|
MYSQL_ROOT_PASSWORD | ‘’ |
MYSQL_DATABASE | mysqldb |
MYSQL_USER, MYSQL_PASSWORD | mysql, mysql |
MYSQL_ALLOW_EMPTY_PASSWORD | yes |
-v | /mysql/data:/var/lib/mysql |
--name | mysqldb |
-d |
The environment variables are specified with –e. Run the following docker run command to start a MySQL instance in a Docker container.
sudo docker run -v /mysql/data:/var/lib/mysql --name mysqldb -e MYSQL_DATABASE=’mysqldb’ -e MYSQL_USER=’mysql’ -e MYSQL_PASSWORD=’mysql’ -e MYSQL_ALLOW_EMPTY_PASSWORD=’yes’ -e MYSQL_ROOT_PASSWORD=’’ -d mysql
The output from the docker run command is shown in Figure 4-5.
Figure 4-5. Running MySQL Database in a Docker Container
Run the following command to list the Docker containers that are running.
sudo docker ps
The Docker container mysqldb that is running the MySQL database instance gets listed as shown in Figure 4-6.
Figure 4-6. Listing Docker Containers
Starting MySQL CLI Shell
Next, we shall log into the MySQL CLI shell. But first we need to start an interactive terminal to run the mysql command to start the MySQL CLI. Start the interactive terminal or shell with the following command.
sudo docker exec -it mysqldb bash
In the interactive terminal run the following command.
mysql
The MySQL CLI gets started as shown in Figure 4-7.
Figure 4-7. Starting MySQL CLI
The interactive terminal may also be started using the container id instead of the container name.
sudo docker exec -it 969088c84a4f bash
Setting the Database to Use
Set the database with the “use” command. The “test” database is not provided by the MySQL database started in a Docker container by default. If the “use test” command is run, the following error message is output.
mysql> use test
ERROR 1049 (42000): Unknown database ’test’
We created a database called “mysqldb” when we started the Docker container for MySQL database with the docker run command. Set the database to “mysqldb” with the following command.
mysql> use mysqldb
The output from the preceding commands is as follows. The database gets set to “mysqldb” as shown in Figure 4-8.
Figure 4-8. Setting Database to mysqldb
Creating a Database Table
Next, create a database table called “Catalog” with columns CatalogId, Journal, Publisher, Edition, Title and Author. Run the following SQL statement.
mysql> CREATE TABLE Catalog(CatalogId INTEGER PRIMARY KEY,Journal VARCHAR(25),Publisher VARCHAR(25),Edition VARCHAR(25),Title VARCHAR(45),Author VARCHAR(25));
The Catalog table gets created as shown in Figure 4-9.
Figure 4-9. Creating a MySQL Database Table
Adding Table Data
Add data to the Catalog table with the following INSERT statement.
mysql> INSERT INTO Catalog VALUES(’1’,’Oracle Magazine’,’Oracle Publishing’,’November December 2013’,’Engineering as a Service’,’David A. Kelly’);
A row of data gets added to the Catalog table as shown in Figure 4-10.
Figure 4-10. Adding a Row of Data to MySQL Table
Querying a Table
Next, query the Catalog table with a SQL query. The following SELECT statement selects all the data in the Catalog table.
mysql> SELECT * FROM Catalog;
The one row of data added gets listed as shown in Figure 4-11.
Figure 4-11. Running a SQL Query
MySQL table name is case sensitive on the OS (RHEL 7.1 OS) used in this chapter. If a variation of the table name Catalog is used, an error is generated. For example, use table name CATALOG in the SQL query and the following error gets generated as shown in Figure 4-12.
Figure 4-12. The table name is Case-sensitive in MySQL
The databases in a MySQL server instance may be listed with the following command in MySQL CLI.
mysql> show databases;
The databases get listed, including the newly created database “mysqldb” as shown in Figure 4-13.
Figure 4-13. Listing MySQL Databases
Exiting TTY Terminal
Exit the MySQL CLI with the “exit” command.
mysql> exit
Bye
Exit the interactive shell or tty with the “exit” command.
root@969088c84a4f:/# exit
exit
The output from the preceding commands is shown in Figure 4-14.
Figure 4-14. Exiting MySQL CLI
Stopping a Docker Container
Stop the Docker container with the docker stop command.
[ec2-user@ip-172-30-1-192 ~]$ sudo docker stop 969088c84a4f
969088c84a4f
Subsequently, list the running Docker containers with the docker ps command. The mysqldb container does not get listed.
sudo docker ps
[ec2-user@ip-172-30-1-192 ~]$ sudo docker ps
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
In the next section we shall create another MySQL Server instance just as we created the MySQL server instance earlier in this chapter. But we cannot use the same container name as an existing container. Another Docker container running a MySQL database, or any other software, may be started if the Docker container name is different. If we created a Docker container to run another MySQL server with the same name “mysqldb”, an error gets generated. For example, run the following docker run command to create another container called “mysqldb”.
sudo docker run --name mysqldb -e MYSQL_ROOT_PASSWORD=mysql -d mysql
The following error gets output.
Error response from daemon: Conflict. The name "mysqldb" is already in use by container 969088c84a4f. You have to delete (or rename) that container to be able to reuse that name.
To create a new Docker container called “mysqldb” first remove the “mysqldb” container already created with the docker rm command. Either the container id or the container name may be used in docker commands for a container such as stop, start, and rm.
sudo docker rm 969088c84a4f
Starting Another MySQL Server Instance
Having removed the “mysqldb” container, create the container again with the docker run command. We shall create the new “mysqldb” container differently. Specify different environment variables for the second run of the docker run command. Specify only the required environment variable MYSQL_ROOT_PASSWORD and set its value to “mysql”.
sudo docker run --name mysqldb -e MYSQL_ROOT_PASSWORD=mysql -d mysql
Subsequently, start the interactive shell with the following command.
sudo docker exec -it 113458c31ce5 bash
Login to the MySQL CLI with the following command in the interactive shell.
mysql –u root –p mysql
Specify the password for the “root” user, which is mysql. MySQL CLI gets started as shown in Figure 4-15.
Figure 4-15. Using a Password to Start MySQL CLI
The mysql command may also be issued as follows.
mysql –u root –p
Specify the password for the “mysql” user. MySQL CLI gets started as shown in Figure 4-16.
Figure 4-16. Alternative mysql Login command
The following mysql command does not start a MySQL CLI.
root@113458c31ce5:/# mysql -u root
The following error is generated.
ERROR 1045 (28000): Access denied for user ’root’@’localhost’ (using password: NO)
List the databases with the show databases command. The default databases include the “mysql” database as shown in Figure 4-17. Previously, the “mysqldb” database also got listed with the show databases command because the “mysqldb” database was created when the docker run command was run.
Figure 4-17. Listing the Default Databases
Set the database as the “mysql” database with the “use mysql” command as shown in Figure 4-18.
Figure 4-18. Using the mysql Database
List the database tables in the mysql database with the show tables command as shown in Figure 4-19.
Figure 4-19. Listing Tables
Listing Docker Container Logs
Next, list the logs for the mysqldb container with the docker logs command.
sudo docker logs -f mysqldb
The logs for the mysqldb container get listed as shown in Figure 4-20.
Figure 4-20. Listing Docker Container Log
Summary
In this chapter we used a Docker image to run MySQL Server in a Docker container. We ran two different variations of the docker run command; one included all the environment variables that may be set for the “mysql” image and the other included only the required environment variable/s. In the next chapter we shall discuss running MongoDB on Docker.