[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 598
  • 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.

Thanks,
EL
0
el123
Asked:
el123
1 Solution
 
hieloCommented:
mysqldump db_name > backup-file.sql

http://dev.mysql.com/doc/refman/5.0/en/mysqldump.html
0
 
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

Reference:
http://dev.mysql.com/doc/refman/5.0/en/backup-policy.html
0
 
UmeshCommented:
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...
0
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 
UmeshCommented:
Oops.. not refreshed :-)
0
 
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
0
 
UmeshCommented:
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 :-)
#!/bin/sh
 
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

0
 
UmeshCommented:
Did you check this?
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

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