?
Solved

MySQL - Create Daily Automatic Backup

Posted on 2009-02-21
13
Medium Priority
?
410 Views
Last Modified: 2012-08-14
Hello,

I want to create a daily backup of my database over the span of 7 days.
db-day1-year.sql.gz
db-day2-year.sql.gz
db-day3-year.sql.gz
db-day4-year.sql.gz
db-day5-year.sql.gz
db-day6-year.sql.gz
db-day7-year.sql.gz

repeats following week...

Server is Apache and I have access to SSH

0
Comment
Question by:UltraFlux
  • 7
  • 3
  • 3
13 Comments
 
LVL 23

Accepted Solution

by:
Maciej S earned 2000 total points
ID: 23701866
Below script will do backup of all your databases.
user in this case is probably root (or other user who has access to all your databases). password is of course this user's password.
If you want to backup just one - remove "-A" option, and add your database name right after password.
In this case, user doesn't have to be root - it will be enough if this user has enough permitions to this database.

Save below script to for example "mysql-backup.sh", add execute permission, and add it to your crontab:
1 2 * * * /path/to/mysql-backup.sh
This will cause, that this script will be run everyday at 2:01am.

One more - you will have db-day0-2009.sql.gz, db-day1.., ......, db-day6-2009.sql.gz
0 - Sunday, 1 - Monday, 2 - Tuesday, ..., 6 - Saturday.
#!/bin/sh
 
DAY=`date +%w`
YEAR=`date +%G`
FILENAME=db-day${DAY}-${YEAR}.sql
 
mysqldump -A --add-drop-database --add-drop-table -u user -p password > ${FILENAME}
gzip -9 ${FILENAME}

Open in new window

0
 

Author Comment

by:UltraFlux
ID: 23701935
mysqldump: unknown option '--add-drop-database'
gzip: db-day6-2009.sql.gz already exists; do you wish to overwrite (y or n)? y

any reason why the add-drop-database is causing problems?

also will the file auto overwrite when I set it up with cron?


0
 
LVL 23

Expert Comment

by:Maciej S
ID: 23701990
As long as you use ">" to redirect, then yes - files will be overwritten.

As for gzip issue with overwriting existing archive - add:

if [ -f "${FILENAME}.gz" ]; then
   rm ${FILENAME}.gz
fi

right above "gzip -9 ${FILENAME}" command.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:UltraFlux
ID: 23702034
so what about the mysqldump

unknown option --add-drop-database

anything to be concerned about here?
0
 
LVL 23

Expert Comment

by:Maciej S
ID: 23702205
This option was introduced in MySQL 4.1.13. Which version do you have?
0
 

Author Comment

by:UltraFlux
ID: 23702256
4.1.25
0
 

Author Comment

by:UltraFlux
ID: 23702297
something isnt working properly


#!/bin/sh
 
DAY=`date +%w`
YEAR=`date +%G`
FILENAME=db-day${DAY}-${YEAR}.sql
FILENAME2=html-day${DAY}-${YEAR}.tgz
 
mysqldump --add-drop-table -u user -pPASS databasename > ${FILENAME}
if [ -f "${FILENAME}.gz" ]; then
   rm ${FILENAME}.gz
fi
gzip -9 ${FILENAME}
 
 
My File is empty

Open in new window

0
 
LVL 3

Expert Comment

by:goobergeffer
ID: 23703406
hmm,  that works fine for me. Can you please output your shell?
0
 

Author Comment

by:UltraFlux
ID: 23706051
This is giving me:

Usage: mysqldump [OPTIONS] database [tables]
OR     mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]
OR     mysqldump [OPTIONS] --all-databases [OPTIONS]
For more options, use mysqldump --help

I cant get this to work, something is not right :\

#!/bin/sh
 
DAY=`date +%w`
YEAR=`date +%G`
FILENAME=db-day${DAY}-${YEAR}.sql
 
mysqldump -A --add-drop-table -u user -p pass > ${FILENAME}
if [ -f "${FILENAME}.gz" ]; then
   rm ${FILENAME}.gz
fi
gzip -9 ${FILENAME}
 
 
I have also TRIED
 
#!/bin/sh
 
DAY=`date +%w`
YEAR=`date +%G`
FILENAME=db-day${DAY}-${YEAR}.sql
 
mysqldump --add-drop-table -u user -p pass mydatabase > ${FILENAME}
if [ -f "${FILENAME}.gz" ]; then
   rm ${FILENAME}.gz
fi
gzip -9 ${FILENAME}

Open in new window

0
 
LVL 3

Expert Comment

by:goobergeffer
ID: 23707071
This is off top of my head, but it maybe it was not compiled properly... You can use the all-databases command I'm sure, use that and check your filing.. If you do have access to a second BLANK mysql install, use the all database backup and see if it includes everything you want..
0
 

Author Comment

by:UltraFlux
ID: 23707077
I cant imagine it not being complied properly, I'm using a very large hosting company "Media Temple"

Testing all-databases
0
 
LVL 3

Expert Comment

by:goobergeffer
ID: 23707109
Ah okay.. Cool cool, you never know.. They might restrict it.. Use the all databases command though..
0
 

Author Comment

by:UltraFlux
ID: 23707371
ok good to go
need to use the

- h

option for internal host

Cheers!
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
Recursive SQL is one of the most fascinating and powerful and yet dangerous feature offered in many modern databases today using a Common Table Expression (CTE) first introduced in the ANSI SQL 99 standard. The first implementations of CTE began ap…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question