how to take backup of a child table with foreign key dependency in mysql using bash [duplicate]

Question

Parent table : p_id, t_id, name , salary
Child table : c_id, t_id , age
key : Child.t_id=Parent.t_id

While taking backup i would like to take pull only the matching records from Child table . How can i do it via bash script ?

Thanks in advance .

Answer 1

Assuming MySQL 5.x

mysqldump has a --where option that can be used to limit/select rows to be backed up.

There isn't an option to join two tables using --where but you can identify which t_id values you want to backup and then run two mysqldumps as follows:

mysqldump -u... -p... --where="t_id in (10,100,200,300,.....)" database_name parent_table > parent.sql

mysqldump -u... -p... --where="t_id in (10,100,200,300,.....)" database_name child_table > child.sql

Once you have the parent.sql and child.sql dump files, load them into the target database and you should be good to go.

Also, note that I am assuming t_id column is indexed on both tables otherwise it may take a long time to dump the rows if the two tables have large number of rows.