Home PC Games Linux Windows Database Network Programming Server Mobile  
           
  Home \ Database \ mysqldump issue a note     - To deploy MySQL database with separate read and write OneProxy (Database)

- RHEL 6.4 installed MySQL 5.6.27 (Database)

- Linux System Getting Started Tutorial: How do you know Shell which is currently being used (Linux)

- CentOS7 install MySQL5.6.22 (Linux)

- httpd-2.4 feature (Server)

- MongoDB 3.2 to upgrade from 3.0.7 (Database)

- A key installation Gitlab 7 on RHEL6.4 and Setup Mail TX (Linux)

- ORA-04031 Error Resolution (Database)

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

- Oracle 11g DataGuard service to start automatically (Database)

- To install Redis under Linux (Database)

- Ubuntu install Eclipse can not find JAVA_HOME problem (Linux)

- To explore the Android ListView caching mechanism again (Programming)

- Findbugs installation documentation (Linux)

- Linux kernel to achieve soft RPS network to receive soft interrupt load balancing to distribute (Linux)

- Ubuntu Control Panel to resolve network-manager icon display issue (Linux)

- GDB remote connections RX Probe online debug program (Programming)

- Ubuntu 14.04 to install file editor KKEdit 0.1.5 version (Linux)

- C # DateTime structure common method (Programming)

- Use Android Studio and Gradle packaged multi-version APK (Programming)

 
         
  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:      
 
- Expert advice: Do not use the computer security IE browser (Linux)
- PostgreSQL export table structure information (Database)
- Killall five options you might not know (Linux)
- Archlinux installation tutorial (Linux)
- Java environment to build a number of issues (Linux)
- SELinux multi-level security (Linux)
- HomeKit Human Interface Guidelines (Linux)
- After reloading the cinder-volume OpenStack not start properly (Server)
- installation and configuration of the PHP environment (Apache2) under Linux (Server)
- To install Internet security firewall Raiders (Linux)
- Oracle large table to clean truncate .. reuse storage (Database)
- Let VMware ESXi virtual switch support VLAN (Linux)
- Linux Oracle delete archived logs (Database)
- Installation and operation GAMIT software under Linux operating system (Linux)
- Git uses a small mind (Linux)
- Run Windows in FreeBSD bhyve (Linux)
- MySQL binlog group to submit XA (two-phase commit) (Database)
- The best tools and techniques to find data on a Unix system (Linux)
- How to Install Suricata IDS on a Linux system (Server)
- Change CentOS 7 NIC name eno16777736 to eth0 (Linux)
     
           
     
  CopyRight 2002-2020 newfreesoft.com, All Rights Reserved.