Register
It is currently Tue Sep 30, 2014 7:49 am

Mysql database backup script - keeps 8 days worth of backups


All times are UTC - 6 hours


Post new topic Reply to topic  [ 7 posts ] 
Author Message
 PostPosted: Mon May 16, 2005 11:01 am   
Site Admin
User avatar

Joined: Sun May 15, 2005 9:36 pm
Posts: 667
Location: Des Moines, Iowa
Here is a backup script for the mysql databases on your system. It is what I use on my own computers. This script keeps 8 days worth of backups :D :D


Code:
for i in `mysql -B -e "show databases" | tail +2`; do
mysqldump --opt $i | gzip -9 > /var/backups/mysql/$i.`date --iso-8601`.gz
ls -t /var/backups/mysql/$i.* | tail +7 | xargs rm -f
done


Geeshock notes :
Quote:
This script keeps 8 days worth of compressed mysql backups in /var/backups/mysql . Another good thing about this script is it backs each individual database backed up instead of the whole database. Thatway if someone needs a database for a week ago ( believe it or not a client waited that long to tell me they deleted a database) you can do it without having to hunt through the entire script.


On the 7th day, I suppose you could have a cron job do do a backup to cdr of all the databases you have saved in /var/backups/mysql or like geeshock does..... rsync them to another server for safe keeping. ;) There are countless ways to use this script and variations on how you want to use the backups to save your data....

It's ONE NICE script :D

Originally posted here:
http://usalug.org/phpBB2/viewtopic.php?t=1657


the --opt part of the command locks the tables, preventing corruption.
FWIW: This has worked on my home machine with SEVERAL databases running at once. No issues.

From the man mysqldump page
Quote:
--opt Same as --add-drop-table --add-locks --all --extended-insert
--quick --lock-tables


Top
 Profile WWW  
 PostPosted: Mon May 16, 2005 11:03 am   
Site Admin
User avatar

Joined: Sun May 15, 2005 9:36 pm
Posts: 667
Location: Des Moines, Iowa
Wide
New member

Registered: Dec 9th, 2004
Posts: 4

New to scripting :)

I am trying to get this script to work & getting an error.

ERROR 1045 access denied for user(using password: NO)

I made it chmod +x filename

I know I'm missing something simple?

:)


EDIT, I'm using mysql 3.2

Last edited by Wide (Dec 12th, 2004 3:06 pm)


Dec 12th, 2004 3:05 pm
E-mail Report | Delete | Edit | Quote
jbsnake
Moderator

From: Georgia, U.S.A.
Registered: Nov 11th, 2004
Posts: 102

you have to have user access on the database schema and table in order to access it using mysql.
if you havn't done anything to the tables in mysql yet...then do this as root:

Code:

mysqladmin -u root password <newpassword>


that creates your root access to mysql using a password you specify
now to add your user account to the user database

Code:

mysql -uroot -p<newpassword> -e"insert into user (host, user)values ('<hostname>', '<username>')" mysql



Note: The <newpassword> should match what you set above for root user and
<hostname> should match the hostname on your system (or be localhost) and
<username> should be the user account on your system that you want to connect with.

since this is probably going to be your main username that you want to connect with on a regular basis...i suggest giving this user account the almost same access as your root account:

Code:

mysql -uroot -p<newpassword> -e"insert into db (host, db, user, select_priv, insert_priv, update_priv, delete_priv)values ('<hostname>', '<databasename>', '<username>', 'Y', 'Y', 'Y', 'Y' )" mysql


if you don't have a database already created to give access to from the above code...

Code:

mysql -uroot -p<newpassword> -e"create database <databasename>"


mysql is set up slightly different than other database programs...in mysql, top level objects are called "databases" whereas in oracle they are called schemas. The next level in mysql are called tables (the same as oracle). To create a table in a mysql database, (the database should have been made first from the above code) you would do this:

Code:

mysql -uroot -p<newpassword> -e"create table <tablename> (
ID int(2),
Name char(20),
);" <databasename>


the above creates a table with 2 attributes. The first one being an ID number with the greatest length of two digits. The second one being an alphanumeric name with the greatest length of twenty characters.
Ofcourse this is kind of a waste...but you can take this and expand on it :)
Let's say you created a database called "Info" and a table in that database called "People". You would add items to that table with the following code:

Code:

mysql -e"insert into Info.People (ID, Name)values ('1', 'Joe Bob')"


So now if you did the code:

Code:

mysql -e"select * from Info.People"


It would display under the ID column the number 1 and under the Name column the name Joe Bob.

Hope this little quick helped you out some :)
Want more info on this...let me know :)

_______________________________________
Arch Linux 0.7 Kernel 2.6.11.7-ARCH

Dec 13th, 2004 12:29 pm



Wide
New member
Registered: Dec 9th, 2004
Posts: 4


Thank you again JBSnake!!

I'll be on this soon as I figure out getting rid of spaces :)

Dec 13th, 2004 4:01 pm



Wide
New member

Registered: Dec 9th, 2004
Posts: 4
Thank you JBSnake!!!!


I managed to get it to work.

I think I learned more figuring it out then the script itself.

Now on to the spacees script :)

Dec 15th, 2004 5:57 pm


Top
 Profile WWW  
 PostPosted: Mon May 16, 2005 11:23 am   
User avatar

Joined: Mon May 16, 2005 11:06 am
Posts: 5
Location: Hertford NC
glad to see you caried this one over :)


Top
 Profile WWW ICQ YIM  
 PostPosted: Sat Sep 16, 2006 12:54 pm   

Joined: Mon Apr 24, 2006 2:39 pm
Posts: 30
Location: Villahermosa
I tried to run this script and gave me the following error:
Code:
jza@intelso:~$ bash mysqlBackUp.sh
mysqlBackUp.sh: line 5: backup/$i.'date --iso-8601'.gz: ambiguous redirect
mysqldump: Got error: 1044: Access denied for user: 'jza@localhost' to database 'mysql' when selecting the database
ls: "show: No such file or directory
ls: +2.*: No such file or directory
ls: -B: No such file or directory
ls: -e: No such file or directory
ls: -p0j3z0a0: No such file or directory
ls: -ujza: No such file or directory
ls: backup/mysql: No such file or directory
ls: databases": No such file or directory
ls: tail: No such file or directory
ls: |: No such file or directory


The script goes as follow:

Code:
for i in 'mysql -ujza -p****-B -e "show databases" | tail +2';
do mysqldump --opt $i |gzip -9 > backup/$i.'date --iso-8601'.gz
ls -t backup/$i.* | tail +7 | xargs rm -f
done


I've tried breaking down the script to see if there was anything wrong, first I tried the following:

Code:
$ mysql -ujza -p****-B -e "show databases
Databases
mydatabase


I tried inserting the pipe tail and so far so good, it reply me just the 'mydatabase' which is what was expected. After that I insert the for and got the follwoing error:
Quote:
-bash: syntax error near unexpected token `'mysql -ujza -p***** -B -e "show databases" | tail +2''


I wonder if its because of the single quotes instead of the stress sign. I later added the do ... part but still gave me the same error.

ok I found a couple of bugs including the for i instead of for $i, also there were a couple of bugs on mysqldump didn't authenticate again to the db. Finally there I am having an error with gzip:

Quote:
mysqlBackUp.sh: line 3: $i.`date --iso-8601`.gz: ambiguous redirect


Top
 Profile WWW ICQ YIM  
 PostPosted: Mon Sep 18, 2006 5:04 am   
User avatar

Joined: Mon Jul 03, 2006 8:58 pm
Posts: 52
Location: Rochester, NY
I belelieve you need to tell gzip to write to stdout with the -c option before redirecting, otherwise it wants a list of files to compress in place (i.e. file.foo => file.foo.gz).


Top
 Profile WWW  
 PostPosted: Tue Sep 19, 2006 3:27 pm   

Joined: Mon Apr 24, 2006 2:39 pm
Posts: 30
Location: Villahermosa
BrionS wrote:
I belelieve you need to tell gzip to write to stdout with the -c option before redirecting, otherwise it wants a list of files to compress in place (i.e. file.foo => file.foo.gz).


Well I put it like the sample posted at the top, gzip seemed to work without the 'date' command. Otherwise it will throw out errors. This seems pretty odd since date by itself return no errors.

Code:
gzip -9 > $i.`date --iso-8690`.gz
> error


while
Code:
gzip -9 > $i.gz
> databasename.gz

Correct


Top
 Profile WWW ICQ YIM  
 PostPosted: Wed Sep 20, 2006 5:44 pm   
User avatar

Joined: Mon Jul 03, 2006 8:58 pm
Posts: 52
Location: Rochester, NY
Then if that's the case, set a variable to the value of the `date` command and then simply use the variable in the file name:

Code:
$date=`date --iso-8690`
gzip -9 > $i.$date.gz


Top
 Profile WWW  
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 7 posts ] 

All times are UTC - 6 hours


Who is online

Users browsing this forum: No registered users and 8 guests


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot post attachments in this forum

Jump to:  


BashScripts | Promote Your Page Too
Powered by phpBB © 2011 phpBB Group
© 2003 - 2011 USA LINUX USERS GROUP