Linux Absolute Beginners #1 : How to safely run mysql queries on command line

in #linux5 years ago (edited)

First a word of warning: This works for me, it does not have to work for you.

Why use mysql interactively when you are interested only in one or two queries. It's much quicker to give you SQL query on command line. The problem is if you don't want to specify password interactively but would like to pass it on command line as well. It's possible but not safe so it's better to use a safer way to do it. Before you can do it you have to use only once mysql_config_editor to set a variable login_pathlike this:

mysql_config_editor set --login-path=<login_path_name> --host=<hostname> --user=<username> --password

In most cases your <hostname> will be localhost and <username> will be root so it can look e.g. like this

mysql_config_editor set --login-path=myhostalias --host=localhost --user=root --password

Then, when prompted you have to enter password for the mysql user you specified with --user option. After you registered your hostalias you can use mysql client without specifying password on command line. You don't have to use -u and -p to specify user and password, you just specify --login-path option with the variable set before e.g like this:

mysql --login-path=myhostalias mydatabase -e "select body from posts where id=20;"
Sort:  

Now that's great. Thanks for sharing.