Home arrow MySQL arrow 7 MySQL Command-line Tool Tips

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.

  1. 7 MySQL Command-line Tool Tips
  2. More MySQL Tips
By: Codex-M
Rating: starstarstarstarstar / 4
March 01, 2011

print this article



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:


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.

>>> More MySQL Articles          >>> More By Codex-M

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort


- Oracle Unveils MySQL 5.6
- MySQL Vulnerabilities Threaten Databases
- MySQL Cloud Options Expand with Google Cloud...
- MySQL 5.6 Prepped to Handle Demanding Web Use
- ScaleBase Service Virtualizes MySQL Databases
- Oracle Unveils MySQL Conversion Tools
- Akiban Opens Database Software for MySQL Use...
- Oracle Fixes MySQL Bug
- MySQL Databases Vulnerable to Password Hack
- MySQL: Overview of the ALTER TABLE Statement
- MySQL: How to Use the GRANT Statement
- MySQL: Creating, Listing, and Removing Datab...
- MySQL: Create, Show, and Describe Database T...
- MySQL Data and Table Types
- McAfee Releases Audit Plugin for MySQL Users

Developer Shed Affiliates


Dev Shed Tutorial Topics: