December 21, 2014
Hot Topics:

10 Command-line Timesavers for MySQL Tasks

  • October 28, 2010
  • By Jason Gilmore
  • Send Email »
  • More Articles »

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 -p
Enter 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:

[client]
host = your_mysql_server
user = your_username
password = your_password

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:

mysql> select * from accounts where username = 'wjgilmore';
+----+-----------+------------------+------------------------------
----+----------+-----------+------------+-----------+--------------
--------------------+---------------------+---------------------+
| id | username | email | password
| zip_code | latitude | longitude | confirmed | recovery
| created_on | last_login |
+----+-----------+------------------+-------------------------------
---+----------+-----------+------------+-----------+-----------------
-----------------+---------------------+---------------------+
| 7 | wjgilmore | wj@wjgilmore.com | 2b877b4b825b48a9a0950dd5bd1f264d
| 43201 | 39.984577 | -83.018692 | 1 |
8bnnwtqlt2289q2yp81tuge82fty501h | 2010-09-16 14:48:41 | 2010-10-27 15:49:44 |
+----+-----------+------------------+----------------------------------
+----------+-----------+------------+-----------+----------------------
------------+---------------------+---------------------+

Convert this ugly display into a vertically-oriented format using the G command:

mysql> select * from accounts where username = 'wjgilmore'\G
*************************** 1. row ***************************
id: 7
username: wjgilmore
email: wj@wjgilmore.com
password: 2b877b4b825b48a9a0950dd5bd1f264d
zip_code: 43201
latitude: 39.984577
longitude: -83.018692
confirmed: 1
recovery: 8bnnwtqlt2289q2yp81tuge82fty501h
created_on: 2010-09-16 14:48:41
last_login: 2010-10-27 15:49:44

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

 


Tags: MySQL, command line

Originally published on http://www.developer.com.

Page 1 of 2



Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 


Enterprise Development Update

Don't miss an article. Subscribe to our newsletter below.

Sitemap | Contact Us

Rocket Fuel