How to install MySQL and MariaDB step by step

With MySQL/MariaDB, you can create a database. The installation on Linux is completed in just a few steps using the terminal.

How to install MySQL

MySQL is one of the most widely used relational database systems in the world and is ideally suited for applications that need to store and query structured data. On Linux, MySQL can be installed directly and conveniently via the package manager. After installation, you can start the database, create users, and set up your first databases.

Installing MySQL on Ubuntu/Debian

Before starting the MySQL installation on Ubuntu or Debian, check whether a version of the software is already installed. Use the sudo mysql --version command for this:

sudo mysql --version

If a version number is displayed, MySQL is already installed. Otherwise, you can install it by first updating your packages and loading the latest versions with the following commands:

sudo apt update
sudo apt upgrade -y

In the next step, install the MySQL package:

sudo apt install mysql-server -y

The -y flag automatically confirms the MySQL installation. Once the process is complete, the service should start on its own. Verify that MySQL is running properly by checking its status:

sudo systemctl status mysql

If the service is shown as ‘inactive’, you can start the database with the following command:

sudo systemctl start mysql

If desired, the following terminal command ensures that the MySQL service starts automatically when the system boots:

sudo systemctl enable mysql

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

sudo systemctl stop mysql
sudo systemctl restart mysql

Installing MySQL on CentOS/Red Hat

On CentOS or Red Hat Enterprise Linux (RHEL), you can also install MySQL easily. As with other distributions, first make sure the service isn’t already installed. To check the version, run the following command:

sudo mysql --version

If no version number is displayed, MySQL still needs to be installed. First, make sure your system is up to date by running sudo yum update.

Next, you can add the MySQL repository to get the latest version and then install MySQL:

sudo yum install mysql-server

After the installation, start the service and check the status:

sudo systemctl start mysqld
sudo systemctl status mysqld

If the status returns active (running), MySQL is ready for use. If you want to stop or restart the service, simply run the following commands:

sudo systemctl stop mysqld
sudo systemctl restart mysqld

How to install MariaDB

Like MySQL, MariaDB is a powerful relational database system and a popular alternative to MySQL. In the MySQL vs MariaDB comparison, MariaDB often stands out with higher performance, additional storage engines, and a more active open-source community, while maintaining compatibility in core functionality and query language. Installation on Linux systems is also straightforward, as MariaDB is available directly through the package manager.

Installing MariaDB on Ubuntu/Debian

Before you install MariaDB on your Ubuntu or Debian system, the first step should be to check if a version is already installed. To do this, enter the following command in the terminal:

mariadb --version

If a version number is displayed, MariaDB is already installed. Otherwise, you should prepare your system for the MariaDB installation by first updating the package lists and bringing all existing packages up to date:

sudo apt update
sudo apt upgrade -y

Then, install MariaDB with the following command:

sudo apt install mariadb-server -y

The -y parameter automatically confirms the installation. Upon completion, the MariaDB service should start immediately. You can check the current status with:

sudo systemctl status mariadb

If the service is not active, simply start it manually:

sudo systemctl start mariadb

To ensure MariaDB automatically loads at system startup in the future, use:

sudo systemctl enable mariadb

To stop or restart the service as needed, you can use the following terminal commands:

sudo systemctl stop mariadb
sudo systemctl restart mariadb

Installing MariaDB on CentOS/Red Hat

On CentOS or Red Hat Enterprise Linux, installing MariaDB is quick and follows a process very similar to the MySQL installation:

Use the command sudo mariadb --version to check whether MariaDB is installed:

sudo mariadb --version

You will receive a version number as feedback if the service is installed. If not, you can install the database management system by first updating your system:

sudo yum update

Next, install MariaDB using the following command:

sudo yum install mariadb-server

MariaDB should start automatically after installation. If it does not, you can start the application with the following command:

sudo systemctl start mariadb

To ensure MariaDB starts automatically on boot, execute the following 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

How to use MySQL/MariaDB

After successfully installing MySQL or MariaDB, you can start using the services right away to create your own databases and users or run queries. The basic commands for MariaDB and MySQL are identical since MariaDB was originally developed as a one-to-one replacement for MySQL.

Login

Enter the following command in your command line to log into the MySQL/MariaDB database:

mysql -u root -p

For a standard installation of MySQL or MariaDB, use the root password that was set when the server was created. If you installed MySQL or MariaDB yourself, enter the password you defined for the root user during the installation process.

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

Creating a database

After logging in, use the SQL query language and the command CREATE DATABASE [database_name]; to create a new database. For example, to create a database named ‘testdb’, enter the following command:

CREATE DATABASE testdb;

Listing and selecting a database

Use the SQL command 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)

With the command USE [database_name];, you can connect to a database and select it for use:

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

Deleting a database

If you want to delete a database, you can use the SQL command DROP DATABASE. For example, to delete the ‘testdb’ database, enter the following command in the terminal:

DROP DATABASE testdb;

To exit the client, the command quit; can help:

quit;

Then press the Enter key to confirm and exit the client.

Creating a table

A table is a structured collection of data within a database, where information is organised into rows and columns. Use the CREATE TABLE command to create such a table.

For example, to create a table named ‘testtable’ with two basic columns, use the following command:

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

With SHOW TABLES;, you can then check whether your table has been created:

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

Deleting a table

With the command DROP TABLE [table_name];, you can delete a table. For example, to delete the test table, the command is:

DROP TABLE testtable;

Then use SHOW TABLES; again to ensure that your table has been deleted:

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

Inserting data into a table

Use the INSERT INTO command to add data to a table.

For example, to insert a record into the test table, use the following command:

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

Selecting table data

With SQL SELECT, you can retrieve data from a table and perform various SQL queries on the database.

For example, to display all the contents of our test table, use the following command:

SELECT * FROM testtable;

This returns the entire table content:

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

You can also filter which columns to display. For example, use SELECT name FROM testtable; to show only the name field for all records:

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

Updating a record

Use SQL UPDATE to update a record.

For example, to change the record with the ID ‘2’ from ‘Bob’ to ‘Carl’, use the following command:

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

Finally, use SELECT to ensure the record has been updated correctly:

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