How to Login to Your Retain MySQL Database and Run Queries

  • 7020622
  • 16-Feb-2016
  • 07-Aug-2017

Environment

Retain
MySQL community edition
Linux

Situation

I'm reading a KB article that provides a database query that I need to run to resolve an issue but I don't know how to use MySQL.  How do I get started?

Resolution

DISCLAIMER:
This knowledge base (KB) article is provided for informational purposes only and as a courtesy service to you, our customer. GWAVA Technical Support does not have any database administration (DBA) expertise, nor does it provide DBA services or support. GWAVA is not responsible for the results of implementing any of the concepts contained in this KB article. Implementation of any of the concepts suggested in this KB article shall be done entirely at your own and sole risk, and GWAVA does not provide any kind of warranties whatsoever resulting from your decision of implementing any of the KB article’s concepts. It is up to you to do any research and to ensure yourself that any implementation and setup of any of the KB article’s concepts in your database system is correctly and properly executed. It is imperative that you have backups of your database system and storage directory before making any implementation. If you don’t have any DBA expertise, you should consult with a DBA expert before any implementation of the KB article’s concepts.  Under no circumstances, shall GWAVA, or any of its employees, be liable, in contract, tort, delict or otherwise, whether negligence is provable or not, for any direct, indirect, incidental, special, punitive, consequential or other damages, loss, cost or liability whatsoever that would result from or are related to the implementation of any of the concepts suggested in the KB article.

To the extent permitted by applicable law, GWAVA shall not be liable to you for any special, consequential, direct, indirect or similar damages, including any loss of data, arising out from migrating any type of messages, attachments, database, metadata in your Retain system to another server and/or location.


Resolution:

These instructions will be for Linux.  The same commands can be issued to MySQL for Windows using the MySQL Command client that ships with it.  MySQL also provides a GUI interface called MySQL Workbench, but this article will not attempt to explain how to use that tool.

From a terminal window (whether on the server itself or through a PuttY session:

  1. Login to MySQL: mysql -u [retain database username] -p

The retain database name, username, and login credentials are stored in the /opt/beginfinite/retain/RetainServer/WEB-INF/cfg/ASConfig.cfg (search for <dbuser> tag to get to the general area).  Make a note of all three of those.

  1. Change to the retain database:  use [database name] and press ENTER.  Example:  use retain [ENTER]
  2. Type your query, ending it with a semicolon ";" or \G

One of these is required to delimit the end of the query.  If you use the standard semicolon ";", it will display the results in table format.  If there are a lot of columns, the results will look messy as you'll see in the sample below.  If you use \G, it not only signifies the end of the query but it also instructs MySQL to display the results with each column on its own row.

If you are simply running a query that is updating information (update [tablename] set [field] = [value];) or getting a count (select count(*) from [tablename];), then use the semicolon.  If you are wanting to display content from a particular table and it has many columns, you may want to consider using the \G.

Here are two samples of the exact same query.  The first was was with the semicolon delimiter and the second one utilized the \G.  

QUERY:  select * from t_message;

QUERY:  select * from t_message\G

Additional Information

This article was originally published in the GWAVA knowledgebase as article ID 2739.