i have very hugh table , around 100 M records and 100 GB in a dump file , when i try to restore it it to a different DB i get sql query lost connection , i want to try and dump this table into chunks (something like 10 chinks of 10 GB) where each chink will be in seperate table. what i managed to optimized so far is this :

mysqldump --skip-triggers --compact --no-create-info --single-transaction --quick --max_allowed_packet 1G  -h {host} -u {user} -P 3306 -p{pwasword} {my_schema} {}> /mnt/datadir/{table_name}.sql

and now the output is that i have 1 file {table_name}.sql i na size of 100 GB i want to get 10 files in sizes of 10 GB each


You can use primary key to specify to dump only id in those range

mysqldump -u root -p db_name table_name --where='id  < 10000' > test1.sql
mysqldump -u root -p db_name table_name --where='id between 10000 and 20000' > test2.sql
 mysqldump -u root -p db_name table_name --where='id between 900000 and 1000000' > test11.sql

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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