Professional Web Applications Themes

Moving MySQL Files - MySQL

Hi, I would apreciate your input on the following solution to the problem below. We have a nightly job that takes too long to run. This is mostly because we have to save our mysql data files (*.MYD and *.MYI) to another location. While this takes place, the mysqld process has to be down and we experience down time. We need to do this at 12AM EST which is a problem for Tokyo since it is smack during their busy hours. What if we just renamed the files, put empty ones in their place and restarted mysqld? That is: we ...

  1. #1

    Default Moving MySQL Files

    Hi,

    I would apreciate your input on the following solution to the problem
    below.

    We have a nightly job that takes too long to run. This is mostly
    because we have to save our mysql data files (*.MYD and *.MYI) to
    another location.

    While this takes place, the mysqld process has to be down and we
    experience down time. We need to do this at 12AM EST which is a
    problem for Tokyo since it is smack during their busy hours.

    What if we just renamed the files, put empty ones in their place and
    restarted mysqld? That is:

    we have these files which are 8GB in total:

    ~/mysql/data/mydb/XYZ.frm
    ~/mysql/data/mydb/XYZ.MYD
    ~/mysql/data/mydb/XYZ.MYI

    After shutting down mysql, we could:

    # Move the old files out of the way so we can archive them later
    mv ~/mysql/data/mydb/XYZ.frm XYZ_old.frm
    mv ~/mysql/data/mydb/XYZ.MYD XYZ_old.MYD
    mv ~/mysql/data/mydb/XYZ.MYI XYZ_old.MYI

    # Copy empty ones in their place so we can start our process
    cp ~/mysql/data/mydb/XYZ_emptyShell.frm XYZ.frm
    cp ~/mysql/data/mydb/XYZ_emptyShell.MYD XYZ.MYD
    cp ~/mysql/data/mydb/XYZ_emptyShell.MYI XYZ.MYI

    Start mysqld.

    Assume XYZ_emptyShell.* are valid mysql files representing an XYZ
    table with 0 rows.

    Would mysql complain if we do this to it?

    yankeerivera@yahoo.com Guest

  2. #2

    Default Re: Moving MySQL Files

    com wrote: 

    You can make a copy of the MyISAM table files *with running mysqld*
    provided that

    a) the table has been flushed to disk and
    b) the table is not written to while you copy files

    both can be accomplished by FLUSH TABLES WITH READ LOCK. Another way
    would be LOCK TABLES ... READ followed by FLUSH TABLE ...
    After you finished copying, just UNLOCK TABLES.

    During copying, either all tables or at least the table in question
    would be locked, allowing read only operation - all writes would have
    to wait until you unlock the table(s). This is not perfect, but
    probably much better than mysqld being completely offline.

     

    <cut>

    This could be done with running mysqld as well

    1. create empty copy of your table (CREATE TABLE ... LIKE ...)
    2. rename tables, effectively swapping old and new table
    (this is an atomic operation, even for multiple renames)
    3. flush the old table
    4. copy the files
    5. drop the old table


    XL
    --
    Axel Schwenke, Support Engineer, MySQL AB

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

  3. #3

    Default Re: Moving MySQL Files

    com wrote:
     
    <cut>...</cut> 

    If you don't need the old data, that would work, but why not just mirror the
    hard drive, that way you always have a backup.

    --

    //Aho
    J.O. Guest

  4. #4

    Default Re: Moving MySQL Files

    >I would apreciate your input on the following solution to the problem 

    I'd like to suggest this procedure:
    1. LOCK TABLES
    2. Rename table XYZ to ZYX (using ALTER TABLE).
    Make sure that no other SQL references table ZYX.
    3. Create a new table XYZ using the same schema with CREATE TABLE.
    4. FLUSH TABLES
    5. UNLOCK TABLES
    6. Copy ZYX files at your leisure, hoping it takes less than 24 hours to
    do the copy.
    7. Delete the ZYX files when the copy is done.

    Advantages: no mysqld downtime. Only a very brief time (Steps 2-5
    ought to execute in under a second) when table XYZ doesn't exist,
    and you're locked during that time. Established connections don't
    have to be dropped. If you could keep references to table XYZ from
    happening in some other way, you could omit steps 1 and 5.


    I'll suggest an alternative to steps 6 and 7:

    6. mysqldump table ZYX and copy it elsewhere
    7. DROP TABLE ZYX

    Gordon Guest

Similar Threads

  1. moving access data to mysql
    By namtax in forum Coldfusion Database Access
    Replies: 7
    Last Post: February 5th, 07:41 PM
  2. Query problem moving from mysql 4 to 5
    By Justin Koivisto in forum MySQL
    Replies: 2
    Last Post: February 21st, 03:09 PM
  3. Moving from MS access to Mysql
    By kenji776 in forum Coldfusion Database Access
    Replies: 4
    Last Post: October 3rd, 07:25 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