howto:mysql:sample

About

This howto will show you how to install the world sample schema of MySQL.

Note

The installation is done with the sql script world.sql

This script is idempotent because it will:

drop the world schema (known also as a database in the MySQL glossary) if exists
and create it

Unzip Steps

Start MySQL

The next steps will be using the mysql howto connection.

We start the mysql docker image.

tabul service start mysql

Ping the connection to check that the database is up.

tabul connection ping mysql

The connection (mysql) has been pinged successfully

Unzip it

The script is located in the world-db.tar.gz archive

We can download it and unzip it with the unzip command.

tabul data unzip https://downloads.mysql.com/docs/world-db.tar.gz

target_data_uri          entry_path           entry_media_type   entry_size   entry_update_time
----------------------   ------------------   ----------------   ----------   ---------------------
world-db/world.sql@tmp   world-db/world.sql   application/sql        398629   2025-10-31 23:05:45.0

The world.sql file was downloaded and extracted at the world-db/world.sql@tmp data uri ie:

in the tmp directory
in the path world-db/world.sql

Execute it

We can make the sql script world-db/world.sql@tmp executable by adding the execution connection

and therefore creating a runtime data uri
known as a sql request.

The runtime data uri against the mysql howto connection is:

(world-db/world.sql@tmp)@mysql

We execute it with the execute command

tabul data execute --target-data-uri world.csv@tmp '(world-db/world.sql@tmp)@mysql'
# The quotes are mandatory in bash because parenthesis are a bash token (ie subshell) 
# --target-data-uri defines where the results output is stored, we will check them in the next step

List of runtime executed
runtime_data_uri                 exit_code   count   latency   data_uri        error_message
------------------------------   ---------   -----   -------   -------------   -------------
(world-db/world.sql@tmp)@mysql           0    5346   4.964s    world.csv@tmp

Check the execution results

The results were stored in the world.csv@tmp that you can inspect with the print, tail or head command

Example:

tabul data head world.csv@tmp
# if you want the full results
# tabul data print world.csv@tmp

The first 10 rows of the data resource (world.csv@tmp):
id   count   statement                                            line   error_code   error_message
--   -----   --------------------------------------------------   ----   ----------   -------------
1    0       /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER   7      0            
2    0       /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTE   8      0            
3    0       /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION   9      0            
4    0       /*!50503 SET NAMES utf8mb4 */                        10     0            
5    0       /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */           11     0            
6    0       /*!40103 SET TIME_ZONE='+00:00' */                   12     0            
7    0       /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, U   13     0            
8    0       /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY   14     0            
9    0       /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='N   15     0            
10   0       /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES   16     0

List the world tables

To list all tables in the world schema with the count, you can use this glob data selector with the data list command

tabul data list -a count world.*@mysql

path                    media_type   count
---------------------   ----------   -----
world.city              table         4079
world.country           table          239
world.countrylanguage   table          984

Archive Entry steps

All-in-One Magic command

You can also execute all the above steps in only one command.

tabul data execute '((archive/world-sql--archive-entry.yml@howto)@tmp)@mysql'

List of runtime executed
runtime_data_uri                 exit_code   count   latency   data_uri                                                         error_message
------------------------------   ---------   -----   -------   --------------------------------------------------------------   -------------
(world-db/world.sql@tmp)@mysql           0    5346   4.684s    execute/20251110-204116-815-pipe-tabul-data-exec/world.log@tmp

How does it works?

Because a runtime can also be an executable, we can also use an archive entry as executable.

We have created this archive entry that represents the file world-db/world.sql in the archive.

tabul data cat archive/world-sql--archive-entry.yml@howto

kind: archive-entry
spec:
  # The data uri of the archive
  data-uri: https://downloads.mysql.com/docs/world-db.tar.gz
  data-def:
    # The path of the file in the archive
    entry-path: world-db/world.sql

The URI used in the command uses it:

((archive/world-sql--archive-entry.yml@howto)@tmp)@mysql

It tells tabulify to:

takes the archive entry manifest archive/world-sql–archive-entry.yml located in the howto directory
execute the archive entry manifest against the temporary directory (tmp) (ie extract the sql file)
execute the extracted world.sql sql file against the mysql howto connection

You may try to install all other schema listed in the MySQL download page with the same procedure.

Table of Contents