SQL large file Split import

first, file compression and decompression

sql文件比较大，通常会压缩至大概十分之一大小。1、压缩文件 非打包tar -czvf  test.tar.gz  test.sql   //压缩 test.sql文件为test.tar.gz(会保留原文件)tar -czvf  test.tar.gz  test/      //压缩目录下的文件 （不过目录的压缩效果就一般了）2、列出压缩文件内容tar -tzvf test.tar.gz rw-r--r-- root/root     0 2010-05-24 16:51:59 test.sql3、解压文件tar -xzvf test.tar.gz (会保留原文件)

Second, large file splitting

Today, there is a problem, to import a large SQL script into the database, who can think that this file has been more than 40G. Just started the direct import, the results imported a few G after the card is no longer executed, on-line check, the feeling is that the file is too large, enough memory can not cache so much content at once. So I think of the way to split the file, I split into more than 100 files, and then find the last execution location, a small file execution, the final perfect solution to the problem.
There is just one thing to note, because the file is split by line, so the CREATE statement that creates the table might be truncated, so check the file before executing it, and then edit the file if you have a problem.

1. Function:
Splitting a file into multiple

2. Syntax:
Split [OPTION] ... [INPUT [PREFIX]]

3. Description:
The input file ' input ' is segmented, output a fixed-size file fragment "Prefixaa,prefixab,...", the default size is 1000 rows, and the default value of prefix is ' x ', if no input file is specified, or input is-, Then it is read from the standard input.

4. Common parameters
-A,--suffix-length=n
The length of the suffix used, the default length is 2, such as ' AA ', ' ab ' suffix, and its length is 2.
-B,--bytes=size
Specifies the size of each output file fragment.
-C,--line-bytes=size
Specifies the maximum line byte (???) for each output file fragment.
-D,--numeric-suffixes
Specifies that the output file fragment suffix is a number instead of the default alphabetic form.
-L,--lines=number
Specifies the number of rows per output file fragment.

Above, the size specified can be specified in the following manner: suffix B for 512 bytes, suffix k for 1 k, and suffix m for 1 m.

5. Use this time
Each 300 lines are split into a file, the split file name begins with a name, and a number is the suffix suffix length of 3
Split-l test.sql-d-A 3 name

Third, database import

1, mysql-uxxxx-pxxxx-h119.xxx.xxx.xxx--port=36991-a; (This is our command to connect to the database with IP and port.) Take on-A to switch the database without pre-reading the table data, otherwise it will be very card)

2.show databases; Query the database.

3. Use XXXX; Use a database

4. Files are then imported:
   Before using each file, check the first end of the file, there is no create build table statement is truncated, if there is manual processing under.
   mysql>source/web/name001; (in MySQL command line execution, first create and enter the database,/WEB/NAME001 is your split sub-file address)

5. An error handling

After the import is complete, in the Open data representation, MySQL Error! Can ' t create/write to file '/tmp/#sql_c62_0. MYI ' ...

Restart the database also error, and then view the log:
cd/var/log/
Tail-50 Mysqld.log
This is the same error in the log.
/usr/libexec/mysqld:can ' t create/write to file '/tmp/ibypwdhe ' (errcode:13)

Solve:
Look under the system directory/TMP permissions are not enough, the previous set is 1666 to 1777 on it.
A description of this can be viewed in another article of mine: http://blog.51cto.com/ycgit/1406909

SQL large file Split import