[thelist] mysql question

[Dan Williams]

Well, my favourite method is to use mysqldump to make a backup file, and
mysql to import that file when needed. The advantage is that this method
is very quick and clean, and gives you one big file which is then
convenient to handle. The syntax you need is:

mysqldump -uusername -ppassword databasename > dumpfile.sql

mysql -uusername -ppassword databasename < dumpfile.sql

Obviously, name dumpfile.sql as whatever you want; databasename is of
course your database name (to copy, simply give a different databasename
for the second command); and remember that if your username is "jedi"
then the parameter will be -ujedi (in other words, keep the first 'u' in
'uusername). Same for -ppassword.

This is also the best method to rename a database; create new db ->
export -> import -> delete old db.

As a note, you will want to clear the backup database each time; I would
drop it and recreate it, that would be quickest.


In case for some reason that method is unavailable to you, there is a
query-based alternative:

SELECT * INTO OUTFILE '/path/to/your/folder/tablename.txt' FROM
tablename;

That will give you a tab delimited file. You can place it in your web
root, then you can just download it with your web browser. To import it
back into another database, you use

LOAD DATA INFILE '/path/to/your/folder/tablename.txt' INTO TABLE
tablename;

(I think that is correct syntax; I'm going from memory and I've never
used those queries, as I favour the first method.)

The downside here of course is that you have to backup one table at a
time, although that could be relatively painless (you could write a
script to grab all the tablenames in the db and back them all up one by
one).


I hope one of those two methods will suit you; they are the most
efficient for what you are asking (better than doing a SELECT into a
resource and then INSERTing them all into the second db or something -
that would be a Bad Thing ;) ).

Cheers

::] krycek [::



-----Original Message-----
From: thelist-bounces at lists.evolt.org
[mailto:thelist-bounces at lists.evolt.org] On Behalf Of Raymond Camden
Sent: 10 September 2003 20:16
To: thelist at lists.evolt.org
Subject: [thelist] mysql question


I'm looking to setup a nightly backup of a mysql db. I need the backup
to actually copy to another database though. Is that possible? I suppose
I could write scripts to handle it, but it would be nice if there were
an even easier solution.

========================================================================
===
Raymond Camden, ColdFusion Jedi Master for Mindseye, Inc
(www.mindseye.com)
Member of Team Macromedia (http://www.macromedia.com/go/teammacromedia)

Email    : jedimaster at mindseye.com
Blog     : www.camdenfamily.com/morpheus/blog
Yahoo IM : morpheus

"My ally is the Force, and a powerful ally it is." - Yoda 


-- 
* * Please support the community that supports you.  * *
http://evolt.org/help_support_evolt/

For unsubscribe and other options, including the Tip Harvester 
and archives of thelist go to: http://lists.evolt.org 
Workers of the Web, evolt !