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.

[NikChillin]

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.

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.