Reset MySQL root password not working

Question

I've setup MySQL on my mac a while ago and I forgot what my root password was.(there are only a few password combinations I use, and none of them seems to work) I've tried many methods, including trying to reset the root password in safe mode. Nothing seems to be working for me.

Below is the log that I got from a few solutions I tried:

1.Normal Login

Yorks-MacBook-Pro:~ yorkwang$ mysql -u root ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

Yorks-MacBook-Pro:~ yorkwang$ mysql -u root -p Enter password: ERROR 1045 (28000): Access denied for user 'root'@'localhost' (usingpassword: YES)

2.Safemode Password Reset

Yorks-MacBook-Pro:~ yorkwang$ mysqld_safe --skip-grant-tables
2016-07-08T18:37:50.6NZ mysqld_safe Logging to '/usr/local/var/mysql/Yorks-MacBook-Pro.local.err'.
2016-07-08T18:37:51.6NZ mysqld_safe Starting mysqld daemon with databases from /usr/local/var/mysql
/usr/local/bin/mysqld_safe: line 135: /usr/local/var/mysql/Yorks-MacBook-Pro.local.err: Permission denied
rm: /tmp/mysql.sock: Permission denied
/usr/local/bin/mysqld_safe: line 169: /usr/local/var/mysql/Yorks-MacBook-Pro.local.err: Permission denied
2016-07-08T18:37:51.6NZ mysqld_safe mysqld from pid file /usr/local/var/mysql/Yorks-MacBook-Pro.local.pid ended
/usr/local/bin/mysqld_safe: line 135: /usr/local/var/mysql/Yorks-MacBook-Pro.local.err: Permission denied

3.Safemode Password Reset (with MYSQL server turned off)

 [1]+  Stopped                 sudo mysqld --skip-grant-tables
 Yorks-MacBook-Pro:~ yorkwang$

 2016-07-08T18:43:50.807384Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
 2016-07-08T18:43:50.809064Z 0 [Warning] Insecure configuration for --secure-file-priv: Current value does not restrict location of generated files. Consider setting it to a valid, non-empty path.
 2016-07-08T18:43:50.810163Z 0 [Note] mysqld (mysqld 5.7.11) starting as process 68112 ...
 2016-07-08T18:43:50.823374Z 0 [Warning] Setting lower_case_table_names=2 because file system for /usr/local/var/mysql/ is case insensitive
 2016-07-08T18:43:50.835814Z 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
 2016-07-08T18:43:50.835842Z 0 [Note] InnoDB: Uses event mutexes
 2016-07-08T18:43:50.835848Z 0 [Note] InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier
 2016-07-08T18:43:50.835852Z 0 [Note] InnoDB: Compressed tables use zlib 1.2.3
 2016-07-08T18:43:50.837573Z 0 [Note] InnoDB: Number of pools: 1
 2016-07-08T18:43:50.839941Z 0 [Note] InnoDB: Using CPU crc32 instructions 2016-07-08T18:43:50.850947Z 0 [Note] InnoDB: Initializing buffer pool, total size = 128M, instances = 1, chunk size = 128M
 2016-07-08T18:43:50.865025Z 0 [Note] InnoDB: Completed initialization of buffer pool
 2016-07-08T18:43:50.891791Z 0 [Note] InnoDB: Highest supported file format is Barracuda.
 2016-07-08T18:43:50.926530Z 0 [Note] InnoDB: Creating shared tablespace for temporary tables
 2016-07-08T18:43:50.926759Z 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
 2016-07-08T18:43:50.958427Z 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB.
 2016-07-08T18:43:50.959275Z 0 [Note] InnoDB: 96 redo rollback segment(s) found. 96 redo rollback segment(s) are active.
 2016-07-08T18:43:50.959287Z 0 [Note] InnoDB: 32 non-redo rollback segment(s) are active.
 2016-07-08T18:43:50.959505Z 0 [Note] InnoDB: Waiting for purge to start
 2016-07-08T18:43:51.014726Z 0 [Note] InnoDB: 5.7.11 started; log sequence number 2494670
 2016-07-08T18:43:51.015106Z 0 [Note] InnoDB: Loading buffer pool(s) from /usr/local/var/mysql/ib_buffer_pool
 2016-07-08T18:43:51.015947Z 0 [Note] Plugin 'FEDERATED' is disabled.
 2016-07-08T18:43:51.029570Z 0 [Note] Found ca.pem, server-cert.pem and server-key.pem in data directory. Trying to enable SSL support using them.
 2016-07-08T18:43:51.029602Z 0 [Note] Skipping generation of SSL certificates as certificate files are present in data directory.
 2016-07-08T18:43:51.031297Z 0 [Note] InnoDB: Buffer pool(s) load completed at 160708 11:43:51
 2016-07-08T18:43:51.033667Z 0 [Warning] CA certificate ca.pem is self signed.
 2016-07-08T18:43:51.034667Z 0 [Note] Skipping generation of RSA key pair as key files are present in data directory.
 2016-07-08T18:43:51.041311Z 0 [Note] Server hostname (bind-address): '*'; port: 3306
 2016-07-08T18:43:51.041463Z 0 [Note] IPv6 is available.
 2016-07-08T18:43:51.041482Z 0 [Note]   - '::' resolves to '::';
 2016-07-08T18:43:51.041495Z 0 [Note] Server socket created on IP: '::'.
 2016-07-08T18:43:51.149448Z 0 [Note] mysqld: ready for connections. Version: '5.7.11'  socket: '/tmp/mysql.sock'  port: 3306  Homebrew
 mysql -u root mysql
 ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)

Additional info: I'm using a macbook pro with OSX 10.11.5

Answer 1

When you are trying to start the MySQL server without the grant tables, you're receiving (multiple) Permission Denied. For instance,

/usr/local/bin/mysqld_safe: line 135: /usr/local/var/mysql/Yorks-MacBook-Pro.local.err: Permission denied

and that's why you're being unable to connect to the server (it never had the chance to start).

So, first of all, kill any mysqld_safe that might be already running (as super user):

$ sudo killall -TERM mysqld_safe; sleep 5; sudo killall -TERM mysqld

Check if the process is dead:

$ ps -ef | grep mysql

If not, and if you don't care about losing database integrity (possible data loss) issue the commands:

$ sudo killall -KILL mysqld_safe; sleep 5; sudo killall -KILL mysqld

Then, try to start the mysqld_safe with super user privileges (note the --skip-grant-tables):

$ sudo mysqld_safe --skip-grant-tables

Then connect to the MySQL Server (avoiding the use of the mysql unix socket):

$ mysql -uroot -h 127.0.0.1 --protocol=tcp

And then, under the MySQL Console:

mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'MyNewPass';
mysql> ALTER USER 'root'@'127.0.0.1' IDENTIFIED BY 'MyNewPass';

References:

B.5.3.2 How to Reset the Root Password