I have had to do this a lot lately with all the database work I have been doing so I figured I would write this down before I forgot about it.

If you need to restore a database from a .sql file here is the command:

mysql -h hostname -u username -pthepassword databasename < dumpfile.sql

If you need to dump a database to a file (for automated backup purposes) you can use this command:

mysqldump -h hostname -u username -pthepassword databasename > backup-file.sql

These can be very helpful when doing database work

The other day I had to move a blog from one server to another and found out that Wordpress sometimes likes to hard code the server address into things like links and images.  I found this handy little SQL statement you can run to find and replace anything that you need changed:

update [Table] set [Column] = replace([column],’[old text]‘,’new text’);

This can save you a lot of heart ache dealing with moving servers and find out that you have a lot of broken images left after migration.

This is a great front end to MySQL and imports and exports super easy. Navicat

So this is the best third party apps I know about for MySQL, MS-SQL Server, Postgre, and Firebird if you are into the third party apps for Windows. I like these because they have code completion which makes writing SQL much faster and accurate. All are free applications from http://www.sqlmanager.net/

EMS mySQL lite
EMS SQL Server lite