Reset MySQL Root Password

If for some reason the root account your MySQL install is locked out, follow this procedure to reset it.

First, stop the MySQL server, this is dependant on how your system operates. For a Debian based box (Ubuntu, Knoppix et al):

sudo /etc/init.d/mysql stop

Next, start up MySQL and tell it to bypass the authentication tables (the & is to background the process):

mysqld_safe --skip-grant-tables &

Launch the MySQL client:

mysql -u root mysql

and finally change the root password:

update user set Password=PASSWORD('new-password') where user='root';
flush privileges;
exit;

Kill the running MySQL server and start it again as per usual.

Content copied from http://www.howtoforge.com/reset-forgotten-mysql-root-password

Database tidbits

Restore a MySQL database from a dump file:

mysql -u root -p  databasename< file.sql

Backup a MySQL database to a file:

mysqldump -u root -p databasename > file.sql

Creating a new user and database in MySQL:

First, load up the mysql client:

mysql -u root -p

Create the database:

CREATE DATABASE databasename;

Create the user:

GRANT ALL PRIVILEGES ON databasename.* TO "databaseuser"@"hostname" IDENTIFIED BY "password";

All done, clean up and exit:

FLUSH PRIVILEGES;
EXIT;