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).