How to work with MySQL server installed on Linux?

Every company needs a good database with easy relational management. That is why most of the sites on the Internet use MySQL.

In order for you to be able to use this DBMS, you need to install it on the server and configure it. Now we will figure out how to install MySQL on your linux server, how to configure it, as well as how to get remote access to your MySQL server.

Installing MySQL on Linux

An example will be shown on Ubuntu 20.04.1 which i created here

In order to install this DBMS without problems, it is initially necessary to update the system. To update the system, use the following command:

Sudo apt-get update

After this command, wait for the end of downloading updates.

As shown in the example above, the update was downloaded and installed.

Next, we will download the MySQL data package itself, the following command is used for this:

Sudo apt-get install mysql-server

During installation, you may be prompted to download additional files, I recommend doing this by typing Y and pressing enter.

Customization

After installing the MySQL server, you should set a password for the administrator. This can be done using a number of commands.

Sudo mysql_secure_installation

After entering the command, you will be prompted to install a plugin to improve server security.

Validate Password - Offers you to choose the use of passwords of various levels. There are three levels in total

• The first 0 is a weak password where the number of characters must be greater than or equal to 8.
• The second 1 is an average password where there must be at least 8 characters, including numbers, letters, special characters.
• The third 2-strong password of 8 characters must include numbers, letters, special characters and uppercase and lowercase letters.

Enter 2 and press enter, and then enter the desired password.

I entered the password with uppercase and lowercase letters, etc. As you can see, it is indicated that password security = 100, then you need to confirm that this password suits you by typing Y and pressing enter.

After that, you will be prompted to delete anonymous users that are created automatically. For greater security, you should delete them by typing Y and pressing enter.

Next, you will be prompted to disable the ability to remotely connect to the root user for greater security, enter Y. (However, if you are going to log in to your account remotely, then enter N).

After that, you can delete the test databases, as well as update the rules by simply typing Y twice.

Health check

After all these commands, you need to check the performance of the MySQL server. To do this, enter the following command.

Systemctl status mysql.service

As can be seen in the example above, the check was successful and the server is running.

If suddenly the server is not active, start it with the command -

sudo systemctl start mysql

Login to mysql root

In order for you to be able to add a user, you will need to enter the following commands:

Sudo mysql

This command introduces you to the server database itself

Creating a database

To create a database, type the following command:

Create database "namebase"

As you can see the database has been created

Adding Users

After you have logged into the server database on behalf of the Root user, you will need to enter the following command to create a user

CREATE USER 'USERNAME'@'host’ IDENTIFIED BY 'password’ ;

• Create user – This command is responsible for creating a user.
• 'username’ – Here you specify the username in quotes
• 'Host’ – Here in quotes you specify the connection method, if you specify localhost, then you cannot connect remotely, in my example I created a remote connection
• Identified by - Authentication method
• 'password’ – here in quotes you need to specify a password that fits your system, which we specified earlier. (Number 2, remember?)

very important do not forget to put at the end ;

Now that the user has been created, he needs to be given some rights, to do this, enter the command:

GRANT ALL PRIVILEGES ON database name. * TO ‘username’@’host’

Database_name. - you need to enter the database name

don't forget to put a period at the end of the name

'Username’ - you need to enter the username

'host’ - indicate whether it will be possible to connect remotely

As you can see, I gave all the permissions to work with this database to the user I just created.

Let's log in using the created user to do this, enter the following commands

Mysql -uUsername -p (press enter)

After that, you will be asked to enter the password of this user.

After entering the password, you will be logged in and will be able to interact with the server. Let's check if we can work with our database.

Enter a couple of commands to check:

• Use mybase - allows you to use the created database
• Show tables; - allows you to see information

it is important not to forget ;

Conclusion

In this article, we have read how you can install a MySQL server on linux and how to configure it to work. We also learned how to secure it, add a user and privileges for it, and also check its performance. Now this MySQL server is ready to work, you can connect to it and interact with it and its databases. In order to understand how you can continue to work with him, it is best to do everything yourself so that no questions arise.

Every MySQL server should be safe and not be afraid of, for example, ddos attacks. Our mivocloud service provides excellent protection against DDOS attacks, as well as management of your servers (for a fee) if you need stable hosting, you can try ours and you will not be disappointed.