5

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;
  • In the future add the option that omits the DROP. – Rick James Jan 1 '16 at 5:43
  • @RickJames What do you mean? As I have no experience with MySQL, could you please explain your suggestions a little bit more by giving example code? Thanks in advance... – Jack Jan 2 '16 at 15:22
  • mysqldump --skip-add-drop-table -- see dev.mysql.com/doc/refman/5.6/en/mysqldump.html You may also want --no-create-info --no-create-db. This does not solve your immediate problem; an editor is needed. – Rick James Jan 2 '16 at 16:54
  • @RickJames Many thanks for your help. What do you mean with "This does not solve your immediate problem; an editor is needed."? Sorry, but I really have no experience and I would be happy if you post an answer as Ollie below so that it would be useful for other people suffering from this problem. – Jack Jan 2 '16 at 17:36
3

.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.

| improve this answer | |
  • Thanks for reply. Unfortunately there are approximately 100 sql files and opening all of them in an editor and removing the DROP TABLE and CREATE TABLE statements is almost impossible as there are several statement in each of them. I am wondering why is it too hard to recover a database from such a kind of backup files. Is there a better approach with sql files? On the other hand, I also copied all the folders to the MySQL Data folder and receive each of them from MySQL Workbench. However, it seems that none of them contain data (there are only table names with no columns on the explorer). – Jack Dec 31 '15 at 14:38
  • Why is it hard to use these "backup files?" Because they're not really backup files, and the person who created them never tried to recover the database from them. Good free text editors, like Notepad++, have ways of scripting repetitive edit operations. – O. Jones Dec 31 '15 at 15:10
  • How can I change the DROP definition above with the replace method of Notepad++? I really no idea and it seems to be impossible. Any idea? – Jack Dec 31 '15 at 15:49
  • As far as I see from you comment I should follow these steps: 1) Apply the first sql script. With this script all the tables will be created. 2) Then comment all the DROP TABLE or CREATE table statements from the next sql scripts so that only the insert statements will be considered and the related data will be added to the current tables. Is that true? Is there anything missing or to be done extra? – Jack Jan 2 '16 at 15:33
1

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 ...
| improve this answer | |
  • Thanks for reply. I use Windows Server and if it is possible, could you please a solution by using MySQL Workbench? If not please clarify me where should I type these commands you suggested? – Jack Jan 4 '16 at 9:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.