MySQL Backup Script

[nicc777]

Hi

For those who are interested, I have created a little perl script to backup a MySQL database.

The script can be found here. ( actual script here )

Working:

You first need to configure it, by editing the file. Things you should change:

$use -> your MySQL username
$password -> your MySQL password

Note : You should use a username that have access to all tables.

The script will then gather the info of all databases ( excluding the mysql db ) and dump the content ( valid SQL statements ) to standard output ( stdout ).

You should call the script like this:

prompt> mysqlbackup.pl > backupfile.sql

The reason I did not include the mysql db are numerous, so you should backup that manually, especially the user table.

When you need to restore the database, just call the backup.sql file from the mysql client, or use phpMyAdmin.

I hope this helps somebody

itfirms.co.za

[Nnyan]

Links where broken but this is a script that I use in crontab.

#! /bin/bash
mydate=$(date +%m%d%Y%I%M%p)
PATH=$PATH:/usr/local/bin
export PATH

/usr/local/mysql/bin/mysqldump --flush-logs --opt -u USERNAME -pPASSWORD DBNAME > /dir1/dir2/dir3/backup.sql
cd /dir1/dir2/dir3/

if [ -e backup.sql ]
then
tar -czvf $mydate-sqlback.tar.gz backup.sql
fi
if [ -e $mydate-sqlback.tar.gz ]
then
lftp <<EOF
open FTP SERVER
user USERNAME PASSWORD
pwd
put $mydate-sqlback.tar.gz
bye
EOF
fi
if [ -e backup.sql ]
then rm /dir1/dir2/dir3/backup.sql

[marist89]

...but, isn't that the same thing as mysqldump?

[Nnyan]

Well....yes it is. I only posted that b/c the links in the first post were broken, its a fairly safe and simple way to backup your db.