Home PC Games Linux Windows Database Network Programming Server Mobile  
           
  Home \ Database \ mysqldump issue a note     - Python2 ---- function using dictionaries (Programming)

- Ubuntu download install and configure Plank Dock (Linux)

- Linux RPM default installation path (Linux)

- How to install OpenOffice Ubuntu or Linux Mint (Linux)

- RMAN parameters of ARCHIVELOG DELETION (Database)

- Linux shell scripts bubble sort (Programming)

- Windows Remote Desktop Management CentOS 6.4 (Linux)

- MongoDB 3.2 to upgrade from 3.0.7 (Database)

- Package the Python script file into an executable file (Programming)

- Into the Java keyword instanceof (Programming)

- Some common Linux commands Study Notes (Linux)

- High-performance JavaScript loaded and executed (Programming)

- map and hash_map STL containers (Programming)

- X security settings in Ubuntu (Linux)

- RAID configuration and management under linux (Server)

- Ubuntu program using the Sound Recorder (Linux)

- Linux administrator should command: sed and awk (Linux)

- Several Ceph performance optimization of new methods and ideas (2015 SH Ceph Day after flu reference) (Server)

- What is Unikernel? (Linux)

- To assign multiple IP addresses NIC on the CentOS 7 (Linux)

 
         
  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:      
 
- To install and deploy Apache under the CentOS (Server)
- Java memory mechanism Description (Programming)
- Linux system server network security management tips (Linux)
- Nginx + ownCloud + PHP + MySQL to build personal private cloud under CentOS7 (Server)
- Java reflection technology explain (Programming)
- Ubuntu 10.04 to Ubuntu 10.10 Upgrade (Linux)
- How to install CentOS 7.x in OpenERP (Odoo) (Linux)
- Depth understanding of the use of Spring Redis (Programming)
- Laravel configuration PhpStorm + Xdebug + Chrome settings Debug Environment (Server)
- Linux shared libraries .so file name and Dynamic Link (Linux)
- Java object initialization (Programming)
- Lua regex (string function) (Programming)
- Linux network cut package is not fully defragment (Linux)
- Firewall chapter of Linux server security configuration (Linux)
- Fedora 20, Fedora 19, CentOS 6 and RHEL6 users how to install Wine 1.7.15 (Linux)
- FreeRadius installation process record (Linux)
- Selection sort, insertion sort, and Shell sort (Programming)
- Zabbix monitoring disk IO status (Server)
- Debian installation (Linux)
- Boost-- time and date - (1) timer library introduction (Programming)
     
           
     
  CopyRight 2002-2022 newfreesoft.com, All Rights Reserved.