[Opensim-users] Backup and Restore of MySQL Opensim DB

[trucker.anthony at 3rdrockgrid.com]

the only silly question is the one that goes unanswered because it was not
asked I have a 12GB database on a public grid and here are the stept that i
take to back up and restore the database
Here's the steps needed to make a backup of your database.
Open command prompt. type... cd\ then press enter 
type... cd .\path to your mysql\bin\ (example: cd .\Program
Files\MySQL\MySQL Server 5.0\bin\ ) then press enter 
type... mysqldump -u root -p --opt yourdb > filename.sql (I always do the
date.. such as... 111008.sql)
This will create a file in the mysql\bin folder with this file name... this
is your database backup file. 
Once this is completed.... right click on the file and choose cut... then
paste it to the database backup folder of your choosing. Open your ftp
program and send a copy of this file to another server... just in case..
then you'll have a backup in 2 places instead of just one. 
Here's the steps needed to delete your old database... create a new
database... and upload a backup file to it. 
Open command prompt. type... cd\ then press enter 
type... cd .\path to your mysql\bin\ (example: cd .\Program
Files\MySQL\MySQL Server 5.0\bin\ ) 
then press enter type... mysql -u root -p then press enter 
enter your password then press enter 
type... drop database yourdb; then press enter (this deletes your old
database) 
type... create database yourdb; then press enter (this creates a database
named yourdb) 
type... use yourdb; then press enter (this selects the database) 
type... source c:\path\to\your\backup\database\file\and\name.sql then press
enter (example: c:\Program Files\MySQL\MySQL Server 5.0\bin\111008.sql) 
This uploads the info from your backup database file to your database. 
now i am using MySQL 5.0 but should be almost the same if not exact hope
this helps you out
TA

On Mon, 19 Jan 2009 22:52:41 +0000, Ai Austin <ai.ai.austin at googlemail.com>
wrote:
> A really silly question... but I ask as I am seeing inconsistent
> advice on backup and restore of MySQL data bases, and an attempt to
> do this via direct copy across two identical Windows machines was not
> successful... and a myslqdump of the opensim db and a subsequent
> reload attempt with mysql indicated the file was too large.. but it
> was only 50MB or so.
> 
> I just have Opensim on MySQL 5.1.30 though I could in future have one
> other systems databases..   I can see th data directory.  I assume
> ALL files that need to be copied are in there.
> 
> the opensim directory was where I THOUGHT al the files were, but
> those just look like unchanging table description files, and their
> date last modified stays at the date the DB ws first created. he main
> files that seem to grow are OUTSIDE that directory at the top level
> of the data directory...
> 
> C:\wamp\bin\mysql\mysql5.1.30\data
> 
> mysql  (directory)
> opensim (directory)
> <machinename>.pid
> ib_logfile0
> ib_logfile1
> ibdata1
> mysql-bin-000001
> ...
> mysql-bin.000008
> mysql-bin.index
> 
> As far as I can tell the REAL data base file is ibdata1 (from its
> size as it grows and the date last modified)
> Assuming I shut down the MySQL server before copying files, what do I
> need to copy to be able to later restore a complete copy of the Opensim
> DB?
> 
> Can anyone set me straight on the right way to take a backup opensim
> data base copy and later restore it by the direct copy method?
> 
> 
> _______________________________________________
> Opensim-users mailing list
> Opensim-users at lists.berlios.de
> https://lists.berlios.de/mailman/listinfo/opensim-users