MySQLdump backup script no longer works, getting "mysqldump: unknown variable 'local-infile=0'"

Question

I've recently upgrade a server to Debian 9 and MySQL to the latest version. I have a simple backup script that I run before performing any work on a production site but this time, when running my script, I encounter the following:

mysqldump: unknown variable 'local-infile=0'

Here is my script. What's going on?

#!/bin/bash
# [skipping commentary]

SITE=prod

# Set the directory that the Drupal root is IN, no trailing slashes
DROOT=[website_root]

# Set the directory for storing backups, no trailing slashes
BUD=/$DROOT/notes/backups

# Don't edit; End of defining variables
echo Doing a full back up...
echo Prepare to enter MySQL password...
# tar -czf $BUD/$SITE-files-$(date +'%Y%m%d%H%M%S').tgz  $DROOT/docroot
mysqldump -u mysql_user -p drupal > $BUD/$SITE-drupal-$(date +'%Y%m%d%H%M%S').sql
mysqldump -u mysql_user -p civicrm > $BUD/$SITE-civicrm-$(date +'%Y%m%d%H%M%S').sql
ls -lh $BUD
pwd
echo Finished with backups...

MySQL version 10.1.37-MariaDB-0+deb9u1 Debian 9.6

Edit: When I ssh and run mysqldump with correct permissions I get the same issue. Weirdest thing, cron that runs similar process is backing up my databases as ordered.

Answer 1

The best way to solve this is simply to rename the variable to: loose-local-infile=1

This will allow mysqldump to merely throw a warning, rather than a fatal error.

The suggestion to comment out the variable is not an option if you want LOAD DATA INFILE functionality out of the box, and MySQL 8+ for security reasons requires you to set this variable for both server (mysqld) and client. It is the [client] variable grouping in your config that chokes mysqldump if you don't add the "loose-" prefix to local-infile.