Reset mysql5 root password in macports

I've just installed macports through macports 1.9.2 on Snow Leopard. Now I can't use mysql because it seems as if I didn't know the password.

$ sudo mysql5 -u root -p 
Enter password:  
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

I'm not sure if this is some error in the install or usage, or I just forgot the password, but I think I need to reset the password. How to do it?

UPDATE: I can start and stop my MySql server, so it shouldn't be broken, but I don't have a command like $ sudo /opt/local/lib/mysql5/bin/mysqld_safe5.

$ ls /opt/local/lib/mysql5/bin/
innochecksum            mysql_client_test       mysql_secure_installation   mysqlbinlog         mysqlimport         ndb_drop_index          ndb_select_all          resolve_stack_dump
msql2mysql          mysql_client_test_embedded  mysql_setpermission     mysqlbug            mysqlshow           ndb_drop_table          ndb_select_count        resolveip
my_print_defaults       mysql_config            mysql_tzinfo_to_sql     mysqlcheck          mysqlslap           ndb_error_reporter      ndb_show_tables
myisam_ftdump           mysql_convert_table_format  mysql_upgrade           mysqld_multi            mysqltest           ndb_mgm             ndb_size.pl
myisamchk           mysql_find_rows         mysql_waitpid           mysqld_safe         mysqltest_embedded      ndb_print_backup_file       ndb_test_platform
myisamlog           mysql_fix_extensions        mysql_zap           mysqldump           ndb_config          ndb_print_schema_file       ndb_waiter
myisampack          mysql_fix_privilege_tables  mysqlaccess         mysqldumpslow           ndb_delete_all          ndb_print_sys_file      perror
mysql               mysql_install_db        mysqladmin     开发者_开发知识库     mysqlhotcopy            ndb_desc            ndb_restore         replace

---

First of all you will need to ensure that your database is stopped:

$ sudo /opt/local/share/mysql5/mysql/mysql.server stop 

Now you should start up the database in the background, via the mysqld_safe command:

$ sudo /opt/local/lib/mysql5/bin/mysqld_safe --skip-grant-tables &

Now that the server is running with the --skip-grant-tables flag you can connect to it without a password and complete the job:

 $ sudo mysql5 --user=root mysql
 Enter password:

 mysql> update user set Password=PASSWORD('new-password-here') WHERE User='root';
 Query OK, 2 rows affected (0.04 sec)
 Rows matched: 2  Changed: 2  Warnings: 0

 mysql> flush privileges;
 Query OK, 0 rows affected (0.02 sec)

 mysql> exit
 Bye

Now that you've done that you just need to stop the server, so that you can go back to running a secure MySQL server with password restrictions in place. First of all bring the server you started into the foreground by typing "fg", then kill it by pressing "Ctrl+c" afterwards.

This will now allow you to start the server:

$ sudo /opt/local/share/mysql5/mysql/mysql.server start

---

I had the same problem. When trying to reset the password (as develroot suggested), I also got:

Rows matched: 0 Changed: 0 Warnings: 0

So the root user had never been created in the first place. I took the following steps in addition to develroot's answer:

flush privileges;
CREATE USER 'root'@'localhost' IDENTIFIED BY 'root'
GRANT ALL ON *.* TO 'root'@'localhost'

---

This is my fix.

After running this: sudo -u mysql mysql_install_db5

I got this error:

ERROR: 1004  Can't create file '/var/tmp/#sql180bd_1_0.frm' (errno: 9)
121221  2:06:21 [ERROR] Aborting

Same as previous reports, my users table was empty --- all the mysql tables were emtpy

Eventually, I figured out to do this:

1. Run mysqld in safe mode.

   sudo /opt/local/bin/mysqld_safe5 --skip-grant-tables &

2. Log in and drop the broken mysql database

   $ mysql5 $ mysql> DROP TABLE mysql;

3. Re-run the mysql_install_db5 as ROOT rather than mysql so it can at least get the job done

   $ sudo -u root mysql_install_db5

4. Then go back and chown -R everything in /opt/local/var/db/mysql5 to mysql:mysql as it needs to be.

   $ sudo chown -R mysql:mysql /opt/local/var/db/mysql5/ $ sudo chown -R mysql:mysql /opt/local/var/run/mysql5/ $ sudo chown -R mysql:mysql /opt/local/var/log/mysql5/

5. everything worked just fine after that, thanks.