How to change the create and use statement in a large sql file without opening it

Question

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 !

Answer 1

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