FreeBSDwiki:MySQL, setting root password

From Ubuntuwiki.net

A default MySQL installation has no root password, meaning that any shell user may log in as root without challenge, but remote SQL clients may not authenticate as root at all. So if you need to allow people shell accounts on a server but don't want them to mess with your MySQL tables, or if you do need to allow someone root access to MySQL from another machine without giving them root access to your server itself, you'll need to set a MySQL root password.

oyabun# /usr/local/bin/mysqladmin -u root password 'new-password'
oyabun# /usr/local/bin/mysqladmin -u root -h oyabun.n3s.local password 'new-password' 

Change 'new-password' to whatever you really want use as your password. In the second line, change oyabun.n3s.local to whatever your current domain qualification is for your server. The MySQL install will tell you - remember, I pulled this example out of the output at end-of-install. If you're copying and pasting this as you go into your putty session or other ssh client, WATCH OUT FOR LINEBREAKS. If you copy the carriage return as well, you really did just set your password to 'new-password.' (Hey, don't laugh, it just happened to me.)

If that happens, you'll have to issue a slightly different command to change a current password.

oyabun# /usr/local/bin/mysqladmin -p password 'the-REAL-password' 
Enter password:
oyabun#

Since you're logged in as root, this will change your password after you enter the current password, which if you screwed up like I did, is/was 'new-password'. Crisis averted.

Note: From now on, you can't just type mysql -u root at a command prompt to enter the mysql client, you have to add -p to make it challenge you for a password as well.

See also: MySQL