Graphically oriented MySQL administration tools abound, but for true speed and no-frills management perhaps nothing is more effective than the command-line driven MySQL client. However, the inordinate amount of typing due to executing common queries, determining what database you're currently logged into, and monitoring database server performance quickly can become tedious. In this article, I'll unveil a few of MySQL's command-line hidden features that can eliminate those extra keystrokes and along the way make you a much more efficient user of this powerful client.
Changing the Client Prompt
If I had a dollar for every time I ran show tables just as a reminder of which database was currently selected, I'd be writing this article from an exotic beach locale. The fact is, when regularly jumping among an IDE, shell prompt, and MySQL client prompt it can be very easy to forget your spot. Eliminate this problem in its entirety by changing MySQL's prompt by using the prompt command:
mysql>prompt mysql (\d)>
Once executed, the currently selected database will appear in the prompt, like so:
mysql (corporate)>
It's also easy to lose track of which database server you're logged in to, not to mention which account you're using. To fix this, modify your prompt using the \u and \h options:
mysql>prompt mysql (\u@\h)>
This produces a prompt similar to the following:
mysql (root@www.wjgilmore.com)>
To render the change permanent, add the command to your .my.cnf file like so:
[mysql] prompt=mysql \\d>
The database, user, and host options are just a few of many available to you. See the MySQL documentation for more information.
Use Shell Aliases to Execute Common MySQL Commands
Suppose your new web application is really starting to take off, with new users registering by the hour. Like a stock IPO, chances are you and your colleagues are regularly peeking the user count. But, constantly logging in to and out of MySQL just to execute a simple query is aggravating. However, if you're otherwise logged in to the server, you can create a shell alias that will log in to the MySQL server, select the appropriate database, and execute a specific command:
%>alias usrcount="mysql -u appadmin -p myapplication
-e \"select count(id) from users\""
In this example, you're logging in to the local MySQL server using the appadmin user account, selecting the myapplication database, and passing in the SELECT query for execution using the little-known -e option. Now, any time you want to determine how many users are in the system, just execute the following command and provide the appadmin user password:
%>usrcount
Enter password:
+-------------+
| count(id) |
+-------------+
| 348 |
+-------------+
To make the alias permanent, add it to your .bashrc or similar shell configuration file.
Rendering Query Results in Vertical Format
Easily reviewing SELECT query results in a shell window can be particularly difficult when a table consists of numerous columns. Remedying this inconvenience is easily done by appending the \G switch to the query:
mysql>SELECT * from users WHERE id=1\G
Executing this query with the \G switch attached formats the output like so:
mysql test>select * from users where id=1\G
*************************** 1. row *********
id: 1
name: Jason
email: jason@example.com
telephone: (614)999-9999
city: Columbus
1 row in set (0.00 sec)
Create a CSV File From Query Results
If you're using a database professionally, chances are a colleague has asked you to dump data from the database into an Excel file to perform further analysis. Did you know you can modify a SELECT query to do all of the CSV formatting for you, and place the data in a textfile? All you need to do is identify how the fields and lines should be terminated. For example, to dump a table named users to a CSV file named users.csv, execute this command:
mysql>SELECT * FROM users INTO OUTFILE '/home/jason/users.csv'
FIELDS TERMINATED BY '\t\' ->LINES TERMINATED BY '\n';
IE 7
Firefox 2.0
