Jump to content

MySQL Backup Script + FTP


Recommended Posts

Hey.

I'm releasing my mysql backup script.

Prerequisites

  • mysqldump
  • gzip
  • ncftp
  • ftp server properly set up
How does the script works?
  • Dumps and compresses the desired mysql databases
  • Transfers compressed files to remote host
  • Removes local compressed files
How to use?

This script takes only one argument (mode).

Mode defines which databases should be dumped.

Run it as: 

sh backupmysql.sh <mode>
Check the source comments for available modes.

Running automatically

You can also define it to run automatically with a cronjob, and even run different modes on different times.

To add a new cronjob, run:

crontab -e

Example cronjob (once a day):

0 0 * * * sh /usr/home/someuser/backupmysql.sh all

Script source:

#!/bin/sh

# Get mode from user input
#	Allowed modes:
#		all:		backups account, common, log, player, webserver
#		game:		backups account, common, player
#		gameLog:	backups log
#		web:		backups webserver
#		exceptLog:	backups account, common, player, webserver
MODE=$1

if [ "$MODE" == "" ]; then
	MODE="exceptLog"
fi

# Bins
MYSQLDUMP=`which mysqldump`
GZIP=`which gzip`
NCFTP=`which ncftp`

# Date for folders and filenames
DAY=$(date +"%Y-%m-%d")
FILETIME=$(date +"%Y-%m-%d.%T")

# Local backup folder (no trailing slash)
LOCAL_FOLDER="/tmp/backup"

# FTP Configuration
REMOTE_HOST="x.x.x.x"
REMOTE_USER="ftp_user"
REMOTE_PASS="ftp_pass"
REMOTE_FOLDER="/" # With trailing slash

# MySQL Configuration
MYSQL_USER="mysql_user"
MYSQL_PASS="mysql_pass"

# Which databases shall we backup?
# Databases should be separated with a space
DATABASES=""
if [ "$MODE" == "all" ]; then
	DATABASES="account common log player webserver"
elif [ "$MODE" == "game" ]; then
	DATABASES="account common player"
elif [ "$MODE" == "gameLog" ]; then
	DATABASES="log"
elif [ "$MODE" == "web" ]; then
	DATABASES="webserver"
elif [ "$MODE" == "exceptLog" ]; then
	DATABASES="account common player webserver"
fi

# Check if DATABASES var is set...
if [ "$DATABASES" == "" ]; then
	echo -e "033[31mThe specified mode doesn't exist...033[0m"
	exit 1
fi

# Dump and compress
for db in $DATABASES
do
	FILE=$db.$FILETIME.gz
	echo -e "033[32mDumping $db!033[0m"
	$MYSQLDUMP -u $MYSQL_USER -p$MYSQL_PASS $db | $GZIP -9 > $LOCAL_FOLDER/$FILE
done

# Transfer all backup files to remote host
echo -e "033[32mnTransfering files!033[0m"
$NCFTP -u$REMOTE_USER -p$REMOTE_PASS $REMOTE_HOST<<EOF
mkdir $REMOTE_FOLDER$DAY
cd $REMOTE_FOLDER$DAY
lcd $LOCAL_FOLDER
mput *
quit
EOF

# Delete local dump files
rm -f $LOCAL_FOLDER/*

  • Love 14
Link to comment
Share on other sites

  • Premium

 

Thank you for script but i have got an error.


root@server:/usr/game# sh backup.sh
: not found
: not found
backup.sh: 44: Syntax error: "elif" unexpected (expecting "then")

 

You probably uploaded a Windows formatted text file.

 

The easiest way to avoid formatting problems is to just copy and paste the script on ee, or save it as Unix format if your text editor supports this.

  • Love 1
Link to comment
Share on other sites

  • 2 weeks later...

Hi, good afternoon.
This is a very nice script but I have a little problem with the cron part and I would like to know if you also happens and if you could solve.
 
I put in the crontab this to test it

* * * * * root sh /Cron/auto_back.sh all

It do the auto_back.sh every min, but not dump the tables and not upload the files, it generates a empty empyfiles.gz, to test if the cron works I put in the script header

echo "test" >> /log_auto

And It works, every mins. insert test in the /log_auto.

 

If I do manually it works.

 

Does anyone know how can be solved?

 

Kind regards.

Link to comment
Share on other sites

Hi, good afternoon.

This is a very nice script but I have a little problem with the cron part and I would like to know if you also happens and if you could solve.

 

I put in the crontab this to test it

* * * * * root sh /Cron/auto_back.sh all

It do the auto_back.sh every min, but not dump the tables and not upload the files, it generates a empty empyfiles.gz, to test if the cron works I put in the script header

echo "test" >> /log_auto

And It works, every mins. insert test in the /log_auto.

 

If I do manually it works.

 

Does anyone know how can be solved?

 

Kind regards.

 

The same error I have with my auto vpn cron.

I think there is a permission problem.

 

btw: nice script

 

Kind regards

Sphinx

Link to comment
Share on other sites

Hi, good afternoon.

This is a very nice script but I have a little problem with the cron part and I would like to know if you also happens and if you could solve.

 

I put in the crontab this to test it

* * * * * root sh /Cron/auto_back.sh all

It do the auto_back.sh every min, but not dump the tables and not upload the files, it generates a empty empyfiles.gz, to test if the cron works I put in the script header

echo "test" >> /log_auto

And It works, every mins. insert test in the /log_auto.

 

If I do manually it works.

 

Does anyone know how can be solved?

 

Kind regards.

You should leave /etc/crontab alone.

Use crontab -e instead (no need to specify the user).

Link to comment
Share on other sites

That's the vi editor.

Click "a" to append text then write the cron line.

To save and quit press Esc, then type :wq and hit Return.

The cronjob will be automatically installed upon exiting.

 

First, thanks for your time, but still does not work, maybe I am doing a bad step, now I have this in the crontab

cron2.jpg

 

I have saved the file with :wq, but still not working.

 

Sincerely thank you very much, kind regards.

Edited by Metin2 Dev
Core X - External 2 Internal
Link to comment
Share on other sites

Hi,

crontab -l gives that (it's added)

* * * * * sh /Backups/auto_back.sh all

And yes, /Backups/auto_back.sh is the full path.
 
The cron log gives that(in every min)

Feb 18 00:40:00 ns335232 /usr/sbin/cron[6356]: (root) CMD (/usr/local/rtm/bin/rtm 35 > /dev/null 2> /dev/null)
Feb 18 00:40:00 ns335232 /usr/sbin/cron[6357]: (root) CMD (/usr/libexec/atrun)
Feb 18 00:40:00 ns335232 /usr/sbin/cron[6358]: (root) CMD (sh /Backups/auto_back.sh all)
Link to comment
Share on other sites

Try to write the username over the shell script to a log file.

Then check the user his permissions.

 

Kind regards

Sphinx

The script prints the user root, and it have full permissions

echo $USER >> /log_auto
 

 

Have you enough free space in /tmp?

Yes I have free space in /tmp, if I do manually, the script works well, but not in cron.

Kind regards and thanks for your time.

Link to comment
Share on other sites

  • 4 weeks later...

Does anybody got this running with cron?

 

It works perfect without cron.

 

I have tried different crons and /var/log/cron shows that the cron was done but nothing happens.

 

 

/usr/sbin/cron[28936]: (root) CMD (sh /usr/home/autobackup/backup.sh all)

 

/usr/sbin/cron[29013]: (root) CMD (sh /root/backup/backupsql.sh all)

...

 

I hope anybody get this.

 

King regards,

 

Nightwish

 

Link to comment
Share on other sites

  • 4 weeks later...
  • 1 year later...
  • Premium

Correction:

Because $MYSQLDUMP its not recognized.

Get directorie mysqldump

which mysqldump and copy result before "-u"



# Dump and compress
for db in $DATABASES
do
    FILE=$db.$FILETIME.gz
    echo -e "033[32mDumping $db!033[0m"
    /usr/local/bin/mysqldump -u $MYSQL_USER -p$MYSQL_PASS $db | $GZIP -9 > $LOCAL_FOLDER/$DAY/$TIME_NOW/$FILE
done

[/code/

 

Debug:

env -i SHELL=/bin/sh PATH=/etc:/bin:/sbin:/usr/bin:/usr/sbin LOCALSCRIPT.SH

Link to comment
Share on other sites

  • 2 months later...
  • Management

On the log file that I put on the cronjob I receive this:

[32mDumping account_pvp![0m
/usr/home/game_pvp/backup_host.sh: -u: not found
[32mDumping common_pvp![0m
/usr/home/game_pvp/backup_host.sh: -u: not found
[32mDumping account_pvp![0m
/usr/home/game_pvp/backup_host.sh: -u: not found

The cronjob is running:

May 17 00:00:00 habiramt2 /usr/sbin/cron[568]: (root) CMD (/usr/home/game/backup_host.sh >> /usr/home/game/backup_log 2>&1)
May 17 00:00:00 habiramt2 /usr/sbin/cron[570]: (root) CMD (/usr/home/game_pvp/backup_host.sh >> /usr/home/game_pvp/backup_log 2>&1)

What is wrong?

Thanks

raw

raw

Link to comment
Share on other sites

  • 2 weeks later...
  • Management

Problem solved

If anyone have problems setting a cronjob, add this just after #!/bin/sh

PATH=/bin:/sbin:/usr/bin:/usr/sbin:/usr/local/bin:/usr/local/sbin
Like this:

5fa3a3f07dc64631af1fa94e40ef8150.png

Edited by Metin2 Dev
Core X - External 2 Internal

raw

raw

Link to comment
Share on other sites

Announcements



×
×
  • Create New...

Important Information

Terms of Use / Privacy Policy / Guidelines / We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.