The goal here is to be able to type a one word shell command at the shell’s command prompt and have that drop you into a MySQL command prompt on the host machine and database of your choosing.
shell>mysqldevwm mysql>\s -------------- mysql Ver 14.14 Distrib 5.6.25, for osx10.10 (x86_64) using EditLine wrapper Connection id: 5162 Current database: web_manager Current user: xxxxx@xxxxxxxxxxxxx SSL: Not in use Current pager: stdout Using outfile: '' Using delimiter: ; Server version: 5.5.43-0ubuntu0.12.04.1 (Ubuntu) Protocol version: 10 Connection: dev via TCP/IP Server characterset: latin1 Db characterset: latin1 Client characterset: utf8 Conn. characterset: utf8 TCP port: 3306 Uptime: 33 days 10 hours 12 min 26 sec Threads: 1 Questions: 1210383 Slow queries: 85 Opens: 1358 Flush tables: 1 Open tables: 114 Queries per second avg: 0.419 --------------
Sure, you could do this with:
shell>mysql -uroot -p -hdev web_manager
But, you’d be prompted for the password every time and that gets old. Of course, you could just include the password on the command line but that would be insecure. Here’s how you fix that.
If you already have the MySQL command line utilities installed on your box, you’re good to go. Just type “mysql –version” to verify you have them. If not, you can download them here: http://dev.mysql.com/downloads/utilities/
The mysql_config__editor command will create an encrypted file with our password and our databases hidden safely inside. Here’s the syntax.
mysql_config_editor set --login-path=pickanyname --host=actualmachine --user=yourusername --password
Here’s an example:
shell>mysql_config_editor set --login-path=mywebdb --host=www.mywebsite.com --user=root --password shell>Enter password:
At this point the system is prompting you to type in the password. Enter the password for the root user at www.mywebsite.com and press enter. This command creates the encrypted file and stores it in your home directory. The file name is .mylogin.cnf. You can view it’s contents by typing:
mysql_config_editor print all
To add another login for another database, just repeat the above steps.
Automating things with bash
At this point you could type:
shell>mysql --login-path=mywebdb webdb
Where webdb is the name of the actual database. But, since that’s a lot to type every time you want to login, we’re going to create a bash script to make it much simpler. Fire up an editor and put the above line in a file, save it to your home directory as mywebdb, then from a shell prompt type:
shell>chmod +x mywebdb
This will make the script directly executable. Now just type:
This should log you into your mysql database. If you move the script to a directory that’s in your path for executables, you can drop the ./ before the script name.