MySQL Tools & Co. are very nice and smart toys, 'though sometimes DB Admins like to overcomplicate things that should be easy and simple.

Over time and experience, I developed a specific taste for the following mysqldump options:

mysqldump -u root -p - -add-drop-databases - -add-drop-table - -databases DB_NAME > filename.sql

Beware of the dreaded "- -".

This command simply dumps ALL the DB data into a single file, but the best part is the "--databases" option, that enables (even if dumping a single DB), the insertion of the “CREATE DATABASE DB_NAME” statements (very useful indeed!).

A pwrusr's most common operation: I want to dump a "whole db" (why?) -> I want to import it as a whole into another place, and that's it!


[BONUS] copy database from one server to another:

mysqldump –user=root –[email protected] - -add-drop-databases - -add-drop-table - -databases DB_NAME | mysql -h my-remote-host-3 –user=root –password=remote-host-3-mysql-password DB_NAME

Automate backups to a specific user for cron-enabled dumps.

1) Create the bck-usr on mysql.

2) Grant some permissions to the user needed to perform the automated backups.

GRANT SELECT,LOCK TABLES ON mydb.* TO [email protected]
flush privileges;

3) Put your script into crontab!

Senior Professional Network and Computer Systems Engineer during work hours and father when home.

Andrea strives to deliver outstanding customer service and heaps of love towards his family.

In this Ad-sponsored space, Andrea shares his quest for "ultimate" IT knowledge, meticulously brought to you in an easy to read format.

my favourite "mysqldump" options.
Rate this post

Ask me anything