How to Set Up MySQL/MariaDB on Linux

Edited 3 weeks ago by ExtremeHow Editorial Team

Database ManagementMySQLMariaDBInstallationConfigurationSQLServer SetupData ManagementCommand LineOptimization

Setting up MySQL or MariaDB on a Linux-based system is an essential task for developers and administrators working with databases. Both MySQL and MariaDB are popular relational database management systems, providing robust solutions for storing and managing data. Despite their powerful features, the setup process is simple once you understand the necessary steps. This guide will guide you through setting up MySQL or MariaDB, from system requirements to securing and testing the installation, using simple language and straightforward examples.

Understanding MySQL and MariaDB

MySQL is an open-source relational database management system. Over the years, it has grown in popularity due to its reliability and robust features. However, in 2009, Oracle Corporation acquired MySQL, and some developers were concerned about potential changes to this open-source project. In response, the original developers of MySQL started MariaDB, a fork of MySQL designed to remain free and open-source. MariaDB offers the same features and compatibility as MySQL, making it a natural alternative.

Prerequisites

Before setting up MySQL/MariaDB, make sure your Linux system meets the following requirements:

• A machine running a modern version of Linux (such as Ubuntu, CentOS, or Debian).
• Root or sudo access to install packages and perform necessary configuration.
• Internet access to download the MySQL or MariaDB package.

Step 1: Updating system packages

It is essential to ensure that your system packages are up-to-date to avoid any compatibility issues. You can update your packages using the package manager provided by your distribution. Here is how you can do it:

sudo apt update sudo apt upgrade

The above commands are for Debian-based systems like Ubuntu. If you are using a Red Hat-based system like CentOS, you can update packages using the following:

sudo yum update

Step 2: Installing MySQL or MariaDB

Depending on your preference, you can install either MySQL or MariaDB. We will cover both options:

Installing MySQL

To install MySQL, use the following command on a Debian-based system:

sudo apt install mysql-server

For Red Hat-based systems, use:

sudo yum install mysql-server

After running the above command, MySQL should be installed on your system. You can verify the installation by checking the version:

mysql --version

Installing MariaDB

If you opt for MariaDB, you can install it by running:

sudo apt install mariadb-server

For Red Hat-based systems, use:

sudo yum install mariadb-server

Verify the installation by checking the MariaDB version:

mysql --version

Step 3: Securing the database server

After installing MySQL or MariaDB, it is very important to secure the installation to protect your data. This usually involves running a security script that comes with the installation package:

Running a security script

Execute the security script by using the following command:

sudo mysql_secure_installation

This script will guide you through several steps, including setting the root password, removing anonymous users, disallowing remote root logins, and more. It is advisable to answer 'yes' to all security-related prompts to increase the security of your server.

Step 4: Configuring MySQL or MariaDB

Configuration involves setting parameters that determine how MySQL or MariaDB works on your system. The main configuration files are typically located in /etc/mysql/ for MySQL and /etc/my.cnf or /etc/my.cnf.d/ for MariaDB.

Editing configuration files

You can edit these files using a text editor such as `nano` or `vi`. Open the configuration file as follows:

sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

Adjust various settings such as bind address, maximum connections or buffer size to suit your server's needs. Save the file and exit the editor after making the necessary changes.

Restarting the service

To make the changes effective, restart the MySQL or MariaDB service:

sudo systemctl restart mysql

Or, for MariaDB:

sudo systemctl restart mariadb

Step 5: Testing the installation

After you've installed and secured your database server, test the installation to make sure everything is working as expected:

Connecting to a database server

Use the following command to connect to MySQL or MariaDB as the root user:

mysql -u root -p

Enter the password you set during the security configuration step when prompted. After logging in, you will see the MySQL or MariaDB shell prompt, indicating a successful connection.

Creating a test database

While logged into the MySQL or MariaDB shell, create a test database to verify that everything is working:

CREATE DATABASE test_db;

Verify the database creation by listing the available databases:

SHOW DATABASES;

You should see the `test_db` database listed along with other databases like `information_schema` and `mysql`.

Managing MySQL/MariaDB server

With the core components operational, understanding how to manage the server's operations will enhance your abilities to effectively maintain and use the database server.

Starting and stopping the service

To start the server:

sudo systemctl start mysql

Or, for MariaDB:

sudo systemctl start mariadb

To stop the server:

sudo systemctl stop mysql

Or, for MariaDB:

sudo systemctl stop mariadb

Checking the service status

To check the status of MySQL or MariaDB to make sure everything is running as expected:

sudo systemctl status mysql

Or, for MariaDB:

sudo systemctl status mariadb

Backing up and restoring the database

Regular backups are vital for data security. MySQL and MariaDB provide tools for exporting and importing databases.

Exporting a database

You can export the database using the `mysqldump` utility:

mysqldump -u root -p test_db > test_db.sql

This command will ask you for the password and create a backup of `test_db`.

Importing the database

To restore a database from a backup, use:

mysql -u root -p test_db < test_db.sql

This command will import the `test_db.sql` file into the `test_db` database.

Troubleshooting

Sometimes, problems may arise during installation or operation. Here are some common troubleshooting steps:

Log files

Check for any error messages in the MySQL or MariaDB log files that may guide troubleshooting efforts. Logs are typically located in /var/log/mysql/ or /var/log/mariadb/.

General issues

• Failed start: Make sure the configuration files are correct and do not contain any syntax errors. Use log files to diagnose start-up failures.
• Access Denied errors: Make sure the correct user credentials are being used and that the user has the required permissions.
• Connection issues: The bind-address in the configuration files must allow connections on the required interfaces.

Conclusion

Setting up MySQL or MariaDB on a Linux system is a straightforward process once you understand the necessary steps and requirements. This guide provides a step-by-step process, from installation to securing, configuring, and managing a database server. With your server up and running, you can now create and manage databases, ensuring you have reliable data storage solutions across your applications and systems.

  Share This Article

If you find anything wrong with the article content, you can request an update