For Debian-based distributions

First, update the apt package index with the command:

sudo apt update

Then install the package:

sudo apt install mysql-server

This command will install MySQL, then basic MySQL configuration is required

Configure MySQL

Run the security script with:

sudo mysql_secure_installation

As you can see, security is at the plinth level. At least there is no anonymous user. To configure the basic things in the server, run the server configuration through mysql_secure_installation. At the time of this installation, the password will be security. Your same password, as you know, should be different

/usr/bin/mysql_secure_installation

Skip root password for root

We haven’t set the root password yet, so when you run the script and ask for the root password, just press Enter. Install new password for root: security, and here you can set the password for root. Do remove an anonymous user. When asked whether to remove an anonymous user, answer Yes

Do not disallow remote connections. Do not prohibit the connection to our North from remote servers (if, of course, this option you need, in other case, prohibit it). Do remove a test database. Test base we do not need – remove it. Do reload the privileges. Overload privileges to activate them

The password is now set for all root users. If you did not set a root password during this configuration, you can do so as follows:

SET PASSWORD FOR 'root'@'localhost' = PASSWORD('security');

SET PASSWORD FOR 'root'@'localhost.localdomain' = PASSWORD('security');

SET PASSWORD FOR 'root'@'127.0.0.1' = PASSWORD('security');

or

UPDATE mysql.user SET Password = PASSWORD('security') WHERE user = 'root';

If you did not run the configuration through mysql_secure_installation or do not want to do so for some other reason, the following commands will remove any users:

DROP USER "@'localhost';

DROP USER "@'localhost.localdomain';

Also, the password for IPv6 localhost (@::1) can be set this way:

SET PASSWORD FOR 'root'@'::1' = PASSWORD(<password>);

The final of our action is coming. There are two things. Open the ports for mysql:

sudo iptables -I INPUT -p tcp --dport 3306 -m state --state NEW,ESTABLISHED -j ACCEPT

sudo iptables -I OUTPUT -p tcp --sport 3306 -m state --state ESTABLISHED -j ACCEPT

Set the default UTF-8 encoding to /etc/my.cnf

[mysqld]

init_connect=‘SET collation_connection = utf8_unicode_ci’

character-set-server = utf8

collation-server = utf8_unicode_ci

[client]

default-character-set = utf8

Overloading the service:

sudo service mysqld restart

Conclusion

mysqld: can't create directory '/var/lib/mysql/' (Errcode: 17 - File exists)

. . .

2019-07-15T14:30:25.572066 Z 0 [ERROR] Abortion

Note that although you have set a password for The MySQL root user, this user is not configured for password authentication when connecting to the MySQL shell.

Check if the service started:

systemctl status mysql.service

mysql.service – MySQL Community Server

Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: en

Active: active (running) since Mon 2019-07-15 14:30:25 UTC; 30min ago

Main PID: 3754 (mysqld)

Tasks: 28

Memory: 149.3 M

CPU: 2.094 s

CGroup: /system.slice/mysql.service

7875 /usr/sbin/mysqld

Optional configure authentication and privileges

On servers running Ubuntu that use MySQL 5.7 (and later), the MySQL root user is authenticated by default using the auth_socket plugin, not by password. This is generally more secure and convenient in many cases, but not when you need to organize access to MySQL from a third-party program, for example, phpMyAdmin.

In order for the MySQL root user to be able to use the login password, you need to change the authentication method from auth_socket to mysql_native_password. To do this, enter the MySQL shell with the following command:

sudo mysql

Next, check which authentication method is used for each of your MySQL users. SELECT user,host,authentication_string,plugin FROM mysql.user. Like this you can see

+------------------+-----------+-------------------------------------------+-----------------------+

| user | host | authentication_string | plugin |

+------------------+-----------+-------------------------------------------+-----------------------+

| root | localhost | | auth_socket |

| mysql.session | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | mysql_native_password |

| mysql.sys | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | mysql_native_password |

| debian-sys-maint | localhost | *CC744277A401A7D25BE1CA89AFF17BF607F876FF | mysql_native_password |

+------------------+-------------------------------------------+-----------------------+-----------+

4 rows in set (0.00 sec)

The string auth_socket means that the user will be able to connect without entering a password when connecting via a local UNUX socket. If you are not satisfied with this behavior of MySQL, enter the following command in the mysql console.

> ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'VERY_VERY_SECURE_PASSWORD';

Naturally VERY_VERY_SECURE_PASSWORD replace really safe, known only to those who should know it

> FLUSH PRIVILEGES;

Check yourself! SELECT user,host,authentication_string,plugin FROM mysql.user. Like this you can see

+------------------+-----------+-------------------------------------------+-----------------------+

| user | host | authentication_string | plugin |

+------------------+-----------+-------------------------------------------+-----------------------+

| root | localhost | *8C744276549879874FC098CABFF17BF607F876FF | mysql_native_password |

| mysql.session | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | mysql_native_password |

| mysql.sys | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | mysql_native_password |

| debian-sys-maint | localhost | *CC744277A401A7D25BE1CA89AFF17BF607F876FF | mysql_native_password |

+------------------+-------------------------------------------+-----------------------+-----------+

4 rows in set (0.00 sec)

For verification:

• Exit the mysql client (ctrl+d or write exit) if you are in it;
• As root, type mysql or (sudo mysql) as another user.

Mysql program should ask for a password if you log in without a password recheck the steps. Add a local tommy superuser with full database access and the right to assign rights to other users:

> GRANT ALL PRIVILEGES ON *.* TO 'tommy'@'localhost' WITH GRANT OPTION IDENTIFIED BY 'VERY_SECURE_TOO_PASSWORD';

Adding user jonny connecting with IP 123.45.67.89 to work with WORDPRESS database:

> GRANT ALL PRIVILEGES ON *.WORDPRESS TO 'tommy'@'123.45.67.89' IDENTIFIED BY 'OTHER_VERY_SECURE_PASSWORD';