MySQL: Create/Drop DB and user

This tip contains a couple of MySQL scripts that allow me to create and drop a database and a user that will “own” said database.

They are accompanied by a couple of ant tasks that will allow you to replace the tokens for dbname, etc.

By themselves, the SQL scripts illustrate how you need to provide the appropriate grants (and how to clear the grants when bouncing the db from scratch.

Note: Listing 3 also runs a ficticious “schema.sql” file.

Listing 1.- Create Database and user


-- DO NOT RUN THIS FILE DIRECTLY.

-- Use the accompanying ant script to create the database.
-- Those ant scripts will set up replace the tokens for

-- dbname, dbuser and dbpassword.
CREATE DATABASE _DBNAME_;
GRANT ALL ON _DBNAME_.* TO _DBUSER_@'%' IDENTIFIED BY '_DBPASSWORD_';
FLUSH PRIVILEGES

Listing 2.- Drop Database and user


-- DO NOT RUN THIS FILE DIRECTLY.

-- Use the accompanying ant script to drop the database.
-- Those ant scripts will set up replace the tokens for

-- dbname, dbuser and dbpassword.
REVOKE ALL ON _DBNAME_.* FROM _DBUSER_;
DROP DATABASE _DBNAME_;
DROP USER _DBUSER_;
FLUSH PRIVILEGES;

Listing 3: Ant taks for create and drop


    

    < property name="jdbc.user.databasename" value="homeinv"/>

    < property name="jdbc.user.name" value="homeinv"/>

    < property name="jdbc.user.password" value="inventory"/>

    < property name="jdbc.user.url" value="jdbc:mysql://localhost/${jdbc.user.databasename}"/>

    

    < property name="jdbc.superuser.name" value="root"/>

    < property name="jdbc.superuser.password" value="change this password"/>

    < property name="jdbc.superuser.url" value="jdbc:mysql://localhost/"/>

    < property name="jdbc.driver.classname" value="org.gjt.mm.mysql.Driver"/>

    < property name="jdbc.jar" value="${lib.dir}/mysql-connector-java-3.1.7-bin.jar"/>

    < property name="database.type" value="mysql"/>

    < property name="tmp.dir" value="${java.io.tmpdir}/${project.name}"/>
    

    < target name="create_database" description="Creates the database schema">

      < delete dir="${tmp.dir}/${database.type}"/>

        < mkdir dir="${tmp.dir}/${database.type}"/>

        < copy file="${database.dir}/${database.type}/_createdb.sql" tofile="${tmp.dir}/${database.type}/_createdb.sql"/>

        < replace file="${tmp.dir}/${database.type}/_createdb.sql">

          < replacefilter token="_DBNAME_" value="${jdbc.user.databasename}"/>

          < replacefilter token="_DBUSER_" value="${jdbc.user.name}"/>

          < replacefilter token="_DBPASSWORD_" value="${jdbc.user.password}"/>

        < /replace>
        < copy file="${database.dir}/${database.type}/schema.sql" tofile="${tmp.dir}/${database.type}/schema.sql"/>

            < replace file="${tmp.dir}/${database.type}/schema.sql">

            

            < replacefilter token="_DBNAME_" value="${jdbc.user.databasename}"/>

            < replacefilter token="_DBUSER_" value="${jdbc.user.name}"/>

            < replacefilter token="_DBPASSWORD_" value="${jdbc.user.password}"/>

          < /replace>
          < sql url="${jdbc.superuser.url}"  userid="${jdbc.superuser.name}" password="${jdbc.superuser.password}" driver="${jdbc.driver.classname}"
    	classpath="${jdbc.jar}" src="${tmp.dir}/${database.type}/_createdb.sql"/>

          < echo>DATABASE CREATED< /echo>
          < sql url="${jdbc.user.url}"  userid="${jdbc.user.name}" password="${jdbc.user.password}" driver="${jdbc.driver.classname}"
		classpath="${jdbc.jar}" src="${tmp.dir}/${database.type}/schema.sql">

          < /sql>

          < echo>SCHEMA CREATED< /echo>

    < /target>
    

    < target name="drop_database" description="Drops the database schema">

      < delete dir="${tmp.dir}/${database.type}"/>

      < mkdir dir="${tmp.dir}/${database.type}"/>

      < copy file="${database.dir}/${database.type}/_dropdb.sql" tofile="${tmp.dir}/${database.type}/_dropdb.sql"/>

        < replace file="${tmp.dir}/${database.type}/_dropdb.sql">

          < replacefilter token="_DBNAME_" value="${jdbc.user.databasename}"/>

          < replacefilter token="_DBUSER_" value="${jdbc.user.name}"/>

          < replacefilter token="_DBPASSWORD_" value="${jdbc.user.password}"/>

        < /replace>

        < sql url="${jdbc.superuser.url}"  userid="${jdbc.superuser.name}" password="${jdbc.superuser.password}" driver="${jdbc.driver.classname}"
    	classpath="${jdbc.jar}" src="${tmp.dir}/${database.type}/_dropdb.sql">

        < /sql>

        < echo>DATABASE DROPPED< /echo>

    < /target>
    < target name="rebuild_database" depends="drop_database,create_database" description="Rebuilds the database (drops it and recreates it again)">

    < /target>