Can you archive your mysql database information?

[scm22ri]

Hi Everyone,

 

Would it be possible to archive or save a certain part(s) of your mysql data information? Doing something like this, typically is it very complicated?

 

Thanks!

[gizmola]

It is non-trivial.  Without a LOT more specifics about what you want to do, and why, there is no way to answer your question without writing a novel on the subject. 

[Mahngiel]

http://davidwalsh.name/backup-mysql-database-php

 

You can take a look at the function David Walsh wrote up to take some pointers.

[hakimserwa]

export it as an sql file and save it somewhere so you can always import your sql file at any time you need.

[jazzman1]

Few months ago, I've written (with google's help ) a bash script to dump my databases.

I have basic skills in Bash and I don't know how professional is it, but.... works for me

 

#! /bin/bash
# list of databases to be backed up separated by space
DATABASES="NAME_OF_DB"
DBUSER="DB_USER"
DBPASS="DB_PASSWORD"
DBSERVER="DB_SERVER"
ASTERISKHOME=/keynote

NOW=`/bin/date +%Y%m%d-%H%M`
DIR=`pwd`
NODATA=
help_dump(){
        echo "dump databases"
        echo " --help      this info"
        echo " --no-data   dump only the sql structure"
}
for i in $*
do
        case $i in
        --no-data)
                NODATA="--no-data"
                ;;
        --cdr)
                CDR=1
                ;;
        --help)
                help_dump
                exit
                ;;
        -h)
                help_dump
                exit
                ;;
        *)
                # unknown option
                #QUITE=
                echo "use the options --no-data and --destination"
                exit
                ;;
        esac
done
if [ ! -d $DIR/$NOW$NODATA ];then mkdir -p $DIR/$NOW$NODATA;fi

if [ -n "$CDR" ];then
        /usr/bin/mysqldump --host=$DBSERVER --socket=$ASTERISKHOME/mysql/mysql.sock --user=$DBUSER --password=$DBPASS --routines $NODATA $CDRDATABSE > $DIR/$NOW$NODATA/$x$NODATA-$NOW.sql
else
        for x in $DATABASES; do
                /usr/bin/mysqldump --host=$DBSERVER --socket=$ASTERISKHOME/mysql/mysql.sock --user=$DBUSER --password=$DBPASS --routines $NODATA --databases $x > $DIR/$NOW$NODATA/$x$NODATA-$NOW.sql
        done
fi
tar zcf db-$NOW$NODATA.tgz $NOW

 

[teng84]

Hi Everyone,

 

Would it be possible to archive or save a certain part(s) of your mysql data information? Doing something like this, typically is it very complicated?

 

Thanks!

anther question that should be answered with question

[scm22ri]

Hi Everyone,

 

My mistake. I should have been more clear. When I said archive/save certain parts of the database I meant as a web developer you'll still be able to use the saved/archived database on a live website. I don't want to back up a database but save the old information and use it on my website. Please advise.

 

Thanks everyone!

[Pikachu2000]

I'm afraid you'll need to be more specific. You can already use the data in a website without archiving it, so as it stands, your question makes little to no sense.

[hakimserwa]

may be he is trying to ask how to go about exporting a database from a local development machine to a live server isnt it?

[scm22ri]

Hey Guys,

 

Thanks for the replies.

 

This is what I mean.

 

At the below URL their are two users. They each have a favorite number.

http://www.whatsmyowncarworth.com/auto-practice/display-member-info.php

hockeyman = 2

coolguy = 3

 

If hockeyman changes his number to 8

and

coolguy changes his number is 12

 

is their anyway to store/archive their old numbers of 2 and 3 in the same mysql database?

 

Thanks

 

[Pikachu2000]

Yes. Just insert a new record with a timestamp instead of replacing the data. Then the current record is the one with the most recent timestamp, etc.

[gizmola]

Yes. Just insert a new record with a timestamp instead of replacing the data. Then the current record is the one with the most recent timestamp, etc.

 

Yes, although Pikachu2k is glossing some details.  This is what I throught you meant, and why I stated it was non-trivial.  If you are storing this favorite number in a user table, then Pikachu's suggestion is not a good idea in most cases, especially if you are relating other tables to user.  The relations are done by the user.id column, and you would not want this to change for a user. 

 

What Pikachu2k is assuming is that the "favorite number" column would be in a seperate related table.  This table might be named "userfavs" or something like that.  If that table was related to User in a one to many fashion, then you could also include a timestamp column in it.  When you joined the tables together you would only return the row that has the largest timestamp.  While this is a sound relational design, which I've employed myself on many occassions, it makes everything a lot more complicated, as now you have joins, etc.

 

Another solution to this that many people implement is to use a second user table.  This table might be named userArchive.  userArchive typically has the same structure as user, but would include a "changedAt" timestamp column.  When a row in the user table is changed, as in the case of the person updating their favorite number, you will programmatically or by trigger, insert the "Old" row into the userArchive table.  This also works really well, but adds significant extra overhead to any table updates, and of course increases the size of the database substantially.  However, many programs that need very granular auditting implement some variation of this system.

 

Last but not least, you can always have your application write out a change log, which summarizes the changes that were made. 

 

It reallly all depends on why you need this feature, and how you plan to use it.  If it's just an audit trail, a text file can work really well, and doesn't require additional tables, although it does require code to work properly.  Triggers are the most reliable and independent implementation, however mysql stored procs and triggers are not very efficient. 

 

Building the archiving right into the structure of the tables also can work well, but will cause your main tables to grow in size, which introduces overhead, as well as making basic queries more complicated. 

 

There is no single right answer to this --- it all depends on the specific use cases you have.