The rlwrap utility for DBA.

Posted in: MySQL, Technical Track

Recently I received a request from one of our clients asking the way to mysqldump data only from a view. The request sounds reasonable at the first glance, but after thinking it a little bit further, we will find out that it is mission impossible. We can use alternative ways to achieve the same purpose though.

According to the MySQL documentation (https://dev.mysql.com/doc/refman/5.7/en/mysqldump.html), “The mysqldump client utility performs logical backups, producing a set of SQL statements that can be executed to reproduce the original database object definitions and table data.” So mysqldump will be able to get the database object definitions as table, view, procedure, trigger, etc. and the data of tables.

As we know, views are stored queries that when invoked produce a result set. A view acts as a virtual table. Mysqldump will only dump out the definition of a view (virtual table/stored query) but not the results of it (virtual table/stored query). If we try to dump out a view with –no-create-info, then nothing will be dumped out!

Example 1: mysqldump out a view will dump out the view definition only:

[root@ewang-vm-01 ~]# mysqldump -uroot -p --extended-insert -S /mysql/data/mysqld.socket m_test m_view > m.dump.sql
Enter password:
[root@ewang-vm-01 ~]# cat m.dump.sql
-- MySQL dump 10.13 Distrib 5.6.26, for linux-glibc2.5 (x86_64)
-- Host: localhost Database: m_test
-- Server version 5.6.26-log
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
-- Temporary view structure for view `m_view`
DROP TABLE IF EXISTS `m_view`;
/*!50001 DROP VIEW IF EXISTS `m_view`*/;
SET @saved_cs_client = @@character_set_client;
SET character_set_client = utf8;
/*!50001 CREATE VIEW `m_view` AS SELECT
1 AS `id`,
1 AS `insert_time`*/;
SET character_set_client = @saved_cs_client;
-- Final view structure for view `m_view`
/*!50001 DROP VIEW IF EXISTS `m_view`*/;
/*!50001 SET @saved_cs_client = @@character_set_client */;
/*!50001 SET @saved_cs_results = @@character_set_results */;
/*!50001 SET @saved_col_connection = @@collation_connection */;
/*!50001 SET character_set_client = utf8 */;
/*!50001 SET character_set_results = utf8 */;
/*!50001 SET collation_connection = utf8_general_ci */;
/*!50001 CREATE ALGORITHM=UNDEFINED */
bin directory in standard path.
It works if you have several similar, binary compatible systems and don't want to spend time compiling the same binaries on each one.


[root@sandbox]$cd rlwrap-0.42
[root@sandbox]$ls -l src/rlwrap
-rwxr-xr-x. 1 root root 225023 Aug 16 12:49 src/rlwrap
[root@sandbox]$cp src/rlwrap /usr/local/bin/
[root@sandbox]$rlwrap --help
Usage: rlwrap [options] command ...

Options:
-a[password prompt] --always-readline[=password prompt] -A --ansi-colour-aware
.....

Of course you may consider to make your own rpm or use EPEL (Extra Packages for Enterprise Linux) yum repository and install it from there. Just keep in mind the version you get from EPEL may be slightly outdated.

[root@sandbox]$yum install https://dl.fedoraproject.org/pub/epel/epel-release-latest-6.noarch.rpm
[root@sandbox]$yum install rlwrap

Having the rlwrap installed you may find use for it.
Here some basic examples how you can use the utility:
Create an alias in your bashrc for sqlplus :

vi ~/.bashrc

and add

alias sqlp='rlwrap sqlplus'

The same you can do for rman :

alias rman='rlwrap rman'

For Oracle GoldenGate command line utility

alias ggsci='rlwrap ./ggsci'

In rlwrap you can use ! and TAB to call list of commands or use prefix and CTRL+R to search for certain command in command history. Also you can create your own dictionary and use it for auto completion.
Let's try to build some dictionary for auto-completion
I created a file "lsql.lst" with the following contents:

[oracle@sandbox ~]$ vi lsql.lst
~
select
from
where
and
update
insert
delete
tablespace
v$database

....
[oracle@sandbox ~]$alias sqlp="rlwrap -f lsql.lst sqlplus / as sysdba"
[oracle@sandbox ~]$ sqlp

SQL*Plus: Release 12.1.0.2.0 Production on Wed Aug 17 15:36:04 2016

Copyright (c) 1982, 2014, Oracle. All rights reserved.

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
.............................................

cdb> desc v$t

--- here we are pressing TAB and getting list of suggestions:

cdb> desc v$t
table tablespace
cdb> desc v$tablespace
Name Null? Type
----------------------------------------------------------------------------------------------------------------- -------- ----------------------------------------------------------------------------
TS# NUMBER

We can make it even better. Let's upload name for all dba_views
In our sqlplus session we run :

cdb> spool lsql.lst append
cdb> select name from V$FIXED_TABLE;
......
cdb>spool off

logoff and logon again to reload the file and try :

cdb> select * from V$PA -- TAB
PARALLEL_DEGREE_LIMIT_MTH PARAMETER PARAMETER2 PARAMETER_VALID_VALUES PATCHES
cdb> select * from V$B -- TAB
BACKUP BACKUP_CONTROLFILE_SUMMARY BACKUP_DATAFILE_SUMMARY BACKUP_SET BACKUP_SYNC_IO BSP BUFFERED_PUBLISHERS
BACKUP_ARCHIVELOG_DETAILS BACKUP_COPY_DETAILS BACKUP_DEVICE BACKUP_SET_DETAILS BGPROCESS BTS_STAT BUFFERED_QUEUES
BACKUP_ARCHIVELOG_SUMMARY BACKUP_COPY_SUMMARY BACKUP_NONLOGGED BACKUP_SET_SUMMARY BH BT_SCAN_CACHE BUFFERED_SUBSCRIBERS
BACKUP_ASYNC_IO BACKUP_CORRUPTION BACKUP_PIECE BACKUP_SPFILE BLOCKING_QUIESCE BT_SCAN_OBJ_TEMPS BUFFER_POOL
BACKUP_COMPRESSION_PROGRESS BACKUP_DATAFILE BACKUP_PIECE_DETAILS BACKUP_SPFILE_DETAILS BLOCK_CHANGE_TRACKING BUFFER BUFFER_POOL_STATISTICS
BACKUP_CONTROLFILE_DETAILS BACKUP_DATAFILE_DETAILS BACKUP_REDOLOG BACKUP_SPFILE_SUMMARY BMAPNONDURSUB BUFFER2
cdb> select * from V$B

You can see we have all "V$" views and it can be extremely handy when you don't really have any time to search for a view name and only vaguely remember that you have a view to look up for certain information.

The rlwrap may not be most sophisticated program but it can make your life much easier. There may be a more advanced tool for sqlplus like SQLcl that provides a lot more options. But—the beauty of rlwrap is in its "lightness" and ability to work not only with sqlplus, but with practically any command line tool.

email

Interested in working with Edwin? Schedule a tech call.

1 Comment. Leave new

Leave a Reply

Your email address will not be published. Required fields are marked *