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:
[root@mysqldell ~] cat /etc/centos-release CentOS Linux release 7.2.1511 (Core) [root@mysqldell ~] yum install -y mariadb-server.x86_64
Having the binaries available lets enable and start the service:
[root@mysqldell ~] systemctl list-unit-files | grep maria mariadb.service disabled [root@mysqldell ~] systemctl enable mariadb.service Created symlink from /etc/systemd/system/multi-user.target.wants/mariadb.service to /usr/lib/systemd/system/mariadb.service. [root@mysqldell ~] systemctl start mariadb.service
This should have started my MariaDB instance:
[root@mysqldell ~] 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?
[root@mysqldell ~] 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:
[root@mysqldell ~] usermod -s /bin/bash mysql [root@mysqldell ~] 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.