Home PC Games Linux Windows Database Network Programming Server Mobile  
           
  Home \ Database \ mysqldump issue a note     - Adding SSH to Github (Linux)

- Standard and IO redirection (Linux)

- Install Ruby on Rails in Ubuntu 15.04 in (Linux)

- Solve the compatibility problem between Linux and Java at the source in bold font (Linux)

- Nine artifact control disk partition under Linux (Linux)

- Examples of RAID levels and achieve Operational Details (Linux)

- Python cause yum upgrade error (Linux)

- Android application security of data transmission security (Programming)

- Manage SQL Server services login (start) account and password (Database)

- Linux shell in back quotes (`) Application (Linux)

- By creating a personal technology blog Detailed GitHub (Linux)

- Number JavaScript type system (Programming)

- Vi syntax highlighting settings (Linux)

- Kafka + Log4j log implement centralized management (Server)

- Zombie process under Linux (Linux)

- Java filter (Programming)

- Use Ansible installation NGINX and NGINX Plus (Server)

- Oracle 11g can not export a variety of empty table solution (Database)

- MNIST presentation and database conversion (Database)

- Java open source monitoring platform Zorka basic use (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:      
 
- Linux log management tools Logrotate (Linux)
- To install minimize RHEL / CentOS 7 (Linux)
- Java keyword final, static (Programming)
- Github with .gitignore ignore specified file (Linux)
- Storm how to assign tasks and load balancing (Programming)
- Configuring Haproxy log support (syslog logging support) (Server)
- To add the Oracle JDBC driver in Maven repository (Linux)
- The array of C language (Programming)
- RHEL7.0 log system (Linux)
- Three methods easy data encryption on Linux (Linux)
- Linux console password solution (Programming)
- Java memory analysis tool uses detailed MAT (Programming)
- Oracle 10g relations with the constraint of column properties NULLABLE (Database)
- Java implementation file encryption and decryption (Programming)
- Use Spring cache and ehcache (Programming)
- How to add and delete bookmarks in Ubuntu (Linux)
- To_teach you three strategies to prevent the LAN IP address theft (Linux)
- How to install the Ruby runtime environment on Mac OS X (Linux)
- Python2.7.7 source code analysis (Programming)
- KVM add virtual disks (Linux)
     
           
     
  CopyRight 2002-2020 newfreesoft.com, All Rights Reserved.