Professional Web Applications Themes

How to move whole tables fast - MySQL

I have two MySQL databases on different servers and computers. I often need to move a number of tables from one database to the other database. So far I've been using "mysqldump" to make a backup, and "mysql" to load the backup in the other DB, but the amount of data is fairly large and it is very slow. Is there a faster method? I though of simply copying the database files, but the two database servers are not the same version, so I don't know how that will work. Thanks /Rune...

  1. #1

    Default How to move whole tables fast

    I have two MySQL databases on different servers and computers. I often need
    to move a number of tables from one database to the other database. So far I've
    been using "mysqldump" to make a backup, and "mysql" to load the backup in
    the other DB, but the amount of data is fairly large and it is very slow. Is
    there a faster method? I though of simply copying the database files, but
    the two database servers are not the same version, so I don't know how that
    will work.

    Thanks
    /Rune


    Rune Guest

  2. #2

    Default Re: How to move whole tables fast

    Rune wrote:
    > I have two MySQL databases on different servers and computers. I often need
    > to move a number of tables from one database to the other database. So far I've
    > been using "mysqldump" to make a backup, and "mysql" to load the backup in
    > the other DB, but the amount of data is fairly large and it is very slow. Is
    > there a faster method? I though of simply copying the database files, but
    > the two database servers are not the same version, so I don't know how that
    > will work.
    >
    > Thanks
    > /Rune
    >
    >
    To speed up a dump, you can use

    mysqldump --opt -u USER -pPASSWORD database_name table_name > file.sql

    That way, mysqldump will try all the known tricks to minimize the rebuild time.
    [url]http://dev.mysql.com/doc/refman/5.0/en/mysqldump.html[/url]

    If you were already using that option and it's still too slow for your needs, you may
    want to consider tion instead of dumping tables.
    [url]http://dev.mysql.com/doc/refman/5.0/en/tion.html[/url]

    Copying tables is a tricky business. To get a clean copy, you should stop the server,
    copy the tables and restart the server. Or you could flush the tables, lock them all
    and copy with the server still running. mysqlhotcopy will do the dirty work for you
    [url]http://dev.mysql.com/doc/refman/5.0/en/mysqlhotcopy.html[/url]
    However, it works only for MyISAM tables, and only on Unix and Netware systems.

    Table formats are backward compatible, i.e. a table from a 4.0 server will be read
    and understood by a 4.1 server, but not the other way around.

    ciao
    gmax

    --
    _ _ _ _
    (_|| | |(_|>< The Data Charmer
    _|
    [url]http://datacharmer.blogspot.com/[/url]
    Giuseppe Maxia Guest

  3. #3

    Default Re: How to move whole tables fast

    "Rune" <slamslam.slam> wrote in message
    news:43d0d796$0$15791$14726298news.sunsite.dk...
    >I have two MySQL databases on different servers and computers. I often need
    >to move a number of tables from one database to the other database. So far
    >I've been using "mysqldump" to make a backup, and "mysql" to load the
    >backup in the other DB, but the amount of data is fairly large and it is
    >very slow. Is there a faster method?
    According to the docs, LOAD DATA INFILE is up to 20x faster than running a
    lot of insert statements from a script. You could dump the table(s) with
    SELECT...INTO OUTFILE, transport that data dump to the other server, and
    load it with LOAD DATA INFILE.

    Read about LOAD DATA INFILE here:
    [url]http://dev.mysql.com/doc/refman/5.0/en/load-data.html[/url]

    There are further tips on performance enhancements to loading data here:
    [url]http://dev.mysql.com/doc/refman/5.0/en/insert-speed.html[/url]
    (e.g. use ALTER TABLE...DISABLE KEYS and SET FOREIGN_KEY_CHECKS=0 before the
    data load, then re-enable them afterwards).

    Regards,
    Bill K.


    Bill Karwin Guest

Similar Threads

  1. Need HELP fast!!
    By Quetzacolt in forum Macromedia Freehand
    Replies: 2
    Last Post: July 8th, 11:11 PM
  2. EAP-FAST
    By Martin Briody in forum Windows Server
    Replies: 0
    Last Post: June 29th, 09:59 AM
  3. Slow printing on fast copier but fast printing on slow printer!
    By Davie_Helms@adobeforums.com in forum Adobe Acrobat Windows
    Replies: 0
    Last Post: May 7th, 08:24 PM
  4. fast forward
    By ste2003 in forum Macromedia Flash
    Replies: 0
    Last Post: December 17th, 07:34 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