MySQL automatic backup script I can run??

[Juicy D. Links]

Anybody have a good one i can execute via the shell and also option to run daily via cron?











Ice is gay

[bns666]

bump i need this tooooooooooo.

[psili]

Not sure about InnoDB tables, but can't you put the following in a shell script:

mysqldump --user=[user] --pass=[pass] --opt [database] > /path/to/backup.sql

[Juicy D. Links]

Originally Posted by bns666

bump i need this tooooooooooo.

i am googling now , se if i can find something

[Juicy D. Links]

Originally Posted by psili

Not sure about InnoDB tables, but can't you put the following in a shell script:

mysqldump --user=[user] --pass=[pass] --opt [database] > /path/to/backup.sql

hmm that looks ripe. i try it

[the alchemist]

To back-up all db's:

mysqldump --all-databases > all_databases.sql

[directfiesta]

http://www.debianhelp.co.uk/mysqlscript.htm

[Juicy D. Links]

niceeeeeeeeeeeee

[SuckOnThis]

Here Juicy, this works fine. You can even have it email you the dump file if you want or just have it saved on the server.



#!/bin/sh

# List all of the MySQL databases that you want to backup in here,
# each seperated by a space
databases="name of database"
# Directory where you want the backup files to be placed
backupdir=/path/to/directory

# MySQL dump command, use the full path name here
mysqldumpcmd=/usr/bin/mysqldump

# MySQL Username and password
userpassword=" --user=username --password=your 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=/usr/sbin/sendmail

# Send Backup? Would you like the backup emailed to you?
# Set to "y" if you do
sendbackup="n"
subject="Your MySQL Backup"
mailto="[email protected]"

# 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 Database"
for database in $databases
do
$mysqldumpcmd $userpassword $dumpoptions $database > ${backupdir}/${database}.dump
done

# Compress all of our backup files
echo "Compressing Dump File"
for database in $databases
do
rm -f ${backupdir}/${database}.dump.gz
$gzip ${backupdir}/${database}.dump
done

# Send the backups via email
if [ $sendbackup = "y" ]
then
for database in $databases
do
$uuencode ${backupdir}/${database}.dump.gz > ${backupdir}/${database}.dump.gz.uu
$mail -s "$subject : $database" $mailto < ${backupdir}/${database}.dump.gz.uu
done
fi


# And we're done
ls -l ${backupdir}
echo "Dump Successful and Complete!"
exit

[Juicy D. Links]

Originally Posted by SuckOnThis

Here Juicy, this works fine. You can even have it email you the dump file if you want or just have it saved on the server.



#!/bin/sh

# List all of the MySQL databases that you want to backup in here,
# each seperated by a space
databases="name of database"
# Directory where you want the backup files to be placed
backupdir=/path/to/directory

# MySQL dump command, use the full path name here
mysqldumpcmd=/usr/bin/mysqldump

# MySQL Username and password
userpassword=" --user=username --password=your 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=/usr/sbin/sendmail

# Send Backup? Would you like the backup emailed to you?
# Set to "y" if you do
sendbackup="n"
subject="Your MySQL Backup"
mailto="[email protected]"

# 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 Database"
for database in $databases
do
$mysqldumpcmd $userpassword $dumpoptions $database > ${backupdir}/${database}.dump
done

# Compress all of our backup files
echo "Compressing Dump File"
for database in $databases
do
rm -f ${backupdir}/${database}.dump.gz
$gzip ${backupdir}/${database}.dump
done

# Send the backups via email
if [ $sendbackup = "y" ]
then
for database in $databases
do
$uuencode ${backupdir}/${database}.dump.gz > ${backupdir}/${database}.dump.gz.uu
$mail -s "$subject : $database" $mailto < ${backupdir}/${database}.dump.gz.uu
done
fi


# And we're done
ls -l ${backupdir}
echo "Dump Successful and Complete!"
exit

thats niceeee save as a .sh file and execute via SSH right?

[GrouchyAdmin]

What's this about my Ass Queue? I'm not very good with computars.

[SuckOnThis]

Originally Posted by Juicy D. Links

thats niceeee save as a .sh file and execute via SSH right?

You can execute it with SSH or run it as a cron.

Just be sure to enter the correct info for 'databases', 'backupdir', 'user', and 'password'

[Juicy D. Links]

script above is rippeee works hotttttt thxxxx

[Juicy D. Links]

Originally Posted by SuckOnThis

You can execute it with SSH or run it as a cron.

Just be sure to enter the correct info for 'databases', 'backupdir', 'user', and 'password'

yup did that

thxxxxxxxxxxxxx

[Juicy D. Links]

Dump Successful and Complete!

[HomerSimpson]

I could write you a custom one ;)

[Tempest]

Originally Posted by SuckOnThis

Here Juicy, this works fine. You can even have it email you the dump file if you want or just have it saved on the server.

Sweet script...

I have one I wrote in Perl that also tars all the DBs together and then Zips it. They get saved with a datestamp as well so I can roll back a few days if need be. At some point I'm going to upgrade it to also FTP the DB backup to another server so if one goes down I still have backups.

[GrouchyAdmin]

Originally Posted by Tempest

Sweet script...

I have one I wrote in Perl that also tars all the DBs together and then Zips it. They get saved with a datestamp as well so I can roll back a few days if need be. At some point I'm going to upgrade it to also FTP the DB backup to another server so if one goes down I still have backups.

Why the hell did you use Perl?

-rwx------ 1 root root 3426 2007-09-29 13:16 backer.sh

This little 3.5k shell script does daily, weekly, monthly, and yearly backups, compresses them all, and keeps any amount of 'snapshots' you want, and offers automated redundancy with rsync and ncftpput.

[bDok]

nice shell script above.. perfect for making a gmail account for the database and shipping it off to that account daily or whenever you want to run it from the cron.

quality reply there. cheers.

[Juicy D. Links]

all this code talk is getting me HARD

[Juicy D. Links]

all this code talk is getting me HARD

[Juicy D. Links]

all this code talk is getting me HARD

[Tempest]

Originally Posted by GrouchyAdmin

Why the hell did you use Perl?

-rwx------ 1 root root 3426 2007-09-29 13:16 backer.sh

This little 3.5k shell script does daily, weekly, monthly, and yearly backups, compresses them all, and keeps any amount of 'snapshots' you want, and offers automated redundancy with rsync and ncftpput.

ha ha ha.. why? Cause I know perl and do a LOT of little scripts with it so it's just second nature to me... I know very little about what's already available on most servers/OSs.. and I'm not great writing cross-OS shell scripts.

Couldn't find backer.sh on one of my FreeBSD and Linux servers.

[GrouchyAdmin]

Originally Posted by Tempest

Couldn't find backer.sh on one of my FreeBSD and Linux servers.

That would be because I made it.

If you always design for the least common amount of bourne compatibility, the shells will usually work. Don't rely on linux or BSD nuances by themselves, test if you have to, and abusing awk is never wrong.

[papill0n]

Originally Posted by SuckOnThis

Here Juicy, this works fine. You can even have it email you the dump file if you want or just have it saved on the server.



#!/bin/sh

# List all of the MySQL databases that you want to backup in here,
# each seperated by a space
databases="name of database"
# Directory where you want the backup files to be placed
backupdir=/path/to/directory

# MySQL dump command, use the full path name here
mysqldumpcmd=/usr/bin/mysqldump

# MySQL Username and password
userpassword=" --user=username --password=your 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=/usr/sbin/sendmail

# Send Backup? Would you like the backup emailed to you?
# Set to "y" if you do
sendbackup="n"
subject="Your MySQL Backup"
mailto="[email protected]"

# 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 Database"
for database in $databases
do
$mysqldumpcmd $userpassword $dumpoptions $database > ${backupdir}/${database}.dump
done

# Compress all of our backup files
echo "Compressing Dump File"
for database in $databases
do
rm -f ${backupdir}/${database}.dump.gz
$gzip ${backupdir}/${database}.dump
done

# Send the backups via email
if [ $sendbackup = "y" ]
then
for database in $databases
do
$uuencode ${backupdir}/${database}.dump.gz > ${backupdir}/${database}.dump.gz.uu
$mail -s "$subject : $database" $mailto < ${backupdir}/${database}.dump.gz.uu
done
fi


# And we're done
ls -l ${backupdir}
echo "Dump Successful and Complete!"
exit

very useful - thanks alot for posting that

[Juicy D. Links]

i make great threads

[Az A Bay Bay]

soRRy cant HELP "(

[GrouchyAdmin]

Originally Posted by Juicy D. Links

i make great threads

Show me one.