Manipulating giant MySQL dump files

Question

What is the easiest way to get data for one table, delete one table, or split the entire dump file into files, each of which contains separate tables? I usually end up doing a lot of vi regex crossovers, but I'm sure there are easy ways to do this with awk / perl, etc. The first page of Google results returns a bunch of broken perl scripts.

Answer 1

When I need to pull one table from a sql dump, I use a combination of grep, head and tail.

For example:

grep -n "CREATE TABLE" dump.sql

, , 200, - 269, :

head -n 268 dump.sql > tophalf.sql
tail -n 69 tophalf.sql > yourtable.sql

, , script, .

- ?

, bash:

grep -n "CREATE TABLE " dump.sql  | tr ':`(' '  ' | awk '{print $1, $4}'

, :

200 FooTable
269 BarTable

Answer 2

mysqldump -T, .

:

- tab = , -T

, . mysqldump tbl_name.sql, CREATE TABLE , tbl_name.txt, . - .

.txt ​​ . --fields-xxx -line-terminated-by.

. , mysqldump , mysqld. FILE, .

Answer 3

script splitted.sql.

, Ive sed -r.

MyDumpSplitter .

Answer 4

Maatkit mk-parallel-dump mk-parallel-restore.

Answer 5

, , SQL, Mysql. , , .

split -l 1000 import.sql splited_file

sql 1000 .

, -