Breaking database into small pieces for transfer

  • Posted by wktd on March 21st, 2010 filed in portal.jsqf.com

  • Is there a way to take say a 170mb mySQL db and break it apart into small pieces for purposes of transferring it somewhere else? I'm not sure I will be able to transfer the entire db as FTP and Telnet port are not open outbound on the firewall, so I may have to rely on email. My thoughts were to break the db into small fragments, like exporting/bcp the tables to a txt file and transferring them, then rebuilding them on the new server. Is this possible? I'm using mySQL 3.23.44. Thanks.


  • Eva, I have Terminal Server access to my server so I am able to zip my dump files directly. However, the compression rate is not that impressive. My 170mb db zips to 130mb, which is still too large to email ;)


  • Originally posted by Blairwarlock
    Ok, I will try tar + gzip. Thanks! no probs :D


  • did you know, you can also tar and gzip compress an sql file for transfer ;)

    backup to sql file using mysqldump

    http://mysql.he.net/doc/m/y/mysqldump.html

    i.e.

    mysqldump --opt -u mysqlusername -p databasename > /path/to/backup.sql

    then in telnet/shell, go into the directory containing the sql file and type

    tar -cvf - backup.sql gzip -c > backupsql.tar.gz


  • Ok, I will try tar + gzip. Thanks!


  • tar + gzip is better

    my 940mb sql file tarred and gzipped is 418MB :)

    otherwise you can dump each table at a time or group of tables

    mysqldump --opt -u mysqlusername -p databasename tablename1> /path/to/tablename1.sql

    mysqldump --opt -u mysqlusername -p databasename tablename2> /path/to/tablename2.sql

    etc etc







    • #If you have any other info about this subject , Please add it free.#
    Your name:
    E-mail:
    Telphone:

    Your comments:


    If you have any other info about Breaking database into small pieces for transfer , Please add it free.