How to merge MySQL backup files (*.sql)

Question

We have several MySQL backup files of a database in *.SQL format and we want to restore the database by using these files. However, as the sql files contains "DROP TABLE" definitions, running one script cause to remove the data created by the previous script. In addition to these sql files, we have also some other backup folders containing *.FRM and *.MYD files. In all of two cases I think these backups are Differential or Incremental and we have to merge/combine all of them. If so, how can we combine them without losing data? As I have no experience on MySQL, could you explain a way by using MySQL Workbench if it is possible? Thanks in advance.

Some lines from one of the sql file:

DROP TABLE IF EXISTS `BLACKLIST`;
    CREATE TABLE `BLACKLIST` (
      `ID` int(11) NOT NULL auto_increment,
      `NAME` varchar(30) NOT NULL,
      `SURNAME` varchar(30) NOT NULL,
      PRIMARY KEY  (`ID`),
      UNIQUE KEY `BLACKLISTINDEX` (`NAME`,`SURNAME`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

Answer 1

.SQL files are text. You can open them in a text editor. Then, find and remove the DROP TABLE and CREATE TABLE statements. Finally, use the workbench or any other client to run the remaining INSERT statements.

On the subject of differential and incremental backups, you'll have to restore your SQL files in the correct order to get the results you hope for. That means you'll have to work out that order very carefully indeed from the files you have. Commercial RDMSs like Oracle have elaborate schemes for making incremental and differential backups, along with schemes for restoring them. MySQL doesn't have that kind of thing, so whatever you have is either home-grown or based on some sort of third party software package.

If this were my project, I'd create a sacrificial copy of the database and all the backup files, .frm files, and other stuff. I'd then mess around trying to get stuff restored in the right order.

Once I was convinced my copy was correct, I'd make an SQL backup of the whole thing. I'd actually create two backup items: one containing just the CREATE TABLE statements, and the other containing just data. The workbench can do that.

Then restore that clean backup to create a new production database.

What the workbench can't do is help you with the FRM and MYD files. To use those you'll need to figure out how to put them in the right place in the server file system and restart the MySQL server that's looking at them. Treating those files as backups is a sketchy and risky business. If you successfully restore them, think of your accomplishment as a talking donkey. It's not a surprise it works poorly, it's a wonder it works at all.

Going forward: your ordinary SQL backup files should not contain data definition language such as DROP TABLE or CREATE table statements. You can create separate files containing only those statements and no data, and keep them around if you need them.

Answer 2

Ollie's answer covers what to do now -- tediously edit the 100 files to get rid of DROP TABLE and CREATE TABLE.

For the future, you can prevent the creation of those statements with this (approximately)

mysqldump  --skip-add-drop-table  --no-create-info  --no-create-db ...

See dev.mysql.com/doc/refman/5.6/en/mysqldump.html

BTW, there is also this, for providing just the CREATEs:

mysqldump  --no-data ...