Install and Use MySQL/MariaDB

Table of Contents

Introduction

Learn how to get started with the popular relational database MySQL/MariaDB. This quickstart guide will cover the installation of both, and an introduction to basic MySQL/MariaDB commands.

Note: For any 1&1 Cloud Server with Plesk, databases should always be installed and managed through the Plesk interface. See our article Create and Manage Databases on a Plesk Server for step-by-step instructions.

MySQL vs MariaDB

MySQL was first developed in 1995. It was acquired by Sun Microsystems in 2008, and then 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.

Requirements

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

Install MySQL on Ubuntu 16.04

MySQL is installed by default on a standard 1&1 Cloud Server running Ubuntu 16.04. Use the sudo mysql --version command to verify that MySQL is 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 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

Install MariaDB on CentOS 7

MariaDB is installed by default on a standard 1&1 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 it 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 commands:

sudo systemctl stop mariadb
sudo systemctl restart mariadb

Log In to the MySQL/MariaDB Client

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

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

Use DROP DATABASE [database name] to delete a database. For example, to delete the testdb database, the command is:

DROP DATABASE testdb;

To exit the client, type:

quit;

Then hit Enter.

Create and Drop a Table

Create 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, Update, 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 testtable the command is:

INSERT INTO testtable VALUES (1, 'Alice');
INSERT INTO testtable 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. 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 specify matching conditions. For example, use SELECT * from testtable where id = '1'; to select only the record with ID of 1:

MariaDB [testdb]> SELECT * from testtable where id = '1';
+----+-------+
| id | name  |
+----+-------+
| 1  | Alice |
+----+-------+
1 row 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)

Content provided by 1&1

Comments