Is it possible to export one file with mysqldump command, with two tables , which one of them has foreign key?
I was wondering if it is possible to export one file with two tables and FK? I found out that you can do put fk on 1 … but can I set up –where with table1.id = table2.id_tab1 ?
mysqldump -u root -p DBNAME SET FOREIGN_KEY_CHECKS = 1 --where table1.id = table2.id_tab1 ; 
No, you can’t do that.
Mysqldump basically does this:
for each table { SELECT * FROM $table WHERE $where; } It only reads tables one at a time, not doing joins. So there is no way the WHERE clause you give in the arguments can reference more than one table. It also means the WHERE clause you give must be applicable to every table dumped.
I was able to trick it using a subquery, but this is limited to dumping one table at a time.
I created tables:
create table test.parent (id int auto_increment primary key); create table test.child (parent_id int, foreign key (parent_id) references test.parent(id)); Put rows into parent and just one row into child:
INSERT INTO parent (id) VALUES (1),(2),(3); INSERT INTO child (parent_id) VALUES (1); Now try to dump parent:
mysqldump test parent ... INSERT INTO `parent` VALUES (1),(2),(3); That works to dump all rows from one table.
We can’t do a join, but can we use something in the WHERE clause to make it check the child table, so we only get rows that match a row from child?
mysqldump test parent --where="exists(select * from child where parent_id=parent.id)" ... mysqldump: Couldn't execute 'SELECT /*!40001 SQL_NO_CACHE */ * FROM `parent` WHERE exists(select * from child where parent_id=parent.id)': Table 'child' was not locked with LOCK TABLES (1100) Whoops? Can we make it not require locks?
mysqldump --help ... -l, --lock-tables Lock all tables for read. (Defaults to on; use --skip-lock-tables to disable.) Yes!
mysqldump --skip-lock-tables test parent --where="exists(select * from child where parent_id=parent.id)" ... INSERT INTO `parent` VALUES (1); So now it dumps data from parent but limits the rows to the one that has a matching row in child.
