How to Setup a MySQL Database on Linux

Every Linux administrator must install and configure a database at some point. This can include the deployment of a dynamic website, such as for WordPress, or data storage for web applications as well as customer, customer and employee records. Databases are crucial for every type of business.

Linux presents the best platform for databases. It is a reliable and open source operating system with many predefined databases already available for installation. Among these predefined databases, MySQL database server is the most popular.

Linux databases

The databases available for Linux range from small built-in tools like SQLite to powerful relational databases like MySQL and even NoSQL databases for big data. Some of the more popular databases available for Linux include:

  • PostgreSQLName
  • MariaDB
  • IBM Db2
  • Oracle database
  • Bird of Fire
  • Amazon’s Relational Database Service
  • Say it again
  • Ingres
  • Hadoop Distributed File System

MySQL is a complete relational database management system, which means it includes everything needed for storing and retrieving data. It uses SQL query language, so anyone familiar with this language should be able to use this database easily. Even for those unfamiliar with SQL, SQL is designed to be easy to learn and readable.

How to configure a database in Linux

To install and configure a MySQL database on Linux — specifically, Ubuntu Server 20.04 — start by logging into Ubuntu Server and installing MySQL with the command:

sudo apt-get install mysql-server -y

Once the installation is complete, start and activate the server with the command:

sudo systemctl enable –now mysql

MySQL should now be installed and running. However, the MySQL administrator account comes without a password, which presents a security issue. To secure the installation of MySQL, run the command:

sudo mysql_secure_installation

The system first asks if you want to enable the “Validate Password” component, which forces all accounts to use very strong passwords. Type Yes to activate this component; otherwise, press any other key. Even if you don’t enable this component, you can still use strong passwords.

When you secure your MySQL installation, you should see the following asking if MySQL requires passwords of a certain strength.

After this step, the system prompts you to enter and verify a password for the MySQL administrator user. Do this and then reply Yes for the remaining questions:

  • Delete anonymous users?
  • Forbid remote root login?
  • Delete the test database and access it?
  • Reload privilege tables now?

Once you have secured the MySQL database server, you can create your first database.

Create a first database

In this tutorial, create a database named technology target. To do this, log in to the MySQL console with the command:

sudo mysql -u root -p

The options are:

  • -uwhich defines a user (in this case the root user)
  • -pwho asks for a password

If prompted for the sudo password, enter it first, then the MySQL root user password when prompted. You will then find yourself on the MySQL console.

MySQL-Console
From the MySQL console, you can create your first Linux database.

The MySQL console is ready for you to create your first database.

To create the technology target database, run the command:

CREATE A DATABASE TECHNOLOGY TARGET;

To verify the creation of the database, run the command:

view databases;

you should see technology target listing (picture 3).

List of MySQL databases
With show databases; command, you should see all available MySQL databases.

Create a new database user with permissions

Then create a user authorized to access the new database. In this example, create the user techtargetuser with the command:

CREATE A USER ‘techtargetuser’@’localhost’ IDENTIFIED BY ‘PASSWORD’;

THE PASSWORD represents a strong or unique password.

When you create a new user, the user does not automatically have access to the database. For example, when you install WordPress, using the technology target MySQL database and administrator user presents a security risk. the technology target database and the techtargetuser account helps mitigate this risk when you grant the user access to the database.

Give the user access to the database with the command:

GRANT ALL ON techtarget.* TO ‘techtargetuser’@’localhost’ WITH GRANT OPTION;

This command grants all permissions on the technology target database — and one or all tables, using .* — to techtargetuser on the host machine, with the possibility of giving other users all the necessary privileges – managed by GRANT OPTION.

With that in place, when you install WordPress, configure the database details like so:

  • Database: techtarget
  • Database user: techtargetuser

Once you have installed a database, you can use your database server to work with all types of applications and services. In most cases, applications that require MySQL, such as WordPress and Nextcloud, populate these databases upon installation, so you don’t need to manually add tables and data.