Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I need a way to change the schema name which appears in create statement and use statement of mysql dump file. The goal is having the database restored with a different database name on another server. The file is a few gigs in size so opening an editor doesn't work.

How do the experts deal with that?

I also need a way to see the changes before I upload the Mysql to a new server to validate it.

Thanks !

share|improve this question
3  
This should be in SuperUser site: How to open and edit large text files –  ypercube Apr 19 '12 at 18:16
    
There are editors and editors ... –  ypercube Apr 19 '12 at 18:17
    
@ypercube actually I think this is on-topic here too. –  Jack Douglas Aug 23 '12 at 14:12
add comment

1 Answer

up vote 4 down vote accepted

I created a script so that you switch the old DBName with the new DBName

Here is the script

DUMPFILE=junk.txt
DUMPFILENEW=junk.new
DBNAME_OLD=dbAccessData
DBNAME_NEW=rolando

LINE_CDB=`grep -n "^CREATE DATABASE" ${DUMPFILE} | sed 's/\`/ /g' | sed 's/:/ /g' | grep " ${DBNAME_OLD} " | awk '{print $1}'`
LINE_USE=`grep -n "^USE " ${DUMPFILE} | sed 's/\`/ /g' | sed 's/:/ /g' | grep " ${DBNAME_OLD} " | awk '{print $1}'`
(( LINES_IN_THE_MIDDLE = LINE_USE - LINE_CDB + 1 ))
LINES_IN_THE_DUMP=`wc -l < ${DUMPFILE}`

DUMP_SECTION1=${DUMPFILE}.Section1
DUMP_SECTION2=${DUMPFILE}.Section2
DUMP_SECTION2A=${DUMPFILE}.Section2a
DUMP_SECTION3=${DUMPFILE}.Section3

(( X = LINE_CDB - 1 ))
head -${X} ${DUMPFILE} > ${DUMP_SECTION1}

head -${LINE_USE} ${DUMPFILE} | tail -${LINES_IN_THE_MIDDLE} > ${DUMP_SECTION2}

(( X = LINES_IN_THE_DUMP - LINE_USE ))
tail -${X} ${DUMPFILE} > ${DUMP_SECTION3}

echo "sed 's/${DBNAME_OLD}/${DBNAME_NEW}/g' < ${DUMP_SECTION2} > ${DUMP_SECTION2A}" > change_dbname.sh
chmod +x change_dbname.sh
./change_dbname.sh
rm -f change_dbname.sh

cp ${DUMP_SECTION1} ${DUMPFILENEW}
cat ${DUMP_SECTION2A} >> ${DUMPFILENEW}
cat ${DUMP_SECTION3}  >> ${DUMPFILENEW}

rm -f ${DUMP_SECTION1}
rm -f ${DUMP_SECTION2}
rm -f ${DUMP_SECTION2A}
rm -f ${DUMP_SECTION3}

cat ${DUMPFILENEW}

Here is the sample file (junk.txt, 30 lines)

-- MySQL dump 10.11
--
-- Host: localhost    Database: dbAccessData
-- ------------------------------------------------------
-- Server version       5.0.51a-community-log

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Current Database: `dbAccessData`
--

CREATE DATABASE /*!32312 IF NOT EXISTS*/ `dbAccessData` /*!40100 DEFAULT CHARACTER SET latin1 */;

USE `dbAccessData`;

--
-- Table structure for table `tblAccountLinks`
--

DROP TABLE IF EXISTS `tblAccountLinks`;

Here is the output

-- MySQL dump 10.11
--
-- Host: localhost    Database: dbAccessData
-- ------------------------------------------------------
-- Server version       5.0.51a-community-log

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Current Database: `dbAccessData`
--

CREATE DATABASE /*!32312 IF NOT EXISTS*/ `rolando` /*!40100 DEFAULT CHARACTER SET latin1 */;

USE `rolando`;

--
-- Table structure for table `tblAccountLinks`
--

DROP TABLE IF EXISTS `tblAccountLinks`;

Give it a Try !!!

share|improve this answer
1  
You are spoiling them :) –  ypercube Apr 19 '12 at 21:32
    
Thank you very much Ronaldo –  Nir Apr 20 '12 at 4:25
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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