Infrastructure at your Service

Daniel Westermann

The (almost) same sample schema for all major relational databases (3) – MySQL/MariaDB

Some time ago I blogged on how to get the “Dell DVD Store Database Test Suite” installed in PostgreSQL and Oracle. This time we’ll do the same on a MariaDB instance. I have to admit here that I am not very familiar with MariaDB/MySQL so the stuff presented here for sure breaks any security best practices πŸ™‚

Again I’ll start with a CentOS 7.2 core installation and use the MariaDB binaries available in the standard repositories:

[[email protected] ~] cat /etc/centos-release
CentOS Linux release 7.2.1511 (Core) 
[[email protected] ~] yum install -y mariadb-server.x86_64

Having the binaries available lets enable and start the service:

[[email protected] ~] systemctl list-unit-files | grep maria
mariadb.service                             disabled
[[email protected] ~] systemctl enable mariadb.service
Created symlink from /etc/systemd/system/multi-user.target.wants/mariadb.service to /usr/lib/systemd/system/mariadb.service.
[[email protected] ~] systemctl start mariadb.service

This should have started my MariaDB instance:

[[email protected] ~] systemctl status mariadb.service
● mariadb.service - MariaDB database server
   Loaded: loaded (/usr/lib/systemd/system/mariadb.service; enabled; vendor preset: disabled)
   Active: active (running) since Mon 2016-04-25 17:58:25 CEST; 10s ago
  Process: 9791 ExecStartPost=/usr/libexec/mariadb-wait-ready $MAINPID (code=exited, status=0/SUCCESS)
  Process: 9712 ExecStartPre=/usr/libexec/mariadb-prepare-db-dir %n (code=exited, status=0/SUCCESS)
 Main PID: 9790 (mysqld_safe)
   CGroup: /system.slice/mariadb.service
           β”œβ”€9790 /bin/sh /usr/bin/mysqld_safe --basedir=/usr
           └─9948 /usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib64/mysql/plugin --log-error=/var/log/mariadb/mariadb.log ...

Apr 25 17:58:23 mysqldell mariadb-prepare-db-dir[9712]: The latest information about MariaDB is available at http://mariadb.org/.
Apr 25 17:58:23 mysqldell mariadb-prepare-db-dir[9712]: You can find additional information about the MySQL part at:
Apr 25 17:58:23 mysqldell mariadb-prepare-db-dir[9712]: http://dev.mysql.com
Apr 25 17:58:23 mysqldell mariadb-prepare-db-dir[9712]: Support MariaDB development by buying support/new features from MariaDB
Apr 25 17:58:23 mysqldell mariadb-prepare-db-dir[9712]: Corporation Ab. You can contact us about this at [email protected]
Apr 25 17:58:23 mysqldell mariadb-prepare-db-dir[9712]: Alternatively consider joining our community based development effort:
Apr 25 17:58:23 mysqldell mariadb-prepare-db-dir[9712]: http://mariadb.com/kb/en/contributing-to-the-mariadb-project/
Apr 25 17:58:23 mysqldell mysqld_safe[9790]: 160425 17:58:23 mysqld_safe Logging to '/var/log/mariadb/mariadb.log'.
Apr 25 17:58:23 mysqldell mysqld_safe[9790]: 160425 17:58:23 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
Apr 25 17:58:25 mysqldell systemd[1]: Started MariaDB database server.

Can I login?

[[email protected] ~] mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 2
Server version: 5.5.47-MariaDB MariaDB Server

Copyright (c) 2000, 2015, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> 

As I just want to get the data loaded I do not care much about security and will unlock the mysql account so I do not have to work as root:

[[email protected] ~] usermod -s /bin/bash mysql
[[email protected] ~] su - mysql

Can I login as mysql?

-bash-4.2$ mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 3
Server version: 5.5.47-MariaDB MariaDB Server

Copyright (c) 2000, 2015, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+
4 rows in set (0.00 sec)

Looks fine so lets continue with the dell stuff. I’ll create the DS2 database and the user that the scripts will use to connect:

MariaDB [(none)]> create user 'web'@'localhost' identified by "web";
Query OK, 0 rows affected (0.00 sec)
riaDB [(none)]> create database DS2;
Query OK, 1 row affected (0.00 sec)
ariaDB [(none)]> GRANT ALL PRIVILEGES ON DS2.* TO 'web'@'%' WITH GRANT OPTION;
Query OK, 0 rows affected (0.00 sec)

The next steps are almost the same as with the Oracle and PostgreSQL versions. Transfer the ds2 files to the target machine and extract them:

-bash-4.2$ cd /var/tmp/
-bash-4.2$ ls
ds21_mysql.tar.gz  ds21.tar.gz 
-bash-4.2$ tar -axf ds21.tar.gz
-bash-4.2$ tar -axf ds21_mysql.tar.gz
-bash-4.2$ ls
CreateConfigFile.pl  drivers                  ds2_change_log.txt  ds2_readme.txt  gpl.txt              mysqlds2
data_files           ds2.1_Documentation.txt  ds2_faq.txt         ds2_schema.txt  Install_DVDStore.pl
-bash-4.2$ cd mysqlds2/
-bash-4.2$ ls
build                     ds2_mysql_change_log.txt  ds2mysqlfns.cs        gpl.txt  monitor_load.txt  my.cnf.example.diff          mysqlds2_create_all.sh  web
ds2_lamp_setup_rhel5.txt  ds2mysqldriver.exe        ds2_mysql_readme.txt  load     my.cnf.example    mysqlds2_create_all_nosp.sh  showinnodb.sql

Looks familiar so lets try to load the data:

-bash-4.2$ ./mysqlds2_create_all.sh
ERROR 1064 (42000) at line 16: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'TYPE=InnoDB' at line 24
ERROR 1146 (42S02) at line 9: Table 'DS2.CUSTOMERS' doesn't exist
ERROR 1146 (42S02) at line 4: Table 'DS2.CUSTOMERS' doesn't exist
ERROR 1146 (42S02) at line 4: Table 'DS2.ORDERS' doesn't exist
ERROR 1146 (42S02) at line 4: Table 'DS2.ORDERLINES' doesn't exist
ERROR 1146 (42S02) at line 4: Table 'DS2.CUST_HIST' doesn't exist
ERROR 1146 (42S02) at line 5: Table 'DS2.PRODUCTS' doesn't exist
ERROR 1146 (42S02) at line 5: Table 'DS2.INVENTORY' doesn't exist

Hmm. What went wrong:

-bash-4.2$ grep -i innodb build/*
build/mysqlds2_cleanup_generic_template.sql:  TYPE=InnoDB;
build/mysqlds2_cleanup_large.sql:  TYPE=InnoDB;
build/mysqlds2_cleanup_med.sql:  TYPE=InnoDB;
build/mysqlds2_cleanup_small.sql:  TYPE=InnoDB;
build/mysqlds2_create_db.sql:  TYPE=InnoDB;
build/mysqlds2_create_db.sql:  TYPE=InnoDB;
build/mysqlds2_create_db.sql:  TYPE=InnoDB; 
build/mysqlds2_create_db.sql:  TYPE=InnoDB; 
build/mysqlds2_create_db.sql:  TYPE=InnoDB;
build/mysqlds2_create_db.sql:  TYPE=InnoDB;
build/mysqlds2_create_db.sql:  TYPE=InnoDB;

Looks like a syntax issue. Fix it:

-bash-4.2$ sed -i 's/TYPE=InnoDB/engine=innodb/' build/*
-bash-4.2$ grep -i innodb build/*
build/mysqlds2_cleanup_generic_template.sql:  engine=innodb;
build/mysqlds2_cleanup_large.sql:  engine=innodb;
build/mysqlds2_cleanup_med.sql:  engine=innodb;
build/mysqlds2_cleanup_small.sql:  engine=innodb;
build/mysqlds2_create_db.sql:  engine=innodb;
build/mysqlds2_create_db.sql:  engine=innodb;
build/mysqlds2_create_db.sql:  engine=innodb; 
build/mysqlds2_create_db.sql:  engine=innodb; 
build/mysqlds2_create_db.sql:  engine=innodb;
build/mysqlds2_create_db.sql:  engine=innodb;
build/mysqlds2_create_db.sql:  engine=innodb;

Try again:

-bash-4.2$ ./mysqlds2_create_all.sh
ERROR 1064 (42000) at line 70: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'TYPE=MyISAM' at line 11
ERROR 1146 (42S02) at line 47: Table 'DS2.PRODUCTS' doesn't exist
ERROR 1146 (42S02) at line 5: Table 'DS2.PRODUCTS' doesn't exist
ERROR 1146 (42S02) at line 5: Table 'DS2.INVENTORY' doesn't exist

Similar issue, so:

-bash-4.2$ sed -i 's/TYPE=MyISAM/engine=myisam/g' build/mysqlds2_create_db.sql
-bash-4.2$ grep -i MyISAM build/*
build/mysqlds2_create_db.sql:  engine=myisam;

… and try again:

-bash-4.2$ ./mysqlds2_create_all.sh
-bash-4.2$ 

Much better. What is there:

MariaDB [DS2]> show tables;
+---------------+
| Tables_in_DS2 |
+---------------+
| CATEGORIES    |
| CUSTOMERS     |
| CUST_HIST     |
| INVENTORY     |
| ORDERLINES    |
| ORDERS        |
| PRODUCTS      |
| REORDER       |
+---------------+
8 rows in set (0.00 sec)

MariaDB [DS2]> select count(*) from PRODUCTS;
+----------+
| count(*) |
+----------+
|    10000 |
+----------+
1 row in set (0.00 sec)


MariaDB [DS2]> show index from CUSTOMERS;
+-----------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table     | Non_unique | Key_name         | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-----------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| CUSTOMERS |          0 | PRIMARY          |            1 | CUSTOMERID  | A         |       20045 |     NULL | NULL   |      | BTREE      |         |               |
| CUSTOMERS |          0 | IX_CUST_USERNAME |            1 | USERNAME    | A         |       20045 |     NULL | NULL   |      | BTREE      |         |               |
+-----------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec)

You can use the same generator script to get a database sized according to your needs:

-bash-4.2$ ./Install_DVDStore.pl 
Please enter following parameters: 
***********************************
Please enter database size (integer expected) : 100
Please enter whether above database size is in (MB / GB) : MB
Please enter database type (MSSQL / MYSQL / PGSQL / ORACLE) : MYSQL
Please enter system type on which DB Server is installed (WIN / LINUX) : LINUX
***********************************
***********************************
Initializing parameters...
***********************************
Database Size: 100 
Database size is in MB 
Database Type is MYSQL 
System Type for DB Server is LINUX 
***********************************

Calculating Rows in tables!! 
Small size database (less than 1 GB) 
Ratio calculated : 10 
Customer Rows: 200000 
Order Rows / month: 10000 
Product Rows: 100000 

Creating CSV files....
Starting to create CSV data files.... 
For larger database sizes, it will take time.
Do not kill the script till execution is complete. 

Creating Customer CSV files!!! 
1 100000 US S 0 
100001 200000 ROW S 0 

Customer CSV Files created!! 

Creating Orders, Orderlines and Cust_Hist csv files!!! 

Creating Order CSV file for Month jan !!! 
1 10000 jan S 1 0 100000 200000 

Creating Order CSV file for Month feb !!! 
10001 20000 feb S 2 0 100000 200000 

Creating Order CSV file for Month mar !!! 
20001 30000 mar S 3 0 100000 200000 

Creating Order CSV file for Month apr !!! 
30001 40000 apr S 4 0 100000 200000 

Creating Order CSV file for Month may !!! 
40001 50000 may S 5 0 100000 200000 

Creating Order CSV file for Month jun !!! 
50001 60000 jun S 6 0 100000 200000 

Creating Order CSV file for Month jul !!! 
60001 70000 jul S 7 0 100000 200000 

Creating Order CSV file for Month aug !!! 
70001 80000 aug S 8 0 100000 200000 

Creating Order CSV file for Month sep !!! 
80001 90000 sep S 9 0 100000 200000 

Creating Order CSV file for Month oct !!! 
90001 100000 oct S 10 0 100000 200000 

Creating Order CSV file for Month nov !!! 
100001 110000 nov S 11 0 100000 200000 

Creating Order CSV file for Month dec !!! 
110001 120000 dec S 12 0 100000 200000 

All Order, Orderlines, Cust_Hist CSV files created !!! 

Creating Inventory CSV file!!!! 

Inventory CSV file created!!!! 

Creating product CSV file!!!! 

Product CSV file created!!!! 

Creating build script for MySQL from templates... 

Template files are stored in respective build folders and the output files are also stored in same folder 

Template files are named with generic_template at end of their filename and the output files without _template at end 

Completed creating and writing build scripts for MySQL database... 

All database build scripts(shell and sql) are dumped into their respective folders. 

These scripts are created from template files in same folders with '_generic_template' in their name. 

Scripts that are created from template files have '_' 100 MB in their name. 

User can edit the sql script generated for customizing sql script for more DBFiles per table and change the paths of DBFiles.

Now Run CreateConfigFile.pl perl script in ds2 folder which will generate configuration file used as input to the driver program

Reload:

-bash-4.2$ ./mysqlds2_create_all.sh
-bash-4.2$ 

And you have a database sized as you want it:

MariaDB [DS2]> select count(*) from PRODUCTS;
+----------+
| count(*) |
+----------+
|   100000 |
+----------+
1 row in set (0.00 sec)

Happy generating πŸ™‚ This should work the same for MySQL.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Daniel Westermann
Daniel Westermann

Principal Consultant & Technology Leader Open Infrastructure