Using mysql 5.5 on Ubuntu 12.04, what's the easiest way to create 30 new users with their own database?
I know how to use the create user command
Script it in bash, probably. Take a list of users, and do something like the following (you will need to modify this heavily):
for line in $ (cat users.txt) do
mysql < create database $line;
mysql < create user $line@localhost identified by defaultpassword;
mysql < grant all privileges on $line to $line@'localhost;
done
#!/bin/bash
UNAMEBASE="drupal"
# number of users to create
COUNT=40
i=1
BTICK='`'
SQL=''
echo "Database User Password" > ./drupal_training_users.txt
until [ $i -gt $COUNT ]; do
UNAME="$UNAMEBASE$i"
UDB="drupal_$i"
UPASS=$(makepasswd --char=8)
echo "$UDB $UNAME $UPASS" >> ./drupal_training_users.txt
Q1="CREATE DATABASE IF NOT EXISTS $UDB;"
Q2="GRANT ALL ON ${BTICK}$UDB${BTICK}.* TO '$UNAME'@'localhost' IDENTIFIED BY '$UPASS';"
SQL="$SQL${Q1}${Q2}"
let i=i+1
done
SQL="$SQL FLUSH PRIVILEGES;"
mysql -u root -p -e "$SQL"
I have adapted this script for create multiple mysql users and databases of a text file.
In this case, users name and db name is the same.
Syntanx text file is similar:
id1010
id2020
id3030
#!/bin/bash
BTICK='`'
SQL=''
File="users.txt"
Lines=$(cat $File)
for Line in $Lines
do
echo "$Line"
UNAME="$Line"
UDB="$Line"
UPASS="123456"
Q1="CREATE DATABASE IF NOT EXISTS $UDB;"
Q2="CREATE USER $UNAME@'%' IDENTIFIED BY '$UPASS';"
Q3="GRANT ALL ON ${BTICK}$UDB${BTICK}.* TO '$UNAME'@'%';"
SQL="$SQL${Q1}${Q2}${Q3}"
done
SQL="$SQL FLUSH PRIVILEGES;"
mysql -u root -p -e "$SQL"