Recent World Backup Day is a good excuse to talk about MySQL database backups.
Usually it is a matter of executing the mysqldump with a bunch of parameters. Wrapping that up with a simple script will set you up for ages.
While it is tempting to pass root credentials to mysqldump it is a very bad idea. Generally use a dedicated account with minimal set of permissions.
Furthermore the backup user can have access to all databases, so dumping them at once won’t be a hassle. Creating such user is a matter of simple SQL query.
GRANT LOCK TABLES, SELECT, SHOW VIEW, EVENT ON *.* TO 'backup_user'@'localhost' IDENTIFIED BY 'xjfEDgQZzq9SQ7znuh8b';
EVENT are not required, but having them will dump views and events in case they are present.
To further increase security pass a
--defaults-extra-file parameter to the mysqldump. It specifies the path to a file that may contain login and password for a given user. With proper permissions the parameter offers better security than passing the login and password directly.