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:
[[email protected] ~]# mysqldump -uroot -p --extended-insert -S /mysql/data/mysqld.socket m_test m_view &gt; m.dump.sql Enter password: [[email protected] ~]# 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 @[email protected]@CHARACTER_SET_CLIENT */; /*!40101 SET @[email protected]@CHARACTER_SET_RESULTS */; /*!40101 SET @[email protected]@COLLATION_CONNECTION */; /*!40101 SET NAMES utf8 */; /*!40103 SET @[email protected]@TIME_ZONE */; /*!40103 SET TIME_ZONE='+00:00' */; /*!40014 SET @[email protected]@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; /*!40014 SET @[email protected]@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; /*!40101 SET @[email protected]@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; /*!40111 SET @[email protected]@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. [[email protected]]$cd rlwrap-0.42 [[email protected]]$ls -l src/rlwrap -rwxr-xr-x. 1 root root 225023 Aug 16 12:49 src/rlwrap [[email protected]]$cp src/rlwrap /usr/local/bin/ [[email protected]]$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.
[[email protected]]$yum install https://dl.fedoraproject.org/pub/epel/epel-release-latest-6.noarch.rpm [[email protected]]$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 :
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:
[[email protected] ~]$ vi lsql.lst ~ select from where and update insert delete tablespace v$database .... [[email protected] ~]$alias sqlp="rlwrap -f lsql.lst sqlplus / as sysdba" [[email protected] ~]$ sqlp SQL*Plus: Release 220.127.116.11.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 18.104.22.168.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.