Tag: mysql 8

  • ERROR 1045 (28000): Plugin caching_sha2_password could not be loaded

    When I try to connect to a MySQL 8 server, I get the following error

    root@ok:~# mysql -u serverok -p'PW_HERE' -h db-mysqlserverok.in -P 3306
ERROR 1045 (28000): Plugin caching_sha2_password could not be loaded: /usr/lib/x86_64-linux-gnu/mariadb19/plugin/caching_sha2_password.so: cannot open shared object file: No such file or directory
root@ok:~#

    This is because of MySQL 8 use more secure authentication. This is not supported by your MySQL installation. What you can do is connect using MySQL 8 client or change the authentication method to use old authentication.

    To create a user with the old authentication method, use the following SQL commands

    CREATE USER 'admin'@'localhost' IDENTIFIED WITH mysql_native_password BY 'PASSWORD_HERE';
GRANT ALL PRIVILEGES ON *.* TO 'admin'@'localhost';
FLUSH PRIVILEGES;

    To change a user to use mysql_native_password, run

    ALTER USER ‘username’@’localhost’ IDENTIFIED WITH ‘mysql_native_password’ BY ‘PASSWORD_HERE’;

    if you need to set this server wide, edit my.cnf and add

    [mysqld]
default_authentication_plugin=mysql_native_password

    See MySQL

  • You must reset your password using ALTER USER

    On MySQL 8, when you start MySQL, it generate a temporary root password, that you can find with command

    grep 'temporary password' /var/log/mysqld.log

    When i try run some SQL commands after login with this temporary password, i get error

    ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.

    To fix this, you need to change your MySQL root password by running

    ALTER USER 'root'@'localhost' IDENTIFIED  BY 'MYSQL_ROOT_PASSWORD';

    MySQL 8 on CentOS Change MySQL Root Password