wget https://cf-courses-data.s3.us.cloud-object-storage.appdomain.cloud/IBM-DB0231EN-SkillsNetwork/datasets/World/world_mysql_script.sqlMySQL Administration
Storage Engines
A storage engine is a software component that handles the operations that store and manage information in a database. MySQL is unusual among relational databases because it supports multiple storage engines.
Each storage engine has a particular set of operational characteristics, including the types of locks to manage query contention and whether the storage engine supports transactions. These properties have implications for database performance, so choose your storage engine based on the type of data you want to store and the operations you want to perform.
Most applications require only one storage engine for the whole database, but you can specify the storage engine on a table-by-table basis if your data has different requirements.
Storage engines available in MySQL
| Engines | Description |
| InnoDB |
|
| MyISAM |
|
| MEMORY |
|
| MERGE |
|
| EXAMPLE |
|
| ARCHIVE |
|
| CSV |
|
| BLACKHOLE |
|
| FEDERATED |
|
Commands for working with Storage Engines
If you’re a DBA working with storage engines in MySQL, you should be familiar with the following common commands.
Show engines
Displays status information about the server’s storage engines. Useful for checking whether a storage engine is supported, or what the default engine is.
mysql> SHOW ENGINES;
Create table
Creates a table using the storage engine specified in the ENGINE clause, as shown in the following examples:
CREATE TABLE Products (i INT) ENGINE = INNODB;
CREATE TABLE Product_Codes (i INT) ENGINE = CSV;
CREATE TABLE History (i INT) ENGINE = MEMORY;
If you do not specify the ENGINE clause, the CREATE TABLE statement creates the table with the default storage engine, usually InnoDB.
Set
For databases with non-standard storage needs, you can specify a different default storage engine using the set command.
Set the default storage engine for the current session by setting the default_storage_engine variable using the SET command. For example, if you are creating a database to store archived data, you can use the following command:
SET default_storage_engine=ARCHIVE;
To set the default storage engine for all sessions, set the default-storage-engine option in the my.cnf configuration file.
Alter table
You can convert a table from one storage engine to another using an ALTER TABLE statement. For example, the following statement set the storage enigne for the Products table to Memory:
ALTER TABLE Products ENGINE = MEMORY;
InnoDB is suitable for most data storage needs but setting and working with different storage engines in MYSQL gives you more control over how your data is stored and accessed. Using the most appropriate storage engine for your data brings operational benefits like faster response times or efficient use of available storage.
MySQL Tables Example
In this example we’ll carry out a variety of functions related to selecting and understanding some of the alternative storage engines available in MySQL. We’ll also explore the system tables which contain meta data about the objects in the server
Data
The World database used in this lab comes from the following source: https://dev.mysql.com/doc/world-setup/en/
An older version has been stored and will be used in this example. Here is the ERD diagram showing the schema of the World database
- First row is the table name
- Second row: pk
- Rest of the rows are additional attributes
SQL on the Cloud
In this first section we’ll use the MySQL command line interface (CLI) to create a database, restore the structure and contents of tables, explore and query tables, and finally, learn how to dump/backup tables from the database.
Steps
Terminal > New Terminal to open a terminal
Fetch the file to (in this instance: the cloud IDE)
Get the password:
Download Script
Get the script to create the db from this link
Create Database
mysql> CREATE DATABASE world;
Query OK, 1 row affected (0.02 sec)Use New db
To use the newly created db use this code
mysql> USE world;
Database changedExecute Script to Create Tables
- Execute the script we downloaded above in order to create the tables specified in the script
- This will take time as it’ll create all the rows in all the tables
SOURCE world_mysql_script.sql;List All Tables
mysql> SHOW TABLES;
+-----------------+
| Tables_in_world |
+-----------------+
| city |
| country |
| countrylanguage |
+-----------------+
3 rows in set (0.00 sec)Storage Engines
In MySQL, Storage Engines are components that handle SQL operations for different table types. The default and most general purpose storage engine in MySQL is InnoDB. When you create a new table in MySQL using the CREATE TABLE command in the Command Line Interface, it creates a InnoDB table by default. This is the most widely useful one and is recommended for most general applications except for a few specialized cases.
As detailed in the MySQL documentation, MySQL is built with a pluggable storage engine architecture that allows storage engines to be easily loaded into and unloaded from a running MySQL server.
List Engines
mysql> SHOW ENGINES;
CSV Engine
You can see that the CSV Engine is supported on the current running MySQL server. CSV files, short for Comma Separated Values, are delimited text files that uses a comma to separate values.
Let’s go ahead and try making a table in our database using the CSV storage engine.
To create a new table with a storage engine other than the default InnoDB database, we specify the storage engine we wish to use inside the
CREATE TABLEcommand.Let’s create a new table called “test_csv” using the CSV engine by entering the following command into the CLI:
Create Table
mysql> CREATE TABLE csv_test (i INT NOT NULL, c CHAR(10) NOT NULL) ENGINE = CSV;
Query OK, 0 rows affected (0.02 sec)
mysql> SHOW TABLES;
+-----------------+
| Tables_in_world |
+-----------------+
| city |
| country |
| countrylanguage |
| csv_test |
+-----------------+
4 rows in set (0.01 sec)Insert Data
mysql> INSERT INTO csv_test VALUES(1,'data one'),(2,'data two'),(2,'data
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0
# Review the values added
mysql> SELECT * FROM csv_test;
+---+------------+
| i | c |
+---+------------+
| 1 | data one |
| 2 | data two |
| 2 | data three |
+---+------------+
3 rows in set (0.00 sec)CSV storage engines function in many of the same ways as the default InnoDB engines, however, there are a few limitations. These include not supporting indexing or partitioning.
System Tables
The MySQL server contains a database called
mysql. This is the system database that contains information required for the server to run, such as meta data on all the other tables in the database. This database is one of the special cases where the default InnoDB storage engine is not used. Instead, the tables in themysqldatabase used the MyISAM storage engine. In general, we mostly query the system tables and rarely modify them directly.
The tables in the mysql database fall into several categories, some of which include:
- Grant System Tables
- Object Information System Tables
- Log System Tables
- Server-Side Help System Tables
For your reference, an exhaustive list of the categories can be found in Section 5.7 of the MySQL documentation.
Grant System Table
Let’s take a deeper look at the Grant System Table category. They contain information about the user accounts and the privileges granted to them.
- First, let’s see all the databases on the MySQL server by entering the following command into the CLI:
mysql> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| world |
+--------------------+
5 rows in set (0.00 sec)Connect to mysql
mysql> USE mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changedShow Tables
mysql> SHOW TABLES;
+------------------------------------------------------+
| Tables_in_mysql |
+------------------------------------------------------+
| columns_priv |
| component |
| db |
| default_roles |
| engine_cost |
| func |
| general_log |
| global_grants |
| gtid_executed |
| help_category |
| help_keyword |
| help_relation |
| help_topic |
| innodb_index_stats |
| innodb_table_stats |
| ndb_binlog_index |
| password_history |
| plugin |
| procs_priv |
| proxies_priv |
| replication_asynchronous_connection_failover |
| replication_asynchronous_connection_failover_managed |
| replication_group_configuration_version |
| replication_group_member_actions |
| role_edges |
| server_cost |
| servers |
| slave_master_info |
| slave_relay_log_info |
| slave_worker_info |
| slow_log |
| tables_priv |
| time_zone |
| time_zone_leap_second |
| time_zone_name |
| time_zone_transition |
| time_zone_transition_type |
| user |
+------------------------------------------------------+
38 rows in set (0.01 sec)User Table
The user table contains user accounts, global privileges, and other nonprivilege columns. There are many columns in this table and is a little unwieldy to look at so let’s take a look at just the first column which lists the names of the users in the database.
mysql> SELECT User from user;
+------------------+
| User |
+------------------+
| root |
| mysql.infoschema |
| mysql.session |
| mysql.sys |
| root |
+------------------+
5 rows in set (0.00 sec)Add New User
Let’s add a new user to the database and see if the change is reflected in the user table. We will go into depth about adding users to a database later in the course in the Hands-on Lab: MySQL User Management, Access Control, and Encryption but for now, we’ll just execute a simple command in the CLI that will create a new user named “test_user”
mysql> CREATE USER test_user;
Query OK, 0 rows affected (0.01 sec)
mysql> SELECT User from user;
+------------------+
| User |
+------------------+
| root |
| test_user |
| mysql.infoschema |
| mysql.session |
| mysql.sys |
| root |
+------------------+
6 rows in set (0.00 sec)Information_Schema Tables
The INFORMATION_SCHEMA is a database found inside every MySQL server. It contains meta data about the MySQL server such as the name of a database or table, the data type of a column, or access privileges. Note that this database contains read-only tables, so you cannot directly use any INSERT, UPDATE, or DELETE commands on them. Let’s go ahead and connect to the database.
View all dbs on server
mysql> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| world |
+--------------------+
5 rows in set (0.00 sec)Connect to Information_schema
mysql> USE information_schema
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changedColumns Table
In the information_schema database, there exists a table called COLUMNS which contains meta data about the columns for all tables and views in the server. One of the columns in this table contains the names of all the other columns in every table. Let’s go ahead and look at the names of the columns in the country table in the world database by entering the following command
mysql> SELECT COLUMN_NAME FROM COLUMNS WHERE TABLE_NAME = 'country';
+----------------+
| COLUMN_NAME |
+----------------+
| Code |
| Name |
| Continent |
| Region |
| SurfaceArea |
| IndepYear |
| Population |
| LifeExpectancy |
| GNP |
| GNPOld |
| LocalName |
| GovernmentForm |
| HeadOfState |
| Capital |
| Code2 |
+----------------+
15 rows in set (0.00 sec)Tables Table
Another point of interest in the information_schema database is the TABLES table which contains meta data about all the tables in the server. One of the columns in this table contains information about a table’s storage engine type. To tie this back to our earlier discussion about storage engines, run the following command in the CLI to view the storage engine type for the ‘country’, ‘city’, ‘countrylanguage’, and finally the ‘csv_test’ table you created:
- As expected, the first three tables mentioned use the default InnoDB storage engine, while the ‘csv_test’ table uses the CSV storage engine.
SELECT table_name, engine FROM INFORMATION_SCHEMA.TABLES
WHERE table_name = 'country' OR table_name = 'city'
OR table_name = 'countrylanguage' OR table_name = 'csv_test';
+-----------------+--------+
| TABLE_NAME | ENGINE |
+-----------------+--------+
| city | InnoDB |
| country | InnoDB |
| countrylanguage | InnoDB |
| csv_test | CSV |
+-----------------+--------+
4 rows in set (0.00 sec)Size
The TABLES table in the information_schema database contains information on the the size of a given table in bytes. This information is stored in two columns: data_length and index_length which stores the size of the data in the table and the size of the index file for that table, respectively.
Therefore, the total size of the table is the sum of the values in these two columns. This value would be given in bytes, however, if you wish to use a more convenient unit, the sum can be converted to kB by dividing by 1024. You can find the size of the tables (in kB) you queried in the previous step with the following:
SELECT table_name, (data_length + index_length)/1024 FROM INFORMATION_SCHEMA.TABLES
WHERE table_name = 'country' OR table_name = 'city'
OR table_name = 'countrylanguage' OR table_name = 'csv_test';
+-----------------+-----------------------------------+
| TABLE_NAME | (data_length + index_length)/1024 |
+-----------------+-----------------------------------+
| city | 544.0000 |
| country | 96.0000 |
| countrylanguage | 160.0000 |
| csv_test | 0.0000 |
+-----------------+-----------------------------------+
4 rows in set (0.03 sec)