MySQL Backup Strategy.

Back up Strategies

If you are admin taking the backup is really an important task in light of MySQL you should do the following

1. Take the Backup regularly(weekly,regularly as you wish).Store the Back-up in some safe place.

2. Keep the binary logging enabled so that changes can be recorded in it.

3. While taking the backup flush the logs so that it generates the new binary log which record the changes after the backup is made.The Syntax is as

mysqldump -uuser -psecret --flush-logs --lock-all-tables --all-databases > dump.sql

use the other options with mysqldump if you need

--flush-logs : will generate new binary log before which the all dump will be generated.

--lock-all-tables : will lock all tables upto the time till the dump is being generated.

4. Keep binary logs in some safe place so in case of some failure you can use the most recent backup's and the binary logs generated after that to reach to the actual level of the database.

How to recover a currupted InnoDB table.

I was really in Problem when one of my InnoDB table get corrupted I posted My Question on dba.stackexchange.com.
The issue was as

mysql> check table City;
+-------------------+-------+----------+----------------------------------------------------------+
| Table             | Op    | Msg_type | Msg_text                                                 |
+-------------------+-------+----------+----------------------------------------------------------+
| world_innodb.City | check | Error    | Incorrect information in file: './world_innodb/City.frm' |
| world_innodb.City | check | error    | Corrupt                                                  |
+-------------------+-------+----------+----------------------------------------------------------+
2 rows in set (0.00 sec)

Then I Created a test scenario and found solution

I created a table called test2

CREATE TABLE `test2` (   `t` int(11) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

I then inserted one record

insert into test2 values(1);

I stopped MySQL and then added a bunch of characters to the test2.frm file with vi.

I then started MySQL and saw the following

mysql> show table status \G;
*************************** 1. row ***************************
           Name: test2
         Engine: NULL
        Version: NULL
     Row_format: NULL
           Rows: NULL
 Avg_row_length: NULL
    Data_length: NULL
Max_data_length: NULL
   Index_length: NULL
      Data_free: NULL
 Auto_increment: NULL
    Create_time: NULL
    Update_time: NULL
     Check_time: NULL
      Collation: NULL
       Checksum: NULL
 Create_options: NULL
        Comment: Incorrect information in file: './test2/test2.frm'

I then created another table with the same structure

CREATE TABLE `test3` (   `t` int(11) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

I then stopped mysql and copied test3.frm over test2.frm

cp /var/lib/mysql/testdb/test3.frm /var/lib/mysql/testdb/test2.frm

I restarted MySQL and had access to test2

mysql> show table status \G;
*************************** 1. row ***************************
           Name: test2
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 1
 Avg_row_length: 16384
    Data_length: 16384
Max_data_length: 0
   Index_length: 0
      Data_free: 25675431936
 Auto_increment: NULL
    Create_time: 2012-02-07 16:24:20
    Update_time: NULL
     Check_time: NULL
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options:
        Comment:

Here is the record that I inserted

mysql> select * from test2;
+------+
| t    |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

If you know the structure of the table City , you could create an identical table and then copy the .frm file over the existing corrupted one