0

I have a bigger database(2.1GB). I want input it into mysql in server part. If I output it from mysql in local part with tar.gz format, it has 420MB, if left a sql format, it has 2.1GB.

How to use SSH import database into mysql? some method here support sql format only:

# mysql -u username -p database_name < file.sql

transfer a 420MB tar.gz to server part, than do a unziped?(maybe a long time during unzipped) cut sql to many pieces? Or better idea?

Another question, should I modify the my.cnf to support a large size file input?

Regards.

1
  • 2
    Why are you taring it? I can understand the gzip compression, but adding it into tar makes it more difficult to work with.
    – Zoredache
    Jan 24, 2012 at 19:45

2 Answers 2

2

Yes, if you have disk space, transfer the compressed file to the server, uncompress there, and then import.

There's no obvious need to cut the file into smaller pieces. It's not like you're posting to Usenet.

If your connection is slow, you can use "rsync", which can resume interrupted transfers, e.g., rsync file.sql.gz server:/tmp/file.sql.gz

3

You have a couple of options here if you don't want to split that SQL import into smaller chunks. Also, it is a good idea to test this on a test machine before doing it for real.

You could use bzip2 -9 to compress file.sql, transfer it and then run the import with this command that doesn't need you to decompress the file:

bzcat file.sql.bz2 | mysql -u username -p database_name

Or if you are more bold, you could directly use ssh to do it with this command:

cat file.sql | ssh -C -c blowfish username@myserver mysql -u username -p database_name

In this command, -C enables compression during transfer and -c blowfish selects a cyphering algorithm that uses less CPU than the default one.

Common sense would suggest to transfer the compressed file so that you can verify the checksum with for example MD5 and then run the import from the compressed file.

Hope this helps.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .