• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar
  • Skip to footer navigation

The Geek Diary

  • OS
    • Linux
    • CentOS/RHEL
    • Solaris
    • Oracle Linux
    • VCS
  • Interview Questions
  • Database
    • oracle
    • oracle 12c
    • ASM
    • mysql
    • MariaDB
  • DevOps
    • Docker
    • Shell Scripting
  • Big Data
    • Hadoop
    • Cloudera
    • Hortonworks HDP

MySQL Table Maintenance – InnoDB, MyISAM, MEMORY, ARCHIVE

by admin

InnoDB Table Maintenance

If a table check indicates problems, restore the table to a consistent state by dumping it with mysqldump, dropping it, and re-creating it from the dump file. In the event of a crash of the MySQL server or the host on which it runs, some InnoDB tables might be in an inconsistent state. InnoDB performs auto-recovery as part of its startup sequence.

Use CHECK TABLE or a client program to find inconsistencies, incompatibilities, and other problems. Restore a table by dumping it with mysqldump:

shell> mysqldump [db_name] [table_name] > [dump_file]

Then drop it and re-create it from the dump file.

shell> mysql [db_name] < [dump_file]

Rarely, the server does not start due to the failure of auto-recovery. If that happens, use the following procedure:

  • Restart the server with the --innodb_force_recovery option set to a value from 1 to 6. These values indicate increasing levels of caution in avoiding a crash, and increasing levels of tolerance for possible inconsistency in the recovered tables. A good value to start with is 4, which prevents insert buffer merge operations.
  • When the server is started with --innodb_force_recovery set to a non-zero value, InnoDB prevents INSERT, UPDATE, or DELETE operations. Therefore, you should dump the InnoDB tables and then drop them while the option is in effect. Then restart the server without the --innodb_force_recovery option. When the server comes up, recover the InnoDB tables from the dump files.
  • If the preceding steps fail, restore the tables from a previous backup.

MyISAM Table Maintenance

The default CHECK TABLE check type is MEDIUM for both dynamic and static format tables. If a static-format table type is set to CHANGED or FAST, the default is QUICK. The row scan is skipped for CHANGED and FAST because the rows are very seldom corrupted.

shell> myisamchk --medium-check [table_name]

CHECK TABLE changes the table if the table is marked as "corrupted" or "not closed properly". If it does not find any problems in the table, it marks the table status as “up to date.” If a table is corrupted, the problem is most likely in the indexes and not in the data. The --myisam-recover option value can consist of a comma-separated list of one or more of the following values:

  • DEFAULT: Default checking
  • BACKUP: Tells the server to make a backup of any table that it must change
  • FORCE: Performs table recovery even when causing the loss of more than one row of data
  • QUICK: Performs a quick recovery. The recovery skips tables that have no gaps (known as “holes”) between rows resulting from deletes or updates.

For example, to tell the server to perform a forced recovery of MyISAM tables found to have problems but to make a backup of any table that it changes, add the following to your option file:

[mysqld] 
myisam-recover=FORCE,BACKUP

MEMORY Table Maintenance

MEMORY tables do not release memory when rows are deleted using a DELETE...WHERE statement. To release memory, you must perform a null ALTER TABLE operation.

ARCHIVE Table Maintenance

ARCHIVE compression issues:

  • Table rows are compressed as they are inserted.
  • On retrieval, rows are uncompressed on demand.
  • Some SELECT statements can deteriorate the compression.

Use of OPTIMIZE TABLE or REPAIR TABLE can achieve better compression. OPTIMIZE TABLE works when the table is not being accessed (by read or write).

Filed Under: mysql, MySQL Cluster

Some more articles you might also be interested in …

  1. Understanding the Different Configuration files used for MySQL Server
  2. What is the meaning of the TRX_TABLES_LOCKED column in the information_schema.INNODB_TRX MySQL table
  3. How to Create a MySQL Docker Container for Testing
  4. How to set the default character set in MySQL and how to propagate it in a master-master replication scenario
  5. How to List Users and Privileges in MySQL
  6. MySQL Error “Too many connections” and how to resolve it
  7. Excluding a table or database from MySQL replication
  8. Configuring mysqld to log slow queries
  9. How to Install MySQL Cluster on a single server for testing purposes
  10. How To Skip a Transaction on MySQL replication slave When GTIDs Are Enabled

You May Also Like

Primary Sidebar

Recent Posts

  • pw-cat Command Examples in Linux
  • pvs: command not found
  • pulseaudio: command not found
  • pulseaudio Command Examples in Linux

© 2023 · The Geek Diary

  • Archives
  • Contact Us
  • Copyright