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

The Geek Diary

CONCEPTS | BASICS | HOWTO

  • OS
    • Linux
    • CentOS/RHEL
    • Solaris
    • Oracle Linux
    • Linux Services
    • VCS
  • Database
    • oracle
    • oracle 12c
    • ASM
    • mysql
    • MariaDB
    • Data Guard
  • DevOps
    • Docker
    • Shell Scripting
  • Interview Questions
  • 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. MySQL Server 8.0 – How to create a REPLICATION SLAVE using MEB
  2. How to reset MySQL database root password
  3. What is the MySQL Enterprise Monitor?
  4. MySQL : How To Find the Slowest Queries
  5. How To Create a Local Yum Repository for MySQL Enterprise Packages
  6. How to use foreign keys to attain referential integrity in MySQL
  7. How to find Which Process Is Killing mysqld With SIGKILL or SIGTERM on Linux
  8. MySQL: How to Set Account Resource Limits
  9. How to Add New Nodes To an Existing MySQL Cluster Setup
  10. How to Perform a Rolling Restart of MySQL NDB Cluster

You May Also Like

Primary Sidebar

Recent Posts

  • How to disable ICMP redirects on CentOS/RHEL
  • What are Oracle Key Vault Roles
  • What Is Oracle Key Vault
  • Auditing with Oracle Database Vault Reports
  • Archives
  • Contact Us
  • Copyright

© 2021 · The Geek Diary