MySQL / MariaDB Database Administration and SQL Language Basics

In this MySQL Tutorial article, I will introduce you to the basic Administrative Tasks needed for MySQL. The purpose is to give system administrators the ability to perform some basic administration tasks before escalating to a qualified DBA. MySQL is a high-performance database server from Oracle Corporation. Red Hat recently dropped support of MySQL in favor of MariaDB, essentially both products are very similar, the commands in this blog will work with both MySQL AND MariaDB.

What is MySQL Server?

MySQL is an open source, relational database management system (RDBMS) based on the structured query language (SQL). MySQL is available on all major operating systems including Windows, Linux and Solaris. It is free to use for individuals and non-production environments under the GNU General Public License, however, if used commercially a commercial licence is required.

MySQL, like other relational databases, stores data in tables, columns and rows. Each entry is defined by a unique identifier. Its raison d’être has always been about the performance and reliability of the database. MySQL was designed and optimized for the web development arena; it is arguably the most common database used in web server deployments. MySQL works very well with Apache and PHP and is often the go to database for LAMP stack deployments. MySQL powers 9 out of 10 websites on the internet today, and is the database chosen by Facebook, Twitter and Wikipedia.[i]

MySQL was created by a Swedish company called MySQL AB in 1995, during the early years the development focus was for speed and productivity, rather than feature sets. Features however, were subsequently added with every major release, but MySQL AB always concentrated on speed, reliability and above all, making an easy-to-use database focused on web applications[ii].

During the late 1990’s, due to its significant performance advantages, MySQL became the database of choice for many companies. The Database market expanded and grew rapidly since the introduction of MySQL. It played a significant part in the history of the internet, enabling inexperienced users access to a free database, resulting in an explosion of websites for blogs, forums and online articles.[iii]

MySQL was successful because it is easy to use, it is simple to install, and uses a query language that is simple to understand. For Example, the SHOW DATABASES command will list the available database in MySQL. It was designed for people who are not experts in Database management, empowering inexperienced users, whilst at the same time offering enough complexity to cater for advanced DBA’s. [iv]

From 2001 onwards, MySQL AB began to add features that appealed to the enterprise markets, such as BDB and InnoDB support (a storage engine used in eCommerce), Sub Queries, Prepared statements, stored procedure etc. With each revision of MySQL, more enterprise grade features were added. Again, the growth of MySQL allowed users to pick up these feature sets with relative ease.

Sun Microsystems purchased MySQL AB in 2008, later in 2009 Oracle acquired the company after buying Sun Microsystems. Recent versions of MySQL are now very rich in product features whilst still maintaining strong performance ability. With Oracle’s drive to the cloud, they have recently started positioning MySQL as the go to cloud database platform, alongside existing, more traditional Oracle database platforms.

MySQL Versions

Since 2007, MySQL started to introduce differing versions of its core MySQL product. These editions focused around the community version, and the enterprise version of MySQL. This change in strategy presented users with a choice, both applications use the same source code, but were offered with differing support levels available to the end user.  [v]

The community edition is supported by the MySQL open source global community via a documentation hub, online blogs, IRC channels and forums. The Enterprise editions are supported by dedicated technical support teams, which offer hotfixes and service pack support,  and more recently include enterprise exclusive features such as Enterprise Backup, Enterprise Monitor, Enterprise Security and Enterprise Audit modules.[vi]

MySQL Forks

Since the purchase of MySQL by Oracle, many of the original development teams have left and setup what are known as fork development companies, these are essentially open source RDBMS software houses which are creating new RDBMS software using the original GNU source code for MySQL. Popular examples include MariaDB and Percona.

MySQL Management Tools

There are a number of tools available to manage and maintain MySQL. In typical Linux installations, DB management is done via the command line using the MySQL client. The MySQL client can be used to create databases, set up permissions and access rights. Additionally, there are a number of popular GUI tools including SQLyog, phpMyAdmin, MySQL Query Browsers, Navicat, MySQL Administrator and MySQL Workbench.

Each tool does similar tasks which include creating new tables, importing/exporting data, managing users and permissions, backups and restores, and creating triggers, views and stored procedures of the data. phpMyAdmin is widely used on Linux, and SQLyog and Workbench are popular on Windows. These tools are very similar in functionality to the Microsoft SQL Server studio. Some of the applications are free, whilst others offer free trials but require a license for full functionality. All tools are relatively simple to learn the basics, and can offer enterprise grade features.

What is a Database

A database is an organized collection of structured information, or data, typically stored electronically in a computer system. The data can then be easily accessed, managed, modified, updated, controlled, and organized. Most databases use structured query language (SQL) for writing and querying data.

MariaDB is basically the same as MySQL. Red Hat dropped support for MySQL in favour of MariaDB, but the products are pretty much identical.

MariaDB is an example of a DBMS (Database Management System). There are four types of DBMS:

  • Hierarchical
  • Network
  • Relational
  • Object-Orientated

Specifically, MariaDB and MySQL is a Relational Database Management System (RDBMS). Data is stored in a structured format using columns and rows. Values must always be related to each other, tables can be related to other tables. The Structured Query Language (SQL) is used to query the data.

MariaDB and MySQL support stored procedures within the database, A stored procedure is a subroutine available to applications that access a relational database management system. Such procedures are stored in the database data dictionary. Uses for stored procedures include data-validation or access-control mechanisms. Keys are used to uniquely identify rows within a table (i.e primary key)

How to Install MySQL Server

MySQL Installation Guide

Note: The commands needed are highlighted in bold

The MySQL Server is supported on various platforms, from Windows to Linux. In this lesson, we will learn how to perform a MySQL installation on Ubuntu. If you are using a different flavor of Linux, please check the MySQL Download Pages.

Step 1 – Update your server

Connect to your Server via SSH and log in using the credentials highlighted at the top of the page.

Once you are logged into your Ubuntu 18.04 server, run the following command to update your base system with the latest available packages.

apt-get update -y

Step 2 – Install MySQL 8 Repository

By default, MySQL 8 is not available in the Ubuntu 18.04 default repository, so you will need to add the MySQL 8 repository in your system.

First, download the repository package with the following command:

wget -c <a href="https://repo.mysql.com//mysql-apt-config_0.8.13-1_all.deb">https://repo.mysql.com//mysql-apt-config_0.8.13-1_all.deb</a>

Once downloaded, install the downloaded package with the following command:

dpkg -i mysql-apt-config_0.8.13-1_all.deb

During the installation process, you will be asked to choose the MySQL version as shown in the following screen:

MySQL, MySQL / MariaDB Database Administration and SQL Language Basics

Scroll down, select Ok and hit Enter to finish the installation.

Step 3 – Install MySQL 8

Next, update the repository with the following command:

apt-get update -y

Once the repository is updated, run the following command to install MySQL 8 in your system.

apt-get install mysql-server -y

During the installation process, you will be asked to set the MySQL root password as shown below:

Next, you will need to select the default authentication plugin for MySQL as shown below:

MySQL, MySQL / MariaDB Database Administration and SQL Language Basics
MySQL, MySQL / MariaDB Database Administration and SQL Language Basics

Select “Use Strong Password Encryption” and hit Enter to finish the installation.

MySQL, MySQL / MariaDB Database Administration and SQL Language Basics

Next, you can verify the installed version of the MySQL with the following command:

mysql -V

You should get the following output:

mysql  Ver 8.0.19 for Linux on x86_64 (MySQL Community Server - GPL)

Step 4 – Manage MySQL Service

You can manage the MySQL service with the systemctl utility.

To start the MySQL service, run the following command:

systemctl start mysql

To enable the MySQL service to start after a system reboot, run the following command:

systemctl enable mysql

To check the status of the MySQL service, run the following command:

systemctl status mysql

Output:

MySQL, MySQL / MariaDB Database Administration and SQL Language Basics

Step 5 – Secure MySQL Installation

It is also recommended to secure the MySQL installation and set the MySQL root password. You can do it using the mysql_secure_installation script:

mysql_secure_installation

You will be asked to provide your current root MySQL password as shown below:

Securing the MySQL server deployment.

Enter password for user root:

Provide your root password and hit Enter. You will be asked to validate the password to improve security as shown below:

MySQL, MySQL / MariaDB Database Administration and SQL Language Basics

Type Y and hit Enter to check the password strength as shown below:

MySQL, MySQL / MariaDB Database Administration and SQL Language Basics

Here, you can select your desired password strength and hit Enter to continue:

Estimated strength of the password: 100
Change the password for root ? ((Press y|Y for Yes, any other key for No) : Y

Type Y and hit Enter to change the password to one with the selected password strength:

New password:
Re-enter new password:

Type your new password and hit Enter to continue. You should see the following output:

Estimated strength of the password: 100
Do you wish to continue with the password provided?(Press y|Y for Yes, any other key for No) : Y

Type Y and hit Enter to continue with your provided password as shown below:

By default, a MySQL installation has an anonymous user,
allowing anyone to log into MySQL without having to have
a user account created for them. This is intended only for
testing, and to make the installation go a bit smoother.
You should remove them before moving into a production
environment.

Remove anonymous users? (Press y|Y for Yes, any other key for No) : Y
Success.

Next, type Y and hit Enter to remove the anonymous user. You should see the following output:

Normally, root should only be allowed to connect from
'localhost'. This ensures that someone cannot guess at
the root password from the network.

Disallow root login remotely? (Press y|Y for Yes, any other key for No) : Y

Creating Databases

After you have logged in, create a database with the following commands, below is a breakdown of the syntax

CREATE DATABASE Syntax: 

CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name [create_specification] … create_specification: [DEFAULT] CHARACTER SET [=] charset_name | [DEFAULT] COLLATE [=] collation_name | DEFAULT ENCRYPTION [=] {‘Y’ | ‘N’}

Create a new database: 

#mysql> CREATE DATABASE db_name; 

List available databases: 

#mysql> SHOW DATABASES; 

Select database for use: 

#mysql> USE db_name;

Show current selected database: 

mysql> SELECT DATABASE(); 

Select database when logging in to MySQL: 

mysql> mysql -u user -p db_name; 

DROP DATABASE Syntax:

To drop, or delete a database you do the following. Here is a breakdown of the syntax.

DROP {DATABASE | SCHEMA} [IF EXISTS] db_name 

Delete an existing database: 

mysql> DROP DATABASE db_name; 

List available databases: 

mysql> SHOW DATABASES; 

Using mysqladmin and mysqlshow

From the Command Line, you can query MySQL without ever having to log into the DB

mysqladmin Syntax is:

shell> mysqladmin [options] command [command-arg] [command [command-arg]] … 

mysqlshow Syntax 

shell> mysqlshow [options] [db_name [tbl_name [col_name]]] 

Using Mysqladmin to Create a database: 

# mysqladmin -u root -p create db_name 

Drop a Database: 

# mysqladmin -u root -p drop db_name 

List available databases: 

# mysqlshow -u root -p

Creating Tables

Here is the CREATE TABLE Syntax

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name

(create_definition,…) [table_options]

[partition_options]

Create a new table:

mysql> CREATE TABLE tbl_name (col_name col_def);

Example:

CREATE TABLE customers (cust_id INT PRIMARY KEY AUTO_INCREMENT, username VARCHAR(50), first_name VARCHAR(50), last_name VARCHAR(50), phone_number VARCHAR (25));

Query OK, 0 rows affected (0.02 sec)

List tables:

mysql> SHOW TABLES [FROM db_name];

List columns in a table:

Log into the database and type

mysql> DESCRIBE tbl_name [db_name.tbl_name];

Example:

mysql> DESCRIBE customers;

+————–+————-+——+—–+———+—————-+

| Field        | Type        | Null | Key | Default | Extra          |

+————–+————-+——+—–+———+—————-+

| cust_id      | int(11)     | NO   | PRI | NULL    | auto_increment |

| username     | varchar(50) | YES  |     | NULL    |                |

| first_name   | varchar(50) | YES  |     | NULL    |                |

| last_name    | varchar(50) | YES  |     | NULL    |                |

| phone_number | varchar(25) | YES  |     | NULL    |                |

+————–+————-+——+—–+———+—————-+

5 rows in set (0.00 sec)

mysql> SHOW COLUMNS FROM tbl_name [FROM db_name];

mysql> SHOW COLUMNS FROM customers;

+————–+————-+——+—–+———+—————-+

| Field        | Type        | Null | Key | Default | Extra          |

+————–+————-+——+—–+———+—————-+

| cust_id      | int(11)     | NO   | PRI | NULL    | auto_increment |

| username     | varchar(50) | YES  |     | NULL    |                |

| first_name   | varchar(50) | YES  |     | NULL    |                |

| last_name    | varchar(50) | YES  |     | NULL    |                |

| phone_number | varchar(25) | YES  |     | NULL    |                |

+————–+————-+——+—–+———+—————-+

5 rows in set (0.00 sec)

List tables and columns using the mysqlshow command:

# mysqlshow -u NAME -p db_name

[[email protected] ~]$ mysqlshow -u root -p prod

Enter password:

Database: prod

+———–+

|  Tables   |

+———–+

| customers |

+———–+

# mysqlshow -u NAME -p db_name tbl_name

[[email protected] ~]$ mysqlshow -u root -p prod customers

Enter password:

Database: prod  Table: customers

+————–+————-+——————–+——+—–+———+—————-+———————————+———+

| Field        | Type        | Collation          | Null | Key | Default | Extra          | Privileges             | Comment |

+————–+————-+——————–+——+—–+———+—————-+———————————+———+

| cust_id      | int(11)     |                    | NO   | PRI |         | auto_increment | select,insert,update,references |         |

| username     | varchar(50) | utf8mb4_0900_ai_ci | YES  |     |         |                | select,insert,update,references |         |

| first_name   | varchar(50) | utf8mb4_0900_ai_ci | YES  |     |         |                | select,insert,update,references |         |

| last_name    | varchar(50) | utf8mb4_0900_ai_ci | YES  |     |         |                | select,insert,update,references |         |

| phone_number | varchar(25) | utf8mb4_0900_ai_ci | YES  |     |         |                | select,insert,update,references |         |

+————–+————-+——————–+——+—–+———+—————-+———————————+———+

List additional information about a table:

mysql> SHOW TABLE STATUS [FROM db_name];

[[email protected] ~]$ mysqlshow -u root -p prod customers

Enter password:

Database: prod  Table: customers

+————–+————-+——————–+——+—–+———+—————-+———————————+———+

| Field        | Type        | Collation          | Null | Key | Default | Extra          | Privileges             | Comment |

+————–+————-+——————–+——+—–+———+—————-+———————————+———+

| cust_id      | int(11)     |                    | NO   | PRI |         | auto_increment | select,insert,update,references |         |

| username     | varchar(50) | utf8mb4_0900_ai_ci | YES  |     |         |                | select,insert,update,references |         |

| first_name   | varchar(50) | utf8mb4_0900_ai_ci | YES  |     |         |                | select,insert,update,references |         |

| last_name    | varchar(50) | utf8mb4_0900_ai_ci | YES  |     |         |                | select,insert,update,references |         |

| phone_number | varchar(25) | utf8mb4_0900_ai_ci | YES  |     |         |                | select,insert,update,references |         |

+————–+————-+——————–+——+—–+———+—————-+———————————+———+

Show statement that created a table:

mysql> SHOW CREATE TABLE tbl_name;

 customers | CREATE TABLE `customers` (

  `cust_id` int(11) NOT NULL AUTO_INCREMENT,

  `username` varchar(50) DEFAULT NULL,

  `first_name` varchar(50) DEFAULT NULL,

  `last_name` varchar(50) DEFAULT NULL,

  `phone_number` varchar(25) DEFAULT NULL,

  PRIMARY KEY (`cust_id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |

Copying and Cloning Tables

Using CREATE TABLE…LIKE:

CREATE TABLE new_tbl LIKE orig_tbl;

mysql> CREATE TABLE customers_test LIKE customers;

Query OK, 0 rows affected (0.02 sec)

mysql> SHOW TABLES;

+—————-+

| Tables_in_prod |

+—————-+

| customers      |

| customers_test |

+—————-+

2 rows in set (0.00 sec)

Using CREATE TABLE…SELECT:

CREATE TABLE new_tbl [AS] SELECT * FROM orig_tbl;

FULL BACKUP

mysql> CREATE TABLE customers_bkup SELECT * FROM customers;

Query OK, 0 rows affected (0.01 sec)

Records: 0  Duplicates: 0  Warnings: 0

mysql> SHOW TABLES;

+—————-+

| Tables_in_prod |

+—————-+

| customers      |

| customers_bkup |

| customers_test |

+—————-+

3 rows in set (0.00 sec

DROP TABLE Syntax

DROP [TEMPORARY] TABLE [IF EXISTS] tbl_name [tbl_name] … [RESTRICT | CASCADE]

Drop an existing table:

DROP TABLE tbl_name;

DROP TABLE db_name.tbl_name;

You may also like...

1 Response

  1. 15/01/2021

    […] If you prefer mySQL or MariaDB, check out this guide for all your Linux sysadmin needs. […]

Leave a Reply

Your email address will not be published. Required fields are marked *