At 12:51 AM 07/05/2001 , you wrote:
>In fact, if you want to restore your database, you just have to drop your 7
>tables in your database before importing the database back.
>Indeed, MySQL use a in memory key_buffer which store as much keys as
>possible. If you delete *.frm, *.MYD and *.MYI, MySQL doesn't know it, and
>still refers to its key_buffer to know whether the entry exist in his
>database. So you get the message "duplicated entry" because of its
>key_buffer.
>So, if you DROP all your 7 tables with mysql, you could import safely what
>you've backed up.
Ok Jocelyn;
Here's what I've learned.
1. As the root administrator, I can do rm -rf all *.frm, *.MYD and *.MYI
from the users/database directory, then do:
a. mysqladmin flush-tables -u root --password=xyzxyz
b. mysql -u jimyjohn--password=zxyxyz userdbdata < my_dump_file.txt
and all goes well -- no errors.
2. As the database user (not root) I can't do 1a. or 1b. above obviously (I
didn't grant the user: mysqladmin: refresh failed this privledge), I set up
my grant options pretty restrictive for users. So what I did as the user
was follow your suggestion and drop each table one by one, exit MySQL and
then import the database--no errors--Thanks!
This has been very helpful in my understanding process.
However, the reason for the post in the first place was to discuss backup
(in a generic sense) strategies. Replication, is out unless I upgrade, then
may not be what I want to do anyway (I haven't digested the manual on this
yet). The manual doesn't reccomend using the update log. And my version
doesn't support mysqlhotcopy.
So, who is responsible for what in the back-up department?
I would think that the database user/owner should be responsible for
keeping their own copy of a mysqldump file, and me, as the Web server
admin/ISP should keep backups of everything in /var/lib/mysql using a
nightly cron.
What do other users and administrators do?
Joe
>----- Original Message -----
>From: "Joe Taraba" <[EMAIL PROTECTED]>
>To: "Fournier Jocelyn [Presence-PC]" <[EMAIL PROTECTED]>
>Sent: Thursday, July 05, 2001 2:55 AM
>Subject: Re: Replication, update log, mysqldump???
>
>
> > At 05:23 PM 07/04/2001 , you wrote:
> > >Hi,
> > >
> > >What don't you drop the table with the SQL query DROP TABLE instead of
> > >deleting physically *.frm, *.MYD and *.MYI ? (if you delete the files
> > >manually, MySQL don't know it has to flush is key_buffer, and so you've
>got
> > >you duplicated keys error).
> >
> > Jocelyn;
> >
> > I don't understand MySQL well enough to know what you are talking about
>here.
> >
> > First of all I'm just trying to understand back-up theories.
> >
> > Second if I drop the table producing the error, I'll just get an error for
> > the next table in line, and so on. There are 7 tables in this database.
> >
> > I know this, from my practice, that a mysqldump file is not a safe way to
> > backup a database--or so it seems. If the database is messed up, even a
> > little, you can't restore it with the dump file. Again, I repeat, if I
> > understand what I have been experiencing. Maybe I am missing a step
> > somewhere, something which needs to be done before importing the database
>back.
> >
> > I'm looking at this from both a users end, and an administrators end. So
> > far my testing has been as the user.
> >
> > Joe
> >
> >
> > >Jocelyn Fournier
> > >Presence-PC
> > >www.presence-pc.com
> > >
> > >----- Original Message -----
> > >From: "Joe Taraba" <[EMAIL PROTECTED]>
> > >To: "Ravi Raman" <[EMAIL PROTECTED]>
> > >Cc: <[EMAIL PROTECTED]>
> > >Sent: Thursday, July 05, 2001 2:09 AM
> > >Subject: RE: Replication, update log, mysqldump???
> > >
> > >
> > > > At 11:28 AM 07/04/2001 , you wrote:
> > > > >hi.
> > > > >
> > > > >w/r/t your first question: mysqldump just generates a text file
> > >containing
> > > > >the sql statements necessary to recreate the dumped table(s).
> > > > >if you use mysqldump -uuser -p > dumpfile.txt you can open it in vi
>or
> > > > >notepad and edit it if desired...
> > > > >the error about the table existing is because the generated file by
> > >default
> > > > >has the table creation statement in it.
> > > > >use mysql -t or mysql --no-create-info to eliminate this.
> > > > >run mysqldump with no arguments to see the full usage info.
> > > > >the duplicate keys error would most likely be because you have
>existing
> > >rows
> > > > >in your table that conflict with the insert statements...pipe the
>output
> > >to
> > > > >a file instead of straight to mysql(if that's what you're doing),
>then
> > >check
> > > > >out what exactly's failing.
> > > >
> > > > I must have missed something in your explanation above:
> > > >
> > > > I did the following:
> > > > mysqdump -u jimmyjoe--password=123pwhere thedbname > my_dump_file.txt
> > > > Then:
> > > > mysql -u jimmyjoe--password=123pwhere thedbname < my_dump_file.txt >
> > >error.txt
> > > > ERROR 1050 at line 10: Table 'bb_instructor' already exists
> > > > ls -al error.txt (= 0 bytes)
> > > >
> > > > Line 10 (thru 20) says:
> > > >
> > > > 10 CREATE TABLE bb_instructor (
> > > > 11 id int(11) DEFAULT '0' NOT NULL auto_increment,
> > > > 12 thread int(11),
> > > > 13 message text,
> > > > 14 subject varchar(80),
> > > > 15 author int(11),
> > > > 16 when datetime,
> > > > 17 lastactivity datetime,
> > > > 18 replies int(11),
> > > > 19 PRIMARY KEY (id)
> > > > 20 );
> > > >
> > > > I understand this, I just dumped the database. I guess what I'm
>missing is
> > > > that you can't re-create an existing database for whatever reason,
> > >corrupt,
> > > > customer scrambled data, or anything. So. You can't overwrite an
>existing
> > > > database using mysql?
> > > >
> > > > If I delete all the associated *.frm, *.MYD, and *.MYI files, and run
>the
> > > > same command again, I get the duplicate keys error.
> > > >
> > > > So, what am I learning here? Is this database damaged in the first
>place?
> > > > Even tho it functions properly as far as all the Perl scripts which
>manage
> > > > it. Or is there a Perl script which is managing something wrong?
> > > >
> > > > Like I said before, this is a learning exercise, I'm trying to fill
>the
> > > > holes in my understanding between the manual and actual command
>execution.
> > > >
> > > > -- more below --
> > > >
> > > >
> > > > >2nd question:
> > > > >replication's good if you need a 'live' secondary...for instance, at
>my
> > >last
> > > > >job we used a replicated secondary database to run intensive
>up-to-date
> > > > >statistic queries that otherwise would have locked important tables.
>(pre
> > > > >row-level locking table types, of course).
> > > >
> > > > I'll need to read more about this feature.
> > > >
> > > > >if you just want backups made at intervals, i'd just dump
>all/important
> > > > >tables to a file periodically via cron.
> > > >
> > > > You mean something like this?
> > > >
> > > > 50 4 * * * cp -af /var/lib/mysql/test/*.*
> > > > /var/lib/mysql/backup-hold/test/ > /dev/null 2>&1
> > > >
> > > > Thanks for your help, I'm not totally stupid, it's just that I seem to
> > >have
> > > > a fog in front of my eyes.
> > > >
> > > > Joe
> > > >
> > > >
> > > > >that's just me.
> > > > >
> > > > >hth.
> > > > >
> > > > >-ravi.
> > > > >
> > > > >-----Original Message-----
> > > > >From: Joe Taraba [mailto:[EMAIL PROTECTED]]
> > > > >Sent: Wednesday, July 04, 2001 1:48 PM
> > > > >To: [EMAIL PROTECTED]
> > > > >Subject: Replication, update log, mysqldump???
> > > > >
> > > > >
> > > > >Hi;
> > > > >
> > > > >I'm trying to learn MySQL, having some difficulties understanding
>backup
> > > > >strategies.
> > > > >
> > > > >mysql Ver 9.38 Distrib 3.22.32, for pc-linux-gnu (i686)
> > > > >
> > > > >Where is the documentation (besides man pages) for mysql Ver 3.22.32
> > > > >
> > > > >According to the the 3.23.29 online docs it appears that there are
>four
> > > > >ways to go:
> > > > >
> > > > >Replication
> > > > >update log
> > > > >mysqldump
> > > > >mysqlhotcopy script
> > > > >
> > > > >The mysqlhotcopy script is apparently not available for my version so
>I
> > > > >manually backed-up (copied) all the *.frm, *.MYD, and *.MYI files for
> > >each
> > > > >database, so I know the usefulness of that approach--sort'a.
> > > > >
> > > > >I tried mysqldump on an existing database, and then tried to load it
>back
> > > > >and got several types of errors.
> > > > >
> > > > >1. Table exists errors, so I deleted all the *.frm, *.MYD, and *.MYI
> > >files
> > > > >for that database.
> > > > >2. I tried to load the dumpfile again and got errors about duplicate
> > >keys.
> > > > >
> > > > >This database is not visible to anyone right now, so there were no
> > > > >activities going on. This is an exact copy of a functioning customer
> > > > >database.
> > > > >
> > > > >I haven't tried using the update log, nor do I fully understand it's
> > > > >usefulness. The online docs say not to use it for versions later than
> > > > ><quote> Now that master-slave internal replication is available
>starting
> > >in
> > > > >Version 3.23.15, using the update log to implement replications is
>not
> > > > >recommended. See section 11 Replication in MySQL. </quote>
> > > > >
> > > > >Replication, I guess is not available for my version either. I
>glanced
> > > > >briefly at the docs on Replication--it looks pretty nifty.
> > > > >
> > > > >So I guess there are actually two questions here:
> > > > >
> > > > >1. Why the errors from mysqldump (reload) from an apparently
>perfectly
> > >good
> > > > >database?
> > > > >
> > > > >2. Should I upgrade my version of MySQL and follow the Replication
> > > > >procedures?
> > > > >
> > > > >A ton of thanks from anyone not taking a holiday (USA) today.
> > > > >
> > > > >Joe
> > > > >
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
