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 ...
14th December 2009, 11:41 AM #1
- Rep Power
MYSql - Script batch create users & dbs
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.
14th December 2009, 12:00 PM #2
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?
14th December 2009, 12:16 PM #3
- Rep Power
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..)
14th December 2009, 12:19 PM #4
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.
14th December 2009, 12:24 PM #5
- Rep Power
Originally Posted by kmount
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)
14th December 2009, 02:32 PM #6
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
and this in D2
="CREATE DATABASE "& A2 &";"
Fill down columns C and D, paste the results into notepad and save each column separately and then just redirect like @kmount suggests.
="GRANT ALL PRIVILEGES ON "&A2&".* to "&A2 &"@localhost identified by '"&B2&"';"
Thanks to srochford from:
kmount (14th December 2009)
14th December 2009, 04:06 PM #7
Absolutely, good post there Steve, saves me cutting and splitting by delimiter
14th December 2009, 08:31 PM #8
- Rep Power
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).
14th December 2009, 08:46 PM #9
Do you want them all in separate files or a single big dump?
If a single big dump it's fairly easy with mysqldump!
10 Ways to Automatically & Manually Backup MySQL Database
# backup mysql database
/usr/bin/mysqldump --opt --all-databases -u root -pPASSWORD > "$MyLocation"/db-`$MyDate`.sql
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.
15th January 2014, 11:06 AM #10
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.
Originally Posted by srochford
By reggiep in forum Windows Server 2008
Last Post: 27th May 2010, 01:30 PM
By kennysarmy in forum Windows
Last Post: 1st September 2009, 06:33 PM
By My220x in forum Scripts
Last Post: 23rd March 2008, 08:48 PM
By baronne in forum Scripts
Last Post: 20th August 2007, 03:20 PM
By PrimaryTech in forum Scripts
Last Post: 7th July 2007, 07:04 PM
Users Browsing this Thread
There are currently 1 users browsing this thread. (0 members and 1 guests)