Backup MySQL data using mysqldump

Backup complete database:

mysqldump -u <username> -p<password> <dbname> > dbname.sql

Backup only specific tables:

mysqldump -u <username> -p<password> <dbname> <tablename> \ > tablename.sql

Restore a .sql file:

mysql -u <username> -p<password> <dbname> < file.sql

Note: There can’t be any empty spaces between -p and the password parameter.

Examples:

mysqldump -u root -p123456 itdb > itdb_280912.sql
mysqldump -u root -p123456 itdb itdb_posts \ > itdb_posts_280912.sql
mysql -u root -p123456 itdb < itdb_backup.sql

Setup automated database backup with mysqldump and cron jobs for Linux

Start cron manager by running command $ crontab -e and type the following line at the bottom:

0 4 * * * sudo mysqldump -u root -p<password> <dbname> | gzip > /mnt/nas/<dbname>_bak_`data ' %Y-%m-%d'`.sql.gz

This will create a job entry in cron which starts every night at 0400 AM. What it does is running mysqldump to backup the specified <dbname> and also compresses it to a gzip file and saves it to /mnt/nas/ (replace with wherever you want to store the file).

Was this post helpful?

Leave a Reply

Your email address will not be published. Required fields are marked *