Mastering Database Operations: Index, View, Backup, and Recovery

Introduction

In this lab, we will learn and practice index, view, backup and recovery. These concepts are very crucial for a database manager.

Learning Objective

Create index
Create view
Backup and Recovery

Preparation

Before we start, we need to prepare the environment.

Start MySQL service and log in as root.

cd ~/project
sudo service mysql start
mysql -u root

Load data in the file. You need to enter the command in the MySQL console to build the database:

source ~/project/init-database.txt

Index

The index is a table-related structure. Its role is equivalent to a book's directory. You can quickly find the content according to the page number in a directory.

When you want to query a table that has a large number of records, and it does not have an index, then all records will be pulled out to match the search conditions one by one, and return the records that match the conditions. It is very time-consuming and results in a large number of disk I/O operations.

If an index exists in the table, then we can quickly find the data in the table by the index value, which greatly speeds up the query process.

There are two ways to set up an index to a particular column:

ALTER TABLE table name ADD INDEX index name (column name);

CREATE INDEX index name ON table name (column name);

Let's use these two statements to build an index.

Build an idx_id index in the id column in the employee table:

ALTER TABLE employee ADD INDEX idx_id (id);

build an idx_name index in the name column in the employee table

CREATE INDEX idx_name ON employee (name);

We use the index to speed up the query process. When there is not enough data, we won't be able to feel its magic power. Here let's use the command SHOW INDEX FROM table name to view the index that we just created.

SHOW INDEX FROM employee;

MariaDB [mysql_labex]> ALTER TABLE employee ADD INDEX idx_id (id);
Query OK, 0 rows affected (0.005 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [mysql_labex]> SHOW INDEX FROM employee;
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
| Table    | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Ignored |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
| employee |          0 | PRIMARY  |            1 | id          | A         |           5 |     NULL | NULL   |      | BTREE      |         |               | NO      |
| employee |          0 | phone    |            1 | phone       | A         |           5 |     NULL | NULL   |      | BTREE      |         |               | NO      |
| employee |          1 | emp_fk   |            1 | in_dpt      | A         |           5 |     NULL | NULL   |      | BTREE      |         |               | NO      |
| employee |          1 | idx_id   |            1 | id          | A         |           5 |     NULL | NULL   |      | BTREE      |         |               | NO      |
| employee |          1 | idx_name |            1 | name        | A         |           5 |     NULL | NULL   | YES  | BTREE      |         |               | NO      |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
5 rows in set (0.000 sec)

When we use the SELECT statement to query, the WHERE condition will automatically judge whether there exists an index.

View

The view is a virtual table derived from one or more tables. It is like a window through which people can view special data provided by the system so that they won't have to view the entire data in the database. They can focus on the ones they're interested in.

How to interpret "View is a virtual table"?

Only the definition of View is stored in the database while its data is stored in the original table;
When we use View to query data, the database will extract data from the original table correspondingly.
Since data in View is dependent on what's stored in the original table, once data in the table has changed, what we see in View will change as well.
Treat View as a table.

Statement format used to create View:

CREATE VIEW view name (column a, column b, column c) AS SELECT column 1, column 2, column 3 FROM table name;

From the statement, we can see that the latter part is a SELECT statement, which means View can also be built on multiple tables. All we need to do is use subqueries or join in the SELECT statement.

Now let's create a simple View named v_emp which includes three columns v_name, v_age, v_phone:

CREATE VIEW v_emp (v_name,v_age,v_phone) AS SELECT name,age,phone FROM employee;

and then enter

SELECT * FROM v_emp;

MariaDB [mysql_labex]> CREATE VIEW v_emp (v_name,v_age,v_phone) AS SELECT name,age,phone FROM employee;
Query OK, 0 rows affected (0.003 sec)

MariaDB [mysql_labex]> SELECT * FROM v_emp;
+--------+-------+---------+
| v_name | v_age | v_phone |
+--------+-------+---------+
| Tom    |    26 |  119119 |
| Jack   |    24 |  120120 |
| Jobs   |  NULL |   19283 |
| Tony   |  NULL |  102938 |
| Rose   |    22 |  114114 |
+--------+-------+---------+
5 rows in set (0.000 sec)

Backup

For security reasons, backup is extremely important in database management.

Exported file only saves the data in a database while backup saves the entire database structure including data, constraints, indexes, view etc. to a new file.

mysqldump is a practical program in MySQL for backup. It produces an SQL script file that contains all essential commands to recreate a database from scratch, such as CREATE, INSERT etc.

Statement to use mysqldump backup:

mysqldump -u root database name > backup file name;   #backup entire database

mysqldump -u root database name table name > backup file name;  #backup the entire table

Try backing up the entire database mysql_labex. Name the file to bak.sql. First press Ctrl+Z to exit the MySQL console, then open the terminal and enter the command:

cd ~/project/
mysqldump -u root mysql_labex > bak.sql;

Use the command "ls" and we'll see the backup file bak.sql;

cat bak.sql

-- MariaDB dump 10.19  Distrib 10.6.12-MariaDB, for debian-linux-gnu (x86_64)
--
-- Host: localhost    Database: mysql_labex
-- ------------------------------------------------------
-- Server version       10.6.12-MariaDB-0ubuntu0.22.04.1

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

……

Recovery

Earlier in this lab, we practiced using a backup file to recover the database. We used a command similar to this:

source ~/project/init-database.txt

This statement recovers the mysql_labex database from the import-database.txt file.

There is another way to recover the database, but before that, we need to create an empty database named test first:

mysql -u root
CREATE DATABASE test;

MariaDB [(none)]> CREATE DATABASE test;
Query OK, 1 row affected (0.000 sec)

MariaDB [(none)]> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| mysql_labex        |
| performance_schema |
| sys                |
| test               |
+--------------------+
6 rows in set (0.000 sec)

Ctrl+Z to exit MySQL. Recover the bak.sql to test the database:

mysql -u root test < bak.sql

We can confirm whether the recovery is successful or not by entering a command to view tables in the test database:

mysql -u root
USE test
SHOW TABLES

MariaDB [(none)]> USE test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [test]> SHOW TABLES;
+----------------+
| Tables_in_test |
+----------------+
| department     |
| employee       |
| project        |
| table_1        |
+----------------+
4 rows in set (0.000 sec)

We can see that the 4 tables have already been recovered to the test database.

Summary

Congratulations! You've completed the lab on other basic operations in MySQL. You've learned how to create indexes, views, and how to backup and recover a database.

Other Basic Operations