0

I have a large sql file (500mb) and want to split it in chunks. I used the shell command split but it doesn't split context-aware before a special pattern (e.g. INSERT) and thus breaks the SQL statement.

The aim is to have two 250mb files both still containing only valid SQL commands. Is this possible?

dtech
  • 13,741
  • 11
  • 48
  • 73
mr.wolle
  • 1,148
  • 2
  • 13
  • 21
  • If you have a 500MB SQL file you're probably doing something wrong, unless it's a backup. If it's a backup: create the backup in smaller chunks: http://stackoverflow.com/questions/132902/how-do-i-split-the-output-from-mysqldump-into-smaller-files – dtech May 15 '13 at 17:48
  • Its for migrate a db to another server. I use phpmyadmin – mr.wolle May 15 '13 at 17:50
  • Then create the dump with mysqldump in smaller files, see the link. – dtech May 15 '13 at 17:51
  • mabye that link will help you http://scriptingmysql.wordpress.com/2011/09/14/splitting-a-mysql-dump-file-into-smaller-files-via-perl/ – MatthiasLaug May 25 '13 at 10:53

1 Answers1

0

Use:

mysqldump -u admin -p database1 > /backup/db/database1.sql

or

mysqldump -u admin -p --all-databases > /backup/db/all_databases.sql

If you have only MyISAM tables you can use:

mysqlhotcopy -u admin -p password123 database1 /backup

for faster backups. mysqlhotcopy doesn't generating sql but copying the files of the database.

For recovery of mysqldumped databases use:

mysql -u admin -p database1 < database.sql

or

mysql -u admin -p <all_databases.sql

For mysqlhotcopy:

To restore the backup from the mysqlhotcopy backup, simply copy the files from the backup directory to the /var/lib/mysql/{db-name} directory. Just to be on the safe-side, make sure to stop the mysql before you restore (copy) the files. After you copy the files to the /var/lib/mysql/{db-name} start the mysql again.

See here: http://www.thegeekstuff.com/2008/07/backup-and-restore-mysql-database-using-mysqlhotcopy/

mr.wolle
  • 1,148
  • 2
  • 13
  • 21