backupninja issueshttps://0xacab.org/liberate/backupninja/-/issues2020-08-14T05:44:07Zhttps://0xacab.org/liberate/backupninja/-/issues/3637Warn about empty dumpfiles / huge table issue2020-08-14T05:44:07ZrhattoWarn about empty dumpfiles / huge table issueSome months ago I had to restore a database from a gziped mysql dump. To my surprise, the dump was an empty file!
Luckily, I had also a mysql hotbackup so I had the opportunity inspect what went wrong with the dump. It happened that I h...Some months ago I had to restore a database from a gziped mysql dump. To my surprise, the dump was an empty file!
Luckily, I had also a mysql hotbackup so I had the opportunity inspect what went wrong with the dump. It happened that I had a huge table (~1.4GB) full of junk that somehow prevented mysqldump do the job.
At the time I was very busy to find what exactly was making mysqldump die: if it was a timeout, a memory issue, buffer size, etc. Neither I still have the original dump to test.
The first thing that passed in my mind was: how backupninja didn't told me that it had an empty dumpfile? Looking at the handler code, I just saw an exit status check:
<pre>
output=`su $user -s /bin/bash -c "set -o pipefail ; $execstr" 2>&1`
code=$?
if [ "$code" == "0" ]
then
debug $output
info "Successfully finished dump of mysql database $db"
else
warning $output
warning "Failed to dump mysql databases $db"
fi
</pre>
Woudn't an additional size check also be needed? The handler could throw a warning in case of a zero-sized dump:
<pre>
if [ ! -s "$dumpdir/${db}.sql" ]; then
warning "Dump of mysql database $db has zero size"
fi
</pre>
*(from redmine: created on 2011-11-17)*Guillaume SubironGuillaume Subironhttps://0xacab.org/liberate/backupninja/-/issues/2495review mysqldump options2020-08-14T05:55:37Zmicahreview mysqldump optionsthe mysqldump default options have changed over time, its about time to review these options. for example is it a good idea to lock the tables during dump? when the tables are locked, then the application usually is blocked. another opti...the mysqldump default options have changed over time, its about time to review these options. for example is it a good idea to lock the tables during dump? when the tables are locked, then the application usually is blocked. another option that might make a lot of sense is to have skip-extended-insert, because by default extended-inserts are enabled in mysqldump, and they make SQLdumps very difficult to edit with an editor, what purpose do these extended inserts serve? I'm not sure, need to research that.
*(from redmine: created on 2010-09-09)*Guillaume SubironGuillaume Subironhttps://0xacab.org/liberate/backupninja/-/issues/605innodb tables should use single transaction2020-08-14T05:55:44ZGhost Userinnodb tables should use single transactionHi,
For better performance and prevent long time locks on big tables, mysqldump should be called with "--single-transaction" instead of "--lock-tables" on innodb tables.
*(from redmine: created on 2008-03-26)*Hi,
For better performance and prevent long time locks on big tables, mysqldump should be called with "--single-transaction" instead of "--lock-tables" on innodb tables.
*(from redmine: created on 2008-03-26)*Guillaume SubironGuillaume Subironhttps://0xacab.org/liberate/backupninja/-/issues/11263mysql handler using defaults-extra-file lets /root/.my.cnf override credentials2018-09-18T23:11:10ZLeLutinmysql handler using defaults-extra-file lets /root/.my.cnf override credentialsIn a really weird situation today where root didn't have any special permissions on mysql (I believe it was intentional, although really weird) the mysql handler was not able to perform dumps of databases other than those owned by the ro...In a really weird situation today where root didn't have any special permissions on mysql (I believe it was intentional, although really weird) the mysql handler was not able to perform dumps of databases other than those owned by the root user.
This was happening because `--defaults-extra-file` is evaluated before `/root/.my.cnf` and so the file in root's homedir was overriding credentials in `/etc/mysql/debian.cnf`.
I'd argue for the handler to switch to using `--defaults-file` instead of `--defaults-extra-file` which doesn't suffer from the same overriding potential, although I don't know if there was a reason in the first place to use the argument with the -extra variant.Jerome CharaouiJerome Charaouihttps://0xacab.org/liberate/backupninja/-/issues/11290Database handlers produce su-related errors in logs2021-01-13T19:25:23ZJerome CharaouiDatabase handlers produce su-related errors in logsThis was initially reported in DEBBUG-879664 against the pgsql handler on the Debian BTS, but I'm also seeing the same problem with the mysql one.
Basically, when backing up multiple smallish databases, `su` is called many times in quic...This was initially reported in DEBBUG-879664 against the pgsql handler on the Debian BTS, but I'm also seeing the same problem with the mysql one.
Basically, when backing up multiple smallish databases, `su` is called many times in quick succession, leading to messages like this appearing in system logs:
```
2017-10-23T01:00:15.795982+02:00 fer systemd[1]: user@500.service: Start request repeated too quickly.
2017-10-23T01:00:15.796132+02:00 fer systemd[1]: Failed to start User Manager for UID 500.
2017-10-23T01:00:15.796271+02:00 fer systemd[1]: user@500.service: Unit entered failed state.
2017-10-23T01:00:15.796409+02:00 fer systemd[1]: user@500.service: Failed with result 'start-limit-hit'.
2017-10-23T01:00:15.796702+02:00 fer su[11455]: pam_systemd(su:session): Failed to create session: Start job for unit user@500.service failed with 'failed'
2017-10-23T01:00:21.594947+02:00 fer systemd[1]: user@500.service: Start request repeated too quickly.
2017-10-23T01:00:21.595070+02:00 fer systemd[1]: Failed to start User Manager for UID 500.
2017-10-23T01:00:21.595212+02:00 fer systemd[1]: user@500.service: Failed with result 'start-limit-hit'.
2017-10-23T01:00:21.596172+02:00 fer su[11486]: pam_systemd(su:session): Failed to create session: Start job for unit user@500.service failed with 'failed'
```https://0xacab.org/liberate/backupninja/-/issues/11291mysqlhotcopy fails when "databases = all" (deprecate method?)2021-01-13T19:23:16ZJerome Charaouimysqlhotcopy fails when "databases = all" (deprecate method?)The failure is caused by mysqlhotcopy trying and failing to hot-copy the `performance_schema` table:
Debug: su root -c "/usr/bin/mysqlhotcopy --quiet --allowold --regexp /.*/./.*/ /var/backups/mysql/hotcopy"
Warning: DBD::mysql:...The failure is caused by mysqlhotcopy trying and failing to hot-copy the `performance_schema` table:
Debug: su root -c "/usr/bin/mysqlhotcopy --quiet --allowold --regexp /.*/./.*/ /var/backups/mysql/hotcopy"
Warning: DBD::mysql::db do failed: command denied to user 'root'@'localhost' for table 'accounts' at /usr/bin/mysqlhotcopy line 523.
Warning: Failed to hotcopy all mysql databases
This has been reported in DEBBUG-735014 as well as [upstream](https://bugs.mysql.com/bug.php?id=66589) (six years ago). Upstream eventually responded that `mysqlhotcopy` is deprecated and not distributed anymore with MySQL 5.7.
I think with regards to this, the fact that `mysqlhotcopy` only supports then MyISAM or ARCHIVE engines, and the InnoDB engine being the default nowadays, I suggest we remove support for mysqlhotcopy from the handler and replace it with support for XtraBackup or [MariaDB Backup](https://mariadb.com/kb/en/library/mariadb-backup-overview/) (the latter being a more featureful fork of the former). Both are packaged in Debian.https://0xacab.org/liberate/backupninja/-/issues/11300Add a $dbport configuration item to mysql2020-08-13T15:41:03ZFrankAdd a $dbport configuration item to mysqlLike having a `dbhost` configuration parameter one should have a `dbport` parameter. On my site it is needed to communicate with Mysql instances inside of docker containers.Like having a `dbhost` configuration parameter one should have a `dbport` parameter. On my site it is needed to communicate with Mysql instances inside of docker containers.