MySQL data recovery experiment with mysqldump and mysqlbinlog

Experiment document: Process: Create a table-backup-simulate downtime (delete a table)-restore a backup-restore the current data.

1. Restore and restore the experiment

Create a database and use the drop statement to simulate database downtime. Use mysqldump and musqlbinlog to restore and restore the database.

1.1 CREATE table chanelfollowing under the channel

Mysql> usechannel;

Database changed

Mysql> showtables;

+ ------------------------- +

| Tables_in_channel |

+ ------------------------- +

| Official_channel |

| Official_channel_widget |

| Personal_channel |

| Personal_channel_widget |

| Tags |

+ ------------------------- +

5 rows in set (0.00 sec)

Mysql> createtable chanel_following (id int primary key, uid int not null );

Query OK, 0 rows affected (1.18 sec)

Mysql> showtables;

+ ------------------------- +

| Tables_in_channel |

+ ------------------------- +

| Chanel_following |

| Official_channel |

| Official_channel_widget |

| Personal_channel |

| Personal_channel_widget |

| Tags |

+ ------------------------- +

6 rows in set (0.00 sec)

Mysql> showcolumns from chanel_following;

+ ------- + --------- + ------ + ----- + --------- + ------- +

| Field | Type | Null | Key | Default | Extra |

+ ------- + --------- + ------ + ----- + --------- + ------- +

| Id | int (11) | NO | PRI | NULL |

| Uid | int (11) | NO | NULL |

+ ------- + --------- + ------ + ----- + --------- + ------- +

2 rows in set (0.01 sec)

1.2 back up data through mysqldump

(Prepare for the Restoration experiment): open another window for easy input.

[Root @ channelme ~] # Mysqldump-uroot-p-B channel -- table chanel_following> chanelfollowing. SQL

Enter password:

[Root @ channelme ~] # The backup is successful.

Note:

Tools such as mysqldump, mysqladmin, and mysqlbinlog are input under the linux Command Line.

If the backup path is not specified, it is in the current path by default, rather than in the mysql data directory.

View the backup content:

[Root @ channelme ~] # Cat chanelfollowing. SQL

-- MySQL dump 10.11

--

-- Host: localhost Database: channel

--------------------------------------------------------

-- Server version 5.5.13-log

 

/*! 40101 SET @ OLD_CHARACTER_SET_CLIENT = @ CHARACTER_SET_CLIENT */;

......

/*! 40111 SET @ OLD_ SQL _NOTES = SQL _notes, SQL _NOTES = 0 */;

--

-- Table structure for table 'chanel _ following'

--

Drop table if exists 'chanel _ following ';

SET @ saved_cs_client =@@ character_set_client;

SET character_set_client = utf8;

Create table 'chanel _ following '(

'Id' int (11) not null,

'Uid' int (11) not null,

Primary key ('id ')

) ENGINE = InnoDB default charset = utf8;

SET character_set_client = @ saved_cs_client;

--

-- Dumping data for table 'chanel _ following'

--

Lock tables 'chanel _ following 'WRITE;

/*! 40000 alter table 'chanel _ following 'disable keys */;

/*! 40000 alter table 'chanel _ following 'enable keys */;

Unlock tables;

/*! 40103 SET TIME_ZONE = @ OLD_TIME_ZONE */;

......

/*! 40111 SET SQL _NOTES = @ OLD_ SQL _NOTES */;

-- Dump completed on 2011-10-10 3:07:39

[Root @ channelme ~] #

The backup document only applies to the chanel_following table, which indicates that the backup is correct.

1.3 insert data in the chanel_following table

Mysql> insertinto chanel_following values );

Query OK, 3 rows affected, 1 warning (0.01sec)

Records: 3 Duplicates: 0 Warnings: 1

Check whether the insert operation is correct:

Mysql> select * from chanel_following;

+ ------------ +

| Id | uid |

+ ------------ +

| 3 | 69686869 |

| 102/100000 |

| 2132723743/2147483647 |

+ ------------ +

Rows in set (0.00 sec)

Here I did an experiment on int. If you are careful, you will find that the third data I inserted is different from the displayed data. This is because the maximum value of an int is 2147483647. If the maximum value of an int is exceeded, it is truncated, and the value of one less bit is 2132723743. Because the uid attribute is not set to non-negative unsigned, It is not 4294967295.

1.4 drop table to simulate downtime

Mysql> droptable chanel_following;

Query OK, 0 rows affected (0.02 sec)

Mysql> select * from chanel_following;

Empty set (0.00 sec)

Deleted successfully.

1.5 restore a database through a backup document

In step 2, we backed up the table in chanelfollowing. SQL. Note the path here.

[Root @ channelme ~] # Mysql-uroot-p channel </root/chanelfollowing. SQL

Enter password:

[Root @ channelme ~] #

View recovery results:

Mysql> show tables;

+ ------------------------- +

| Tables_in_channel |

+ ------------------------- +

| Chanel_following |

| Official_channel |

| Official_channel_widget |

| Personal_channel |

| Personal_channel_widget |

| Tags |

+ ------------------------- +

6 rows in set (0.00 sec)

Successful.

You can also run the source command in mysql:

Mysql> source \ root \ chanelfollowing. SQL

Query OK, 0 rows affected (0.00sec)

......

Query OK, 0 rows affected (0.00sec)

Mysql> showcolumns from chanel_following;

+ ------- + --------- + ------ + ----- + --------- + ------- +

| Field | Type | Null | Key | Default | Extra |

+ ------- + --------- + ------ + ----- + --------- + ------- +

| Id | int (11) | NO | PRI | NULL |

| Uid | int (11) | NO | NULL |

+ ------- + --------- + ------ + ----- + --------- + ------- +

2 rows in set (0.01 sec)

Restored successfully.

Note that mysqldump is used for backup and cannot be recovered. The mysql command is used for recovery.

 

1.6 mysqlbinlog recovery

Use mysqldump to restore to the created chanel_following table. If the data has not been restored, use mysqlbinlog to restore.

Mysql> showbinary logs;

+ ------------------ + ----------- +

| Log_name | File_size |

+ ------------------ + ----------- +

| Mysql-bin.000001 | 29692 |

......

| Mysql-bin.000021 | 1571 |

+ ------------------ + ----------- +

21 rows in set (0.00 sec)

 

Mysql> showbinlog events in 'mysql-bin.000021 ';

+ ------------------ + ------ + ------------- + ----------- + ------------- + ---------------------------------- +

| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |

+ ------------------ + ------ + ------------- + ----------- + ------------- + ---------------------------------- +

| Mysql-bin.000021 | 4 | Format_desc | 1 | 107 | Server ver: 5.5.13-log, Binlogver: 4 |

......

| Mysql-bin.000021 | 487 | Query | 1 | 621 | use 'channel'; create table chanel_following (id int primary key, uid int not null) |

| Mysql-bin.000021 | 621 | Query | 1 | 692 | BEGIN |

| Mysql-bin.000021 | 692 | Query | 1 | 843 | use 'channel'; insert into chanel_following values (), () |

| Mysql-bin.000021 | 843 | Xid | 1 | 870 | COMMIT/* xid = 1296 */|

......

+ ------------------ + ------ + ------------- + ----------- + ------------- + ---------------------------------- +

14 rows in set (0.00 sec)

We can see that the entire data is inserted between 692 and 870. Perform the following restoration:

[Root @ channelme ~] # Mysqlbinlog-uroot-p -- start-position = 692 mysqlbin.000021

Enter password:

/*! 40019 SET @ session. max_insert_delayed_threads = 0 */;

/*! 50003 SET @ OLD_COMPLETION_TYPE = @ COMPLETION_TYPE, COMPLETION_TYPE = 0 */;

DELIMITER /*! */;

Mysqlbinlog: File 'mysqlbin. 000021 'notfound (Errcode: 2)

DELIMITER;

# End of log file

[Root @ channelme ~] # Mysqlbinlogmysql-bin.000021 -- start-position = 692 -- stop-position = 870 | mysql-uroot-p

Enter password: mysqlbinlog: File 'mysql-bin.000021 'not found (Errcode: 2)

Note: I only want to check whether the binlog name is incorrect. I forgot to enter/root, and the log file is in the mysql DATA DIRECTORY !!!!!

Enter the data directory:

[Root @ channelme data] # ls

Channel mysql-bin.000001 mysql-bin.000016

......

Mysql-bin.000006 mysql-bin.000021 mysql-bin.index

[Root @ channelmedata] # mysqlbinlog mysql-bin.000021 -- start-position = 692 -- stop-position = 870 | mysql-uroot-p

Enter password:

[Root @ channelme data] #

Recovered. Finally, no error is reported. Check that:

Mysql> select * from chanel_following;

+ ------------ +

| Id | uid |

+ ------------ +

| 3 | 69686869 |

| 102/100000 |

| 2132723743/2147483647 |

+ ------------ +

3 rows in set (0.00 sec)

OK. The experiment is completed.

In addition, if the disk breaks down, log files will also be lost. Therefore, to recover the binary logs, the MySQL server needs to save them to a safe location (RAID disks, SAN ,...), it should be different from the storage location of data files to ensure that these logs are not stored on the destroyed hard disk. (That is, we can use the -- log-bin option to start the server and specify a location different from the data directory on another physical device. In this way, logs will not be lost even if the device that contains the directory is lost ).

Write it by yourself to prevent loss and facilitate access... Thank you for your correction. After the upload, a space is discarded. Do not make any mistakes in the simulated shoes.