Home PC Games Linux Windows Database Network Programming Server Mobile  
           
  Home \ Database \ mysqldump issue a note     - Linux security-related basics (Linux)

- How to fix the Ubuntu Error script returned error exit status 1 (Linux)

- CentOS 6.5 / Linux restart network card being given Determining if ip address x.x.x.x is already in use (Linux)

- Zabbix monitoring Oracle Database use Orabbix plug (Enhanced Edition) (Database)

- Create RAID 1 (mirroring) with two disks (Linux)

- CentOS install video converter FFmpeg and cutting tools segmenter (Linux)

- Differences Shell scripting languages and compiled languages (Programming)

- Linux regex awk Comments (Linux)

- Linux Command Study Notes (Linux)

- Linux static library generated Guide (Programming)

- Some security configuration of Linux systems (Linux)

- A deep understanding of Java enum (Programming)

- Linux redirection and piping (Linux)

- Ora-00439: feature not enabled: managed standby (Database)

- CentOS 6.5 can not connect to the network under VMware (Linux)

- Django Signals from practice to source code analysis (Programming)

- RedHat6.4 installation tutorial --- Minimal Edition (Linux)

- Deploy Apache Spark cluster environment in Ubuntu (Server)

- Recycle Bin function realization in Linux (Linux)

- Logging information through the web GUI (LogAnalyzer) (Server)

 
         
  mysqldump issue a note
     
  Add Date : 2018-11-21      
         
         
         
  Recently some students use mysqldump to export data, export data found offline environment stuck and cause mysql busy! Meanwhile dump being given as follows:

mysqldump: Could not execute 'SELECT DISTINCT TABLESPACE_NAME, FILE_NAME, LOGFILE_GROUP_NAME,
EXTENT_SIZE, INITIAL_SIZE, ENGINE FROM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE = 'DATAFILE'
 AND TABLESPACE_NAME IN (SELECT DISTINCT TABLESPACE_NAME FROM INFORMATION_SCHEMA.PARTITIONS
  WHERE TABLE_SCHEMA IN ( 'dbname')) ORDER BY TABLESPACE_NAME, LOGFILE_GROUP_NAME

 So this statement was explain, explain information is found as follows:

+ ---- + ------ + ------------ + ------------- + ---------- -------------- + ----- + --------- + ------ + ------ + ----- -------------------------------------------------- -------------------------------------------------- ------------- +
| Id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+ ---- + ------ + ------------ + ------------- + ---------- -------------- + ----- + --------- + ------ + ------ + ----- -------------------------------------------------- -------------------------------------------------- ------------- +
| 1 | SIMPLE | FILES | ALL | NULL | NULL | NULL | NULL | NULL | Using where; Using temporary; Using filesort |
| 1 | SIMPLE | PARTITIONS | ALL | NULL | TABLE_SCHEMA | NULL | NULL | NULL | Using where; Open_full_table; Scanned 1 database; Distinct; FirstMatch (FILES); Using join buffer (Block Nested Loop) |
+ ---- + ------ + ------------ + ------------- + ---------- -------------- + ----- + --------- + ------ + ------ + ----- -------------------------------------------------- -------------------------------------------------- ------------- +
2 rows in set (0.01 sec)
You can see the sql will open a library, then open all the tables. We know, information.files table is a memory engine, data tables are in fact does not exist, so information on these tables need to get through all of the physical file.

mysql> show create table INFORMATION_SCHEMA.FILES G
*************************** 1. row ******************** *******
       Table: FILES
Create Table: CREATE TEMPORARY TABLE `FILES` (
  `FILE_ID` bigint (4) NOT NULL DEFAULT '0',
  `FILE_NAME` varchar (64) DEFAULT NULL,
  `FILE_TYPE` varchar (20) NOT NULL DEFAULT '',
  `TABLESPACE_NAME` varchar (64) DEFAULT NULL,
  `TABLE_CATALOG` varchar (64) NOT NULL DEFAULT '',
  `TABLE_SCHEMA` varchar (64) DEFAULT NULL,
  `TABLE_NAME` varchar (64) DEFAULT NULL,
  `LOGFILE_GROUP_NAME` varchar (64) DEFAULT NULL,
  `LOGFILE_GROUP_NUMBER` bigint (4) DEFAULT NULL,
  `ENGINE` varchar (64) NOT NULL DEFAULT '',
  `FULLTEXT_KEYS` varchar (64) DEFAULT NULL,
  `DELETED_ROWS` bigint (4) DEFAULT NULL,
  `UPDATE_COUNT` bigint (4) DEFAULT NULL,
  `FREE_EXTENTS` bigint (4) DEFAULT NULL,
  `TOTAL_EXTENTS` bigint (4) DEFAULT NULL,
  `EXTENT_SIZE` bigint (4) NOT NULL DEFAULT '0',
  `INITIAL_SIZE` bigint (21) unsigned DEFAULT NULL,
  `MAXIMUM_SIZE` bigint (21) unsigned DEFAULT NULL,
  `AUTOEXTEND_SIZE` bigint (21) unsigned DEFAULT NULL,
  `CREATION_TIME` datetime DEFAULT NULL,
  `LAST_UPDATE_TIME` datetime DEFAULT NULL,
  `LAST_ACCESS_TIME` datetime DEFAULT NULL,
  `RECOVER_TIME` bigint (4) DEFAULT NULL,
  `TRANSACTION_COUNTER` bigint (4) DEFAULT NULL,
  `VERSION` bigint (21) unsigned DEFAULT NULL,
  `ROW_FORMAT` varchar (20) DEFAULT NULL,
  `TABLE_ROWS` bigint (21) unsigned DEFAULT NULL,
  `AVG_ROW_LENGTH` bigint (21) unsigned DEFAULT NULL,
  `DATA_LENGTH` bigint (21) unsigned DEFAULT NULL,
  `MAX_DATA_LENGTH` bigint (21) unsigned DEFAULT NULL,
  `INDEX_LENGTH` bigint (21) unsigned DEFAULT NULL,
  `DATA_FREE` bigint (21) unsigned DEFAULT NULL,
  `CREATE_TIME` datetime DEFAULT NULL,
  `UPDATE_TIME` datetime DEFAULT NULL,
  `CHECK_TIME` datetime DEFAULT NULL,
  `CHECKSUM` bigint (21) unsigned DEFAULT NULL,
  `STATUS` varchar (20) NOT NULL DEFAULT '',
  `EXTRA` varchar (255) DEFAULT NULL
) ENGINE = MEMORY DEFAULT CHARSET = utf8
Depth source, discovered the role of mysqldump is the sql dump tablespace, take a look at the source code of the part

{ "All-tablespaces", 'Y',
   "Dump all the tablespaces.",
   & Opt_alltspcs, & opt_alltspcs, 0, GET_BOOL, NO_ARG, 0, 0, 0, 0,
   0, 0},
   
    { "No-tablespaces", 'y',
   "Do not dump any tablespace information.",
   & Opt_notspcs, & opt_notspcs, 0, GET_BOOL, NO_ARG, 0, 0, 0, 0,
   0, 0},
   
   
 if (opt_alltspcs) // If you choose to dump all of the tablespace information
    dump_all_tablespaces ();
   
 if (opt_alldbs) // If you dump all of the db
  {
    if (! opt_alltspcs &&! opt_notspcs) // If no dump all tablesapce, is also not specified not dump all tablespce
      dump_all_tablespaces ();
    dump_all_databases ();
  }
 else
  {
    // Check all arguments meet length condition. Currently database and table
    // Names are limited to NAME_LEN bytes and stack-based buffers assumes
    // That escaped name will be not longer than NAME_LEN * 2 + 2 bytes long.
    int argument;
    for (argument = 0; argument     {
      size_t argument_length = strlen (argv [argument]);
      if (argument_length> NAME_LEN)
      {
        die (EX_CONSCHECK, "[ERROR] Argument '% s' is too long, it can not be"
          "Name for any table or database n.", Argv [argument]);
      }
    }
    if (argc> 1 &&! opt_databases)
    {
      / * Only one database and selected table (s) * /
      if (! opt_alltspcs &&! opt_notspcs)
        dump_tablespaces_for_tables (* argv, (argv + 1), (argc - 1));
      dump_selected_tables (* argv, (argv + 1), (argc - 1));
    }
    else
    {
      / * One or more databases, all tables * /
      if (! opt_alltspcs &&! opt_notspcs)
        dump_tablespaces_for_databases (argv);
      dump_databases (argv);
    }
  }
We have seen, if not dump all the tablespace specified nor specified tablespace does not dump all the information, all of the default dump tablespace information! ! !
We continue to look at the document, the description of the tablespace:

--all-tablespaces, -Y
Adds to a table dump all SQL statements needed to create any tablespaces used by an NDB table. This information is not otherwise included in the output from mysqldump. This option is currently relevant only to MySQL Cluster tables, which are not supported in MySQL 5.7.
Tablespace can see this information only in the presence of only NDB cluster, we are non-NDB online, so when we dump the data does not need this information.
So turn off the display of all on ok!

-y, --no-tablespaces
                      Do not dump any tablespace information.
     
         
         
         
  More:      
 
- Linux in order to make NMAP hide and seek with the firewall (Linux)
- CentOS 7 How to install MySQL Server (Database)
- IP Security Policy is to learn how to prevent Ping and closed ports (Linux)
- Ubuntu 14.04 LTS installed Hadoop 1.2.1 (distributed cluster mode) (Server)
- How to Install Cantata MPD 1.3.3 for Ubuntu and Derived Version Users (Linux)
- Java look and feel mode (Facade mode) (Programming)
- Ubuntu install Oracle 10g process and problem solution (Linux)
- Oracle 12c detailing the new features (Database)
- Linux System Getting Started Learning: Linux in the last command (Linux)
- How to install and configure a VNC server on CentOS 7.0 (Server)
- Oracle to create an external table (Database)
- Configure the Linux kernel and use iptables to do port mapping (Linux)
- Six Ways to view slides can be implemented Android (Programming)
- Linux user directory (Linux)
- Oracle 11gr2 new APPEND_VALUES tips (Database)
- CentOS6 installation configuration rsh (Server)
- Ubuntu System Log Configuration / var / log / messages (Linux)
- Linux command Detailed chpasswd bulk edit user password (Linux)
- RedHat virtual machine to install VMware Tools (Linux)
- Nodejs complete installation instructions for Express (Linux)
     
           
     
  CopyRight 2002-2022 newfreesoft.com, All Rights Reserved.