SQL and Root File backup to FTP

thedjtrollin · 04-15-2013, 06:43 AM

I have recently moved from Windows server 2008 and I need to create a backup script for my server.

So far I have created the script to move the Tar files to the backup server. I cannot figure out how to do the following things

1) Create a backup of /root/ and compress it with the date and time.
2) Backup all MYSQL DB and compress them with the Date and Time as the File name

If anyone could help it would be apreciated.

Regards,
Harry

chrism01 · 04-15-2013, 08:12 AM

Here's the basics:

Code:

#grab current date time in YYYYMMDD-HHMM format see http://linux.die.net/man/1/date
now=$(date +%Y%m%d-%H%M)

# tar cmd would be 
tar zcvf root-${now}.tgz /root

For mysql, use the mysqldump cmd to ensure a consistent backup
https://dev.mysql.com/doc/refman/5.1/en/mysqldump.html

eklavya · 04-15-2013, 08:38 AM

Tips for your database (mysql) script :

1) First list the name all databases in any text file.
Databases are listed as a directory in /var/lib/mysql, if you will execute

Code:

ls -l /var/lib/mysql | grep "^d"

It will give you all directories name of /var/lib/mysql, in short it is detail list of all databases of your mysql
Now cut last column, that is list of databases and save list in a text file using redirection (>) operator

2) Now calculate the lines of text file, it is exact same number of your databases.
store number of lines in a variable.

3) Start your loop
i=1
while value of i is equal to or less than above variable
do
first line of text file (tail -n +$i text_file | head -n 1) - store this in second variable
copy database into backup dir (mysqldump -h host-name -u user-name -pPassword "second-variable" > "backup/firstdatabase.sql")
i=`expr $i + 1`
done

4) use date with firstdatabse.sql so it will store sql file with date & time.

5) Remove the text file

The script will take backup of your all databases as a .sql file.
Every database is saved as a .sql file (separate back ups of each database)
if any database is added, it will take back up of the new database too, you do not need to take back up of new database manually.

This is just an idea, you don't need to follow exactly same as above. you can make your script better and robust. This is the best part of shell scripting.

thedjtrollin · 04-17-2013, 05:22 AM

This is what it looks like so far.
Is there any problems with this?

Code:

#Backup the Root files where the Servers are stored
now=$(date +%Y%m%d-%H%M)
tar zcvf Servers-${now}.tgz /root
#SQL Backup
now=$(date +%Y%m%d-%H%M)
mysqldump -u root -p*PASSWORD EDITED* XenForo > XenForo-${now}.sql
mysqldump -u root -p*PASSWORD EDITED* VitalityRPG > VitalityRPG-${now}.sql
mysqldump -u root -p*PASSWORD EDITED* Skyblocks > Skyblocks-${now}.sql
#Upload all to FTP Backup
ftp -n -i $SERVER <<EOF
user admin *PASSWORD EDITED*
cd Backup/MCvitality
mput Servers-${now}.tqz
mput Xenforo-${now}.sql
mput VitalityRPG-${now}.sql
mput Skyblocks-${now}.sql
quit
EOF

chrism01 · 04-17-2013, 06:14 AM

Looks basically ok.

1. Test each cmd separately to check it works the way you think
2. you do know that '/root' is the root user's home dir, not the root dir of the system ('/')
3. typo: mput Servers-${now}.tqz - you've got a 'q' instead of a 'g'
4. consider what you're going to do with the copy of the backups on the local system.
A good idea is to create a dedicated dir just for the backup files

thedjtrollin · 04-17-2013, 06:41 AM

Quote:

Originally Posted by chrism01

Looks basically ok.

1. Test each cmd separately to check it works the way you think
2. you do know that '/root' is the root user's home dir, not the root dir of the system ('/')
3. typo: mput Servers-${now}.tqz - you've got a 'q' instead of a 'g'
4. consider what you're going to do with the copy of the backups on the local system.
A good idea is to create a dedicated dir just for the backup files

2) All my files are saved in the Root user Dir
3) Cheers I just noticed that and changed it as well.
4) I am gonna FTP to my backup server what is located in a diffrent Data Centre and copy the files to that.
*Edit*
Just re-read your comment and for the Local copies I will either Delete them or Move them into /backup/${now}

chrism01 · 04-17-2013, 08:43 AM

Quote:

Just re-read your comment and for the Local copies I will either Delete them or Move them into /backup/${now}

In the latter case I'd recommend looking into the logrotate tool to keep them purged down, or run a find+rm cmd in cron once a day, otherwise your disk will fill up.

PS If your worried about security of the link, use scp/sftp instead of ftp.

thedjtrollin · 04-17-2013, 09:42 AM

Quote:

Originally Posted by chrism01

In the latter case I'd recommend looking into the logrotate tool to keep them purged down, or run a find+rm cmd in cron once a day, otherwise your disk will fill up.

PS If your worried about security of the link, use scp/sftp instead of ftp.

How would I set up SFTP or SCP? also what I might do is once it is copied I will just delte the files from the server and then at the end of the week I will set up a cron on the other server to delete the backups and just keep the Sunday one.

Regards,
Harry

chrism01 · 04-18-2013, 12:03 AM

scp is easy

Code:

scp <local-file-pattern> user@remotehost:/path/to/dir

For an automated process, use ssh auth keys
http://www.linuxtopia.org/online_boo...erate-keypairs