Other aop/dbrepair/overview

Table Of Contents

Previous topic

MySQL DB Repair

Next topic

MySQL Backup/Recovery Tools

This Page

Daya Bay Links

Content Skeleton

MySQL Table Repair

Considerations for the repair of corrupt MySQL tables.

  1. database backup mandatory before attempting repairs, as data loss is a very real possibility

  2. large disk space is required for the dump files, filling disks is known to be a cause of MySQL corruption

    • estimate the disk space required for the dump, using queries shown below
    • check disk space available is comfortably adequate, remember the tarball and hotcopy directory will need to exist at the same time : so double the total obtained from the DB query
  3. if the DB is actively being updated consider

    • need locking or other means to ensure consistent set of tables
    • must not lock for too long, or will kill writers : also backups are a large CPU load
  4. doing backups is expensive and time consuming, with default settings of mysqldump the table will be locked for possibly an extended period

Check table types and sizes

The MB sizes include indices which are not dumped, so dumpfiles might not be as big as feared (we shall see).

mysql> select table_name,table_type, engine, round((data_length+index_length-data_free)/1024/1024,2) as MB  from information_schema.tables where table_schema = 'tmp_ligs_offline_db' ;
+-----------------------+------------+-----------+---------+
| table_name            | table_type | engine    | MB      |
+-----------------------+------------+-----------+---------+
| ChannelQuality        | BASE TABLE | MyISAM    |   47.31 |
| ChannelQualityVld     | BASE TABLE | MyISAM    |    0.53 |
| DaqRawDataFileInfo    | BASE TABLE | FEDERATED |   67.04 |
| DaqRawDataFileInfoVld | BASE TABLE | FEDERATED |   13.23 |
| DqChannel             | BASE TABLE | MyISAM    | 3570.58 |
| DqChannelStatus       | BASE TABLE | MyISAM    | 2338.56 |
| DqChannelStatusVld    | BASE TABLE | MyISAM    |   20.12 |
| DqChannelVld          | BASE TABLE | MyISAM    |   19.91 |
| LOCALSEQNO            | BASE TABLE | MyISAM    |    0.00 |
+-----------------------+------------+-----------+---------+
9 rows in set (0.09 sec)
mysql> select table_name,table_type, engine, round((data_length+index_length-data_free)/1024/1024,2) as MB  from information_schema.tables where table_schema = 'tmp_offline_db' ;
+---------------------------+------------+--------+-------+
| table_name                | table_type | engine | MB    |
+---------------------------+------------+--------+-------+
| CableMap                  | BASE TABLE | MyISAM |  3.86 |
| CableMapVld               | BASE TABLE | MyISAM |  0.03 |
| CalibPmtFineGain          | BASE TABLE | MyISAM | 10.17 |
| CalibPmtFineGainVld       | BASE TABLE | MyISAM |  0.08 |
| CalibPmtHighGain          | BASE TABLE | MyISAM |  9.14 |
| CalibPmtHighGainPariah    | BASE TABLE | MyISAM | 49.42 |
| CalibPmtHighGainPariahVld | BASE TABLE | MyISAM |  0.38 |
| CalibPmtHighGainVld       | BASE TABLE | MyISAM |  0.08 |
| DcsAdWpHv                 | BASE TABLE | MyISAM | 35.81 |
| DcsAdWpHvVld              | BASE TABLE | MyISAM |  0.27 |
| Demo                      | BASE TABLE | MyISAM |  0.00 |
| DemoVld                   | BASE TABLE | MyISAM |  0.00 |
| DqChannelPacked           | BASE TABLE | MyISAM | 18.61 |
| DqChannelPackedVld        | BASE TABLE | MyISAM | 18.87 |
| HardwareID                | BASE TABLE | MyISAM |  5.50 |
| HardwareIDVld             | BASE TABLE | MyISAM |  0.02 |
| LOCALSEQNO                | BASE TABLE | MyISAM |  0.00 |
| McsPos                    | BASE TABLE | MyISAM |  0.00 |
| McsPosVld                 | BASE TABLE | MyISAM |  0.00 |
| PhysAd                    | BASE TABLE | MyISAM |  0.00 |
| PhysAdVld                 | BASE TABLE | MyISAM |  0.00 |
| SupernovaTrigger          | BASE TABLE | MyISAM |  0.00 |
| SupernovaTriggerVld       | BASE TABLE | MyISAM |  0.00 |
+---------------------------+------------+--------+-------+
23 rows in set (0.05 sec)

mysql> select count(*) from DqChannelPacked ;
+----------+
| count(*) |
+----------+
|   323000 |
+----------+
1 row in set (0.00 sec)