MYSql - Script batch create users & dbs

[peterdoherty]

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.

[kmount]

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?

[peterdoherty]

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..)

[kmount]

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.

[peterdoherty]

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)

[srochford]

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

="CREATE DATABASE "& A2 &";"

and this in D2

="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.

[kmount]

Absolutely, good post there Steve, saves me cutting and splitting by delimiter

[peterdoherty]

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

[kmount]

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.