Managing MariaDB - RHCE Section - RHCSA & RHCE Red Hat Enterprise Linux 7: Training and Exam Preparation Guide (EX200 and EX300), Third Edition (2015) 

RHCSA & RHCE Red Hat Enterprise Linux 7: Training and Exam Preparation Guide (EX200 and EX300), Third Edition (2015)

Part II. RHCE Section

Chapter 25. Managing MariaDB

This chapter describes the following major topics:

·        Introduction to databases and database management systems

·        Understand relational database

·        Overview of MariaDB

·        Recognize MariaDB packages, daemon, commands, and configuration and log files

·        Review SELinux requirements for MariaDB operation

·        Identify MariaDB database and table operations

·        Interact with MariaDB via its shell interface

·        Create and manage database and tables, and query records

·        Understand physical and logical backups

·        Use tools to create and restore logical backups

RHCE Objectives:

83. Install and configure MariaDB

84. Backup and restore a database

85. Create a simple database schema

86. Perform simple SQL queries against a database

MariaDB is an open source database management system software that allows the creation and administration of databases. A database is a structured repository of data that is accessed and administered by a database management software, such as MariaDB. Databases created with MariaDB use the relational model to identify the database structure and data storage, organization, and handling. A relational database uses a table-based format.

Understanding Databases, DBMS, and MariaDB

database is a structured collection of data, which is comprised of facts and figures of something, and can be processed to generate meaningful results. An example of “something” is a database that stores data about airlines, hotels, car rentals, and vacation packages. People access this database via a website to search and make bookings and reservations using a number of different combinations and options that are configured in the database. Moreover, the database also provides people with the ability to limit their searches to a specific service or expand to include two or more.

Databases are widely used in both public and private sectors, ranging from banking and financial services companies to manufacturing, telecommunication, airline, technology, education, research, libraries, hoteling, and government agencies. They are used for storage and retrieval of data to meet general and specific business requirements.

A database with a large amount of stored data accessed by a number of users concurrently using a variety of combinations and options requires a database management system (DBMS) that is able to store, manage, and manipulate that data. A DBMS is a software application, such as MariaDB, MySQL, Oracle, IBM DB2, Sybase, Ingress, Informix, PostgreSQL, and Microsoft SQL Server. Such management systems allow the definition, creation, configuration, administration, performance management, backup, and recovery of a database; storage, modification, and deletion of data in the database; and querying and retrieving of that data to produce desired reports for users or to feed to a requesting application. Furthermore, a DBMS also allows the administrator to place security controls on users in terms of what they can do.

Databases can be accessed by directly logging in to them. This type of access is typically granted to database architects and administrators who are responsible for their design, build, and management. Databases are accessed by end users or applications through other applications for data query and retrieval. This type of access does not require direct logging into the database.

What is a Relational Database?

relational database is a type of database that is structured based on the relational model suggested by Edgar. F. Codd in 1970. It is a set of tables comprising rows (a.k.a. tuples or records) and columns (a.k.a. fields or attributes) for data storage and organization. Each row represents a single record, which is made up of column values. A column is made up of the same type of values. See Figure 25-1 for the three table components: row, column, and value.

Images

Figure 25-1 Table Components

In order to protect against entering duplicate records in a table, a column with unique values for each record is identified, and defined as a primary key. In Figure 25-1, column “Sno” can be chosen as a primary key, as it contains a distinct value for each record.

ImagesA table is like a simple spreadsheet.

For flexible data storage, organization, and retrieval, MariaDB allows the creation and use of multiple tables in a database to store distinct records about something, and then linking the tables together using a unique key. For instance, we can have a database with two tables, one consisting of the records shown in Figure 25-1, and the other with data about their inventions and a “Sno”. We can then define the “Sno” column as a foreign key to establish a relationship between the two tables. A user querying record “1” will get information from both tables. This is an example of a one-to-one table relationship. MariaDB also supports one-to-many and many-to-many relationships.

Overview of MariaDB

MariaDB is a relatively new name in the RDBMS space. It is an enhanced binary replacement for the famous relational database management software MySQL, which was bought by Sun Microsystems (now Oracle) in 2008. Following the acquisition, the development of MySQL slowed down and there was less involvement from the community due to internal company policies. This led the original developers of MySQL to fork MariaDB with the intent to keep it free under GNU GPL and re-open the doors for its development to the community. The latest stable version of MariaDB is 10.0, which is equivalent to MySQL version 5.6 with several enhancements. MariaDB versions up to 5.5 followed MySQL version numbering; however, MariaDB developers later decided to change it to start at 10.0.

MariaDB Software Packages and Service Daemon

In order to configure the MariaDB service, several software packages need to be installed on the system. These packages add necessary support to the operating system, and bring the tools and configuration files that are used for setting it up. Table 25-1 describes these packages.

Package

Description

mariadb

Provides MariaDB client programs and a configuration file.

mariadb-server

Contains MariaDB server, tools, and configuration and log files.

Mariadb-libs

Comprises of essential library files for MariaDB client programs.

Table 25-1 MariaDB Software Packages

We may use the yum command as shown below to check the install status of these packages:

image

The MariaDB server package also loads the mysqld daemon binary file in addition to commands and configuration files. This daemon process listens on port 3306 and supports both TCP and UDP protocols for operation. It must run on the system to allow client access.

MariaDB Commands

MariaDB offers several commands for administration and query. However, only the commands we use in this chapter are described in Table 25-2.

Command

Description

mysql

Command line shell interface for administration and query.

mysql_secure_installation

Improves the security of MariaDB installation.

mysqldump

Backs up or restores one or more table or database.

Table 25-2 MariaDB Commands

The mysql shell interface is explained shortly, and the three tools listed in Table 25-2 are used in this chapter.

MariaDB Configuration Files

The primary configuration file for MariaDB is the /etc/my.cnf, which sets global defaults for mysql shell program, mysqld_safe startup script, and the mysqld daemon process. The uncommented line entries from this file are presented below:

grep –v ^# /etc/my.cnf

[mysqld]

datadir=/var/lib/mysql

socket=/var/lib/mysql/mysql.sock

symbolic-links=0

[mysqld_safe]

log-error=/var/log/mariadb/mariadb.log

pid-file=/var/run/mariadb/mariadb.pid

!includedir /etc/my.cnf.d

The my.cnf file contains two configuration groups by default. These groups are [mysqld] and [mysqld_safe] with some settings under each one of them. Their purpose is to separate the configuration needed by the mysqld daemon, mysqld_safe startup program, and mysql client program (client group not included in the default file) at startup. The directives in this file set several defaults, including the locations to store database files, log files, and the PID file. The includedir directive at the bottom of the file instructs the startup program to look for additional configuration files in the/etc/my.cnf.d directory and process them if they exist. By default, there are three configuration files in this directory, which are listed below:

image

These files set configurations for general clients, specific MariaDB client tools, and MariaDB server program mysqld, respectively. The default files have several configuration groups defined, but they all are empty.

For our purposes in this chapter, there is no need to modify any of these files.

Logging MariaDB Messages

The default file for storing MariaDB logs is mariadb.log located in the /var/log/mariadb directory, as defined in the /etc/my.cnf file. This log file captures all errors, alerts, warnings, informational, and other general messages related to MariaDB. Some sample entries from this file are shown below:

150220 7:39:15 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql

150220 7:39:15 InnoDB: The InnoDB memory heap is disabled

150220 7:39:15 InnoDB: Mutexes and rw_locks use GCC atomic builtins

150220 7:39:15 InnoDB: Compressed tables use zlib 1.2.7

150220 7:39:15 InnoDB: Using Linux native AIO

150220 7:39:15 InnoDB: Initializing buffer pool, size = 128.0M

150220 7:39:15 InnoDB: Completed initialization of buffer pool

InnoDB: The first specified data file ./ibdata1 did not exist:

InnoDB: a new database to be created!

150220 7:39:15 InnoDB: Setting file ./ibdata1 size to 10 MB

InnoDB: Database physically writes the file full: wait…

150220 7:39:15 InnoDB: Log file ./ib_logfile0 did not exist: new to be created

InnoDB: Setting log file ./ib_logfile0 size to 5 MB

InnoDB: Database physically writes the file full: wait…

150220 7:39:15 InnoDB: Log file ./ib_logfile1 did not exist: new to be created

InnoDB: Setting log file ./ib_logfile1 size to 5 MB

InnoDB: Database physically writes the file full: wait…

InnoDB: Doublewrite buffer not found: creating new

InnoDB: Doublewrite buffer created

InnoDB: 127 rollback segment(s) active.

InnoDB: Creating foreign key constraint system tables

InnoDB: Foreign key constraint system tables created

150220 7:39:16 InnoDB: Waiting for the background threads to start

150220 7:39:17 Percona XtraDB (http://www.percona.com) 5.5.35-MariaDB-33.0 started; log sequence number 0

150220 7:39:17 [Note] Plugin ‘FEEDBACK’ is disabled.

150220 7:39:17 [Note] Server socket created on IP: ‘0.0.0.0’.

150220 7:39:17 [Note] Event Scheduler: Loaded 0 events

150220 7:39:17 [Note] /usr/libexec/mysqld: ready for connections.

Version: ‘5.5.35-MariaDB’ socket: ‘/var/lib/mysql/mysql.sock’ port: 3306 MariaDB Server

This file may be referenced for troubleshooting, auditing, or other similar purposes.

Managing MariaDB

Managing MariaDB involves several database administration tasks, including various operations performed on databases and tables. These tasks include the installation of MariaDB software packages; listing, creating, switching into, and dropping databases; creating, describing, listing, renaming, and dropping tables; inserting, updating, querying, and deleting records in tables; and backing up and restoring databases and tables. These tasks are described in this section, along with demonstrations and hands-on exercises.

SELinux Requirements for MariaDB Operation

SELinux protects systems by setting appropriate controls using contexts and booleans. Before you proceed with the exercises, let’s look at MariaDB-specific SELinux contexts on processes, files, and port, and also look at the booleans that may need to be modified for MariaDB to function properly. By default, the mysqld daemon process runs confined in its own domain, and it is labeled appropriately with domain type mysqld_t. This can be confirmed with the ps command:

image

The SELinux file type associated with the mysqld daemon file is mysql_exec_t, configuration files in the /etc/my.cnf.d directory is etc_t, database files in the /var/lib/mysql directory is mysqld_db_t, and log files located in the /var/log/mariadb directory is mysql_log_t. Here is an ll command output that verifies this information:

image

The SELinux type associated with mysqld port is mysqld_port_t, and it is in place by default. Here is the semanage command output that validates this information:

semanage port –l | grep mysqld

mysqld_port_t tcp 1186, 3306, 63132-63164

From a SELinux boolean perspective, there are two booleans that are associated with MariaDB, and we can see this with the getsebool command:

getsebool –a | grep mysql

mysql_connect_any → off

selinuxuser_mysql_connect_enabled → off

These booleans allow or disallow the mysqld daemon process to connect to any port and users to connect to the local MariaDB server, respectively. Both booleans are turned off by default and do not need to be toggled for the exercises in this chapter.

By looking at the above SELinux settings for MariaDB, we can establish that there are no changes required to make it function smoothly in the SELinux enforcing mode.

Exercise 25-1: Install and Configure MariaDB

This exercise should be done on server1.

In this exercise, you will install the MariaDB server software, including any dependent packages. You will secure its installation and set up a password for the root user account to access MariaDB.

1.   Install the MariaDB server software package:

yum –y install mariadb-server

…… . .

Installed:

mariadb-server.x86_64 1:5.5.35-3.el7

Dependency Installed:

  mariadb.x86_64 1:5.5.35-3.el7

Complete!

2.   Set MariaDB service to autostart at system reboots:

systemctl enable mariadb

ln –s ‘/usr/lib/systemd/system/mariadb.service’ ‘/etc/systemd/system/multi-user.target.wants/mariadb.service’

3.   Secure access to MariaDB installation using the mysql_secure_installation script and enter information as highlighted:

image

4.   Add the MariaDB service to firewalld persistently and reload the rules to allow traffic on port 3306:

firewall-cmd --permanent --add-service mysql ; firewall-cmd --reload

5.   Start the MariaDB service and check its operational status:

systemctl start mariadb

systemctl status mariadb

mariadb.service – MariaDB database server

Loaded: loaded (/usr/lib/systemd/system/mariadb.service; enabled)

Active: active (running) since Fri 2015-02-20 07:39:18 EST; 5s ago

Process: 11225 ExecStartPost=/usr/libexec/mariadb-wait-ready $MAINPID (code=exited, status=0/SUCCESS)

Process: 11146 ExecStartPre=/usr/libexec/mariadb-prepare-db-dir %n (code=exited, status=0/SUCCESS)

Main PID: 11224 (mysqld_safe)

Cgroup: /system.slice/mariadb.service

├─11224 /bin/sh /usr/bin/mysqld_safe –basedir…

└─11382 /usr/libexec/mysqld –basedir=/usr –d…

…… . .

This completes the installation and initial configuration of MariaDB service.

Starting the MariaDB Shell and Understanding its Usage

Once you have the MariaDB server software installed and root user password setup, you can invoke its shell interface with the mysql command. Enter the user password when prompted.

mysql –u root –p

Enter password:

Welcome to the MariaDB monitor. Commands end with ; or \g.

Your MariaDB connection id is 26

Server version: 5.5.35-MariaDB MariaDB Server

Copyright © 2000, 2013, Oracle, Monty Program Ab and others.

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.

MariaDB [(none)]>

The MariaDB shell prompt appears, indicating that you have successfully logged in to it. The prompt indicates that there is no current connection to a database. There are several subcommands available here, so type help for a list:

image

All subcommands have a short description indicating what they are used for. We will use the statususe, and exit subcommands in this chapter.

Displaying Connection Status

The status subcommand at the MariaDB prompt can be used to display the connection status:

image

The output provides general information about the connection, including an ID assigned to this session, the database it is connected to, the user name who invoked this session, and other miscellaneous information.

Subcommands for Database and Table Operations

The mysql MariaDB interface offers several subcommands for database management. Table 25-3 describes them.

Subcommand

Description

Database and Table Operations

create

Creates a database or table.

drop

Drops a database or table.

show

Lists databases or tables.

Table Operations

delete

Removes a record from a table.

describe (or desc)

Shows the structure of a table.

insert

Inserts data into a table.

rename

Renames a table.

select

Retrieves data from a table.

update

Updates a record in a table.

Table 25-3 Database Administration Commands

Table 25-3 separates subcommands specific to both databases and tables, and tables only. Typical operations performed on both databases and tables include creating, dropping, and listing (or showing) them. Likewise, typical operations for table-only administration include the rename function, as well as inserting, updating, querying, and deleting records in tables.

Each subcommand executed at the MariaDB prompt expects a semicolon (:) at the end.

The usage of these subcommands is demonstrated in the following exercises.

Exercise 25-2: Create Database and Table, and Insert Records

This exercise should be done on server1.

In this exercise, you will create a database called rhce. You will create a table called scientists in this database using column names as shown in Table 25-4 below.

Images

Table 25-4 For use with Exercise 25-2

You will insert all the records as shown in Table 25-4 into the table and confirm their addition.

1.   List what databases are available on the system using the show subcommand:

Images

It shows the presence of two default databases.

2.   Create a database called rhce using the create subcommand:

MariaDB [none]> create database rhce;

Query OK, 1 row affected (0.00 sec)

3.   Verify the database creation using the show subcommand:

Images

The output shows a new database called rhce on the list.

4.   Select the new database for further actions using the use subcommand:

MariaDB [(none)]> use rhce;

Database changed

MariaDB [rhce]>

Observe that the prompt has changed to reflect the selected database.

5.   Create a table called scientists in the rhce database using the create subcommand. For text columns, limit the number of characters to 20, and use integer type for Sno and Age columns.

MariaDB [rhce]> create table scientists (Sno int,FirstName varchar(20),LastName varchar(20),City varchar(20),Country varchar(20),Age int);

Query OK, 0 rows affected (0.10 sec)

6.   Display the structure of the table with the describe (or desc) subcommand:

Images

The output shows six columns. The first column indicates the field name, the second column specifies the type of data that can be stored in the field and any character limits that are set, the third column can be left to a null value, the fourth column denotes whether the field is a table key, the fifth column shows the default value for the field, and the last column displays any special properties associated with the field.

7.   Insert all the records to the table using the insert subcommand and ensuring that values are enclosed within quotes:

MariaDB [rhce]> insert into scientists values(‘1’,‘Albert’,’Einstein’,’Ulm’,’Germany’,’76’);

MariaDB [rhce]> insert into scientists values(‘2’,‘Isaac’,’Newton’,’Woolsthorpe’,’UK’,’84’);

MariaDB [rhce]> insert into scientists values(‘3’,‘Marie’,’Curie’,’Warsaw’,’Poland’,’67’);

MariaDB [rhce]> insert into scientists values(‘4’,‘Galileo’,’Galilei’,’Pisa’,’Italy’,’78’);

MariaDB [rhce]> insert into scientists values(‘5’,‘Thomas’,’Edison’,’Milan’,’USA’,’84’);

MariaDB [rhce]> insert into scientists values(‘6’,‘Alexander’,’Bell’,’Edinburg’,’UK’,’75’);

MariaDB [rhce]> insert into scientists values(‘7’,‘Louis’,’Pasteur’,’Dole’,’France’,’73’);

MariaDB [rhce]> insert into scientists values(‘8’,‘Nicolaus’,’Copernicus’,’Toruri’,’Poland’,’70’);

MariaDB [rhce]> insert into scientists values(‘9’,‘James’,’Maxwell’,’Edinburg’,’UK’,’48’);

MariaDB [rhce]> insert into scientists values(‘10’,‘Pierre’,’Curie’,’Paris’,’France’,’47’);

8.   Query all the records to confirm their addition to the table using the select subcommand:

Images

The above confirms the addition of all ten records to the scientists table in the rhce database.

Exercise 25-3: Perform SQL Queries against a Database

This exercise should be done on server1.

In this exercise, you will perform a number of queries against the database rhce.

1.   Query all stored records:

Images

2.   Query records for all scientists who died at the age of 84:

Images

3.   Query records for all scientists who lived for 75 or more years:

Images

4.   Query records for all scientists with last name “Curie”:

Images

5.   Query records for all scientists who were born in Poland and Germany:

Images

6.   Query records for all scientists sorted by their first names:

Images

7.   Query records for all scientists sorted by their last names in descending (desc) order:

Images

8.   Query records for all scientists who were born in countries starting with the letter U and followed by any letters (the % sign is used as a wildcard character):

Images

9.   Query records for all scientists who were born in France, Germany, and Italy, with output sorted in reverse on the first column:

Images

10.   Query records for all scientists who died in their 70s:

Images

Exercise 25-4: Rename Table, and Update and Delete Records

This exercise should be done on server1.

In this exercise, you will rename the table scientists to science without affecting the data that it contains. You will update two records in the table and then delete them.

1.   Rename the table scientists to science using the rename subcommand:

MariaDB [rhce]> rename table scientists to science;

Query OK, 0 rows affected (0.37 sec)

2.   Confirm the above change:

Images

3.   Verify that the records in the renamed table are unaffected:

Images

4.   Replace the fields Albert Einstein with Benjamin Franklin using the update subcommand:

MariaDB [rhce]> update science set FirstName=’Benjamin’,LastName=’Franklin’ where Sno=’1’;

Query OK, 1 row affected (0.06 sec)

Rows matched: 1 Changed: 1 Warnings: 0

5.   Confirm the above change by querying the table:

Images

6.   Replace the fields Paris and 73 for Sno 7 with Cannes and 75 using the update subcommand:

MariaDB [rhce]> update science set City=’Cannes’,Age=’75’ where Sno=’7’;

Query OK, 1 row affected (0.04 sec)

Rows matched: 1 Changed: 1 Warnings: 0

7.   Confirm the above change:

Images

8.   Delete record numbers 1 and 7 from the table using the delete subcommand:

MariaDB [rhce]> delete from science where Sno=’1’ or Sno=’7’;

Query OK, 2 rows affected (0.36 sec)

9.   Confirm the deletion:

Images

Backing Up and Restoring a Database or Table

Backup is a function of duplicating data to an alternative location for use in the event of a data loss. The alternative location is usually an external physical medium attached to the system locally or accessible via the network. A backup can also be redirected to a file for some specific use.

Restore is the opposite function of backup. It retrieves data from a backup location and puts it back to its original place.

There are two types of backups: physical backups and logical backups. A physical backup is a backup of actual files that may belong to an operating system, a database, or some other application. In contrast, a logical backup consists of SQL statements necessary to restore the data in case of data loss. This type of backup does not include database configuration or log files. A logical backup can be used for data migration from one server to another.

MariaDB offers a tool called mysqldump for creating logical backups of data. To restore logical backups, the mysql tool can be used. The syntax to employ these tools for backup and restore is provided below.

To backup all databases on the system, use the --all-databases option with the command and redirect the output to a file:

mysqldump –u root –p --all-databases > db_bkp_all.sql

To restore a specific database, such as DB1, from the above:

mysql –u root –p DB1 < db_bkp_all.sql

To backup specific databases, use the --databases option with the command and specify the database names to be backed up:

mysqldump –u root –p --databases DB1 DB2 DB3 > db_bkp_db123.sql

To restore all three databases from the above:

mysql –u root –p DB1 DB2 DB3 < db_bkp_db123.sql

To backup specific tables, such as tbl1 and tbl2, located in a database called DB1:

mysqldump –u root –p DB1 tbl1 tbl2 > tbl_bkp_tbl12.sql

To restore only tbl1 from the above:

mysql –u root –p DB1 tbl1 < tbl_bkp_tbl12.sql;

Exercise 25-5: Backup, Drop, Recreate, and Restore a Database

This exercise should be done on server1.

In this exercise, you will create a dump of the database rhce in a file in the /tmp directory and then drop the entire database. You will recreate the database and restore the original structure from the dump.

1.   Create a dump of the rhce database using the mysqldump command from the OS prompt and store it in rhce_db.sql file in the /tmp directory:

mysqldump –u root –p rhce > /tmp/rhce_db.sql

Enter password:

2.   Drop the rhce database:

mysql –u root –p

…… . .

MariaDB [(none)]> drop database rhce;

Query OK, 1 row affected (0.05 sec)

3.   Confirm the removal of the database:

Images

4.   Recreate the rhce database and exit the MariaDB shell:

MariaDB [(none)]> create database rhce;

Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> quit

5.   Restore the rhce database from the /tmp/rhce_db.sql dump using the mysql command:

mysql –u root –p rhce < /tmp/rhce_db.sql

Enter password:

6.   Log back in with the rhce database selected and verify the presence of the science table:

Images

7.   Query the science table; you should be able to see all the records before the database was deleted:

Images

Now that you have completed all of the exercises, you may go ahead and drop the table or the database. To remove the table along with all the records that it contains, run the drop subcommand and verify with show:

MariaDB [(rhce)]> drop table science;

MariaDB [(rhce)]> show tables;

Similarly, you can drop the database along with all the tables that it contains:

MariaDB [(rhce)]> drop database rhce;

MariaDB [(rhce)]> show databases;

If you want to restore the database, the dump is in place in the /tmp directory.

Chapter Summary

This chapter presented MariaDB database software and discussed its administration. The chapter introduced the concepts of databases, database management systems, and relational databases, and provided an overview of MariaDB and how it originated. Just like any other network service, MariaDB has its own service daemon, commands, configuration files, log file, service port, SELinux requirements, and so on. The various database administration operations that are executed on databases and enclosed objects were also discussed. A few exercises were presented to strengthen this learning. Finally, the chapter discussed backup and restore functions, and how to use the available tools to perform these tasks.

Chapter Review Questions

1.     Define the term “database” in a simple sentence.

2.     What does DBMS stand for?

3.     What are the three major components in a table?

4.     A relational database has two or more tables linked by a key. True or False?

5.     What is the difference between a primary key and a foreign key?

6.     MariaDB is a fork of MySQL database. True or False.

7.     What is the name of the server daemon for MariaDB?

8.     Which MariaDB software package contains the mysql shell interface program?

9.     Which port is opened in the firewall to allow network access to MariaDB?

10.  What are the six operations mentioned in this chapter that can be performed on a database?

11.  What are the eleven operations mentioned in this chapter that can be performed on a table?

12.  Write the mysql command to create a database called rhcsa.

13.  Write a query to display all records in a table matching the name “Abraham”.

14.  What are the two major types of backups?

15.  What is included in a logical backup?

16.  Which command is used to create logical database backups?

Answers to Chapter Review Questions

1.     A database is a collection of data.

2.     DBMS is an acronym for Database Management System.

3.     A table is composed of rows, columns, and values.

4.     True.

5.     A primary key is used within a table to uniquely identify a record, whereas a foreign key is used to relate two or more tables.

6.     True.

7.     The MariaDB daemon name is mysqld.

8.     The mariadb software package includes this client program.

9.     MariaDB server port number is 3306.

10.  The five operations that can be performed on a database are create, list, use, drop, backup, and restore.

11.  The eleven operations that can be performed on a table are create, describe, list, rename, drop, backup, restore, insert, update, query, and delete.

12.  The command to create database called rhcsa will be “create database rhcsa;”.

13.  The query statement would be “select * from <table> where name=’Abraham’;”.

14.  The two major types of backups are physical and logical backups.

15.  A logical backup includes the SQL statements necessary to rebuild a database.

16.  The mysqldump command is used to create logical backups of databases.

DIY Challenge Labs

The following labs are useful to strengthen the concepts and topics learned in this chapter. It is expected that you perform these labs without any additional help. A step-by-step guide is not provided, as the implementation of these labs requires the knowledge that has been presented in this chapter. Use defaults or your own thinking for missing information.

Lab 25-1: Create a Database and Table, and Add Records

Install MariaDB software for both server and client. Create a database called db1 and create a table called tb1 in it. The table should include data about the five tallest skyscrapers in the world. It should have five columns: Height, Skyscraper Name, City, Country, and Continent. Perform SQL queries on the building records using different criteria.

Images

Lab 25-2: Update and Delete Records

Delete the first three records from db1 and replace them with the three tallest concrete towers in the world (below table). Update the last two records in db1 with the fourth and fifth tallest concrete structures (below table). Perform SQL queries using different criteria.

Images

Lab 25-3: Rebuild a Database

Take a logical backup of the db1 database and then drop the database. Rebuild the database and confirm the table and all records in it.