Store MySQL / MariaDB backup credentials safely

Backup scripts shouldn't contain a database password. Store the login once for the account that runs the backups (usually root), and let mysql and mysqldump read it automatically.

MySQL: mysql_config_editor

sudo mysql_config_editor set --login-path=client --host=localhost --user=backup --password
sudo mysql_config_editor print --all          # passwords are shown masked

This writes an obfuscated ~/.mylogin.cnf with mode 600. mysql and mysqldump use the client login path by default.

MariaDB: ~/.my.cnf

MariaDB doesn't ship mysql_config_editor, so use an option file instead:

sudo install -m 600 /dev/null /root/.my.cnf
sudo tee /root/.my.cnf >/dev/null <<'CNF'
[client]
user=backup
password="your password here"
CNF

Quote the password if it contains # or ;.

Create a least-privilege backup account

Send the SQL on stdin rather than with mysql -e "...", so the password doesn't show up in ps:

sudo mysql <<'SQL'
CREATE USER 'backup'@'localhost' IDENTIFIED BY '...';
GRANT SELECT, SHOW VIEW, TRIGGER, LOCK TABLES, EVENT, PROCESS, RELOAD ON *.* TO 'backup'@'localhost';
SQL

Test

sudo mysql -e 'SELECT CURRENT_USER();'