User login

In a shell script, create a database if it does not exist

Searched words: 
mysql create database from bash script mysql if database exists automate creation of database if it doesn't already exist PHP script bash script if mysql database does not exist create

Use the MySQL: SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME = 'database_name'

<?php
if (mysql_num_rows(mysql_query("SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME = '". $dbname ."'"))) {
  echo "Database $dbname already exists.";
}
else {
  // go on and create the user, database, etc.
  // create the user

  if (!mysql_query("CREATE USER '". $dbuser ."'@'%' IDENTIFIED BY '". $dbpass ."'"))  die(mysql_error());
  echo "User $dbuser created.\n";

  // does this do anything
  if (!mysql_query("GRANT USAGE ON * . * TO '". $dbuser ."'@'%' IDENTIFIED BY '". $dbpass ."' WITH MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 0")) die(mysql_error());
  echo "Granted usage.\n";
 
  // create the database.
  // CREATE DATABASE `livingcon_live` ;
  if (!mysql_query("CREATE DATABASE `". $dbname. "`"))  die(mysql_error());
  echo "Database $dbname created.\n";
 
  // GRANT ALL PRIVILEGES ON `livingcon_live` . * TO 'livingcon_live'@ '%';  -- spaces don't work b/n @ and %
  if (!mysql_query("GRANT ALL PRIVILEGES ON `". $dbname ."` . * TO '". $dbuser ."'@'%'"))  die(mysql_error());
  echo "Granted privileges of user $dbuser to database $dbname \n";
 
  echo "Copy and save this password for use on the Drupal install page, if needed:\n $dbpass \n";
}
?>

That's pretty much the whole PHP abomination which can be found at scripts/_agaric_makedatabase.sh

Alternate, better methods of creating the database, not used at the moment:

Bash: http://drupal.org/node/196417 (First Draft of BASH script for creating new multisite)
Perl: system('mysql -u *** -p -e \'create database activity_1_'.$date.'\'');

For another way of checking if a database exists, see:

http://articles.techrepublic.com.com/5100-10878_11-1045433.html

Resolution

Comments

Bash script to check for a db

dbuser=user
dbpass=pass
#STEP 1 - CREATE DB
#check if db exists first
echo "Creating a database for $newdb"
DBS=`mysql -u$dbuser -p$dbpass -Bse 'show databases'| egrep -v 'information_schema|mysql'`
for db in $DBS; do
if [ "$db" = "$newdb" ]
then
echo "This database already exists : exiting now"
    exit
  fi
done
mysqladmin -u$dbuser -p$dbpass create $newdb;
echo "Database $newdb created"

How to check if a mysql database exists or nor via Bash script

I found this easier:

check if db exists first: databases in MySQL are folders in the filesystem; what makes it easy to find weather a database exists or not:

if test -d "/var/lib/mysql/$dbname"; then
echo -e "Database \"$dbname\" already exists"
else ...

Post new comment

The content of this field is kept private and will not be shown publicly.
  • You may post code using <code>...</code> (generic) or <?php ... ?> (highlighted PHP) tags.
  • You can use Markdown syntax to format and style the text. Also see Markdown Extra for tables, footnotes, and more.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Allowed HTML tags: <a> <em> <strong> <cite> <code> <ul> <ol> <li> <dl> <dt> <dd> <img> <blockquote> <small> <h2> <h3> <h4> <h5> <h6> <sub> <sup> <p> <br> <strike> <table> <tr> <td> <thead> <th> <tbody> <tt> <output>
  • Syntax highlight code surrounded by the {syntaxhighlighter SPEC}...{/syntaxhighlighter} tags, where SPEC is a Syntaxhighlighter options string or "class="OPTIONS" title="the title".
  • Lines and paragraphs break automatically.

More information about formatting options

By submitting this form, you accept the Mollom privacy policy.