How To Use MySQL Commands without Password Prompt

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?

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.