Dropped Tables with FromDual Backup Manager

Fri, 2019-05-24 07:23 — Shinguz

Some applications have the bad behaviour to CREATE or DROP tables while our FromDual Backup Manager (bman) backup is running.

This leads to the following bman error message:

/opt/mysql/product/5.7.26/bin/mysqldump --user=dba --host=migzm96i --port=3306 --all-databases --quick --single-transaction --flush-logs --triggers --routines --hex-blob --events | tee >(md5sum --binary >/tmp/checksum.23357.md5) | gzip -1
to Destination: /var/mysql/dumps/mysql96i/daily/bck_mysql96i_full_2019-05-22_06-50-01.sql.gz
ERROR: /opt/mysql/product/5.7.26/bin/mysqldump command failed (rc=253).
mysqldump: [Warning] Using a password on the command line interface can be insecure.
Error: Couldn't read status information for table m_report_builder_cache_157_20190521035354 ()
mysqldump: Couldn't execute 'show create table `m_report_builder_cache_157_20190521035354`': Table 'totara.m_report_builder_cache_157_20190521035354' doesn't exist (1146)

There are various strategies to work around this problem:

If the table is only temporary create it with the CREATE command as a TEMPORARY TABLE instead of a normal table. This workaround would not work in this case because the table is a caching table which must be available for other connections as well.
Try to schedule your application job or your bman job in the way they do not collide. With bman that is quite easy but sometimes not with the application.
Try to create the table in its own schema (e.g. cache) which is excluded from bman backup. So you can easily do a bman backup without the cache schema. For example like this:
```
$ bman --target=brman@127.0.0.1:3306 --type=schema --schema=-cache --policy=daily
```

If this strategy also does not work (because you cannot change the application behaviour) try to ignore the table. The underlying command mysqldump knows the option --ignore-table:

mysqldump --help
...
  --ignore-table=name Do not dump the specified table. To specify more than one
                      table to ignore, use the directive multiple times, once
                      for each table.  Each table must be specified with both
                      database and table names, e.g.,
                      --ignore-table=database.table.

This option can be used in bman as well. Options to the underlying application are passed through FromDual Backup Manager as follows:

$ bman --target=brman@127.0.0.1:3306 --type=full --policy=daily --pass-through='--ignore-table=totara.m_report_builder_cache_157_20190521035354'

The problem here is, that this table contains a timestamp in its table name (20190521035354). So the table name is changing all the time. To pass through wildcards with --ignore-table is not possible with mysqldump. The tool mysqldump does not support (yet) this feature. The only solution we have in this case is, to ignore the error message with the risk that possible other error messages are also ignored. This is achieved again with the --pass-through option:
```
$ bman --target=brman@127.0.0.1:3306 --type=full --policy=daily --pass-through='--force'
```