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