Home / Questions / 186315
Accepted
nachfren
Asked: 2020-08-05 06:09:40 +0800 CST 2020-08-05 06:09:40 +0800 CST

ERROR 1045 (28000): Access denied for user 'root'@'localhost' when loading sql

  • 772

Version: Server version: 10.1.32-MariaDB

I'm having trouble setting a password in mysql o mariadbusexampp 5.6

I install xampp I enter the console and mysql -u rootI log in fine.

Following different examples that I have seen on different web pages, I have tried different ways to change the password, each time I have uninstalled, I have installed xampp again.

Test 1:

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

Test 2:

# mysqladmin -u root -p password clave
Enter password:

PC-CASA@DESKTOP-11KK10F c:\xampp
# mysql -u root -pclave
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 3
Server version: 10.1.32-MariaDB mariadb.org binary distribution

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> use mysql
Database changed
MariaDB [mysql]>

Trying either of the two ways if I access from the console or the workbench it accesses perfectly, if I restart mysql it also accesses perfectly.

The problem comes when I load a *.sqlfile with 2 databases, the load is done correctly, I see the tables and everything, but when I restart mysql when trying to access it again, it gives me the error:

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

Proof

MariaDB [(none)]> ALTER USER 'root'@'localhost' IDENTIFIED BY 'clave';
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'USER 'root'@'localhost' IDENTIFIED BY 'clave'' at line 1
MariaDB [(none)]>
mysql

2 Answers

  1. Best Answer

    I tell you the following, I have in my computer equipment

    1. MySQL 8
    2. MariaDB 10.3

    Although I don't work with XAMPP, I do the password change in this way

    ALTER USER 'root'@'localhost' IDENTIFIED BY 'nuevacontraseña';
    1. root or the username equivalent you have
    2. localhost or the equivalent of your computer equipment
    3. newpassword here you are going to put the new password that you want to assign be careful with what you write

    If the change was successful you should see a message similar to the following

    Query OK, 0 rows affected (0.20 sec)

    TEST 1. MYSQL 8

    mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'nuevopassword';
Query OK, 0 rows affected (0.20 sec)

    TEST 2. MariaDB 10.3

    MariaDB [(none)]> ALTER USER 'root'@'localhost' IDENTIFIED BY 'nuevopassword';
Query OK, 0 rows affected (0.001 sec)

    For both situations, once the change has been made and you see said message, close the terminal and try to log in, but with the new password that you set.

    Here I leave you an official link to the MySQL documentation that explains it in more detail, however as I show you in the example it also works for mariaDB

    https://dev.mysql.com/doc/refman/8.0/en/resetting-permissions.html

    • 2

  2. Try the following. Login to the mariadb root account with mysql -u root -p. Enter your password, if you have one. Create the new user by entering:

    CREATE USER 'nombre_usuario'@'localhost' IDENTIFIED BY 'tu_contrasena';

    Now give permissions to your user by entering:

    GRANT ALL PRIVILEGES ON *.* TO 'nombre_usuario'@'localhost';

    If you want it to have access to all databases. Otherwise:

    GRANT ALL PRIVILEGES ON DB_NAME.* TO 'nombre_usuario'@'localhost';

    Sales of mariadb. You log in again with the new user: mysql -u username -p. You enter your password. And you apply your sql.

    • 0