OLTP database - MySQL

Objectives

I’ll be working this project at times on the cloud and at times on the local machine. We’ll start with these steps:

  • design the schema for OLTP database.
  • load data into OLTP database.
  • automate admin tasks.

Start MySQL Server

From the cloud, choose DataBases > MySQL > Create an instance > MySQL CLI >

Create DB

mysql> CREATE DATABASE sales;

Query OK, 1 row affected (0.00 sec)

Create table named sales_data based on the image below

# Change to the correct db
mysql> USE sales;
Database changed

CREATE TABLE sales_data (
                          product_id INT NOT NULL,
                          customer_id INT NOT NULL,
                          price DECIMAL(10,2) NOT NULL,
                          quantity INT,
                          timestamp TIMESTAMP
                        );
Query OK, 0 rows affected (0.03 sec)

mysql> SHOW tables;
+-----------------+
| Tables_in_sales |
+-----------------+
| sales_data      |
+-----------------+
1 row in set (0.00 sec

Load Data

Count Rows

mysql> SELECT count(*) FROM sales_data;
+----------+
| count(*) |
+----------+
|     2605 |
+----------+
1 row in set (0.01 sec)

Create an Index

Create an index named ts on the timestamp field.

mysql> CREATE INDEX ts ON sales_data(timestamp);

Query OK, 0 rows affected (0.09 sec)
Records: 0  Duplicates: 0  Warnings: 0

List Indexes

mysql> SHOW INDEX FROM sales_data;
+------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table      | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| sales_data |          1 | ts       |            1 | timestamp   | A         |        2605 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
+------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
1 row in set (0.01 sec)

Write Script to Export Data

Write a bash script named datadump.sh that exports all the rows in the sales_data table to a file named sales_data.sql

Take a screenshot of the contents of the datadump.sh bash file command you used and the output.

Name the screenshot as exportdata.jpg. (images can be saved with either .jpg or .png extension)

End of assignment.

# In new terminal
touch datadump.sh
# this will open up a file enter this into the file, save it


#!/bin/bash


# Backup the MySQL database
mysqldump sales > sales_data.sql

echo "Backup completed: sales_data.sql"
# Run the bash file to backup the db
~:/home/project$ touch datadump.sh
~:/home/project$ bash datadump.sh

Backup completed: sales_data.sql