Professional Web Applications Themes

Delete *.MYD, -MYI and -frm files - MySQL

Hello group, I am using mysql 5.0 on Suse10.0 for a logging system where one table is used for each day. So the tables of yesterday and earlier are not used for writing, only for reading. When I want to make a backup of the tables is it then safe to do cp tablename.* /tmp/backup from a shell script, or do I need to use the mysqldrop command? And as for deleting of the tables, is it safe to delete the tables' files (with "rm yesterday.* "), or do I need to execute the 'DROP TABLE yesterday' command? Again, is ...

  1. #1

    Default Delete *.MYD, -MYI and -frm files

    Hello group,

    I am using mysql 5.0 on Suse10.0 for a logging system where one table is
    used for each day.
    So the tables of yesterday and earlier are not used for writing, only for
    reading.

    When I want to make a backup of the tables is it then safe to do

    cp tablename.* /tmp/backup

    from a shell script, or do I need to use the mysqldrop command?

    And as for deleting of the tables, is it safe to delete the tables' files
    (with "rm yesterday.* "), or do I need to execute the 'DROP TABLE yesterday'
    command?

    Again, is is guaranteed that the tables are not written to.

    Any hints appreciated,
    Hans


    Hans Guest

  2. #2

    Default Re: Delete *.MYD, -MYI and -frm files

    "Hans" <nieuwslezerzonnet.nl> wrote:
    >
    > When I want to make a backup of the tables is it then safe to do
    >
    > cp tablename.* /tmp/backup
    >
    > from a shell script?
    Copying MyISAM files is safe, provided that

    a) the tables have been flushed to disk (FLUSH TABLES)
    b) the tables are not opened for writing
    > And as for deleting of the tables, is it safe to delete the tables' files
    > (with "rm yesterday.* "), or do I need to execute the 'DROP TABLE yesterday'
    > command?
    I suggest DROP TABLE because it does not interfere with read access to
    those tables. Also DROP TABLE clears the caches of MySQL (query cache,
    table cache).


    XL
    --
    Axel Schwenke, Senior Software Developer, MySQL AB

    Online User Manual: [url]http://dev.mysql.com/doc/refman/5.0/en/[/url]
    MySQL User Forums: [url]http://forums.mysql.com/[/url]
    Axel Schwenke Guest

  3. #3

    Default Re: Delete *.MYD, -MYI and -frm files

    Hans wrote:
    > Hello group,
    >
    > I am using mysql 5.0 on Suse10.0 for a logging system where one table is
    > used for each day.
    > So the tables of yesterday and earlier are not used for writing, only for
    > reading.
    >
    > When I want to make a backup of the tables is it then safe to do
    >
    > cp tablename.* /tmp/backup
    >
    No, it is almost never safe to do just a copy of the database file and
    expect it to work later. The only exception is if everything is shut
    down and you're taking a backup of everything - i.e. a system backup.

    Export the data or must mysqldump instead.
    > from a shell script, or do I need to use the mysqldrop command?
    >
    > And as for deleting of the tables, is it safe to delete the tables' files
    > (with "rm yesterday.* "), or do I need to execute the 'DROP TABLE yesterday'
    > command?
    >
    Yes you need to drop the tables.
    > Again, is is guaranteed that the tables are not written to.
    >
    > Any hints appreciated,
    > Hans
    >
    >
    The bottom line is - this is a database, not a set of files. Use the
    appropriate database commands; that's what they're there for. Even if
    something might happen to work for another person - or even works for
    you at this moment, too many things can go wrong otherwise.

    --
    ==================
    Remove the "x" from my email address
    Jerry Stuckle
    JDS Computer Training Corp.
    [email]jstucklexattglobal.net[/email]
    ==================
    Jerry Stuckle Guest

  4. #4

    Default Re: Delete *.MYD, -MYI and -frm files

    >I am using mysql 5.0 on Suse10.0 for a logging system where one table is
    >used for each day.
    >So the tables of yesterday and earlier are not used for writing, only for
    >reading.
    I have to wonder about this design. If you start doing unions of a month's
    worth of tables, you know you've got a big problem.
    >When I want to make a backup of the tables is it then safe to do
    >
    >cp tablename.* /tmp/backup
    >
    >from a shell script, or do I need to use the mysqldrop command?
    "mysqldrop"? No. mysqldump, yes.

    If the data has been flushed and the tables are not open for writing,
    you're OK. How long does it take for that to happen if nobody issues
    a flush command and the server is fairly quiet? A week?

    Also, this quits working should you start using InnoDB or some other
    storage engine. DROP TABLE will still work.
    >And as for deleting of the tables, is it safe to delete the tables' files
    >(with "rm yesterday.* "), or do I need to execute the 'DROP TABLE yesterday'
    >command?
    You said the tables were possibly in use for reading. Deleting the
    tables out from under a running query might be rather upsetting. Also,
    deleting open tables might keep the disk space still allocated as long
    as the server keeps the tables open.
    >Again, is is guaranteed that the tables are not written to.
    But not guaranteed that they aren't read.

    Gordon Burditt Guest

  5. #5

    Default Re: Delete *.MYD, -MYI and -frm files

    [email]gordonb.6j2d9burditt.org[/email] (Gordon Burditt) wrote:
    >>I am using mysql 5.0 on Suse10.0 for a logging system where one table is
    >>used for each day.
    >>So the tables of yesterday and earlier are not used for writing, only for
    >>reading.
    >
    > I have to wonder about this design. If you start doing unions of a month's
    > worth of tables, you know you've got a big problem.
    MERGE tables comes to mind. Also there is the ARCHIVE engine that
    was designed specifically for putting log data into MySQL tables.

    However IMHO there are only few cases that justify putting log
    records into SQL tables *in real time*. Most applications are much
    better off with writing conventional log files.


    XL
    --
    Axel Schwenke, Senior Software Developer, MySQL AB

    Online User Manual: [url]http://dev.mysql.com/doc/refman/5.0/en/[/url]
    MySQL User Forums: [url]http://forums.mysql.com/[/url]
    Axel Schwenke Guest

  6. #6

    Default Re: Delete *.MYD, -MYI and -frm files


    "Axel Schwenke" <axel.schwenkegmx.de> wrote in message
    news:doqice.gpa.lnxl.homelinux.org...
    <snip>
    >
    > However IMHO there are only few cases that justify putting log
    > records into SQL tables *in real time*. Most applications are much
    > better off with writing conventional log files.
    Not this one, its not logging as in 'writing what happened', it's more like
    a voicerecording system for a telephone infrastructure.

    Gr, Hans


    Hans Guest

  7. #7

    Default Re: Delete *.MYD, -MYI and -frm files


    "Gordon Burditt" <gordonb.6j2d9burditt.org> wrote in message
    news:12epodil8oou085corp.supernews.com...
    > >I am using mysql 5.0 on Suse10.0 for a logging system where one table is
    >>used for each day.
    >>So the tables of yesterday and earlier are not used for writing, only for
    >>reading.
    >
    > I have to wonder about this design. If you start doing unions of a
    > month's
    > worth of tables, you know you've got a big problem.
    >
    Yeah, I know, you've discouraged me before to use this design
    (news:128b4j9a5tmjp38corp.supernews.com). But I did, so that is not subject
    of discussion here :-)
    >>When I want to make a backup of the tables is it then safe to do
    >>
    >>cp tablename.* /tmp/backup
    >>
    >>from a shell script, or do I need to use the mysqldrop command?
    >
    > "mysqldrop"? No. mysqldump, yes.
    Got me!
    >
    > If the data has been flushed and the tables are not open for writing,
    > you're OK. How long does it take for that to happen if nobody issues
    > a flush command and the server is fairly quiet? A week?
    The backup would be executed from a sceduled shell script, in which the
    FLUSH command could also be executed.
    >
    > Also, this quits working should you start using InnoDB or some other
    > storage engine. DROP TABLE will still work.
    >
    Now that is one thing that starts me thinking...
    > You said the tables were possibly in use for reading. Deleting the
    > tables out from under a running query might be rather upsetting. Also,
    > deleting open tables might keep the disk space still allocated as long
    > as the server keeps the tables open.
    >
    >
    >>Again, is is guaranteed that the tables are not written to.
    >
    > But not guaranteed that they aren't read.
    >
    You convinced me. I guess i'll be using the mysql tools for backup.

    Thanks

    Hans.


    Hans Guest

Similar Threads

  1. Question problems copying or renaming files unlock delete files
    By anhgun123 in forum Brainstorming Area
    Replies: 1
    Last Post: February 27th, 11:34 AM
  2. How to delete PDF-files?
    By Lars Forslin in forum Macromedia Contribute General Discussion
    Replies: 3
    Last Post: October 23rd, 06:02 PM
  3. Help! How do I delete old files
    By Resource Coord in forum Macromedia Contribute Connection Administrtion
    Replies: 0
    Last Post: April 28th, 07:06 PM
  4. delete files
    By Jenne in forum Windows XP/2000/ME
    Replies: 1
    Last Post: July 15th, 10:02 AM
  5. Cannot delete certain MP3 & WMA files
    By AR in forum Windows XP/2000/ME
    Replies: 3
    Last Post: July 2nd, 05:10 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139