Ubuntu18.04 of linux Method steps for installing MySQL

The installation

mysql

sudo apt-get --purge remove mysql-server mysql-common mysql-client
sudo apt-get install mysql-server mysql-common mysql-client

mysqladmin -u root password your-new-password
sudo /etc/init.d/mysql restart

mariadb

apt-get install mariadb-server

Character set modification utf8

If mariadb is installed, the default character set is utf8. mysql is not

mysql> show variables like 'char%';
+--------------------------+----------------------------+
| Variable_name      | Value           |
+--------------------------+----------------------------+
| character_set_client   | utf8            |
| character_set_connection | utf8            |
| character_set_database  | latin1           |
| character_set_filesystem | binary           |
| character_set_results  | utf8            |
| character_set_server   | latin1           |
| character_set_system   | utf8            |
| character_sets_dir    | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+

mysql> show variables like 'collation%';
+----------------------+-------------------+
| Variable_name    | Value       |
+----------------------+-------------------+
| collation_connection | utf8_general_ci  |
| collation_database  | latin1_swedish_ci |
| collation_server   | latin1_swedish_ci |
+----------------------+-------------------+

Modify the character set:

sudo vim /etc/mysql/my.cnf

Add the following

[mysqld]
collation-server = utf8_unicode_ci
init-connect='SET NAMES utf8'
character-set-server = utf8

Resume:

service mysql restart

Login access problem

Ubuntu18.04 After installing mysql or mariadb, it was discovered that neither the regular user nor the remote had access to the connection.

[

ERROR 1045: Access denied for user: 'root@localhost' (Using
password: YES)

]

Changing the password is also wrong. then sudo mysql -u root You can log in. This is clearly not what we want.

The solution

Delete root and recreate the user.

First, login

sudo mysql -u root

Then view the current user

SELECT User,Host FROM mysql.user;
+------------------+-----------+
| User       | Host   |
+------------------+-----------+
| admin      | localhost |
| debian-sys-maint | localhost |
| magento_user   | localhost |
| mysql.sys    | localhost |
| root       | localhost |

Delete your root account

mysql> DROP USER 'root'@'localhost';
Query OK, 0 rows affected (0,00 sec)

Recreate root:

apt-get install mariadb-server

0

authorization

apt-get install mariadb-server

1

About resetting passwords

host for % Allows remote login

apt-get install mariadb-server

2

or

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

or

apt-get install mariadb-server

4

Allow login from anywhere

apt-get install mariadb-server

5

reference
https://askubuntu.com/questions/766334/cant-login-as-mysql-user-root-from-normal-user-account-in-ubuntu-16-04
https://help.ubuntu.com/community/MysqlPasswordReset