How to Download and Create Sample database in MySql

January 18, 2021

How to Download and Create Sample Database in MySql

In this article, we are going to demonstrate How to Download & Create Sample Database in MySql. You can download a sample database for MySql directly inside mysql server or download it first from GitHub and then transfer it to mysql server.

Click here to download the sample database from GitHub, Click on the green button CODE and then Click on Download Zip, refer to below image.

Alternatively, you can also download a sample database directly inside Server using wget command (require internet access). I will create a new directory as a backup and will download it inside the backup dirrectory. Once master.zip file downloaded unzip it. Follow the below steps:

[root@test-machine01 u01]# mkdir backup
[root@test-machine01 u01]# chown -R mysql:mysql backup
[root@test-machine01 u01]# ls -ld backup
drwxr-xr-x. 2 mysql mysql 6 Jan 18 10:43 backup

[root@test-machine01 u01]# cd backup
[root@test-machine01 backup]# wget 'https://codeload.github.com/datacharmer/test_db/zip/master' -O master.zip
--2021-01-18 10:45:56--  https://codeload.github.com/datacharmer/test_db/zip/master
Resolving codeload.github.com (codeload.github.com)... 140.82.121.10
Connecting to codeload.github.com (codeload.github.com)|140.82.121.10|:443... connected.
HTTP request sent, awaiting response... 200 OK
Length: unspecified [application/zip]
Saving to: âmaster.zipâ

    [                                                                                                                                           <=>       ] 36,688,498   583KB/s   in 32s

2021-01-18 10:46:29 (1.09 MB/s) - âmaster.zipâ saved [36688498]

[root@test-machine01 backup]# ls -ltr
total 35832
-rw-r--r--. 1 root root 36688498 Jan 18 10:46 master.zip
[root@test-machine01 backup]#
[root@test-machine01 backup]#

[root@test-machine01 backup]# unzip master
Archive:  master.zip
e5f310ac7786a2a181a7fc124973725d7aa4ce7c
   creating: test_db-master/
  inflating: test_db-master/Changelog
  inflating: test_db-master/README.md
  inflating: test_db-master/employees.sql
[root@test-machine01 backup]# ls -ltr
total 35836
drwxr-xr-x. 4 root root     4096 Sep  7 07:24 test_db-master
-rw-r--r--. 1 root root 36688498 Jan 18 10:46 master.zip

[root@test-machine01 backup]# cd test_db-master

[root@test-machine01 test_db-master]# ls -ltr
total 168340
-rwxr-xr-x. 1 root root     2013 Sep  7 07:24 test_versions.sh
-rw-r--r--. 1 root root     4715 Sep  7 07:24 test_employees_sha.sql
-rw-r--r--. 1 root root     4711 Sep  7 07:24 test_employees_md5.sql
-rwxr-xr-x. 1 root root     1800 Sep  7 07:24 sql_test.sh
-rw-r--r--. 1 root root      272 Sep  7 07:24 show_elapsed.sql
drwxr-xr-x. 2 root root       77 Sep  7 07:24 sakila
-rw-r--r--. 1 root root     4325 Sep  7 07:24 README.md
-rw-r--r--. 1 root root     4568 Sep  7 07:24 objects.sql
-rw-r--r--. 1 root root 21708736 Sep  7 07:24 load_titles.dump
-rw-r--r--. 1 root root 39080916 Sep  7 07:24 load_salaries3.dump
-rw-r--r--. 1 root root 39805981 Sep  7 07:24 load_salaries2.dump
-rw-r--r--. 1 root root 39806034 Sep  7 07:24 load_salaries1.dump
-rw-r--r--. 1 root root 17722832 Sep  7 07:24 load_employees.dump

After unzip command all necessary files will be available inside folder test_db-master, Use the below command to load data. Once the command is finished new database employees will be created along with tables.

[root@test-machine01 backup]# cd test_db-master


[root@test-machine01 test_db-master]# mysql -hlocalhost -uroot -p < employees.sql
Enter password:
INFO
CREATING DATABASE STRUCTURE
INFO
storage engine: InnoDB
INFO
LOADING departments
INFO
LOADING employees
INFO
LOADING dept_emp
INFO
LOADING dept_manager
INFO
LOADING titles
INFO
LOADING salaries
data_load_time_diff
00:00:53
[root@test-machine01 test_db-master]#


mysql>  show databases;
+--------------------+
| Database           |
+--------------------+
| employees          |
| information_schema |
| mysql              |
| performance_schema |
| repl_test          |
| sys                |
+--------------------+
6 rows in set (0.01 sec)

mysql> use employees
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+----------------------+
| Tables_in_employees  |
+----------------------+
| current_dept_emp     |
| departments          |
| dept_emp             |
| dept_emp_latest_date |
| dept_manager         |
| employees            |
| salaries             |
| titles               |
+----------------------+
8 rows in set (0.00 sec)

mysql>

This document is only for learning purpose and always validate in the LAB environment first before applying in the LIVE environment.

Hope so you like this article!
Please share your valuable feedback/comments/subscribe and follow us below and don’t forget to click on the bell icon to get the latest update. Click here to know more about our pursuit.