• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 610
  • Last Modified:

MySQL: how to take the backup of the database using mysqldump

Hi, I just want to take the mysql db backup using mysqldump.

Can any help.

1 Solution
mysqldump db_name > backup-file.sql

Michael WorshamInfrastructure / Solutions ArchitectCommented:
To allow for consistent reads from the database and to control character set issues, I recommend doing it like this:

mysqldump --single-transaction --result-file=db_name.sql db_name --default-character-set=utf8

UmeshMySQL Principle Technical Support EngineerCommented:
As 'hielo' mentioned 'mysqldump' is the tool to take mysql database backup.

Also, What kind of tables do you use ? innodb or myisam? if your answer is innodb then don't forget to use --single-transaction option to get a consistent dump.. and if its myisam table then without locking you will not be getting a consistent backup...so make sure its locked before you dump...
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

UmeshMySQL Principle Technical Support EngineerCommented:
Oops.. not refreshed :-)
el123Author Commented:
thank Gurus, my tables are all Innodb, and I want script too, so I can automate them to be taken on certain schedules
UmeshMySQL Principle Technical Support EngineerCommented:
Try this script.. once this script is in place.. make sure you have some rotation/archiving policies..so that backups won't eat much space :-)
date = `date +%Y%m%d`
# Simple dump
mysqldump --user=root --password=my_pwd --single-transaction \
     --all-databases > /backup_dir/backup-${date}.sql
# To save space..you may want to use this compressed dump...
mysqldump --user=root --password=my_pwd --single-transaction \
     --all-databases | gzip > /backup_dir/backup-${date}.sql.gz

Open in new window

UmeshMySQL Principle Technical Support EngineerCommented:
Did you check this?
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now