16

I lost the access onto MySQL v. 5.6.14. I had access and suddenly I lost it.

Looking for notes I found out the url below. I did the steps, but not success. http://dev.mysql.com/doc/refman/5.5/en/resetting-permissions.html

C:\Program Files\MySQL\bin>mysqld

2013-11-14 19:46:01 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details)

C:\Program Files\MySQL\bin>mysql -u root mysql1

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

Notice: I don't have mysqld_safe or safe_mysqld files in MySQL version

Could you please help me? Below you can see the dump file generated by the command:

C:\mysqld --init-file=C:\\mysql-init.txt

C:\Program Files\MySQL\bin>mysqld --init-file=C:\\mysql-init.txt --console

2013-11-14 17:57:20 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated.     Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2013-11-14 17:57:20 5180 [Note] Plugin 'FEDERATED' is disabled.
2013-11-14 17:57:20 5180 [Note] InnoDB: The InnoDB memory heap is disabled
2013-11-14 17:57:20 5180 [Note] InnoDB: Mutexes and rw_locks use Windows interlocked     functions
2013-11-14 17:57:20 5180 [Note] InnoDB: Compressed tables use zlib 1.2.3
2013-11-14 17:57:20 5180 [Note] InnoDB: Not using CPU crc32 instructions
2013-11-14 17:57:20 5180 [Note] InnoDB: Initializing buffer pool, size = 128.0M
2013-11-14 17:57:20 5180 [Note] InnoDB: Completed initialization of buffer pool
2013-11-14 17:57:20 5180 [ERROR] InnoDB: .\ibdata1 can't be opened in read-write mode
2013-11-14 17:57:20 5180 [ERROR] InnoDB: The system tablespace must be writable!
2013-11-14 17:57:20 5180 [ERROR] Plugin 'InnoDB' init function returned error.
2013-11-14 17:57:20 5180 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
2013-11-14 17:57:20 5180 [ERROR] Unknown/unsupported storage engine: InnoDB
2013-11-14 17:57:20 5180 [ERROR] Aborting
2013-11-14 17:57:20 5180 [Note] Binlog end
2013-11-14 17:57:20 5180 [Note] Shutting down plugin 'partition'
2013-11-14 17:57:20 5180 [Note] Shutting down plugin 'PERFORMANCE_SCHEMA'
2013-11-14 17:57:20 5180 [Note] Shutting down plugin 'INNODB_SYS_DATAFILES'
2013-11-14 17:57:20 5180 [Note] Shutting down plugin 'INNODB_SYS_TABLESPACES'
2013-11-14 17:57:20 5180 [Note] Shutting down plugin 'INNODB_SYS_FOREIGN_COLS'
2013-11-14 17:57:20 5180 [Note] Shutting down plugin 'INNODB_SYS_FOREIGN'
2013-11-14 17:57:20 5180 [Note] Shutting down plugin 'INNODB_SYS_FIELDS'
2013-11-14 17:57:20 5180 [Note] Shutting down plugin 'INNODB_SYS_COLUMNS'
2013-11-14 17:57:20 5180 [Note] Shutting down plugin 'INNODB_SYS_INDEXES'
2013-11-14 17:57:20 5180 [Note] Shutting down plugin 'INNODB_SYS_TABLESTATS'
2013-11-14 17:57:20 5180 [Note] Shutting down plugin 'INNODB_SYS_TABLES'
2013-11-14 17:57:20 5180 [Note] Shutting down plugin 'INNODB_FT_INDEX_TABLE'
2013-11-14 17:57:20 5180 [Note] Shutting down plugin 'INNODB_FT_INDEX_CACHE'
2013-11-14 17:57:20 5180 [Note] Shutting down plugin 'INNODB_FT_CONFIG'
2013-11-14 17:57:20 5180 [Note] Shutting down plugin 'INNODB_FT_BEING_DELETED'
2013-11-14 17:57:20 5180 [Note] Shutting down plugin 'INNODB_FT_DELETED'
2013-11-14 17:57:20 5180 [Note] Shutting down plugin 'INNODB_FT_DEFAULT_STOPWORD'
2013-11-14 17:57:20 5180 [Note] Shutting down plugin 'INNODB_METRICS'
2013-11-14 17:57:20 5180 [Note] Shutting down plugin 'INNODB_BUFFER_POOL_STATS'
2013-11-14 17:57:20 5180 [Note] Shutting down plugin 'INNODB_BUFFER_PAGE_LRU'
2013-11-14 17:57:20 5180 [Note] Shutting down plugin 'INNODB_BUFFER_PAGE'
2013-11-14 17:57:20 5180 [Note] Shutting down plugin 'INNODB_CMP_PER_INDEX_RESET'
2013-11-14 17:57:20 5180 [Note] Shutting down plugin 'INNODB_CMP_PER_INDEX'
2013-11-14 17:57:20 5180 [Note] Shutting down plugin 'INNODB_CMPMEM_RESET'
2013-11-14 17:57:20 5180 [Note] Shutting down plugin 'INNODB_CMPMEM'
2013-11-14 17:57:20 5180 [Note] Shutting down plugin 'INNODB_CMP_RESET'
2013-11-14 17:57:20 5180 [Note] Shutting down plugin 'INNODB_CMP'
2013-11-14 17:57:20 5180 [Note] Shutting down plugin 'INNODB_LOCK_WAITS'
2013-11-14 17:57:20 5180 [Note] Shutting down plugin 'INNODB_LOCKS'
2013-11-14 17:57:20 5180 [Note] Shutting down plugin 'INNODB_TRX'
2013-11-14 17:57:20 5180 [Note] Shutting down plugin 'BLACKHOLE'
2013-11-14 17:57:20 5180 [Note] Shutting down plugin 'ARCHIVE'
2013-11-14 17:57:20 5180 [Note] Shutting down plugin 'MRG_MYISAM'
2013-11-14 17:57:20 5180 [Note] Shutting down plugin 'MyISAM'
2013-11-14 17:57:20 5180 [Note] Shutting down plugin 'MEMORY'
2013-11-14 17:57:20 5180 [Note] Shutting down plugin 'CSV'
2013-11-14 17:57:20 5180 [Note] Shutting down plugin 'sha256_password'
2013-11-14 17:57:20 5180 [Note] Shutting down plugin 'mysql_old_password'
2013-11-14 17:57:20 5180 [Note] Shutting down plugin 'mysql_native_password'
2013-11-14 17:57:20 5180 [Note] Shutting down plugin 'binlog'
2013-11-14 17:57:20 5180 [Note] mysqld: Shutdown complete

C:\Program Files\MySQL\bin>
Community
  • 1
  • 1
Marcos
  • 844
  • 3
  • 10
  • 25
  • Have you tried `mysql -u root -p mysql1`? – gen_Eric Nov 14 '13 at 22:10
  • 1
    C:\Program Files\MySQL\bin>mysql -u root -p mysql1 Enter password: ****** ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES) C:\Program Files\MySQL\bin> – Marcos Nov 14 '13 at 22:15

5 Answers5

17

I had the same problem. mysql -u root -p worked for me. It later asks you for a password. You should then enter the password that you had set for mysql. The default password could be password, if you did not set one. More info here.

wcyn
  • 3,826
  • 2
  • 31
  • 25
  • not much info there on what the default password is. It seems to be `password` (one can open a bunch of windows and check!) – barlop Oct 15 '17 at 07:49
12

Follow the steps given below:

  1. Stop your MySQL server completely. This can be done by accessing the Services window inside Windows XP and Windows Server 2003, where you can stop the MySQL service.

  2. Open your MS-DOS command prompt using "cmd" inside the Run window. Inside it navigate to your MySQL bin folder, such as C:\MySQL\bin using the cd command.

  3. Execute the following command in the command prompt: mysqld.exe -u root --skip-grant-tables

  4. Leave the current MS-DOS command prompt as it is, and open a new MS-DOS command prompt window.

  5. Navigate to your MySQL bin folder, such as C:\MySQL\bin using the cd command.

  6. Enter mysql and press enter.

  7. You should now have the MySQL command prompt working. Type use mysql; so that we switch to the "mysql" database.

  8. Execute the following command to update the password: 

    UPDATE user SET Password = PASSWORD('NEW_PASSWORD') WHERE User = 'root';

However, you can now run any SQL command that you wish.

After you are finished close the first command prompt and type exit; in the second command prompt windows to disconnect successfully. You can now start the MySQL service.

Enamul Hassan
  • 5,266
  • 23
  • 39
  • 56
user5144705
  • 121
  • 1
  • 2
  • 1
    Sharing my experience. While performing **step 8**, if someone gets error like this: **ERROR 1054 (42S22): Unknown column 'Password' in 'field list'**. Then follow this link: http://stackoverflow.com/a/31122246/1465553 – Saurav Sahu May 23 '16 at 15:49
  • 2
    On newer versions of mysql use ` UPDATE user SET authentication_string = PASSWORD('NEW_PASSWORD') WHERE User = 'root'; ` – jfgrissom Aug 08 '16 at 22:24
1

I encountered the same problem, probably when I uninstalled it and tried to install it again. This happens because of the database file containing login details is still stored in the pc, and the new password will not match the older one. So you can solve this by just uninstalling mysql, and then removing the left over folder from the C: drive (or wherever you must have installed).

Pokechu22
  • 4,984
  • 9
  • 37
  • 62
Nabil
  • 11
  • 5
1

The following steps are to reset the password for a user in case you forgot, this would also solve your mentioned error.

First, stop your MySQL:

sudo /etc/init.d/mysql stop

Now start up MySQL in safe mode and skip the privileges table:

sudo mysqld_safe --skip-grant-tables &

Login with root:

mysql -uroot

And assign the DB that needs to be used:

use mysql;

Now all you have to do is reset your root password of the MySQL user and restart the MySQL service:

update user set password=PASSWORD("YOURPASSWORDHERE") where User='root';

flush privileges;

quit and restart MySQL:

quit

sudo /etc/init.d/mysql stop sudo /etc/init.d/mysql start Now your root password should be working with the one you just set, check it with:

mysql -u root -p
Ram Patra
  • 16,266
  • 13
  • 66
  • 81
0

Your problem is here:

2013-11-14 17:57:20 5180 [ERROR] InnoDB: .\ibdata1 can't be opened in read-write mode

There's some problem with the ibdata1 file - maybe the permissions have changed on it? Perhaps some other process has it open. Does it even exist?

Fix this and possibly everything else will fall into place.