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

Labby - Aug 17 - - Dev Community

Introduction

MindMap

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
Enter fullscreen mode Exit fullscreen mode

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

source ~/project/init-database.txt
Enter fullscreen mode Exit fullscreen mode

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);
Enter fullscreen mode Exit fullscreen mode

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);
Enter fullscreen mode Exit fullscreen mode

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

CREATE INDEX idx_name ON employee (name);
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode 
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)
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

and then enter

SELECT * FROM v_emp;
Enter fullscreen mode Exit fullscreen mode 
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)
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

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

cat bak.sql
Enter fullscreen mode Exit fullscreen mode 
-- 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 */;

……
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode 
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)
Enter fullscreen mode Exit fullscreen mode

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

mysql -u root test < bak.sql
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode 
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)
Enter fullscreen mode Exit fullscreen mode

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.

🚀 Practice Now: Other Basic Operations

Want to Learn More?

Image
Why You Should Focus On The Improvement Of Asbestos Compensation
Image
How Asbestos Attorney Lawyer Mesothelioma Rose To The #1 Trend On Social Media
Image
15 Things You Don't Know About Wall Hung Electric Fires
Image
20 Things You Need To Know About Best Wall Mounted Electric Fires
Image
What's The Job Market For Asbestos Litigation Professionals Like?
Image
Ethical Considerations in AI-Driven Software Testing
Image
You'll Never Guess This Truck Key Copy's Benefits
Image
How To Beat Your Boss In The Key Truck
Image
10 Inspirational Graphics About Truck Key Cutting
Image
Panduan Hindarkan Agen Judi Online Penipu
Image
tanda-tanda dan syarat-syarat dari situs slots online terpilih
Image
hal yang perlu dicegah saat main slots online
Image
The 10 Scariest Things About Mesothelioma Attorneys
Image
13 Things You Should Know About Key Truck That You Might Not Have Known
Image
8 Tips To Improve Your Attorney For Asbestos Game
Image
What Can A Weekly Assessment In Mental Health Project Can Change Your Life
Image
Watch Out: How Accident Lawyer Tulsa Is Taking Over And What To Do About It
Image
5 Must-Know Hismphash Practices You Need To Know For 2023
Image
10 Things You've Learned In Kindergarden To Help You Get Accident Lawyer Boston
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .