Create a MySQL test environment (+ millions of test data) in 5 minutes

TL;DR

--Set up a MySQL Docker container --Import millions of test data from MySQL official (https://dev.mysql.com/doc/employee/en/employees-installation.html)

This makes it easy to test MySQL locally. You can use it for purposes such as wanting to test the MySQL function itself, regardless of the contents of the data.

If you copy and paste the command, you should be able to create the environment in about 5 minutes.

Premise

--Docker and Git are already installed and configured --I used Ubuntu 20.04 (WSL2) when creating this article. Please read the command as appropriate.

Method

1. Prepare settings and files to be made persistent


#Prepare a folder to put the configuration file
mkdir -p ~/mysql/config
#Prepare a folder to mount DB data
mkdir ~/mysql/mount_dir

Next, create a configuration file.


vi ~/mysql/config/config-file.cnf

You can set it as you like (it can be empty), but here, as an example, let's put a restriction on lowercase table names.


[mysqld]
lower_case_table_names=1

2. Run the MySQL Docker container

In this article, we will install MySQL 8.0.21.


#Image acquisition
docker pull mysql:8.0.21
#Container execution
docker run --name mysql -p 3306:3306 \
-v $HOME/mysql/config:/etc/mysql/conf.d \
-v $HOME/mysql/mount_dir:/var/lib/mysql \
-e MYSQL_ROOT_PASSWORD=root -d mysql:8.0.21

By mounting the folder when running docker run, you can keep the data and settings without being deleted if you want to update the container later.

If you can connect to mysql with the following command, the MySQL installation is completed successfully.

mysql -h127.0.0.1 -uroot -proot --port=3306

3. Insert data

A large amount of test data can be used by using the test_db repository introduced on the MySQL official website.

cd ~/mysql
#Download the GitHub repository
git clone [email protected]:datacharmer/test_db.git
# employees.Import sql
cd test_db
mysql -h127.0.0.1 -uroot -proot --port=3306 -t < employees.sql

The following is the number of data items. The ʻemployees` table alone has more than 300,000 items, and there are millions of data in total, which is convenient when you want to check while moving the MySQL specifications.

+--------------+------------------+------------------------------------------+
| table_name   | expected_records | expected_crc                             |
+--------------+------------------+------------------------------------------+
| employees    |           300024 | 4d4aa689914d8fd41db7e45c2168e7dcb9697359 |
| departments  |                9 | 4b315afa0e35ca6649df897b958345bcb3d2b764 |
| dept_manager |               24 | 9687a7d6f93ca8847388a42a6d8d93982a841c6c |
| dept_emp     |           331603 | f16f6ce609d032d6b1b34748421e9195c5083da8 |
| titles       |           443308 | d12d5f746b88f07e69b9e36675b6067abb01b60e |
| salaries     |          2844047 | b5a1785c27d75e33a4173aaa22ccf41ebd7d4a9f |
+--------------+------------------+------------------------------------------+

Please refer to the Official Document for detailed usage instructions.

Remarks: Migrate data from another environment

The following article describes how to migrate data, so please refer to it if necessary.

Migrate data to another environment using [MySQL] mysqldump

4. (Bonus) Clean up afterwards

If you no longer need MySQL after testing, remove it with the following command.

#Delete container
docker rm -f mysql
#If you don't need any data or settings, delete the entire folder
sudo rm -rf ~/mysql

Recommended Posts

Create a MySQL test environment (+ millions of test data) in 5 minutes
Create a database in a production environment
Create a MySQL environment with Docker from 0-> 1
Try to create a browser automatic operation environment using Selenide in 5 minutes
Create a native extension of Ruby in Rust
How to create a Java environment in just 3 seconds
How to create a data URI (base64) in Java
Create a MySQL container for application testing in Ansible AWX
Let's create a TODO application in Java 3 Save temporary data in MySQL-> Get all-> Display on top
Let's create a TODO application in Java 4 Implementation of posting function
A story of frustration trying to create a penetration environment on Ubuntu 20.04
Spring Data JPA: Write a query in Pure SQL in @Query of Repository
Let's create a TODO application in Java 6 Implementation of search function
Let's create a TODO application in Java 8 Implementation of editing function
Build a browser test environment using Capybara in the Docker development environment
Let's create a TODO application in Java 1 Brief explanation of MVC
Install Rails in the development environment and create a new application
Let's create a TODO application in Java 5 Switch the display of TODO
Create a Vue3 environment with Docker!
Create a new app in Rails
Create a Servlet program in Eclipse
Store UUID data in MySQL in 16 bytes
Check MySQL logs in Docker environment
[Rails] I want to send data of different models in a form
[Environment construction] Rails + MySQL + Docker (Beginners can also use it in 30 minutes!)
Create a web environment quickly using Docker
Implementation of DBlayer in Java (RDB, MySQL)
Create variable length binary data in Java
Building a Lambda development environment in Eclipse
Create Rails 6 + MySQL environment with Docker compose
Edit Mysql with commands in Docker environment
Let's create a Java development environment (updating)
Create a TODO app in Java 7 Create Header
Creating a Servlet in the Liberty environment
Japanese setting of mysql in Docker container
Take a thread dump in a JRE environment
[First environment construction] I tried to create a Rails 6 + MySQL 8.0 + Docker environment on Windows 10.
Introduce Docker to the development environment and test environment of existing Rails and MySQL applications
How to make a unique combination of data in the rails intermediate table
[When using MiniMagick] A memorandum because I stumbled in the CircleCI test environment.
About the solution of the error that occurred when trying to create a Japanese file of devise in the Docker development environment