2

I want to backup my database except t1 table's one record (where option='siteurl').

I'm trying mysqldump's --where command.

mysqldump -u root --skip-extended-insert --where "option='siteurl'" konsertvdb t1 > sql.sql

This will work if I backup only the t1 table. But I want to backup all tables, except t1's one record (where option='siteurl') . 

> mysqldump -u root --skip-extended-insert --where "option='siteurl'" konsertvdb > sql.sql
mysqldump: Couldn't execute 'SELECT /*!40001 SQL_NO_CACHE */ * FROM `wp_commentmeta` WHERE option='siteurl'': Unknown column 'option' in 'where clause' (1054)

*(wp_commentmeta is one of my other tables.)

Can you tell me is it possible?

Paul White
  • 83,961
  • 28
  • 402
  • 634
Eray
  • 123
  • 5

1 Answers1

3

Its too simple if you have gone through the mysqldump manual

First run a mysql dump ignore the table you would like to skip

mysqldump -p -u root db_name --ignore-table=db_name.tbl_name > sql.sql

Then once more for your table

mysqldump -p -u root db_name tbl_name --where "id!=2" >> sql.sql

As a note, if you are giving something in --where, it applicable for all the tables your are using. It will be useful in case of multi-tenant db.

georgecj11
  • 518
  • 4
  • 11