How to install MySQL and MariaDB step by step

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

How to install MySQL

MySQL is one of the most widely used re­la­tion­al database systems in the world and is ideally suited for ap­plic­a­tions that need to store and query struc­tured data. On Linux, MySQL can be installed directly and con­veni­ently via the package manager. After in­stall­a­tion, you can start the database, create users, and set up your first databases.

In­stalling MySQL on Ubuntu/Debian

Before starting the MySQL in­stall­a­tion 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 auto­mat­ic­ally confirms the MySQL in­stall­a­tion. 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 auto­mat­ic­ally 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

In­stalling MySQL on CentOS/Red Hat

On CentOS or Red Hat En­ter­prise Linux (RHEL), you can also install MySQL easily. As with other dis­tri­bu­tions, 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 re­pos­it­ory to get the latest version and then install MySQL:

sudo yum install mysql-server

After the in­stall­a­tion, 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 re­la­tion­al database system and a popular al­tern­at­ive to MySQL. In the MySQL vs MariaDB com­par­is­on, MariaDB often stands out with higher per­form­ance, ad­di­tion­al storage engines, and a more active open-source community, while main­tain­ing com­pat­ib­il­ity in core func­tion­al­ity and query language. In­stall­a­tion on Linux systems is also straight­for­ward, as MariaDB is available directly through the package manager.

In­stalling 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 in­stall­a­tion 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 auto­mat­ic­ally confirms the in­stall­a­tion. Upon com­ple­tion, the MariaDB service should start im­me­di­ately. 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 auto­mat­ic­ally 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

In­stalling MariaDB on CentOS/Red Hat

On CentOS or Red Hat En­ter­prise Linux, in­stalling MariaDB is quick and follows a process very similar to the MySQL in­stall­a­tion:

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 man­age­ment system by first updating your system:

sudo yum update

Next, install MariaDB using the following command:

sudo yum install mariadb-server

MariaDB should start auto­mat­ic­ally after in­stall­a­tion. If it does not, you can start the ap­plic­a­tion with the following command:

sudo systemctl start mariadb

To ensure MariaDB starts auto­mat­ic­ally 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 suc­cess­fully in­stalling 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 ori­gin­ally developed as a one-to-one re­place­ment 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 in­stall­a­tion 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 in­stall­a­tion 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 struc­tured col­lec­tion of data within a database, where in­form­a­tion 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)