0

i like to create a backup script in java which will backup a table but only some selected rows. Please see this example table:

table names:
    id, group, name
    1, 1, "John"
    2, 1, "Frank"
    3, 1, "Smith"
    4, 2, "Anny"
    5, 2, "Gustav"

table ages:
id, group, age
1, 1, 54
2, 1, 30
3, 1, 55
4, 2, 20
5, 2, 45

i like to backup the tables "names" and "ages" but only the rows where group=1. Is there any mysqldump script where i can define the tables to backup and a where clause that must fit?

Thanks

Thanks Man i just tryed it but the result is not exactly what i need:

DROP TABLE IF EXISTS `names`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `names` (
...
) ENGINE=MyISAM AUTO_INCREMENT=619 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

mysql dump creates a drop table and a create table. It could not drop the table because there is much more data inside. It must create a delete statement based on the where clause like:

Delete from names where group = 1

without this i am not able to insert the backup a day later to restore and delete some faulty data. If someone inserts it without watching out he deletes the hole table.

Mike
  • 693
  • 1
  • 13
  • 31

1 Answers1

0

Mysqldump has a --where option but the condition must be relevant to all tables.

In your case, it seems it is possible, since you have the same column name and same values you're interested in for both tables.

But this is made a little bit more complicated because you use group as your column name, and that's a reserved keyword in MySQL.

I tested this by creating tables matching your tables' names in my test schema, and running this command:

mysqldump test --tables names ages --where '`group` = 1'

Note the use of quotes. The back-ticks are necessary to delimit the reserved keyword so it can be used as a column identifier in SQL. The single-quotes are necessary to delimit the where condition, to prevent the back-ticks from being interpreted as command substitution by the shell. Double-quotes allow command substitution in a string, so this must use single-quotes.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
  • Thanks group is a varchar field not a bigint. so i have to edit the command to mysqldump test --tables names ages --where '`group` = `1`' ? – Mike Mar 24 '21 at 13:46
  • My mistake, I assumed the value '1' in your question above was an integer. Back-ticks are for identifiers, not values. You should not use back-ticks around the value. You should read https://stackoverflow.com/q/11321491/20860 – Bill Karwin Mar 24 '21 at 13:48
  • Thanks. Please i testet it please see my edited post – Mike Mar 24 '21 at 13:57
  • mysqldump doesn't generate delete statements. You'll have to do that yourself before you restore the dump file. – Bill Karwin Mar 24 '21 at 14:40