Mysqldump subset of database with relational data using a master table

backupmigrationmysqldumprelations

I have found a lot of articles describing the concept of exporting and importing relational data, but I have not been able to find any article describing this more generally for a MySQL database, which is why I am creating this question.

Problem:

I am interested in doing three operations:

  1. Exporting a subset of my database (including relations)
  2. Removing a subset of my database (including relations)
  3. Importing a subset of my database (including relations)

The subset can then be inserted into another database (while maintaining the relationships) or can be kept as backup if the subset is also deleted from the main database.

Datebase

Imagine the following setup:

  • A master has one slave and one cook.
  • The cook can have multiple assistants.
  • All people have one peopleproperty associated with them.

The Schema can be seen here:
MySQL Schema of database. Illustrating the relations.

Question:

How can I export, delete and import a subset of masters where id<1000, including their slaves, their cooks and the cooks assistants.

The peopleproperty table is small and will be identical on all databases (the live database containing a lot of data, the development database containing some of the data, and the backup database containing all historical data).

Database Scheme:

Here is he database scheme if needed:

-- -----------------------------------------------------
-- Table `mydb`.`cook`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`cook` (
  `id` INT NOT NULL,
  `cookfield` VARCHAR(45) NULL,
  `master_id` INT NOT NULL,
  `userproperty_id` INT NOT NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_user_master_idx` (`master_id` ASC),
  INDEX `fk_user_userproperty1_idx` (`userproperty_id` ASC),
  CONSTRAINT `fk_user_master`
    FOREIGN KEY (`master_id`)
    REFERENCES `mydb`.`master` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_user_userproperty1`
    FOREIGN KEY (`userproperty_id`)
    REFERENCES `mydb`.`peopleproperty` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `mydb`.`assistant`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`assistant` (
  `id` INT NOT NULL,
  `assistantfield` VARCHAR(45) NULL,
  `cook_id` INT NOT NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_assistant_cook1_idx` (`cook_id` ASC),
  CONSTRAINT `fk_assistant_cook1`
    FOREIGN KEY (`cook_id`)
    REFERENCES `mydb`.`cook` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `mydb`.`peopleproperty`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`peopleproperty` (
  `id` INT NOT NULL,
  `peoplepropertyfield` VARCHAR(45) NULL,
  `assistant_id` INT NOT NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_peopleproperty_assistant1_idx` (`assistant_id` ASC),
  CONSTRAINT `fk_peopleproperty_assistant1`
    FOREIGN KEY (`assistant_id`)
    REFERENCES `mydb`.`assistant` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `mydb`.`master`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`master` (
  `id` INT NOT NULL,
  `masterfield` VARCHAR(45) NULL,
  `peopleproperty_id` INT NOT NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_master_peopleproperty1_idx` (`peopleproperty_id` ASC),
  CONSTRAINT `fk_master_peopleproperty1`
    FOREIGN KEY (`peopleproperty_id`)
    REFERENCES `mydb`.`peopleproperty` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `mydb`.`slave`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`slave` (
  `id` INT NOT NULL,
  `slavefield` VARCHAR(45) NULL,
  `peopleproperty_id` INT NOT NULL,
  `master_id` INT NOT NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_slave_peopleproperty1_idx` (`peopleproperty_id` ASC),
  INDEX `fk_slave_master1_idx` (`master_id` ASC),
  CONSTRAINT `fk_slave_peopleproperty1`
    FOREIGN KEY (`peopleproperty_id`)
    REFERENCES `mydb`.`peopleproperty` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_slave_master1`
    FOREIGN KEY (`master_id`)
    REFERENCES `mydb`.`master` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

Best Answer

Try the Jailer subsetting tool. It's for dumping subsets of relational data, keeping referential integrity.

Related Question