How to install and use MySQL/MariaDB

MySQL/MariaDB are used to create databases. In Linux, installation takes just a few steps in the terminal.

Cheap domain names – buy yours now
  • Free website protection with SSL Wildcard included
  • Free private registration for greater privacy
  • Free 2 GB email account

Requirements

  • A Cloud Server running Linux (CentOS 7 or Ubuntu 16.04)
Note

For any Cloud Server with Plesk, databases should always be installed and managed through the Plesk interface.

MySQL vs. MariaDB

MySQL was first developed in 1995 and acquired by Sun Microsystems in 2008, and then again by Oracle in 2010. MariaDB was developed as a fork of the MySQL project in 2009, due to concerns about Oracle’s proprietary requirements. Although MySQL’s source code is publicly available under the terms of the GNU General Public License, MariaDB is a fully open source project.

MariaDB was developed as a ‘drop-in’ replacement for MySQL. As such, both software packages are functionally equivalent and interchangeable.

MySQL is the default on Ubuntu systems, while MariaDB is the default on CentOS systems. Therefore, this guide will cover installing and updating MySQL on Ubuntu 16.04 and MariaDB on CentOS 7.

Install MySQL on Ubuntu 16.04

MySQL is usually installed by default on a standard Cloud Server running Ubuntu 16.04. Use the sudo mysql --version command to verify that MySQL is already installed:

user@localhost:~# sudo mysql --version
mysql  Ver 14.14 Distrib 5.7.17, for Linux (x86_64) using  EditLine wrapper

If MySQL is not installed, you can install it by first updating your packages:

sudo apt-get update

Then install MySQL:

sudo apt-get install mysql-server

Follow the prompts to install MySQL.

MySQL should start itself automatically when installed. If it does not start, you can start it with the following command:

sudo systemctl start mysql

Enable MySQL to start at boot with the command:

sudo systemctl enable mysql

If you need to stop or restart MySQL, use the commands:

sudo systemctl stop mysql
sudo systemctl restart mysql
VPS Hosting
Fully virtualised servers with root access
  • Unlimited traffic and up to 1 Gbit/s bandwidth
  • Fast SSD NVMe storage
  • Free Plesk Web Host Edition

Install MariaDB on CentOS 7

In contrast to MySQL, MariaDB is usually installed by default on a standard Cloud Server running CentOS 7. Use the sudo mysql –version command to verify that MariaDB is installed:

[user@localhost ~]# sudo mysql --version
mysql  Ver 15.1 Distrib 5.5.52-MariaDB, for Linux (x86_64) using readline 5.1

If MariaDB is not installed, you can install the database management system by first updating your system:

sudo yum update

Then install MariaDB:

sudo yum install mariadb-server

MariaDB should start itself automatically when installed. If it does not start, you can start it with the command:

sudo systemctl start mariadb

Enable MariaDB to start at boot with the command:

sudo systemctl enable mariadb

If you need to stop or restart MariaDB, use the following commands:

sudo systemctl stop mariadb
sudo systemctl restart mariadb

Log in to the MySQL/MariaDB client

From the command line, enter the following command to register with the MySQL/MariaDB client:

mysql -u root -p

For a default MySQL/MariaDB installation, use the default root password which was set when the server was created. If you installed MySQL/MariaDB, enter the password which you set for the root user during the installation process.

After entering the password, you will be taken to the MySQL/MariaDB client prompt.

[root@localhost ~]# mysql -u root -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 83
Server version: 5.5.52-MariaDB MariaDB Server
Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]

Create, select, and drop a database

Create a Database

Use CREATE DATABASE[database name]; to create a database. For example, to create a database named testdb the command is:

CREATE DATABASE testdb;

List and Select a Database

Use SHOW DATABASES; to list all available databases:

MariaDB [(none)]> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| testdb             |
+--------------------+
4 rows in set (0.00 sec)

Use USE[database name]; to connect to a database and select it for use:

MariaDB [(none)]> USE testdb;
Database changed

Drop a Database

If you want to delete a database, use DROP DATABASE[database name]. For example, to delete the testdb database, enter the following command in the terminal:

DROP DATABASE testdb;

To exit the client, type:

quit;

Hit Enter to confirm exiting the client.

Cloud Backup powered by Acronis
Mitigate downtime with total workload protection
  • Automatic backup and easy recovery
  • Intuitive scheduling and management
  • AI-based threat protection

Create and drop a table

Use CREATE TABLE[table name] (column definitions); to create a table. A full list of CREATE TABLE parameters can be found in the CREATE TABLE chapter of the official MySQL reference manual.

For example, to create a table testtable with two basic columns, the command is:

CREATE TABLE testtable (
    id char(5) PRIMARY KEY,
    name varchar(40)
    );

Use SHOW TABLES; to verify that your table was created:

MariaDB [testdb]> SHOW TABLES;
+------------------+
| Tables_in_testdb |
+------------------+
| testtable        |
+------------------+
1 row in set (0.00 sec)

Drop a Table

Use DROP TABLE[table name]; to delete a table. For example, to delete the testtable table, the command is:

DROP TABLE testtable;

Use SHOW TABLES; to verify that your table was deleted:

MariaDB [testdb]> SHOW TABLES;
Empty set (0.00 sec)

Working with records: insert, select, and update data

Insert Data into a Table

Use INSERT INTO[table name] VALUES (data, data...); to insert data into a table. A full list of INSERT parameters can be found in the ‘INSERT Syntax’ chapter of the official MySQL Reference Manual.

For example, to insert a record into the table testable the command is:

INSERT INTO testable VALUES (1, 'Alice');
INSERT INTO testable VALUES (2, 'Bob');
Note

It is important to list the values in the same order as the columns of the table. In our example, the table’s first column is id and the second column is name. Therefore, we need to insert the ID as the first value, and the name as the second.

Select Table Data

Use SELECT to select data from a table and generate different queries for a database. A full list of SELECT parameters can be found in the ‘SELECT Syntax’ chapter of the official MySQL Reference Manual.

For example, to list all of the contents of our testtable the command is:

SELECT * from testtable;

This will return all of the table contents.

MariaDB [testdb]> SELECT * from testtable;
+----+-------+
| id | name  |
+----+-------+
| 1  | Alice |
| 2  | Bob   |
+----+-------+
2 rows in set (0.00 sec)

You can also filter out which columns you want to select. For example, use SELECT name FROM testtable; to see only the name field for all records:

MariaDB [testdb]> SELECT name FROM testtable;
+-------+
| name  |
+-------+
| Alice |
| Bob   |
+-------+
2 rows in set (0.00 sec)

Update a Record

Use UPDATE[table name] SET [new values] WHERE [matching condition] to update a record. A full list of UPDATE parameters can be found in the ‘UPDATE Syntax’ chapter of the official MySQL Reference Manual.

For example, to change the record with ID of 2 from Bob to Carl the command is:

UPDATE testtable SET name = 'Carl' WHERE id = '2';

Use SELECT to verify that the record was updated correctly:

MariaDB [testdb]> SELECT * FROM testtable;
+----+-------+
| id | name  |
+----+-------+
| 1  | Alice |
| 2  | Carl  |
+----+-------+
2 rows in set (0.00 sec)
Was this article helpful?