Who here knows how to reset a MySQL root password?

LoneIgadzra

That question is a bit rhetorical, since the instructions are right here: http://dev.mysql.com/doc/refman/5.0/en/resetting-permissions.html
Too bad they don't work on my installation.

I have MySQL installed on my MacBook Pro. I went to change the root account password, and something got screwed up so now I can't connect. The terminal window conveniently forgot what I did since I did some ls commands with very long printounts in and around all this, trying to even find where the damn thing's configuration file or databases are stored (like it's some big secret). The instructions in the manual don't work. This is what happens:

betaagamemnon:/ william$ sudo mysqld_safe --init-file=~/password
Starting mysqld daemon with databases from /usr/local/mysql/data
STOPPING server from pid file /usr/local/mysql/data/betaagamemnon.pid
070914 19:35:58  mysqld ended

The password is not changed, as I cannot connect by typing "mysql -u root -p" and entering the password in my init file. I don't know if I have two mysql's in here or what, but for some reason the "whereis" command refuses to give me any results. This is what I have:

betaagamemnon:/ william$ cd /usr/local
betaagamemnon:/usr/local william$ ls -FG
bin/                       lib/                       php5/
etc/                       man/                       sbin/
include/                   mysql@                     share/
info/                      mysql-5.0.41-osx10.4-i686/

I realize I should be using the mysql forums, but I already did and the helpful folks there are ignoring me. If worst comes to worst, is there some way I can back up my databases without being able to connect to the server, so I can just reinstall?

Jimmy King

To backup the db's just copy the actual files... unfortunately I don't know where they are on OS X. You should be able to do something like 'find / -name *.MYI', though, and they'll pop up so you know where they are if you don't already know. There should be a directory for each db with several files in it for each table.

Just copy them somewhere else, re-install mysql, copy them back to the same place and then go set your user accounts and access permissions back up.

LoneIgadzra

EDIT: Just noticed the problem while looking at this. Still, I'll leave it up to serve as a lesson and to chronicle my folly:

Well, that seemed promising. I got the database I need copied into a new installation and chmod'ed correctly, but for some reason I get read errors:

mysql> explain ded_dories;
ERROR 1017 (HY000): Can't find file: './instantboats_com/ded_dories.frm' (errno: 13)

Where the permissions are like so (I set the group as admin so I wouldn't need to keep doing sudo just to browse):

betaagamemnon:/usr/local/mysql-5.0.45-osx10.4-i686/data william$ ls -lFG
total 41040
-rw-rw----    1 mysql  admin     32818 Sep 15 12:00 betaagamemnon.err
-rw-rw----    1 mysql  admin         5 Sep 15 11:41 betaagamemnon.pid
-rw-rw----    1 mysql  admin   5242880 Sep 15 11:41 ib_logfile0
-rw-rw----    1 mysql  admin   5242880 Sep 15 11:41 ib_logfile1
-rw-rw----    1 mysql  admin  10485760 Sep 15 11:41 ibdata1
drwxr-x---   21 mysql  admin       714 Sep 15 11:47 instantboats_com/
drwxr-x---   53 mysql  admin      1802 Sep 15 11:41 mysql/
drwxr-x---    2 mysql  admin        68 Jul  4 13:53 test/
betaagamemnon:/usr/local/mysql-5.0.45-osx10.4-i686/data william$ ls -lFG instantboats_com/
total 264
-rw-rw----   1 660  admin    65 Sep 15 11:47 db.opt
-rw-rw----   1 660  admin  2684 Sep 15 11:47 ded_dories.MYD
-rw-rw----   1 660  admin  4096 Sep 15 11:47 ded_dories.MYI
-rw-rw----   1 660  admin  9480 Sep 15 11:47 ded_dories.frm
-rw-rw----   1 660  admin   980 Sep 15 11:47 ded_pages.MYD
-rw-rw----   1 660  admin  3072 Sep 15 11:47 ded_pages.MYI
-rw-rw----   1 660  admin  8654 Sep 15 11:47 ded_pages.frm
-rw-rw----   1 660  admin    84 Sep 15 11:47 ib_boats.MYD
-rw-rw----   1 660  admin  4096 Sep 15 11:47 ib_boats.MYI
-rw-rw----   1 660  admin  8912 Sep 15 11:47 ib_boats.frm
-rw-rw----   1 660  admin  1912 Sep 15 11:47 ib_books.MYD
-rw-rw----   1 660  admin  4096 Sep 15 11:47 ib_books.MYI
-rw-rw----   1 660  admin  9134 Sep 15 11:47 ib_books.frm
-rw-rw----   1 660  admin  9360 Sep 15 11:47 ib_pages.MYD
-rw-rw----   1 660  admin  3072 Sep 15 11:47 ib_pages.MYI
-rw-rw----   1 660  admin  8724 Sep 15 11:47 ib_pages.frm
-rw-rw----   1 660  admin    32 Sep 15 11:47 payson_prices.MYD
-rw-rw----   1 660  admin  4096 Sep 15 11:47 payson_prices.MYI
-rw-rw----   1 660  admin  8648 Sep 15 11:47 payson_prices.frm

12gauge

By the way, did you read the comments?

Version 4.1.8. With this version the given command

mysqld_safe --skip-grant-tables &

does not work as is. As soon as you enter the command, mysqld_safe quits. This is because, with 4.1.8 they have put security check to see if mysql is being run as root. So, it needs to be explicity specified that it needs to be run as root. So, everything else in the given instruction remains the same. Insted of the above command run

mysqld_safe --skip-grant-tables -u root &

Jimmy King

man, I completely forgot about --skip-grant-tables