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

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