Is it possible to backup database in mysql without using mysql user ?
Problem: I want to make a backup system using ansible, but I have a problem at the time of database backup due to it is required to declare user and password mysql user in order to perform these backups. So for reasons of security and efficiency, I want to backup without the declaration of user and password.
First things first
You "could" only do this reasonably, under one of two conditions.
- Your database had no username/password (very unlikely and never advised)
- Your database is offline (you can clone it with a copy of some kind)
But, please don't do these!
Why not?
- It's not secure
- It's not safe
- It's risking your database and your business unnecessarily
- Your database almost always, contains some sensitive information, and you should never keep it unprotected even if you choose not to store it encrypted.
- You should not be relying on taking a database offline in order to make a backup. Downtime is dead time, and every moment of downtime cost money and potentially customers.
How your database should be stored/utilized on a production machine
- It should be encrypted if possible
- It should always be protected by strong usernames and passwords, those will stop most common attacks against the database.
- Encryption will stop almost all attacks except for the most sophisticated.
- You can and should consider even automatically changing (regularly) the backup username and password, while your service is live.
Now you are likely thinking...how do you expect me to do this? You said I can't include the credentials in the script.
What am I to do then?
Instead you'll want to utilize a secure credential distribution method, it could be something simple or complex. But generally, a few rules of thumb for a credential distribution service.
- Credentials should never be stored on the disk of the client machine which requested them, clients should only keep the credentials as long as needed. Caching (in memory) maybe acceptable for temporary periods of time where credential requests would otherwise be very frequent.
- Credentials should if possible be encrypted in memory and not stored as plain text, each client can use a random encryption key. The thing to keep in mind here is to minimize the attack surface area, as eliminating is not a possibility.
- The service/distribution method should only be accessible by authorized machines
- The service (and clients) should be appropriately protected by a firewall, including protected against any possible DDOS or known network attack
- The service should operate with the expectation of an host or availability zone failure, it should be possible to detect these issues and failover as needed
- Use signed server certificates and ensure the clients always validate them, you can even choose to go the self-signed route as this should only ever be an internal service
- You can and should (if possible) also use signed client certificates to help restrict unauthorized machines from unauthorized access of the service and to improve security (This is a nice to have, some products don't offer this)
- The service should only vend specific credentials to known authorized machines
- The service should only vend those credentials over encrypted and secured channels such as HTTPS (even on an internal network)
There are a variety of existing methods to distribute credentials to your applications. I highly recommend using something which has already been tested and proven rather than developing your own whenever possible.
Note: You should also distribute your production private keys/certificates in a similar manner. Never include them in an install package, source repo, or just leave them lying on the machine when that machine is effectively down. A startup script can download the needed certificate, and when the service exits you can use the shutdown script to cleanly wipe the private key/certificate file. I stop short of saying wipe these files after the application starts up as some applications re-read or lock these files while the service is running.
Note2: None of this is intended to completely stop an attack or compromise, these recommendations are only to limit the attack surface area. Because, the more things you leave lying around, the easier it is for attackers to not only find them, but to also use them.
Here are a few examples of secure credential distribution methods