04 May 2013

Force database name on import MySQL even if CREATE DATABASE and USE statements are specified

Many advices over Internet are:
You have to preform proper backup first with right options like --no-create-db. Otherwise dump file contains SQL commands which says to server 'create database with this name' and 'use that database'. That's super advice, damn, I wouldn't know that without you! What if you need to use dump which is already done before time ago (already containing these lines) and you can't create new backup with new options because the data you need are only in that old backup.

Importing options in MySQL doesn't support ignore or something similar to avoid these SQL commands. It is necessary to alter or remove these lines containing mentioned commands.

If the database dump file is small, you can edit it by hand, find lines 'CREATE DATABASE' and 'USE', then replace with desired name or even remove them.

If the file is big, then it's not so easy to edit file by hand. Opening file bigger than available free operating memory will cause problems. You can do it by using sed from command line:

 #  sed '/^CREATE DATABASE\|^USE/s/old-name/new-name/' original.sql > new.sql

This command use original.sql as input, will find lines beginning with CREATE DATABASE or USE and alter old-name to new-name and all together goes to new.sql file.

Check content of new.sql if the names are properly set.:

 #  grep '^CREATE DATABASE\|^USE' new.sql
 CREATE DATABASE /*!32312 IF NOT EXISTS*/ `new-name` /*!40100 DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci */;
 USE `new-name`;

Some time-saving hints:
If you will be importing database anyway, you can save some time by piping output directly to mysql instead of creating new file and then importing in to database. And as it was in my case, I need to recover database from gzipped package. But I wanted to keep backup file untouched and do not create intermediate file. Then the command will be like this:

 #  gunzip < /path/to/backup-file.sql.gz | sed '/^CREATE DATABASE\|^USE/s/old-name/new-name/' | mysql -u user -p

This commands read backup-file.sql.gz which goes to gunzip to decompress the content then on the fly piping to sed which I described above and one more pipe directly to mysql to import the database. With this command you need to be careful - you need to be sure that you are not going to rewrite existing data in database if you want to keep them. It is better to use MySQL user with limited permissions to run this command safely.

No comments:

Post a Comment