+ Post New Thread
Results 1 to 10 of 10
How do you do....it? Thread, MYSql - Script batch create users & dbs in Technical; Hi, We are in the process of setting up a mysql server (running on W2k3) for student use and have ...
  1. #1

    Join Date
    Nov 2008
    Location
    Belfast
    Posts
    28
    Thank Post
    0
    Thanked 4 Times in 4 Posts
    Rep Power
    12

    MYSql - Script batch create users & dbs

    Hi,

    We are in the process of setting up a mysql server (running on W2k3) for student use and have to set up a user and db for each student (100's).

    Is it possible to script the creation of user accounts (& set db permission) & script the creation of dbs from a file? UserId and DB name will be thier student ID which I can have in a CSV file already.

    Tried googling but had no luck.

    Any help much appreciated.

  2. #2


    Join Date
    Feb 2007
    Location
    Northamptonshire
    Posts
    4,657
    Thank Post
    350
    Thanked 789 Times in 710 Posts
    Rep Power
    344
    Hi there,

    Couple of ways to do this I suppose, easiest is going to be supplying a batch file to the process.

    From the command prompt you should be able to do

    mysql -u root -p < name_of_file.txt

    In there you'll want the

    CREATE DATABASE usernamehere;
    GRANT ALL PRIVILEGES ON usernamehere.* to usernamehere@localhost identified by 'somerandompassword';

    for each person. (obviously changing the privileges if necessary)


    A quick bit of data manipulation should get you a nasty big list to throw into that file.

    How are you handling the passwords?

  3. #3

    Join Date
    Nov 2008
    Location
    Belfast
    Posts
    28
    Thank Post
    0
    Thanked 4 Times in 4 Posts
    Rep Power
    12
    thanks,

    yeah will only be allowing them a few permission on the DB (SELECT/DELETE/UPDATE/INSERT/CREATE I think). Theyre only doing basic stuff.

    passwords will be set to their DOB (not majorly concerned with security as the server is isolated etc..)

  4. #4


    Join Date
    Feb 2007
    Location
    Northamptonshire
    Posts
    4,657
    Thank Post
    350
    Thanked 789 Times in 710 Posts
    Rep Power
    344
    Ok, should be pretty straight forward to do this!

    How is your CSV constructed?

    EDIT: Just nipping out, will look into this a bit more once I'm back.

  5. #5

    Join Date
    Nov 2008
    Location
    Belfast
    Posts
    28
    Thank Post
    0
    Thanked 4 Times in 4 Posts
    Rep Power
    12
    Quote Originally Posted by kmount View Post
    Ok, should be pretty straight forward to do this!

    How is your CSV constructed?

    EDIT: Just nipping out, will look into this a bit more once I'm back.
    Cheers,

    CSV file just contains 2 fields (username/studentid & password) - permissions will be the same for each user on their appropriate DB (the db name will be their username/studentid)

  6. #6

    Join Date
    Aug 2005
    Location
    London
    Posts
    3,144
    Thank Post
    113
    Thanked 518 Times in 447 Posts
    Blog Entries
    2
    Rep Power
    121
    There was a comment the other day about a test set for a technician which needed them to use Excel - this is a classic example of where you can use a spreadsheet to do much of the donkey work.

    Assuming you've got column A with username, column B with password and the data starts at row 2 then you can put this in C2
    Code:
    ="CREATE DATABASE "& A2 &";"
    and this in D2
    Code:
    ="GRANT ALL PRIVILEGES ON "&A2&".* to "&A2 &"@localhost identified by '"&B2&"';"
    Fill down columns C and D, paste the results into notepad and save each column separately and then just redirect like @kmount suggests.

  7. Thanks to srochford from:

    kmount (14th December 2009)

  8. #7


    Join Date
    Feb 2007
    Location
    Northamptonshire
    Posts
    4,657
    Thank Post
    350
    Thanked 789 Times in 710 Posts
    Rep Power
    344
    Absolutely, good post there Steve, saves me cutting and splitting by delimiter

  9. #8

    Join Date
    Nov 2008
    Location
    Belfast
    Posts
    28
    Thank Post
    0
    Thanked 4 Times in 4 Posts
    Rep Power
    12
    cheers everyone works a treat!

    now any ideas on how to script a backup to backup all dbs (was going to then schedule the script in using a scheduled task).

    Thanks

  10. #9


    Join Date
    Feb 2007
    Location
    Northamptonshire
    Posts
    4,657
    Thank Post
    350
    Thanked 789 Times in 710 Posts
    Rep Power
    344
    Do you want them all in separate files or a single big dump?

    If a single big dump it's fairly easy with mysqldump!

    Something like

    #!/bin/sh
    # backup mysql database
    MyDate='/bin/date +%Y-%m-%d'
    MyLocation='/foo/bar'
    /usr/bin/mysqldump --opt --all-databases -u root -pPASSWORD > "$MyLocation"/db-`$MyDate`.sql
    10 Ways to Automatically & Manually Backup MySQL Database

    But if you want individual ones you're going to need to get a "list" from show databases; and parse that each time to create them.

    I use automysqlbackup on linux, not sure if there's a windows equivalent.

  11. #10
    NikChillin's Avatar
    Join Date
    Nov 2007
    Location
    on the sofa
    Posts
    911
    Thank Post
    54
    Thanked 121 Times in 85 Posts
    Rep Power
    85
    Quote Originally Posted by srochford View Post
    There was a comment the other day about a test set for a technician which needed them to use Excel - this is a classic example of where you can use a spreadsheet to do much of the donkey work.

    Assuming you've got column A with username, column B with password and the data starts at row 2 then you can put this in C2
    Code:
    ="CREATE DATABASE "& A2 &";"
    and this in D2
    Code:
    ="GRANT ALL PRIVILEGES ON "&A2&".* to "&A2 &"@localhost identified by '"&B2&"';"
    Fill down columns C and D, paste the results into notepad and save each column separately and then just redirect like @kmount suggests.
    I know this is an old thread but I am trying to achieve the same thing, however, using MYSQL 5.5, I keep getting syntax errors. I am a novice at this and need to create 150 databases with their own permissions. I will also try the backup listed later too. hopefully have more luck there.

SHARE:
+ Post New Thread

Similar Threads

  1. Tool to bulk create users / create single user easily?
    By reggiep in forum Windows Server 2008
    Replies: 9
    Last Post: 27th May 2010, 12:30 PM
  2. Edit email field in A.D. for batch of users.
    By kennysarmy in forum Windows
    Replies: 4
    Last Post: 1st September 2009, 05:33 PM
  3. Batch FTP Script
    By My220x in forum Scripts
    Replies: 2
    Last Post: 23rd March 2008, 07:48 PM
  4. Replies: 0
    Last Post: 20th August 2007, 02:20 PM
  5. Shutdown batch script stalls if 1 PC is shut down
    By PrimaryTech in forum Scripts
    Replies: 8
    Last Post: 7th July 2007, 06:04 PM

Thread Information

Users Browsing this Thread

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

Posting Permissions

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