#1045 Cannot log in to the MySQL server

Alles, was MariaDB und MySQL betrifft, kann hier besprochen werden.

#1045 Cannot log in to the MySQL server

Postby Neilbritto » 07. October 2013 17:49

I'm running Xampp 1.8.2 on Windows 7 64bit.

My problem started with not remembering the root password. I am unable to:
    Login to the localhost/phpmyAdmin with root account
    Log in to mysql via the command line with root account
    change the root account password without errors (see below)

Detail:
. No changes I make in PHPMyAdmin's security page, or CONFIG.INC.PHP, or in the command line seem to bear any positive results for me. This has to be an utterly newbie maneuver.

Though it seems this issue has been addressed numerous times already, I've read numerous articles here and on different sites (Stackoverflow) regarding this error and still I find no thread with the answer that will meet my needs.

What I have done is repeat a few flavors of the below, numerous times, to get going. I still cannot get into phpMyAdmin or do anything in the command line with the root account.

I have:

1 reset my root Passwords for Windows Systems via a file (mysql-init.txt) containing these commands:

Code: Select all
UPDATE mysql.user SET Password=PASSWORD('1234') WHERE User='root';

FLUSH PRIVILEGES;


2 after turning off MySql, the above commands were run via the command line from my XAMPP shell (run as administrator), the syntax like so:

Code: Select all
msyqld --init-file=C:\My Website\Xampp\mysql\mysql-init.txt Note: the file resides in the mysql folder. I placed it there.


3 I at points I have modified my config.inc.php file, changing the password, but with no real different
outcome.

Code: Select all
$cfg['Servers'][$i]['auth_type']     = 'cookie';      // Authentication method (config, http or cookie based)
$cfg['Servers'][$i]['user'] = 'root';
$cfg['Servers'][$i]['password'] = 'hello';
$cfg['Servers'][$i]['extension'] = 'mysqli';
$cfg['Servers'][$i]['AllowNoPassword'] = true;
$cfg['Lang'] = '';

/* Bind to the localhost ipv4 address and tcp */
$cfg['Servers'][$i]['host'] = '127.0.0.1';
$cfg['Servers'][$i]['connect_type'] = 'tcp';

/* User for advanced features */
$cfg['Servers'][$i]['controluser'] = 'pma';
$cfg['Servers'][$i]['controlpass'] = '';

/* Advanced phpMyAdmin features */
$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]['designer_coords'] = 'pma_designer_coords';
$cfg['Servers'][$i]['tracking'] = 'pma_tracking';
$cfg['Servers'][$i]['userconfig'] = 'pma_userconfig';
$cfg['Servers'][$i]['recent'] = 'pma_recent';
$cfg['Servers'][$i]['table_uiprefs'] = 'pma_table_uiprefs';


4 if i try logging onto the PHPMyadmin webpage with the root account, I get "#1045 Cannot log in to the MySQL
server"

5 if i try logging into mysql from the command line (run as administrator), with the command: mysql -u root -p (password entered).... I get the same thing.

From the command line i've run this command as well...
Code: Select all
mysqladmin -u root password 1234
error: "Access denied for user 'root'@'Localhost' (using password: NO)

6 Also did this: SET PASSWORD FOR root@localhost = PASSWORD('1234'); Seemed to work, but i was still not able to sign on using the account with that password (error 1045).

I've heard it mentioned that there are two root accounts. Can anyone explain or point to the most succinct sources on how I am to start up using the database using these accounts? I must be doing something utterly stupid.
Please help if you can.

Thanks
Neilbritto
 
Posts: 3
Joined: 17. November 2011 22:31
Operating System: WIN2k, WIN7

Re: #1045 Cannot log in to the MySQL server

Postby Nobbie » 09. October 2013 13:21

Neilbritto wrote:3 I at points I have modified my config.inc.php file, changing the password, but with no real different
outcome.

Code: Select all
$cfg['Servers'][$i]['auth_type']     = 'cookie';      // Authentication method (config, http or cookie based)
$cfg['Servers'][$i]['user'] = 'root';
$cfg['Servers'][$i]['password'] = 'hello';
$cfg['Servers'][$i]['extension'] = 'mysqli';
$cfg['Servers'][$i]['AllowNoPassword'] = true;
$cfg['Lang'] = '';


This is useless, as for auth_type 'cookie' the above values are NOT evaluated against MySQL (and als NOT evaluated against the values you enter in the login dialog), instead the values you provide in the login dialog are evaluated against MySQL. Only for auth_type 'config' (neither for 'cookie' nor for 'http') the user and password values from the config.php.inc are taken for login into MySQL (due to the fact, that there is no login dialog for auth_type 'config'). Anyway, you supplied "hello" instead of "1234" as password. It is very confusing what you are doing there.

Neilbritto wrote:I've heard it mentioned that there are two root accounts.


No and yes. There are not two root accounts in MySQL, but on Linux Operating System the system administrator (same as "Administrator" in Windows Environments) is called "root" as well. But this is a completely different context and you cannot compare a Linux User with a MySQL User.

Neilbritto wrote:Can anyone explain or point to the most succinct sources on how I am to start up using the database using these accounts


Unfortunatey, it is the worst thing you can do to forget the "root" password of MySQL. There are some reset procedures devilered with Xampp, but these have been faulty for many years (i dont know the current status). You *should* have a backup of your data and i would recommend to do a full de-install and new install of MySQL and finalley restore the backup.

P.S.:

Code: Select all
mysqladmin -u root password 1234


This is wrong syntax anyway, i think it must be

mysqladmin -u root -p 1234

or

mysqladmin --user root --password 1234

instead. See the manual (man mysqladmin) if in doubt. Same for the "mysql" client.
Nobbie
 
Posts: 8762
Joined: 09. March 2008 13:04


Return to MariaDB - MySQL

Who is online

Users browsing this forum: No registered users and 2 guests