Mysql – Create multiple thesql users and databases

MySQL

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

Best Answer

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

Related Solutions

Mysql – Which MySQL users have access to a database

You can append \G to the command to get results displayed in 'grid' veiw

SELECT * FROM mysql.db WHERE Db = '<database name in LC>'\G;

MySQL – Export and Import Privileges to a New Server

Do not mess with the mysql db. There is a lot more going on there than just the users table. Your best bet is the "SHOW GRANTS FOR" command. I have a lot of CLI maintenance aliases and functions in my .bashrc (actually my .bash_aliases that I source in my .bashrc). This function:

mygrants()
{
  mysql -B -N $@ -e "SELECT DISTINCT CONCAT(
    'SHOW GRANTS FOR \'', user, '\'@\'', host, '\';'
    ) AS query FROM mysql.user" | \
  mysql $@ | \
  sed 's/\(GRANT .*\)/\1;/;s/^\(Grants for .*\)/## \1 ##/;/##/{x;p;x;}'
}

The first mysql command uses SQL to generate valid SQL which is piped to the second mysql command. The output is then piped through sed to add pretty comments.

The $@ in the command will allow you to call it as: mygrants --host=prod-db1 --user=admin --password=secret

You can use your full unix tool kit on this like so:

mygrants --host=prod-db1 --user=admin --password=secret | grep rails_admin | mysql --host=staging-db1 --user=admin --password=secret

That is THE right way to move users. Your MySQL ACL is modified with pure SQL.

Best Answer

Related Solutions

Mysql – Which MySQL users have access to a database

MySQL – Export and Import Privileges to a New Server

Related Topic