I use MySQL all the time when building websites. I have a self-built CMS and a number of clients all running off of the system. Here are a few SQL commands that I use regularly:

Dump databases

> mysqldump --quick -all-databases -uadmin -p > alldatabases.db

Dumps all of the databases to a single file. Along with regular server backups I use this to keep copies of everything for my own reference.

–quick == forces MySQL to retrieve and dump the tables a row at a time rather than buffering. Useful for large tables

–all-databases == self explanatory I hope

-uadmin == username admin

-p == ask for password. My installation needs the password to perform any actions

To dump a single database:

> mysqldump --quick -uadmin-p database_name > filename.db

I personally use .db at the end of my files, but they’re simply text files of INSERT instructions and can be named however you like. From a dumped database, you can import all of that content into a new database:

> mysql -uadmin -p database_name < filename.db

This will fail if for example, the keys for a table already exist in both the database and the import file.

Search and Replace

I find this really useful. To search and replace words across a table, in a particular field:

mysql> UPDATE table_name SET field_name=REPLACE(field_name_again,

'look for me','change to me) WHERE..

The WHERE part is optional. Leave out any WHERE filter if you want to apply to all records in the table.

More to come…