10 Command-line Timesavers for MySQL Tasks

October 28, 2010

Although several great GUI-based MySQL clients exist, among them phpMyAdmin and SQLYog, I've always preferred to use the native mysql command-line client. It does take some time to get acquainted with using a command-line interface (CLI), particularly if you don't regularly work with an operating system offering a robust CLI environment. However, after some practice you'll be able to manage users, navigate your databases, and perform other tasks with incredible ease.

In this article I'll introduce you to 10 mysql client tips and tricks that I've accumulated over the years. Whether you adopt one or all I guarantee you'll save a considerable amount of time and effort when using this powerful MySQL interface.

Incidentally, the mysql client is available for all operating systems, Windows included. However, because Windows' native CLI is horrible, consider installing Console, a great alternative solution that offers a great set of features including convenient text selection and multiple tabs.

1. Automate the Login Process

A properly configured MySQL server will require you to authenticate by specifying at minimum a username and password, typically done by passing the username along as an argument to the mysql command, and then for security purposes entering the password blindly via a subsequent prompt:

%>mysql -u root -pEnter password:Welcome to the MySQL monitor. Commands end with ; or \g....

Go through this ritual a few thousand times over the course of a year, and you've just lost several hours of time due to merely logging in. Eliminate this tedious step by creating a file named .my.cnf and placing it in your home directory. If on Windows, name the file my.ini and place it in your MySQL installation directory. In this file, add the following information, replacing the placeholders with your login information:

Be sure to set this file's permissions appropriately so prying eyes can't access the sensitive data.

2. Automatically Switch to a Database

After logging into the client you'll need to switch to the desired database, typically done using the use command. For instance:

mysql>use wjgilmore_dev;

If you're working exclusively on a particular project and want to save yourself the hassle of this additional step, identify the database within the configuration file introduced in the previous step by adding the following line within a section named [client] (the same used in the previous section):

database = your_database_name

3. Send Commands from a Script

When designing a new database I prefer to design the schema and relationships using MySQL Workbench. MySQL Workbench is a particularly powerful application in that you can manage your schemas graphically and then either synchronize the changes with the MySQL server or export the SQL statements to a file, which can subsequently be imported into MySQL.

If you prefer to hand code your schemas, would like to simultaneously create a number of stored procedures, or would like to execute a lengthy join, you can pass the SQL into the mysql client by saving the SQL to a file and then passing the file into the client like this:

%>mysql < schema.sql

Of course, you'll also need to either specify your connection credentials, or have done so within the configuration file introduced in previous tips.

4. Display Results Vertically

Even relatively simple table schemas can consist of so many columns that it's impossible to review their contents in a practical way. For instance, consider the following table, which consists of just 11 columns:

5. Enable Tab Completion

Repeatedly typing, let alone remembering, table and column names can quickly become tedious. Save yourself the hassle and enable tab completion by either passing --auto-rehash to the mysql client or better yet enabling it within the my.ini file:

[mysql]auto-rehash

6. Change the Prompt

More than once I've attempted to view or modify a schema only to be told that the table didn't exist. Sheer panic ensued over the possibility I mistakenly deleted it, until I realized that I was logged into the wrong database server. By modifying the mysql client prompt to reflect the name of the database you're currently using, you can eliminate this confusion entirely. To change your prompt interactively, log in to the mysql client and execute the following command:

mysql>prompt [\d]>[dev_wjgilmore_com]>

Because you'll probably want the change to be permanent, add the following line to your configuration file:

prompt = [d]>

You're not limited to including just the database name. Among other information, you can include the current time and date, hostname, and username. Consult the MySQL manual for more information.

7. Prevent Catastrophes with Safe Updates

In the previous tip I mentioned the panic of suspecting a table has accidentally been deleted. More than one administrator has fallen victim to a similarly serious gaffe, not because of accidentally executing the DROP TABLE command but rather because of the far more insidious omission of a WHERE clause when executing the UPDATE command. For instance, suppose you wanted to modify a user's username by executing the following command:

Naturally, executing the latter command will result in every value in the users table's User column being set to wjgilmore. Avoid such catastrophic errors by adding the following line to your configuration file:

safe-updates

8. Use the Command Documentation

Many users are aware of the mysql client's built-in documentation, which presents a list of useful commands when help is executed:

mysql>help...List of all MySQL commands:Note that all text commands must be first on line and end with ';'? (\?) Synonym for `help'.clear (\c) Clear the current input statement.connect (\r) Reconnect to the server. Optional arguments are db and host.delimiter (\d) Set statement delimiter....

However, the documentation goes much deeper than this. For instance, if you can't remember what the DESCRIBE command is used for, pass the command name to help:

DESCRIBE provides information about the columns in a table. It is ashortcut for SHOW COLUMNS FROM. These statements also displayinformation for views. (See [HELP SHOW COLUMNS].)...

9. Use the Pager

Sometimes you want to peruse a table's rows, but there are too many to view in a single screen. Rather than grapple with using the LIMIT clause, enable the client pager and set it to your system's paging utility:

mysql>pager morePAGER set to 'more'

To disable the pager, execute the nopager command.

10. Dump Output to a File

You'll occasionally need to dump SQL result output to a text file. You could use the SELECT INTO OUTFILE command, or from within the mysql client you can enable the tee command and assign an output file, causing all subsequent output to be sent to that file:

mysql>tee sales_report.txt

Conclusion

Whether you pick and choose or apply all 10, the tips and tricks introduced in this article will undoubtedly save you a great deal of time, effort, and potentially pain! Do you know of any other MySQL tips and tricks? Tell us about them in the comments!