Database backups, recommended methods

classic Classic list List threaded Threaded
7 messages Options
Reply | Threaded
Open this post in threaded view
|

Database backups, recommended methods

missyfitz
What is the recommended method(s) for doing a database backup? Is it simply a matter of backing up the mySQL database file to external storage or dvd, or is there some method for exporting all the tables to a text file? I've seen reference to the latter someplace, but don't have it bookmarked and am not sure if that's the better way to go, or possibly the only way to go...

Am I safe simply archiving the mySQL database file itself, or should I be using some method to export all the tables / data to a separate file and then archiving that file? If so, can you describe briefly how to do the export to grab all the database data, or point me in the right direction? Also, is there an automated way (WinXP) to do this, say, every 6 hours, or is it best done manually?

(I'm a mySQL novice, as I'm sure many people here are, so I think this info clearly outlined would be beneficial to a lot of us)
Reply | Threaded
Open this post in threaded view
|

Re: Database backups, recommended methods

core
HI Missyfitz,

Many people will have their own recommendations for backing up
databases, all relative to their own experience levels.  As far as I'm
concerned (even as a seasoned tech) tasks like database management
should be easy and require as little of our precious time as possible.  
There's a great web-based tool for backing up databases that I use and
highly recommend to anyone of any experience level called MySQLDumper.  
It's open source and can be found here:

http://www.mysqldumper.net

I won't go into too many details as you can read about them for
yourself, but I will say that it can back up single/multiple databases
and compress them which makes archiving them very easy.  It takes about
1 minute to install and has a fairly straightforward interface.  It does
require a functional web server, preferably on the same machine as your
SQL database(s) which is usually the case.

Check it out.  If you don't get all the info you need from the site,
I'll be happy to help you in any way I can as I've been using
MySQLDumper for some time now.  Cheers.

core

On 6/7/2011 5:12 PM, missyfitz wrote:

> What is the recommended method(s) for doing a database backup? Is it simply a
> matter of backing up the mySQL database file to external storage or dvd, or
> is there some method for exporting all the tables to a text file? I've seen
> reference to the latter someplace, but don't have it bookmarked and am not
> sure if that's the better way to go, or possibly the only way to go...
>
> Am I safe simply archiving the mySQL database file itself, or should I be
> using some method to export all the tables / data to a separate file and
> then archiving that file? If so, can you describe briefly how to do the
> export to grab all the database data, or point me in the right direction?
> Also, is there an automated way (WinXP) to do this, say, every 6 hours, or
> is it best done manually?
>
> (I'm a mySQL novice, as I'm sure many people here are, so I think this info
> clearly outlined would be beneficial to a lot of us)
>
> --
> View this message in context: http://opensim-users.2152040.n2.nabble.com/Database-backups-recommended-methods-tp6451598p6451598.html
> Sent from the opensim-users mailing list archive at Nabble.com.
> _______________________________________________
> Opensim-users mailing list
> [hidden email]
> https://lists.berlios.de/mailman/listinfo/opensim-users

_______________________________________________
Opensim-users mailing list
[hidden email]
https://lists.berlios.de/mailman/listinfo/opensim-users
Reply | Threaded
Open this post in threaded view
|

Re: Database backups, recommended methods

InuYasha Meiji
As for myself I simply made a winods batch file, instructing windows to perform a list of commends.   I called MySQL Backup.  A file I run manualy.  It contans this:

cd /.
cd program files
cd MySQL
cd MySQL Server 5.1
cd bin
mysqldump -u <root user name> -p<your password> --databases opensim >\tmp\opensim.sql

mysqldump comes with mysql 5.1 and higher, this batch file simply sends commands to find it in my installed MYSQL directory where mysqldump resides and gets all of my opwnsim's data, tables and all and places it as a sql file into my TMP directory.  Then I use a program like 7zip to compress it to a 7z file that I burn to a DVD or later once the backup becomes to large a set of DVD spanned across several disk using 7zip.

To restore my database I made another batch file I created and lucky me, I only had to use once.  It does prove that it worked though.  It looks like this.

cd /.
cd program files
cd MySQL
cd MySQL Server 5.1
cd bin
mysql -u <root user name. -p<your password> opensim < /tmp/opensim.sql


And this restores it for me.  Once I unzip the sql file and place it in my tmp directory folder.  Of corse ,root user is your root user name, and the password too.  I took mine out for security reasons.

Good luck, I wish you only the best.
InuYasha.




On 6/7/2011 8:36 PM, Mimetic Core wrote:
HI Missyfitz,

Many people will have their own recommendations for backing up databases, all relative to their own experience levels.  As far as I'm concerned (even as a seasoned tech) tasks like database management should be easy and require as little of our precious time as possible.  There's a great web-based tool for backing up databases that I use and highly recommend to anyone of any experience level called MySQLDumper.  It's open source and can be found here:

http://www.mysqldumper.net

I won't go into too many details as you can read about them for yourself, but I will say that it can back up single/multiple databases and compress them which makes archiving them very easy.  It takes about 1 minute to install and has a fairly straightforward interface.  It does require a functional web server, preferably on the same machine as your SQL database(s) which is usually the case.

Check it out.  If you don't get all the info you need from the site, I'll be happy to help you in any way I can as I've been using MySQLDumper for some time now.  Cheers.

core

On 6/7/2011 5:12 PM, missyfitz wrote:
What is the recommended method(s) for doing a database backup? Is it simply a
matter of backing up the mySQL database file to external storage or dvd, or
is there some method for exporting all the tables to a text file? I've seen
reference to the latter someplace, but don't have it bookmarked and am not
sure if that's the better way to go, or possibly the only way to go...

Am I safe simply archiving the mySQL database file itself, or should I be
using some method to export all the tables / data to a separate file and
then archiving that file? If so, can you describe briefly how to do the
export to grab all the database data, or point me in the right direction?
Also, is there an automated way (WinXP) to do this, say, every 6 hours, or
is it best done manually?

(I'm a mySQL novice, as I'm sure many people here are, so I think this info
clearly outlined would be beneficial to a lot of us)

--
View this message in context: http://opensim-users.2152040.n2.nabble.com/Database-backups-recommended-methods-tp6451598p6451598.html
Sent from the opensim-users mailing list archive at Nabble.com.
_______________________________________________
Opensim-users mailing list
[hidden email]
https://lists.berlios.de/mailman/listinfo/opensim-users

_______________________________________________
Opensim-users mailing list
[hidden email]
https://lists.berlios.de/mailman/listinfo/opensim-users



-- 
________________________________________________________________
Opensim User: Standalone Grid on Version 0.7.1.1 with 49 Regions
on Windows 7, 64-bit. Phenom 9500 2.2 ghz Quad Core, Terabyte Hard 
Drive, 8gig DDR2 RAM.  Used XAMPP to load PHP Version 5.3.0, Apache
and MySQL 5.1.41-community edition.  Modules not yet successful
________________________________________________________________

_______________________________________________
Opensim-users mailing list
[hidden email]
https://lists.berlios.de/mailman/listinfo/opensim-users
Reply | Threaded
Open this post in threaded view
|

Re: Database backups, recommended methods

missyfitz
So is it safe to just backup the database file itself? If I needed to restore a database or, for example if I reinstall Windows and mySQL, can I take an actual database file and just load it up in a fresh copy of mySQL?

Or does a database have to be exported to a text file in order to then be imported / usable in a new copy of mySQL?

Because if I can just use an actual copy of a database as a backup then all I really need to do is include it in my normal system-wide backup scheme, which basically just creates a mirrored dataset in a different location...

Every site I read regarding backing up databases always talks about the 'mysqldump' procedure, but never about essentially taking the database file itself and just burning it onto a dvd or whatever.

Are there any password issues with moving a database to a new mySQL installation or to a new computer entirely, if I were to just burn the database itself onto a dvd or add it into my existing backup scheme?
Reply | Threaded
Open this post in threaded view
|

Re: Database backups, recommended methods

Edmund Edgar
On 9 June 2011 13:16, missyfitz <[hidden email]> wrote:
> So is it safe to just backup the database file itself?

This can be done, but you'll need to have locked the database to
prevent changes and flush outstanding changes to disk before you do it
to be sure you have a workable backup. (Stopping the mysql server will
achieve the same effects.) If you don't do this, there may be changes
that are stored in RAM but haven't yet been flushed to disk.

Also, you'll probably want to make sure you restore to the same
version of MySQL, unless you want your life to be needlessly
interesting.

People do sometimes do this for very big databases - one client I
worked with had a database close to 1TB, which would have taken too
long to run with mysqldump. Since it's not usually practical to lock
your production database every night to do a backup, the usual
solution is to replicate the master database to a slave database, stop
the slave, do the backup, and start it again and let it catch up with
the master again. On Linux, there's a thing called an LVM snapshot
that will help you minimize the time you have to stop the slave.
There's also a tool called mylvmbackup to help you with this process.

But unless you have a very big database or some other special
requirements, you're almost definitely better off using mysqldump.

--
Edmund Edgar
Founder, KK Social Minds
Educational Technology for the Web and Virtual Worlds

[hidden email]
+81 090 3912 3380
Skype: edmundedgar
Second Life: Edmund Earp
Linked In: edmundedgar
Twitter: @edmundedgar
http://www.socialminds.jp
_______________________________________________
Opensim-users mailing list
[hidden email]
https://lists.berlios.de/mailman/listinfo/opensim-users
Reply | Threaded
Open this post in threaded view
|

Re: Database backups, recommended methods

Slavin, Simon
In reply to this post by missyfitz

On 9 Jun 2011, at 5:16am, missyfitz wrote:

> So is it safe to just backup the database file itself?

Some notes on doing this:

A) A MySQL database is not just one file.  Depending on which version you're using you'll probably find a little nest of files in a directory.
B) Any programs which use the database file must be halted when you do this (even those which just read it and don't write it).
C) That restored file is only guaranteed to work with the identical version of MySQL it was made with.  So if you're trying to restore it in two years time you'll have to find an old version of MySQL, but install it and make it run on newer hardware.

Experienced sysadmins prefer to back up SQL databases by dumping the data in text form (usually as SQL commands).  Then you can .zip it up to make it small.  Then if you ever need to restore the data you have a far wider number of options about what to read it into, and you can even inspect it by eye and understand what you're seeing.  MySQL even comes with a tool which does dumping like this:

<http://dev.mysql.com/doc/refman/5.0/en/mysqldump-sql-format.html>

Simon.
_______________________________________________
Opensim-users mailing list
[hidden email]
https://lists.berlios.de/mailman/listinfo/opensim-users
Reply | Threaded
Open this post in threaded view
|

Re: Database backups, recommended methods

Gary Beck
In reply to this post by missyfitz
For backups I use the Data Export & Restore feature of MySQL Workbench.
MySQL Workbench uses scripts to invoke mysqldump for you through a GUI
dialog.

My process is: Shutdown Opensim and then use MySQL Workbench to export to a
self-contained file.  That exports all the tables to a single file.  To
restore I point to that file for import and it reloads it all.

I run Opensim at two different locations.  I take a copy of the exported
dump file with me on a USB drive to restore the database when I change
locations.  That seems to work well.  I have noticed some script state
issues where I needed to reset a script or touch-start something after a
restore.

----- Original Message -----
From: "missyfitz" <[hidden email]>
To: <[hidden email]>
Sent: Tuesday, June 07, 2011 18:12
Subject: [Opensim-users] Database backups, recommended methods


> What is the recommended method(s) for doing a database backup? Is it
> simply a
> matter of backing up the mySQL database file to external storage or dvd,
> or
> is there some method for exporting all the tables to a text file? I've
> seen
> reference to the latter someplace, but don't have it bookmarked and am not
> sure if that's the better way to go, or possibly the only way to go...
>
> Am I safe simply archiving the mySQL database file itself, or should I be
> using some method to export all the tables / data to a separate file and
> then archiving that file? If so, can you describe briefly how to do the
> export to grab all the database data, or point me in the right direction?
> Also, is there an automated way (WinXP) to do this, say, every 6 hours, or
> is it best done manually?
>
> (I'm a mySQL novice, as I'm sure many people here are, so I think this
> info
> clearly outlined would be beneficial to a lot of us)
>
> --
> View this message in context:
> http://opensim-users.2152040.n2.nabble.com/Database-backups-recommended-methods-tp6451598p6451598.html
> Sent from the opensim-users mailing list archive at Nabble.com.
> _______________________________________________
> Opensim-users mailing list
> [hidden email]
> https://lists.berlios.de/mailman/listinfo/opensim-users 

_______________________________________________
Opensim-users mailing list
[hidden email]
https://lists.berlios.de/mailman/listinfo/opensim-users