Q. How do I use MySQL command without prompting password? How do I use mysqldump command without using the password in the script? How do I use MySQL commands without passing the password in crontab?
Advertisement
In this tutorial, you will find a way to use MySQL commands without passing passwords as a command line parameter or password prompt. This is helpful for using MySQL commands in a shell script, or configure MySQL/mysqldump cronjobs without passing password.
- 1. Create a .my.cnf file in users home directory from which command or script to run.
vi ~/.my.cnf and add the following content to this file. Change username and password as per your system configuation.
[mysql] user = myuser password = secret [mysqldump] user = myuser password = secret
- 2. Now secure the newly created configuration file.
chmod 600 ~/.my.cnf - 3. All done. Lets test the setup. I have used the following commands to verify the above configurations. First commands will allow you login to mysql server without prompting password and second command will backup all your databases without password.
mysql -h localhost -u myuser mysqldump -h localhost -u myuser --all-databases > alldb.sql
You can use commands in any shell script or schedule crontab for backup. You don’t need to specify MySQL user password in script or command.
5 Comments
This works fine if I type the mysql command directly into the command line. But it fails when the same mysql command is used inside a shell script. Is there something I need to do to get the script to recognize the my.cnf file?
Hi Thesun,
Did you find any solution for this issue.
Hi Thesun,
I have tested this with a shell script and its working fine for me. Can you please provide more information about the issue.
The above works fine but fails to address the password prompt isue when mysqlimport is executed from within a shell script.
Try adding the following content to .my.cnf
[mysqlimport]
user = myuser
password = secret