1

I'm running a shared server with multiple users. Each user has their own database in MySQL and associated credentials. I've created a template systemd timer & service (ie. sitetasks@.timer & sitetasks@.service) to run mysqldump daily to backup each users database. User-specific information is read by mysqldump from the .my.cnf file in each users' home directory.

I can then run the systemd timer/service for a specific user (ie. sitetasks@user1.service) to have that user's database backed up.

However, I can't find a way to specify the database name in .my.cnf. I've looked at the --databases option but this is just a flag, not a way to specify a database or set of databases.

I can think of several ways to solve this differently:

  1. create user-specific systemd timer/service -- but this doesn't scale well
  2. drop a .db-name file into the users home directory and read it when calling mysqldump (something like mysqldump $(cat .db-name) > backup.sql) - but now I need an additional file
  3. similar to #2 but parsing each user's wp-config.php (the db's are for Wordpress)
  4. ensure the db-name is always the same as the user name

None of these are as scalable & portable as being able to use MySQL's existing .my.cnf options file though.

Is there a way to specify a default database name in .my.cnf?

1

Every user has access to the database to which he was given access rights, so if you gave mysqldump instruction to dump all databases: --all-databases, only this user's database would be backed up and of course all general dbs he has access to. Usually it's information_schema only. It doesn't make dump size much bigger, in fact - no information from that schema is dumped - because it's dynamic info there.
So by ensuring privileges for every user - you have simple solution to back up those users databases by just dumping "all databases".

  • Interesting. I had assumed mysqldump would error without rights to all DBs. It looks like the dump using --all-databases is missing CREATE TABLE and USE directives which are useful on restore, but I imagine I can force it to include these with some additional switches? – chr0mag Dec 1 '17 at 16:52
  • 1
    Correction: --all-databases includes CREATE TABLE & USE directives. Pass --no-create-db to omit CREATE TABLE. – chr0mag Dec 2 '17 at 10:33

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.