Run MySQL client as user without user's password if have root access

Question

If importing a SQL dump file using the mysql command line client, if it wasn't created by the superuser and guaranteed to not have been modified, for security it's best practice to not execute it as the root user.

I know this can be done using the -u and -p arguments, but is there a way to not have to know the password for the user if you already have the ability to log in as root (e.g. the root password is in /root/.my.cnf)? Like is there some way to log in as root then switch to the particular user, or some other easy way to accomplish this?

• jkavalik: You can put the password for a given user to their ~/.my.cnf and use only -u <user>.
• user101203: @jkavalik That sounds like a decent solution if i'm going to be doing it often for the same user, but I'm hoping to avoid having to look up their password altogether.
• tesla747: you can as well encrypt the password and use that as a string in your command line.
• user101203: @tesla747 it would be nice to not have to do anything with the user's password since I already have root privileges. For example, in bash on Linux if you're root, you can change to any user using su without having to know their password.

Answer 1

It appears that this is not directly possible. The common workaround appears to be specifying the password (and other parameters) in your $HOME/.my.cnf as shown in this SO answer.

If the job is run from a systemd timer/service where $HOME is not in the environment then you can include the file explicitly with mysqldump --defaults-extra-file=/path/to/.my.cnf. Note that this needs to be the very first argument to mysqldump.