Sunday, 15 Nov, 2015

mysqldump

mysqldump

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 –password=P@ssw0rd - -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 bck-usr@pwrusr.com
flush privileges;

3) Put your script into crontab!

Rate this post