Run Percona Server for MySQL in a Docker container¶
You are welcome to name any items to match your organization’s standards or use your table structure and data. If you do, the results are different from the expected results.
Prerequisites¶
- Docker Engine installed and running
- Stable internet connection
- Basic understanding of the command-line interface (CLI)
Always adapt the commands and configurations to your specific environment and security requirements.
Start a Docker container¶
To use the “Docker run” command, specify the name or ID of the image you want to use and, optionally, some flags and arguments that modify the container’s behavior. The command has the following options:
Option | Description |
---|---|
-d |
Runs the container in detached mode, allowing the container to operate in the background. |
-p 3306:3306 |
Maps the container’s MySQLport (3306) tothe same port yourhost, enabling external access. |
--name psmysql |
Provides a meaningful name to the container. If you do not use this option, Docker adds a random name. |
-e MYSQL_ROOT_PASSWORD=secret |
Adds an environmental variable and changes the password from the default password. |
--v myvol:/var/lib/mysql |
Mounts a host directory (myvol) as the container’s data volume, ensuring persistent storage for the database between container lifecycles. |
percona/percona-server:8.0.34 |
The image with the tag (8.0.34) to specify a specific release. |
You must provide at least one environment variable to access the database, such as MYSQL_ROOT_PASSWORD
, MYSQL_DATABASE
, MYSQL_USER
, and MYSQL_PASSWORD
or the instance refuses to initialize.
If needed, you can replace the secret
password with a stronger password.
For this document, we add the 8.0.34
tag. In Docker, a tag is a label assigned to an image and is used to maintain different versions of an image. If we did not add a tag, Docker uses latest
as the default tag and downloads the latest image from percona/percona-server on the Docker Hub.
To run the Docker ARM64 version of Percona Server for MySQL, use the 8.0.34-26 1-aarch64
tag instead of 8.0.34
.
$ docker run -d -p 3306:3306 --name psmysql \
-e MYSQL_ROOT_PASSWORD=secret \
-v myvol:/var/lib/mysql \
percona/percona-server:8.0.34-26 1-aarch64
Expected output
Unable to find image 'percona/percona-server:8.0.34-26 1-aarch64' locally
8.0.34-26 1-aarch64: Pulling from percona/percona-server
d6f6a69cdebb: Pull complete
4f8794caafba: Pull complete
d80629460c71: Pull complete
f550e519928f: Pull complete
fb91f65fb039: Pull complete
e8f7e0c2fbae: Pull complete
Digest: sha256:4944f9b365e0dc88f41b3b704ff2a02d1459fd07763d7d1a444b263db8498e1f
Status: Downloaded newer image for percona/percona-server:8.0.34-26 1-aarch64
01d4f6d188b609ff92158605f8528d640aa28ff5720efa0286b36f51d4bec11c
Connect to the database instance¶
To connect to a MySQL database on a container, use the Docker exec command with the database instance connect command. You must know the name or ID of the container that runs the database server and the database credentials.
The Docker exec command runs a specified command in a running container. The database instance connect command connects to a MySQL server with the user name and password.
For this example, we have the following options:
Option | Description |
---|---|
it |
Interact with the container and be a pseudo-terminal |
psmysql |
Running container name |
mysql |
Connects to a database instance |
-u |
Specifies the user account used to connect |
-p |
Use this password when connecting |
You must enter the password when the server prompts you.
Connect to the database instance example
$ docker exec -it psmysql mysql -uroot -p
You are prompted to enter the password, which is secret
. If you have changed the password, use your password. You will not see any characters as you type.
Enter password:
You should see the following result.
Expected output
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.34-26 Percona Server (GPL), Release 26, Revision 0fe62c85
Copyright (c) 2009-2023 Percona LLC and/or its affiliates
Copyright (c) 2000, 2023, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
Create a database¶
Benefits and what to watch out for when creating databases and tables
Creating a database and table has the following benefits:
- Store and organize your data in a structured and consistent way.
- Query and manipulate your data using SQL.
- Enforce data integrity and security using constraints, triggers, views, roles, and permissions.
- Optimize your data access and performance using indexes, partitions, caching, and other techniques.
When you create a table, design your database schema carefully, changing it later may be difficult and costly. You should experiment with concurrency, transactions, locking, isolation, and other issues that may arise when multiple users access the same data. You must backup and restore your data regularly, as data loss or corruption may occur due to hardware failures, human errors, or malicious attacks.
To create a database, use the CREATE DATABASE
statement. You can optionally specify the character set and collation for the database in the statement. After the database is created, select the database using the USE
statement or the -D
option in the MySQL client.
mysql> CREATE DATABASE mydb;
Expected output
Query OK, 1 row affected (0.01 sec)
mysql> use mydb;
Expected output
Database changed
Create a table¶
Create a table using the CREATE TABLE
statement. You can specify the values for each column or use the DEFAULT keyword for columns with default values, data types, constraints, indexes, and other options.
mysql> CREATE TABLE `employees` (
`id` mediumint(8) unsigned NOT NULL auto_increment,
`name` varchar(255) default NULL,
`email` varchar(255) default NULL,
`country` varchar(100) default NULL,
PRIMARY KEY (`id`)
) AUTO_INCREMENT=1;
Expected output
Query OK, 0 rows affected, 1 warning (0.03 sec)
Insert data into the table¶
Insert data into the table using the INSERT INTO
SQL statement. This statement adds multiple records into a table in one statement.
mysql> INSERT INTO `employees` (`name`,`email`,`country`)
VALUES
("Erasmus Richardson","posuere.cubilia.curae@outlook.net","England"),
("Jenna French","rhoncus.donec@hotmail.couk","Canada"),
("Alfred Dejesus","interdum@aol.org","Austria"),
("Hamilton Puckett","dapibus.quam@outlook.com","Canada"),
("Michal Brzezinski","magna@icloud.pl","Poland"),
("Zofia Lis","zofial00@hotmail.pl","Poland"),
("Aisha Yakubu","ayakubu80@outlook.com","Nigeria"),
("Miguel Cardenas","euismod@yahoo.com","Peru"),
("Luke Jansen","nibh@hotmail.edu","Netherlands"),
("Roger Pettersen","nunc@protonmail.no","Norway");
Expected output
Query OK, 10 rows affected (0.02 sec)
Records: 10 Duplicates: 0 Warnings: 0
Run a SELECT query¶
SELECT queries retrieve data from one or more tables based on specified criteria. They are the most common type of query and can be used for various purposes, such as displaying, filtering, sorting, aggregating, or joining data. SELECT queries do not modify the data in the database but can affect the performance if the query involves large or complex datasets.
mysql>SELECT id, name, email, country FROM employees WHERE country = 'Poland';
Expected output
+----+-------------------+---------------------+---------+
| id | name | email | country |
+----+-------------------+---------------------+---------+
| 5 | Michal Brzezinski | magna@icloud.pl | Poland |
| 6 | Zofia Lis | zofial00@hotmail.pl | Poland |
+----+-------------------+---------------------+---------+
2 rows in set (0.00 sec)
Run an Update query¶
UPDATE queries modify existing data in a table. They are used to change or correct the information stored in the database. UPDATE queries can update one or more columns and rows simultaneously, depending on the specified conditions. They may also fail if they violate any constraints or rules defined on the table.
An example of an UPDATE query and then run a SELECT with a WHERE clause to verify the update.
mysql> UPDATE employees SET name = 'Zofia Niemec' WHERE id = 6;
Expected output
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> SELECT name FROM employees WHERE id = 6;
Expected output
+--------------+
| name |
+--------------+
| Zofia Niemec |
+--------------+
1 row in set (0.00 sec)
Run an INSERT query¶
INSERT queries add new data to a table. They are used to populate the database with new information. INSERT queries can insert one or more rows at a time, depending on the syntax. The query may fail if it violates any constraints or rules defined on the table, such as primary keys, foreign keys, unique indexes, or triggers.
Insert a row into a table and then run a SELECT with a WHERE clause to verify the record was inserted.
mysql> INSERT INTO `employees` (`name`,`email`,`country`)
VALUES
("Kenzo Sasaki","KenSasaki@outlook.com","Japan");
Expected output
Query OK, 1 row affected (0.01 sec)
mysql> SELECT id, name, email, country FROM employees WHERE id = 11;
Expected output
+----+--------------+-----------------------+---------+
| id | name | email | country |
+----+--------------+-----------------------+---------+
| 11 | Kenzo Sasaki | KenSasaki@outlook.com | Japan |
+----+--------------+-----------------------+---------+
1 row in set (0.00 sec)
Run a Delete query¶
DELETE queries remove existing data from a table. They are used to clean up the information no longer needed or relevant in the database. The DELETE queries can delete one or more rows at a time, depending on the specified conditions. They may also trigger cascading deletes on related tables if foreign key constraints are enforced.
Delete a row in the table and run a SELECT with a WHERE clause to verify the deletion.
mysql> DELETE FROM employees WHERE id >= 11;
Expected output
Query OK, 1 row affected (0.01 sec)
mysql> SELECT id, name, email, country FROM employees WHERE id > 10;
Expected output
Empty set (0.00 sec)
Clean up¶
The following steps do the following:
-
Exits the MySQL command client shell and the Docker container
-
Removes the Docker container and the Docker image
-
Removes the Docker volume.
The steps are as follows:
-
To exit the MySQL command client shell we use
exit
. You can also use the\q
orquit
commands. The execution of the statement also closes the connection.-
An example of exiting the MySQL command client shell and closing the connection.
mysql> exit
Expected output
Bye
-
-
You may want to remove the docker container and the image if they are no longer needed or to free up disk space. To remove a docker container, use the command
docker rm
followed bypsmysql
, the container ID or name. To remove a docker image, use the commanddocker rmi
followed bypercona/percona-server:8.0.34
, the image ID or name and the tag. If you are running the ARM64 version of Percona Server, edit the Docker command to use the8.0.34-26.1-aarch64
tag withdocker image rmi percona/percona-server:8.0.34-26.1-aarch64
-
An example of removing a Docker container.
$ docker container rm psmysql -f
Expected output
* An example of removing a Docker image. If running the ARM64 version of Percona Server, edit the Docker command to use thepsmysql
8.0.34-26.1-aarch64
tag. This edit changes the command todocker image rmi percona/percona-server:8.0.34-26.1-aarch64
$ docker image rmi percona/percona-server:8.0.34
Expected output
Untagged: percona/percona-server:8.0.34 Untagged: percona/percona-server@sha256:4944f9b365e0dc88f41b3b704ff2a02d1459fd07763d7d1a444b263db8498e1f Deleted: sha256:b2588da614b1f382468fc9f44600863e324067a9cae57c204a30a2105d61d9d9 Deleted: sha256:1ceaa6dc89e328281b426854a3b00509b5df13826a9618a09e819a830b752ebd Deleted: sha256:77471692427a227eb16d06907357956c3bb43f0fdc3ecf6f8937e1acecae24fe Deleted: sha256:8db06cc7b0430437edc7f118b139d2195cb65e2e8025f9a4517d16778f615384 Deleted: sha256:e5a57a2fafec4ab9482240f28927651d56545c19626e344aceb8be3704c3c397 Deleted: sha256:f86198f39b893674d44d424c958f23183bf919d2ced20e1f519714d0972d75ed Deleted: sha256:db9672f7e12e374d5e9016b758a29d5444e8b0fd1246a6f1fc5c2b3c847dddcf
-
-
Remove the docker volume if a container does not use the volume and you no longer need it.
-
An example of removing a Docker volume.
$ docker volume rm myvol
Expected output
myvol
-
Troubleshooting¶
-
Connection Refusal: Ensure Docker is running and the container is active. Verify port 3306 is accessible on the container’s IP address.
-
Incorrect Credentials: Double-check the root password you set during container launch.
-
Data Loss: Always back up your data regularly outside the container volume.
Security measures¶
-
Strong Passwords: Utilize complex, unique passwords for the root user and any additional accounts created within the container. The alphanumeric password should contain at least 12 characters. The password should include uppercase and lowercase letters, numbers, and symbols.
-
Network Restrictions: Limit network access to the container by restricting firewall rules to only authorized IP addresses.
-
Periodic Updates: Regularly update the Percona Server image and Docker Engine to mitigate known vulnerabilities.
-
Data Encryption: Consider encrypting the data directory within the container volume for an additional layer of security.
-
Monitor Logs: Actively monitor container logs for suspicious activity or errors.
Remember, responsible container management and robust security practices are crucial for safeguarding your MySQL deployment. By following these guidelines, you can leverage the benefits of Docker and Percona Server while prioritizing the integrity and security of your data.
Next step¶
Get expert help¶
If you need assistance, visit the community forum for comprehensive and free database knowledge, or contact our Percona Database Experts for professional support and services.