More MySQL Tips - MySQL

Fourth Tip: Press the arrow up key to recall previously-typed commands or queries

When you are typing a lot of queries into the command line, there's a good chance that you will be repeating at least some of the same queries in the process. So instead of typing the query all over again, you can save time by pressing the arrow up key on your keyboard to show the commands you previously typed.

Fifth Tip: Automate a MySQL command-line login using Putty Connection Manager

You might find it time consuming to go through the following steps:

• Open up Putty, and then click "Open" to connect to your SSH server.
  
  
• Type the MySQL connect command in the command-line: -bash-3.2$ mysql -uUSERNAME -pPASSWORD -Hhostname
  
  
• Type the name of the database you are going to use:
mysql> use DATABASEXXX;

How about just clicking "Open" in PuTTY and then automatically connecting directly to the desired MySQL database? To do this, follow the steps below:

1. Download the Putty Connection Manager: http://puttycm.free.fr/download/puttycm.exe.

2. Download pcmcrypt.dll here: http://puttycm.free.fr/download/pcmcrypt.dll.

3. Create a folder named PuttyCM on your desktop.

4. Copy puttycm.exe and pcmcrypt.dll from your Downloads folder (after downloading it) to the PuttyCM folder on your desktop. Make sure puttycm.exe and pcmcrypt.dll are in the same folder.

5. Double click puttycm.exe inside PuttyCM folder.

6. It will ask for the location of putty.exe (the exe file you used in Tip#1 of this tutorial). Just find it.

7. It will then load the sessions saved, if you have saved sessions in Putty.exe before.

8. Go to File-> New-> Database.

9. Assign a user-friendly name, check "Enable database encryption," and type a pass phrase you should remember. Then click "Create database."

10. Go to File->Save database and save it to a secure location.

11. Under Connection Manager, right click on SSH folder, then go to New->Connection.

12. Enter the name of your connection, SSH hostname and select SSH for protocol as well as the port number used by SSH server. Check "Enable login macro mode."

13. Click the Login Macro tab under Configuration. Suppose you would like to automatically connect to the MySQL database named DATABASEXXX in the command-line, and then run a select query to retrieve all of the information in TABLEXXXX.

Commands 1 through 3 are executed in sequence after you've successfully logged in to your SSH server command line. In "Auto-connect credentials," use your SSH username under Login and SSH password in "Password." Make sure "Enable post-login commands" is checked. Click Apply, and then save the database.

14. Click "Macro options." Below are the suggested macro options (just set the timeout to 6000 ms for all options).

Finally, in the connection manager, expand SSH, right click on the item (e.g phpwordpress, see screen shot below), and then click "Connect."

It should be able to connect to the desired MySQL database automatically, and then execute the query using the command line method, without forcing you to type those lengthy commands and queries manually.

Sixth Tip: Show results in vertical format

Sometimes SELECT query results may get distorted in the command line because of the many columns to be shown. Suppose you have a table named CUSTOMERRECORDS with lots of columns. You can show the results vertically using the command below:

mysql> select * from customerrecordsG

You need to append G at the end of the MySQL query. See screen shot:

This will make the results readable, even with the many columns there are in the table.

Seventh Tip: Running SSH Bash Commands in MySQL command line

One of the useful tips is that you can actually run shell/SSH server commands in the MySQL command line. This is done by appending ! before any SSH bash commands. Suppose you are now logged in to MySQL:

mysql>

You want to get back to the SSH bash prompt without exiting your existing MySQL session. Just type ! bash

mysql> ! bash

And the MySQL prompt will change to an SSH bash prompt:

-bash-3.2$

To get back to your MySQL session, simply type exit.

-bash-3.2$ exit

And the prompt will change back again to a MySQL prompt.