mysql backup password without root home

Question

What's the best way to provide a MySql root password (~/.my.cnf) to cron job without root to have the home folder? the server I'm using is centos

Answer 1

What's the best way to provide a MySql ... password

Using a separate configuration file, as described in the Manual.

What's the best way to provide a MySql root password

You should not use any high-level credentials for Application connectivity. Not root, not mysql.
Always created a dedicated account for your Application, grant it appropriate permissions, and use that.

Why? You should always keep the Best Tools in the ToolBox for yourself - so that you can clean up the mess made by everybody else!

... without root to have the home folder?

The Linux root account will always have access to the mysql home folder.
By design, root must have access to everything.

Answer 2

Use mysql_config_editor

mysql_config_editor set -G project_name -u database_name -p -h localhost

Notes

• The -p prompts for the password
• -P 3306 for port (default 3306)
• -h could point to a remote server
• Other options are possible

Docker example:

mysql_config_editor set -G o8016 -P 8016 -u root -p -h `hostname`

Usage:

mysql  --login-path=o8016

mysql_config_editor hides the pwd in a 'dot file' in your home directory is such a way that access is limited to you and, of course, "root".