Installing MySQL on CentOS

When we develop our applications, we often want to install a database. Here, I’ll show you how to install the most common relational database, MySQL.

For this, you’ll need to be logged into your system via the ssh command and have an account with sudo privileges, preferably a user other than root. As we saw in the previous post, creating a sudo user is very easy.

MySQL is not available in the default CentOS repositories, so you’ll need to install the repositories on your server.

1 - Install MySQL 8.0 on CentOS

As of today, at the time I’m writing this article, the latest version of MySQL is version 8.0, so we’ll install this version. To do so, you only need to follow two simple steps:

• Enable the MySQL 8.0 repository

Run the following command in the terminal, notice the use of sudo to apply superuser permissions.

$ sudo yum localinstall https://dev.mysql.com/get/mysql80-community-release-el7-1.noarch.rpm

• Install MySQL on CentOS 7

$ sudo yum install mysql-community-server

During the yum installation, which is the system that allows us to install applications on CentOS, we’ll be asked to import the MySQL GPG key. Enter y and press Enter.

And with these simple steps, MySQL will be installed on your Linux server.

2 - Enable the MySQL service on Linux

When installation is complete, you’ll need to enable the service in your Linux environment. Use the following commands:

$ sudo systemctl enable mysqld
$ sudo systemctl start mysqld

You can check the status of your service with the following command.

$ sudo systemctl start mysqld

3 - Change MySQL Password

When you install MySQL, it generates a default password for you, this password is temporary and should be changed. To find out what password was generated, run the following command:

$ sudo grep 'temporary password' /var/log/mysqld.log

The output will look similar to this:

2019-12-24T12:23:31.128787Z 5 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: Ezd3EFw,

Be sure to make note of this password, since you’ll need it for the next command.

Run the mysql_secure_installation command, which will prompt you for the temporary password and then allow you to set a password of your choice.

4 - Connect to MySQL from the command line

To use MySQL in your PowerShell terminal (or whichever one you are using), you’ll use the MySQL client, which was installed during step 1.

To log in to your MySQL server with the root user, enter the following command:

$ mysql -u root -p

Note: -u lets you specify the user and -p prompts for your password.

After you press enter, you’ll be prompted for a password. Here, enter the new password you created in the previous step.

And with these simple steps, you’ll be connected to your MySQL server and can execute queries as if you were using a desktop client.

5 - Creating a Database

Now you’re connected to MySQL with an active user, let’s create a database called ejemplodb by running the following command:

mysql> CREATE DATABASE ejemplodb;

You’ll see that the database is created.

6 - Creating Tables

To create a table, you’ll use the same process. But first, you’ll need to specify that you want to use the database you just created by running this command:

mysql> use ejemplodb;

In this example, I’ll create a small table for users and their email addresses.

CREATE TABLE usuario(
  id INT PRIMARY KEY,
  nombre VARCHAR(50),
  email VARCHAR(30)
);

And with that, your table is created. This process can be a bit tedious, if you have lots of tables, writing everything out can be boring and prone to errors.

There are several ways to import everything at once:

• Using the mysqld command

$ mysqld nombre_bd < fichero.sql

For this process, we’ll see later in more detail how to do it properly for making backups.

• Pasting content by copy-paste

In PowerShell, when connected to your MySQL server, you can paste, but instead of pressing ctrl+v, use the right mouse button.

Conclusion.

In this chapter, we’ve seen how to install a MySQL database on Linux and secure it with a strong password. There was also a brief example of creating a database and some tables.

In later chapters, we’ll see how to create backups.