Categorized: Backup, Database

MySQL Commands You Should Have Committed to Memory Already

mysqldump -h database_host -uUsername -p database_name > dump.sql

If you need to create the database first:

mysqladmin -h database_host -uUsername -p create database_name

Then load the SQL file:

mysql -h database_host -uUsername -p database_name < dump.sql

Comments

JTJ → January 16th, 2008 at 11:38 pm

A few things to note:

If your host is localhost, you don’t have to do the -h
It might be a little safer to just put -p without the password in the command. That way, it’ll prompt you for it, and it’s not in your history or anything.
If you’re just doing a backup dump, you can save space by piping your dump through gzip like so: mysqldump -u user -p database_name | gzip > dump_file.sql.gz
Finally, sometimes there are encoding issues when dumping to a file and importing the dump. Depending on what character encoding your database/tables use, sometimes helps: --default_character-set=latin1 --skip-set-charset