1

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

halfer
  • 19,824
  • 17
  • 99
  • 186
York Wang
  • 1,909
  • 5
  • 15
  • 27
  • 1
    `mysql -u root -p` or look at your conf file, or http://stackoverflow.com/q/25896082 – Drew Jul 08 '16 at 19:15

1 Answers1

3

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

pah
  • 4,700
  • 6
  • 28
  • 37
  • Hi thanks for the reply. I tried your method but got stucked at step 3. `Yorks-MacBook-Pro:~ yorkwang$ mysql -uroot ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)' not too sure what did i do wrong here – York Wang Jul 08 '16 at 21:54
  • It means that the mysqld_safe isn't running. What is the output of step 2? (append the output to your question, so I can take a look). – pah Jul 08 '16 at 21:57
  • Yorks-MacBook-Pro:~ yorkwang$ sudo mysqld_safe --skip-grant-tables 2016-07-08T21:53:01.6NZ mysqld_safe Logging to '/usr/local/var/mysql/Yorks-MacBook-Pro.local.err'. 2016-07-08T21:53:01.6NZ mysqld_safe Starting mysqld daemon with databases from /usr/local/var/mysql 2016-07-08T21:53:02.6NZ mysqld_safe mysqld from pid file /usr/local/var/mysql/Yorks-MacBook-Pro.local.pid ended – York Wang Jul 08 '16 at 21:58
  • Not too sure how did i mess up so bad here. I've been searching a solution online since yesterday :/ – York Wang Jul 08 '16 at 22:07
  • Issue the following command: `$ ps -ef | grep mysql` and paste the output please. – pah Jul 08 '16 at 22:10
  • `501 68112 1 0 11:43am ?? 0:02.69 mysqld --skip-grant-tables 501 31415 19463 0 3:11pm ttys000 0:00.00 grep mysql` – York Wang Jul 08 '16 at 22:19
  • 1
    try the following now: `$ mysql -uroot -h 127.0.0.1` – pah Jul 08 '16 at 22:19
  • Yes! I got in! thank you so much! How do I go from here now? just reset my password I guess? – York Wang Jul 08 '16 at 22:24
  • Follow step 4 of my answer (ALTER ...), then restart mysql. – pah Jul 08 '16 at 22:24
  • hi, sry to bother you again. :( So I changed the password following step 4, everything worked fine. Then I exit mysql, restart my computer, and it looks like my new password did not take effect for some reasons. So I went back and try to repeat the process, and encountered a new issue: – York Wang Jul 08 '16 at 23:42
  • ##After typing in `$ ps -ef | grep mysql` , The following output pops up `74 97 1 0 4:07pm ?? 0:00.94 /usr/local/mysql/bin/mysqld --user=_mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data --plugin-dir=/usr/local/mysql/lib/plugin --log-error=/usr/local/mysql/data/mysqld.local.err --pid-file=/usr/local/mysql/data/mysqld.local.pid 501 19105 943 0 4:38pm ttys000 0:00.00 grep mysql` – York Wang Jul 08 '16 at 23:43
  • ##Then I tried to log in using `$ mysql -uroot -h 127.0.0.1` and it returns `ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)` ##I'm not sure where i messed up this time : – York Wang Jul 08 '16 at 23:44
  • I think there's a problem in your MySQL installation... anyway, do the following: `$ sudo killall -TERM mysqld mysqld_safe` and then repeat the steps 2, 3 and 4. Also, after step 4, issue an additional: `mysql> ALTER USER 'root'@'127.0.0.1' IDENTIFIED BY 'MyNewPass';` (note that i changed 'localhost' to '127.0.0.1'). Then, restart your system, and try to login to MySQL with: `$ mysql -uroot -h 127.0.0.1 -p` ... if that works, it means that the MySQL unix socket for local communications does not exist or it's improperly configured. – pah Jul 08 '16 at 23:48
  • Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/116843/discussion-between-york-wang-and-threadp). – York Wang Jul 08 '16 at 23:55