Results 1 to 4 of 4

Thread: MYSQL database script

  1. #1
    Senior Member
    Join Date
    Jun 2005
    Posts
    194
    Thanks
    39
    Thanked 13 Times in 10 Posts
    Rep Power
    10

    Default MYSQL database script

    I need mysql database script that can create database and user for each database with random password? How do I proceed with this? any help and pointers are appreciated in advance.

  2. #2
    Never say die nixcraft's Avatar
    Join Date
    Jan 2005
    Location
    BIOS
    Posts
    4,498
    Thanks
    17
    Thanked 788 Times in 506 Posts
    Rep Power
    10

    Default

    Try
    Code:
    #!/bin/bash
    # Copyright (c) Vivek Gite <vivek@nixcraft.com>
    # ----------------------- 
    MYSQLADM="root"
    MYSQLADMPASS="YOUR-ROOT-PASSWORD-HERE"
    MYSQLADMIP="localhost"
    MYSQL=/usr/bin/mysql
    
    genpasswd() { 
        local l=$1
               [ "$l" == "" ] && l=16
              tr -dc A-Za-z0-9_ < /dev/urandom | head -c ${l} | xargs 
    }
    
    usage(){
        echo "Usage: $0 db-name db-user"
        exit 1
    }
    
    [ $# -ne 2 ] && usage
    
    
    dbname=$1
    dbuser=$2
    dbpasswd=$(genpasswd)
    
    /usr/bin/mysql -u "$MYSQLADM" -h "$MYSQLADMIP" -p"$MYSQLADMPASS" mysql -e "CREATE DATABASE $dbname; GRANT ALL ON  $dbname.* TO $dbuser@localhost IDENTIFIED BY '$dbpasswd';"
    
    echo "Login information:"
    echo "+ Database name : $dbname"
    echo "+ Database '$dbname' username : $dbuser"
    echo "+ Database '$dbname' password : $dbpasswd"
    echo "+ To login to '$dbname' use the following command:"
    echo "mysql -u $dbuser -h $MYSQLADMIP -p'$dbpasswd' $dbname"
    Save and close the script. Run it as follows
    Code:
    chmod +x mysql.sh
    ./mysql.sh dbnane dbuser
    Create foo database with bar username and random password.
    Code:
    ./mysql.sh foo bar
    In the end it will provide output line this:
    Code:
     Login information:
    Database name : foo
    Database 'foo' username : bar
    Database 'foo' password : rqt2f2ZcwZ9SuEOh
    MySQL Login command for user: mysql -u bar -h localhost -p'rqt2f2ZcwZ9SuEOh' foo
    All [Solved] threads are closed by mods / admin to avoid spam issues. See Howto mark a thread as [Solved]


  3. The Following User Says Thank You to nixcraft For This Useful Post:

    jerry (15th July 2009)

  4. #3
    Senior Member
    Join Date
    Jun 2005
    Posts
    194
    Thanks
    39
    Thanked 13 Times in 10 Posts
    Rep Power
    10

    Default

    Thanks. If foo already exists it will create user and db again? how can i stop script if db already exists in server?

  5. #4
    Never say die nixcraft's Avatar
    Join Date
    Jan 2005
    Location
    BIOS
    Posts
    4,498
    Thanks
    17
    Thanked 788 Times in 506 Posts
    Rep Power
    10

    Default

    You can try something as follows:
    Code:
    #!/bin/bash
    # Copyright (c) Vivek Gite <vivek@nixcraft.com>
    # ----------------------- 
    MYSQLADM="root"
    MYSQLADMPASS="YOUR-PASSWORD-HERE"
    MYSQLADMIP="localhost"
    MYSQL=/usr/bin/mysql
    
    genpasswd() { 
        local l=$1
               [ "$l" == "" ] && l=16
              tr -dc A-Za-z0-9_ < /dev/urandom | head -c ${l} | xargs 
    }
    
    usage(){
        echo "Usage: $0 db-name db-user"
        exit 1
    }
    
    is_db_exists(){
        local d=$1
        /usr/bin/mysql -u "$MYSQLADM" -h "$MYSQLADMIP" -p"$MYSQLADMPASS"  -e "use $d;" 2>/dev/null
        if [ $? -eq 0 ]
        then
            echo "$d database exists. Cannot create a new one.."
            exit 2
        fi
    }
    [ $# -ne 2 ] && usage
    
    
    dbname=$1
    dbuser=$2
    dbpasswd=$(genpasswd)
    
    is_db_exists $dbname
    
    /usr/bin/mysql -u "$MYSQLADM" -h "$MYSQLADMIP" -p"$MYSQLADMPASS" mysql -e "CREATE DATABASE $dbname; GRANT ALL ON  $dbname.* TO $dbuser@localhost IDENTIFIED BY '$dbpasswd';"
    
    echo "Login information:"
    echo "+ Database name : $dbname"
    echo "+ Database '$dbname' username : $dbuser"
    echo "+ Database '$dbname' password : $dbpasswd"
    echo "+ To login to '$dbname' use the following command:"
    echo "mysql -u $dbuser -h $MYSQLADMIP -p'$dbpasswd' $dbname"
    All [Solved] threads are closed by mods / admin to avoid spam issues. See Howto mark a thread as [Solved]


Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Import SQL Database to MySQL!
    By nabila in forum Databases servers
    Replies: 6
    Last Post: 27th August 2009, 12:39 PM
  2. Mysql Log for specific user or database ?????
    By demuytree in forum Databases servers
    Replies: 2
    Last Post: 8th July 2009, 09:24 AM
  3. [Solved] MySQL Rename Database SQL Command
    By myfoot in forum Databases servers
    Replies: 1
    Last Post: 30th August 2008, 05:54 PM
  4. need to convert text files into mysql database
    By zafar466 in forum Databases servers
    Replies: 2
    Last Post: 12th July 2008, 12:31 PM
  5. ssh backup particular mysql table / database
    By brothers in forum Databases servers
    Replies: 11
    Last Post: 25th October 2007, 05:06 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •