Take the 2-minute tour ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

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.

share|improve this question
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 '12 at 19:45

2 Answers 2

up vote 2 down vote accepted

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

share|improve this answer

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.