I am a newbie in MySQL, Apache, PHP, and phpMyAdmin. The first server I built was on Windows Vista x32bit and it was successful. Then, I try to build the second server on another laptop, it is Windows 7 x64bit and it stucked in error code #2054. I use ‘localhost’ with a fixed IP address. I installed the server individually from .msi files, NOT a package installer.
After I enter my ‘root’ and my ‘password’, then I clicked ‘Go’, it popped up two error messages, one is «#2054 Cannot log in to the MySQL server» and another is «Connection for controluser as defined in your configuration failed.»
I searched many resources online, but out of luck.
How can I solve these two errors?
Any info in helpful. Thanks in advance.
Below is my config.inc.php:
enter code here <?php
/* vim: set expandtab sw=4 ts=4 sts=4: */
/**
* phpMyAdmin sample configuration, you can use it as base for
* manual configuration. For easier setup you can use setup/
*
* All directives are explained in Documentation.html and on phpMyAdmin
* wiki <http://wiki.phpmyadmin.net>.
*
* @package PhpMyAdmin
*/
/*
* This is needed for cookie based authentication to encrypt password in
* cookie
*/
$cfg['blowfish_secret'] = 'a8b7c6d'; /* YOU MUST FILL IN THIS FOR COOKIE AUTH! */
/*
* Servers configuration
*/
$i = 0;
/*
* First server
*/
$i++;
/* Authentication type */
$cfg['Servers'][$i]['auth_type'] = 'cookie';
/* Server parameters */
$cfg['Servers'][$i]['host'] = 'localhost';
$cfg['Servers'][$i]['connect_type'] = 'tcp';
$cfg['Servers'][$i]['compress'] = false;
/* Select mysql if your server does not have mysqli */
$cfg['Servers'][$i]['extension'] = 'mysqli';
$cfg['Servers'][$i]['AllowNoPassword'] = false;
/*
* phpMyAdmin configuration storage settings.
*/
/* User used to manipulate with storage */
// $cfg['Servers'][$i]['controlhost'] = '';
$cfg['Servers'][$i]['controluser'] = 'phpmyadmin';
$cfg['Servers'][$i]['controlpass'] = 'johan1234';
/* Storage database and tables */
$cfg['Servers'][$i]['pmadb'] = 'phpmyadmin';
$cfg['Servers'][$i]['bookmarktable'] = 'pma_bookmark';
$cfg['Servers'][$i]['relation'] = 'pma_relation';
$cfg['Servers'][$i]['table_info'] = 'pma_table_info';
$cfg['Servers'][$i]['table_coords'] = 'pma_table_coords';
$cfg['Servers'][$i]['pdf_pages'] = 'pma_pdf_pages';
$cfg['Servers'][$i]['column_info'] = 'pma_column_info';
$cfg['Servers'][$i]['history'] = 'pma_history';
$cfg['Servers'][$i]['table_uiprefs'] = 'pma_table_uiprefs';
$cfg['Servers'][$i]['tracking'] = 'pma_tracking';
$cfg['Servers'][$i]['designer_coords'] = 'pma_designer_coords';
$cfg['Servers'][$i]['userconfig'] = 'pma_userconfig';
$cfg['Servers'][$i]['recent'] = 'pma_recent';
/* Contrib / Swekey authentication */
// $cfg['Servers'][$i]['auth_swekey_config'] = '/etc/swekey-pma.conf';
/*
* End of servers configuration
*/
/*
* Directories for saving/loading files from server
*/
$cfg['UploadDir'] = '';
$cfg['SaveDir'] = '';
/**
* Defines whether a user should be displayed a "show all (records)"
* button in browse mode or not.
* default = false
*/
//$cfg['ShowAll'] = true;
/**
* Number of rows displayed when browsing a result set. If the result
* set contains more rows, "Previous" and "Next".
* default = 30
*/
//$cfg['MaxRows'] = 50;
/**
* Use graphically less intense menu tabs
* default = false
*/
//$cfg['LightTabs'] = true;
/**
* disallow editing of binary fields
* valid values are:
* false allow editing
* 'blob' allow editing except for BLOB fields
* 'all' disallow editing
* default = blob
*/
//$cfg['ProtectBinary'] = 'false';
/**
* Default language to use, if not browser-defined or user-defined
* (you find all languages in the locale folder)
* uncomment the desired line:
* default = 'en'
*/
//$cfg['DefaultLang'] = 'en';
//$cfg['DefaultLang'] = 'de';
/**
* default display direction (horizontal|vertical|horizontalflipped)
*/
//$cfg['DefaultDisplay'] = 'vertical';
/**
* How many columns should be used for table display of a database?
* (a value larger than 1 results in some information being hidden)
* default = 1
*/
//$cfg['PropertiesNumColumns'] = 2;
/**
* Set to true if you want DB-based query history.If false, this utilizes
* JS-routines to display query history (lost by window close)
*
* This requires configuration storage enabled, see above.
* default = false
*/
//$cfg['QueryHistoryDB'] = true;
/**
* When using DB-based query history, how many entries should be kept?
*
* default = 25
*/
//$cfg['QueryHistoryMax'] = 100;
/*
* You can find more configuration options in Documentation.html
* or here: http://wiki.phpmyadmin.net/pma/Config
*/
?>
At the first time I entered to phpMyAdmin with the default root user settings I changed the password of root and as well I changed the way it encrypts the password (there is two options when changing password I choosed the second one)
After that it thrown me out to reconnect with new password.
Since than I am unable to log in to phpMyAdmin and receiving the #2054 error.
I tried to change password through ssh(I think it not succeeded),
also tried to change config.inc.php settings
from
$cfg['Servers'][$i]['auth_type'] = 'cookie';
to
$cfg['Servers'][$i]['auth_type'] = 'config';
$cfg['Servers'][$i]['user'] = 'root';
$cfg['Servers'][$i]['password'] = '';
as well tried with password I entered in phpMyAdmin and that I tried to set in shell.
Also tried to set
$cfg['Servers'][$i]['host'] = '127.0.0.1';
And it did not succeeded.
I hope there is a way to log in without reinstalling the phpMyAdmin.
If you need some details to help me — say what you need and how to get it.
Regards.
asked Dec 12, 2013 at 14:31
Reinstalling phpMyAdmin won’t help, because you’re really dealing with the MySQL authentication tables. phpMyAdmin is just a tool to access MySQL.
You should be able to leave your auth_type set to cookie if you prefer. Changing the auth_type does affect how you log in (in this case, whether or not you’re prompted for the password when connecting), but it’s not preventing you from connecting in this case.
To reset your password, see the MySQL manual for help.
answered Dec 13, 2013 at 5:49
Isaac BennetchIsaac Bennetch
11.7k2 gold badges32 silver badges43 bronze badges
2002 Cannot log in to the MySQL server
i solved the above problem by simply running :
sudo service mysql start
then retrying log-on—success
answered Feb 12, 2015 at 14:32
Полностью ошибка звучит так
SQLSTATE[HY000] [2054] The server requested authentication method unknown to the client
Причины возникновения — связка MySQL 8 и PHP 7. Дословно — при подключении к бд не воспринимается метод аутентификации. Причина в том, что в MySQL 8 используется методика аутентификации, отличная от предыдущих версий. В MySQL 8 была изменена схема хранения пароля, начиная с версии 8.0.4 длина хэша пароля увеличена до максимального (255 символов), а также изменили плагин аутентификации – вместо mysql_native_password используется caching_sha2_password. Потому, авторизоваться паролем, который был создан для подключения например пользователем bazauser при установке базы данных, не получиться.
Чтобы авторизация прошла достаточно выполнить следующую команду в консоли MySQL
ALTER USER ‘bazauser’@’localhost’ IDENTIFIED WITH mysql_native_password BY ‘newpassword’;
где bazauser — ваше имя пользователя, newpassword — ваш пароль пользователя
Столкнулся намедни с проблемой аутентификации пользователя root в phpmyadmin. Версия mysql 8. Ошибки возникают следующие:
The server requested authentication method unknown to the client mysqli_real_connect(): The server requested authentication method unknown to the client [caching_sha2_password] mysqli_real_connect(): (HY000/2054): The server requested authentication method unknown to the client
Решений есть несколько.
1. Добавить настройку в файл my.cnf:
default_authentication_plugin=mysql_native_password
2. Авторизоваться в mysql под root в консоле и выполнить:
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'пароль'; ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY 'root'; ALTER USER 'default'@'%' IDENTIFIED WITH mysql_native_password BY 'secret';
Первый вариант мне не помог, а вот второй да.
Info:
- Docker version: 17.12.0-ce, build c97c6d6
- Laradock commit: 13f55ef
- System info: Win
- System info disto/version: 10
Issue:
New fresh laradock installation. All default settings. Driver is shared.
Except:
DATA_SAVE_PATH=../../data
Can’t connect to the mysql from phpMyAdmin.
#2054 - The server requested authentication method unknown to the client
mysqli_real_connect(): The server requested authentication method unknown to the client [caching_sha2_password]
mysqli_real_connect(): (HY000/2054): The server requested authentication method unknown to the client
But I can connect to mysql inside mysql container with standard root/root credentials.
I tried to delete all containers and images. Several times downloaded laradock. Tried to reinstall Docker. No success.
Expected behavior:
Can connect to mysql with phpMyAdmin
Logs
$ docker logs laradock_mysql_1
2018-02-23T13:22:10.682788Z 0 [Warning] [MY-011070] Disabling symbolic links using --skip-symbolic-links (or equivalent) is the default. Consider not using this option as it is deprecated and will be removed in a future release.
2018-02-23T13:22:10.682938Z 0 [Warning] [MY-010915] 'NO_ZERO_DATE', 'NO_ZERO_IN_DATE' and 'ERROR_FOR_DIVISION_BY_ZERO' sql modes should be used with strict mode. They will be merged with strict mode in a future release.
2018-02-23T13:22:10.695441Z 0 [System] [MY-010116] /usr/sbin/mysqld (mysqld 8.0.4-rc-log) starting as process 1 ...
mbind: Operation not permitted
mbind: Operation not permitted
2018-02-23T13:22:11.537504Z 0 [Warning] [MY-010068] CA certificate ca.pem is self signed.
2018-02-23T13:22:11.542367Z 0 [Warning] [MY-000000] Insecure configuration for --pid-file: Location '/var/run/mysqld' in the path is accessible to all OS users. Consider choosing a different directory.
2018-02-23T13:22:11.549449Z 0 [Warning] [MY-010315] 'user' entry 'mysql.infoschema@localhost' ignored in --skip-name-resolve mode.
2018-02-23T13:22:11.549556Z 0 [Warning] [MY-010315] 'user' entry 'mysql.session@localhost' ignored in --skip-name-resolve mode.
2018-02-23T13:22:11.549594Z 0 [Warning] [MY-010315] 'user' entry 'mysql.sys@localhost' ignored in --skip-name-resolve mode.
2018-02-23T13:22:11.549629Z 0 [Warning] [MY-010315] 'user' entry 'root@localhost' ignored in --skip-name-resolve mode.
2018-02-23T13:22:11.549676Z 0 [Warning] [MY-010323] 'db' entry 'performance_schema mysql.session@localhost' ignored in --skip-name-resolve mode.
2018-02-23T13:22:11.549727Z 0 [Warning] [MY-010323] 'db' entry 'sys mysql.sys@localhost' ignored in --skip-name-resolve mode.
2018-02-23T13:22:11.549779Z 0 [Warning] [MY-010311] 'proxies_priv' entry '@ root@localhost' ignored in --skip-name-resolve mode.
2018-02-23T13:22:11.565161Z 0 [Warning] [MY-010330] 'tables_priv' entry 'user mysql.session@localhost' ignored in --skip-name-resolve mode.
2018-02-23T13:22:11.565260Z 0 [Warning] [MY-010330] 'tables_priv' entry 'sys_config mysql.sys@localhost' ignored in --skip-name-resolve mode.
2018-02-23T13:22:11.570621Z 0 [System] [MY-010931] /usr/sbin/mysqld: ready for connections. Version: '8.0.4-rc-log' socket: '/var/run/mysqld/mysqld.sock' port: 3306 MySQL Community Server (GPL).