7 MySQL Command-line Tool Tips

This tutorial will introduce you to seven MySQL command-line tool tips that help increase your productivity and skills using the command-line method. These are written with beginners in mind, but can still be applied by any MySQL user.

First Tip:  Use Secure Shell when connecting to MySQL server

Security is an important priority when communicating with a MySQL database. This is one problem with using a GUI method, such as phpMyAdmin. The problem stems from the fact that not all hosts use the SSL/https protocol for phpMyAdmin MySQL sessions. MySQL transactions using phpMyAdmin are encrypted when the https protocol is used (you will see the padlock icon in your browser address bar).

To encrypt your MySQL sessions (which prevents third parties from eavesdropping on your communications), you can use SSH (secure shell) technology. Most paid Linux/Unix hosting accounts include SSH as a free feature, so make the most of it.

Once SSH is active in your hosting account, you can use an SSH client such as PuTTY (http://www.chiark.greenend.org.uk/~sgtatham/putty/download.html) to communicate first with your SSH server. Once it is connected, you can then connect to your MySQL server.

Here are some basic steps to help you start securing your MySQL command-line session: http://www.devshed.com/c/a/MySQL/MySQL-in-SSH-Basic-Guide/1/

Second Tip: Memorize the basic MySQL queries

1. Connect to MySQL server (after successfully connected to SSH server, illustrated in the first tip):

-bash-3.2$ mysql -uUSERNAME -pPASSWORD -Hhostname

2. Show the list of MySQL database in that MySQL server:

mysql> show databases;

3. Use the database named DATABASEXXX (you cannot do table queries unless you use a database first)

mysql> use DATABASEXXX;

4. Display the full list of database tables under DATABASEXXX:

mysql> show tables;

5. Display or describe TABLEXXX’S database field name definitions:

mysql> describe TABLEXXX;


6. Display TABLEXXX’s entire contents:

mysql> select * from TABLEXXX;

7. Display all of TABLEXXX’s content where Fieldname1 is 5.

mysql> select * from TABLEXXX where Fieldname1= ‘5 ‘;

8. Display the value of fieldnamexxx where fieldname1 is 5 in TABLEXXX:

mysql> select fieldnamexxx from TABLEXXX where Fieldname1= ‘5 ‘;

9. Create a table named TABLEXXX with the following fields and definition:

id = integer, NOT NULL, primary key index and auto-incrementing
fieldname1 = VARCHAR(60)
fieldname2 = VARCHAR(20)
dateandtime = TIMESTAMP(8)

mysql> create table TABLEXXX (
-> id INT Not Null Auto_Increment,
-> Primary key(id),
-> fieldname1 VARCHAR(60),
-> fieldname2 VARCHAR(20),
-> dateandtime TIMESTAMP(8));

10. Insert the following records into the newly-created TABLEXXX defined previously:

Fieldname1 = hi command line
Fieldname2 = hello MySQL

mysql> INSERT INTO TABLEXXX (Fieldname1,Fieldname2) VALUES (‘hi command line’,’hello MySQL’);

Note: You do not need to insert the id and timestamp, as these are automatically generated by MySQL during your insertion.

11. Update the value of fieldname1 from “hi command line” to “I love command line” where fieldname2 is “hello Mysql” in TABLEXXX

mysql> UPDATE TABLEXXX SET fieldname1=’I love command line’ WHERE fieldname2=’hello MySQL’;

12. Delete a MySQL row where fieldname2 is “hello MySQL”.

mysql> DELETE from TABLEXXX WHERE fieldname2=’hello MySQL’

13. Delete a table named TABLEXXX along with its contents:

mysql> DROP TABLE TABLEXXX ;

For other useful commands, such as importing/exporting databases and uploading/download .SQL files, you can read this tutorial: http://www.devshed.com/c/a/MySQL/MySQL-Command-Line-and-SSH-Importing-and-Exporting-Database/

Third Tip: Right click in the PuTTY command-line tool green prompt to paste information

Beginners find it tricky to use the command-line method at first, because they think everything will have to be typed into the command line. Actually, this is not true. Suppose that, after being connected to SSH server using PuTTY, you are going to connect to your MySQL server. So you type:

-bash-3.2$ mysql -uMySQL_USERNAME -p

Before you type the password above (ensure that green prompt is positioned after p parameter):

Select and copy the password from the password manager you are using, like KeePass. To paste the password, simply RIGHT CLICK directly over the green prompt.

{mospagebreak title=More MySQL Tips}

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.

[gp-comments width="770" linklove="off" ]

chat sex hikayeleri Ensest hikaye