Results 1 to 10 of 10
Thread: backing up database
-
09-13-2009, 04:53 AM #1Web Hosting Evangelist
- Join Date
- Jul 2005
- Posts
- 529
backing up database
I was just wondering what methods admins here use to backup their website?
I normally used phpmyadmin, but for larger databases it does not help as much.
What methods do you use to copy your database, and save it to your server and/or PC machine at home/office?
-
09-13-2009, 06:50 AM #2Junior Guru Wannabe
- Join Date
- Jul 2009
- Posts
- 71
Hello,
I think in case of linux server with cpanel the cpanel backup option would suffice as it would take the backup of the domain as well as the corresponding mysql databases. If you want mysql backup option it would be better if you make your own backup scipt for mysql alone and take backup either remote or locally(provided that shell access is available)
I prefer the second method as it is more fast, saves a lot of the system resources and time.
cheers
-
09-13-2009, 09:12 AM #3Disabled
- Join Date
- Mar 2009
- Location
- Israel
- Posts
- 1,212
if you have SSH access for your hosting account , you can use the mysqldump command.
-
09-13-2009, 11:59 AM #4
I use this for some sites. Creates daily backups, rotates weekly, can email or FTP an off-site copy. Enjoy!
Code:#!/bin/sh # This script will backup one or more mySQL databases # and then optionally email them and/or FTP them # This script will create a different backup file for each database by day of the week # i.e. 1-dbname1.sql.gz for database=dbname1 on Monday (day=1) # This is a trick so that you never have more than 7 days worth of backups on your FTP server. # as the weeks rotate, the files from the same day of the prev week are overwritten. # name it backup.sh (or whatever), and set up a cron (daily): # /bin/sh /path/to/script/backup.sh > /dev/null ############################################################ #===> site-specific variables - customize for your site # List all of the MySQL databases that you want to backup in here, # each seperated by a space databases="database_name" # Directory where you want the backup files to be placed backupdir=/home/user/backup # MySQL dump command, use the full path name here mysqldumpcmd=/usr/bin/mysqldump # MySQL Username and password use root mysql info if many dbs are being backed up userpassword=" --user=mysql_username --password=*****" # MySQL dump options dumpoptions=" --quick --add-drop-table --add-locks --extended-insert --lock-tables" # Unix Commands gzip=/bin/gzip uuencode=/usr/bin/uuencode mail=/bin/mail # Send Backup? Would you like the backup emailed to you? # Set to "y" if you do sendbackup="n" subject="My Backup" mailto="your_alias@example.com" #===> site-specific variables for FTP ftpbackup="y" ftpserver="ip.address.goes.here" ftpuser="ftp_username" ftppasswd="*****" # If you are keeping the backups in a subdir to your FTP root ftpdir="/" #===> END site-specific variables - customize for your site ############################################################ # Get the Day of the Week (0-6) # This allows to save one backup for each day of the week # Just alter the date command if you want to use a timestamp DOW=`date +%w` # Create our backup directory if not already there mkdir -p ${backupdir} if [ ! -d ${backupdir} ] then echo "Not a directory: ${backupdir}" exit 1 fi # Dump all of our databases echo "Dumping MySQL Databases" for database in $databases do $mysqldumpcmd $userpassword $dumpoptions $database > ${backupdir}/${DOW}-${database}.sql done # Compress all of our backup files echo "Compressing Dump Files" for database in $databases do rm -f ${backupdir}/${DOW}-${database}.sql.gz $gzip ${backupdir}/${DOW}-${database}.sql done # Send the backups via email if [ $sendbackup = "y" ] then for database in $databases do $uuencode ${backupdir}/${DOW}-${database}.sql.gz > ${backupdir}/${database}.sql.gz.uu $mail -s "$subject : $database" $mailto < ${backupdir}/${DOW}-${database}.sql.gz.uu done fi # FTP it to the off-site server echo "FTP file to $ftpserver FTP server" if [ $ftpbackup = "y" ] then for database in $databases do echo "==> ${backupdir}/${DOW}-${database}.sql.gz" ftp -n $ftpserver <<EOF user $ftpuser $ftppasswd bin prompt cd $ftpdir lcd ${backupdir} put ${DOW}-${database}.sql.gz quit EOF done fi # And we're done ls -l ${backupdir} echo "Dump Complete!" exit
Your one stop shop for decentralization
-
09-18-2009, 03:25 AM #5Newbie
- Join Date
- Sep 2009
- Posts
- 16
Yes, Use mysqldump
-
09-18-2009, 03:31 AM #6Disabled
- Join Date
- Dec 2007
- Posts
- 3,597
If you can't run that personally you always can relay on your web hosting provider and ask for the help
-
09-20-2009, 05:48 AM #7Junior Guru Wannabe
- Join Date
- Jan 2008
- Posts
- 84
Normally I'd do a manual mysqldump over SSH. But bear's script looks interesting, maybe I'll give it a try, thanks for sharing!
-
09-29-2009, 10:53 PM #8Newbie
- Join Date
- Mar 2008
- Posts
- 13
yes, mysqldump through ssh
-
10-17-2009, 01:21 AM #9WS Developer
- Join Date
- Nov 2003
- Location
- USA
- Posts
- 877
i use a software
-
10-20-2009, 03:28 AM #10Newbie
- Join Date
- Jun 2009
- Posts
- 28
in linux
it would be better to write a script for managing mysql backup
suppose you have a database 'shopcart' and you want to backup this to /user1/db_backup
for that create a file db_backup.sh and add the following content in to it
dt=$(date +%T-%d_%m_%Y)
backup_path="/user1/db_backup";
backup_name="$backup_path/shopcart_$dt";
mysqldump shopcart>$backup_name.sql
save the file and run
#sh db_backup.sh
it will create a db backup of the databae shopcart in the directory /usr1/db_backup
the format of the backup file is something like 'shopcart_12:53:04-20_10_2009.sql'
if you need automated daily backup you can do it using cron
hope it helps you
Rasin
Similar Threads
-
Backing up database data
By pergesu in forum Hosting Security and TechnologyReplies: 2Last Post: 10-25-2005, 02:39 PM -
Backing up a MySQL Database
By GameStudioD in forum Programming DiscussionReplies: 9Last Post: 09-08-2004, 04:51 PM -
backing up a database woes
By aqi32 in forum Web Hosting LoungeReplies: 7Last Post: 04-06-2004, 05:15 PM -
Backing Up Database Problem
By NetGeek in forum Hosting Security and TechnologyReplies: 9Last Post: 07-12-2002, 02:43 PM