metin2united
MadTiago

MySQL Backup Script + FTP

28 posts in this topic

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/*

  • Like 14

Share this post


Link to post
Share on other sites

 

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.

  • Like 1

Share this post


Link to post
Share on other sites

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.

 

Thanks it worked.

Share this post


Link to post
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.

Share this post


Link to post
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

Share this post


Link to post
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).

Share this post


Link to post
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.

  • Like 1

Share this post


Link to post
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.

Share this post


Link to post
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)

Share this post


Link to post
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.

Share this post


Link to post
Share on other sites

A tip, if you hate vi like me, is to change the default editor for your shell to ee.

 

In bash

 

export EDITOR=/usr/bin/ee

 

In sh/csh

 

setenv EDITOR /usr/bin/ee

Share this post


Link to post
Share on other sites

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

 

Share this post


Link to post
Share on other sites

I config this script ... but dont work ....

Look in the picture : PhhPf.png

 

 

But the folder in FTP exists ... he created automated ... but its empty ...

 

Why ??? Anyone have same problem ?

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

  • Recently Browsing   0 members

    No registered users viewing this page.