7 MySQL Command-line Tool Tips

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.