Re: MySQL Workbench 5.1.9 Beta available - create script

Thanks Ugo,

I modified your script slightly. 

After all that though, I'm even more disappointed at the results! I only
have 146 tables and this thing is all over the map. It would take hours
or days to clean up the spaghetti it made. Plus it's very slow with that
many "objects". I'm on Ubuntu (no compiz) with 2GB RAM Intel Pentium 4
CPU 3.20GHz. Not sure how they justify the price... Guess I'll look at
other solutions on Windows (as they seem to be more robust) and keep
checking back on Workbench.

#!/bin/bash

#----------------------------------------
# mysql_dumpcreate.sh 
# mysql CREATE statements backup
#
# 25 March 2009
# http://phpcamaleo.org
#----------------------------------------

MYUSER='USERNAME'
MYPASS='PASSWORD'
MYHOST='IP ADDRESS'
DBNAME='DEFAULT DBNAME'
MYCONNECT='/usr/bin/tsocks'  #if you don't use socks, then just set this
to ''
# IMPORTANT! you may use a different character-set...
MYDUMPOPTS="--skip-opt --no-data --default-character-set=utf8
--set-charset --create-options --quote-names --quick --comments"

PATH=/sbin:/bin:/usr/sbin:/usr/bin

DATE=`/bin/date +%Y-%m-%d`

usage="\nSave only the CREATE statements of a MySQL database\n\n"
usage+="\tuse: mysql_dumpcreate.sh -p PATH -d DATABASE\n\n"
usage+="\t\t-p\tthe path where you want to save\n"
usage+="\t\t-d\tthe database name\n"
usage+="\n\texample: mysql_dumpcreate.sh -p ~/ -d $DBNAME\n\n"

while getopts ":p:d:" opt
do
case $opt in
p  ) PATH=$OPTARG ;;
d  ) DBNAME=$OPTARG ;;
\? ) echo  $usage
     exit 1
esac
done
shift $(($OPTIND - 1))

#echo $OPTIND

if [ $OPTIND -ne 5 ]; then
#
# show the help
#
echo -e $usage
exit 1
fi 

FILE=$DBNAME

echo -e "Creating the database '"$DBNAME"' CREATE statements in:
'"$PATH/$FILE.create.sql"'"

if [ -f $PATH/$FILE.create.sql ]; then
	/bin/rm -f $PATH/$FILE.create.sql
fi
if [ -f $PATH/$FILE.create.log ]; then
	/bin/rm -f $PATH/$FILE.create.log
fi

$MYCONNECT /usr/bin/mysqldump $MYDUMPOPTS -u$MYUSER -p$MYPASS -h$MYHOST
$DBNAME \
--log-error=$PATH/$FILE.create.log \
> $PATH/$FILE.create.sql

if [ -f $PATH/$FILE.create.log ]; then
	/bin/cat $PATH/$FILE.create.log
fi

echo -e "*** END OF JOB ***"


-----Original Message-----
From: Ugo Grandolini <[email protected]>
To: Daevid Vincent <[email protected]>
Cc: Johannes Taxacher <[email protected]>,
[email protected], [email protected]
Subject: Re: MySQL Workbench 5.1.9 Beta available
Date: Wed, 25 Mar 2009 08:45:08 +0100

On Mon, 2009-03-23 at 13:37 -0700, Daevid Vincent wrote: 

> I'm disappointed to see that I STILL cannot connect to a remote database
> to reverse engineer the schema. :-(


as stated in the workbench-en.a4.pdf document:

The Community Edition (OSS)
The Community Edition is the foundation of all MySQL Workbench editions—
versions that are currently available or those that
will become available in the future. All editions of MySQL Workbench are
based on the Community Edition and all future improvements
to the base framework and feature set will be included in this version.
The Community Edition is a full feature product
that puts a powerful database management tool into the hands of the
MySQL community.

The Standard Edition
The Standard Edition is a commercial extension that builds on top of the
OSS Edition and adds modules and plugins, allowing for
an optimized work flow. The highlights of this edition are the added
schema object privilege system, schema validation plugins,
model reporting, and online printing, as well as reverse engineering and
synchronization against live database connections. If you
use the MySQL Workbench in a professional environment upgrading to the
commercial edition can greatly improve your work
flow.

Thus I guess we need to pay to be able to use this tool on a remote db.

I am wondering if SUN may change a bit these conditions allowing to
reverse engineer a db directly without having the need to prepare the
CREATE statements first. If not you may use mysqldump to prepare the
CREATE.sql file and the import it within WB.

I am also on Ubuntu and made a rough bash script (I am not confident
with bash) to automate the process:

#!/bin/bash

PATH=/sbin:/bin:/usr/sbin:/usr/bin
#----------------------------------------
# mysql CREATE statements backup
#
# 25 March 2009
# http://phpcamaleo.org
#----------------------------------------
DATE=`/bin/date +%Y-%m-%d`

usage="\nSave only the CREATE statements of a MySQL database\n\n"
usage+="\tuse: mysql_dumpcreate.sh -p PATH -d DATABASE \n\n"
usage+="\t\t-p\tthe path where you want to save \n"
usage+="\t\t-d\tthe database name \n"

while getopts ":p:d:" opt
do
case $opt in
p  ) PATH=$OPTARG ;;
d  ) DBNAME=$OPTARG ;;
\? ) echo  $usage
     exit 1
esac
done
shift $(($OPTIND - 1))

#echo $OPTIND

if [ $OPTIND -ne 5 ]; then
#
# show the help
#
echo -e $usage
exit 1
fi 

FILE=$DBNAME

echo -e "Creating the database '"$DBNAME"' CREATE statements \nin:
'"$PATH/$FILE.create.sql"'"

if [ -f $PATH/$FILE.create.sql ]; then
/bin/rm -f $PATH/$FILE.create.sql
fi
if [ -f $PATH/$FILE.create.log ]; then
/bin/rm -f $PATH/$FILE.create.log
fi
if [ -f $PATH/$FILE.create.rar ]; then
/bin/rm -f $PATH/$FILE.create.rar
fi
if [ -f $PATH/$FILE.create.rar.log ]; then
/bin/rm -f $PATH/$FILE.create.rar.log
fi

#
# IMPORTANT!
#
# you may use a different character-set...
#
/usr/bin/mysqldump --no-data \
--default-character-set=utf8 --set-charset \
--add-drop-table \
--quote-names \
--quick \
-u {user name} -p{password} \
$DBNAME \
--log-error=$PATH/$FILE.create.log \
> $PATH/$FILE.create.sql

#
# uncomment the following to compress the output file: you need rar or
edit the code to compress in a different format
#
#echo "Compressing the output file " $PATH/$FILE.create.sql " to "
$PATH/$FILE.create.rar
#/bin/rar a -inul $FILE.create.rar $FILE.create.sql
#
#echo -e "Removing the source file " $PATH/$FILE.create.sql 
#/bin/rm $FILE.create.sql

echo -e "*** END OF JOB ***"

you can easily import the generated file in wb :)

have a nice day,
Ugo Grandolini