Problems encountered in installing mysql8 with ubuntu

Problems encountered:

Mysql version
mysql Ver 8.0.28-0ubuntu0.20.04.3 for Linux on x86_64 ((Ubuntu))
The following are the errors that I will report after uninstalling and installing mysql several times. No matter how to change the configuration file according to the online tutorial, I will eventually report that the local link is rejected. My errors are as follows:

root@ubuntu:/etc/mysql/mysql.conf.d# mysql
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)In this case, no password is entered
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)In this case, the password is entered, but it is not entered correctly

According to the online tutorial, after mysql8 is successfully installed, there is a default user and password. The specific viewing methods are as follows:

sudo  cat /etc/mysql/debian.cnf

After entering the command, you will be prompted to enter the password of the current ubuntu user. After entering, my content is displayed as follows:

# Automatically generated for Debian scripts. DO NOT TOUCH!
host     = localhost
user     = debian-sys-maint(This is the default user)
password = X4k1p11iFNCzUGQn (This is the password)
socket   = /var/run/mysqld/mysqld.sock
host     = localhost
user     = debian-sys-maint
password = X4k1p11iFNCzUGQn
socket   = /var/run/mysqld/mysqld.sock

My login method is as follows:

 mysql -udebian-sys-maint -pX4k1p11iFNCzUGQn

If you execute it in this order, you will still be prompted that you cannot log in. I've been worried about this for a long time. I've found many ways to solve it. I still try to reinstall it. The final process is to completely uninstall, restart the system, and then use the default user login after reinstallation.

mysql8 installation

Be sure to update the source

sudo apt-get update  #Update source
sudo apt-get install mysql-server #install

Verify installation

systemctl status mysql

The following prompts indicate that the installation is successful

Log in as the default mysql user and use the following command to view the root user

use mysql
select User,Host,plugin,authentication_string from user;

The print result is as follows. You can see the corresponding authentication of the root user_ The string field is empty and the password has not been initialized

| User             | Host      | plugin                | authentication_string                                                  |
| debian-sys-maint | localhost | caching_sha2_password | $A$005$%n(ds3wwI8J,"ff3AsLC578..CIRj9ll0.Wqf3AGoE5YAfDAfI.uv3qD |
| mysql.infoschema | localhost | caching_sha2_password | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| mysql.session    | localhost | caching_sha2_password | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| mysql.sys        | localhost | caching_sha2_password | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| root             | localhost | auth_socket           |                                                                        |
5 rows in set (0.00 sec)

The next step is to initialize the password for the root user

alter user 'root'@'localhost' identified by '123456';

Refresh mysql permissions

flush privileges;

When executed

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '123456';

Will report an error

ERROR 1819 (HY000): Your password does not satisfy the current policy requirements

Because the password setting does not meet the requirements, use the following command to view the password setting requirements:

SHOW VARIABLES LIKE 'validate_password%';

| Variable_name | Value |
| validate_password.check_user_name | ON |
| validate_password.dictionary_file | |
| validate_password.length | 8 |
| validate_password.mixed_case_count | 1 |
| validate_password.number_count | 1 |
| validate_password.policy | MEDIUM |
| validate_password.special_char_count | 1 |
7 rows in set (0.02 sec)

You can see validate_password.length is 8, that is, the length of the password is 8 characters, which is validated_ password. The policy is MEDIUM, and the password level is intermediate. You can modify validate_ password. The policy field is LOW level, and the command is as follows:

set global validate_password.policy=0;

After successful execution, re execute the sql statement to modify the root password. Remember to execute flush privileges again, exit, and then execute the following command to restart the mysql service.

service mysql restart

Set the host of root to allow remote access. Similarly, run flush privileges after execution

update mysql.user set Host ='%' where User = 'root';

The Host field of user is% indicating that any address can be linked. Generally, this field defaults to localhost and only local connections are allowed.

Uninstall mysql completely:

View mysql dependencies

dpkg --list | grep mysql

Delete instruction

sudo apt-get remove mysql-common

sudo apt-get autoremove --purge mysql-server

Then use the following command to check whether there are dependencies that have not been uninstalled

dpkg --list | grep mysql 

Finally, clear the residual data with the following command

dpkg -l |grep ^rc|awk '{print $2}' |sudo xargs dpkg -P

dpkg -P is a completely deleted file. You can find relevant tutorials on the Internet

Check the list of software installed from MySQL APT. the list is not displayed after execution, which proves that MySQL service has been completely uninstalled

dpkg -l | grep mysql | grep i

Keywords: Linux Ubuntu debian

Added by pbsonawane on Thu, 10 Mar 2022 02:41:13 +0200