The beginning of a MySQL database script

Here is some code that I use at the beginning of a MySQL database script to (a) create a database, (b) create a local user to access that database ('foo_user'@'localhost'), (c) create a remote user that can access the database ('foo_user'@'%'), and (d) then use that database (which I need to do before starting a bunch of CREATE TABLE statements):

CREATE DATABASE foo;

GRANT ALL PRIVILEGES
ON foo.*
TO 'foo_user'@'localhost'
IDENTIFIED BY 'foo_pass'
WITH GRANT OPTION;

GRANT ALL PRIVILEGES
ON foo.*
TO 'foo_user'@'%'
IDENTIFIED BY 'foo_pass'
WITH GRANT OPTION;

USE foo;

As mentioned, after this point in my script I would follow these statements with all of my CREATE TABLE statements to create my MySQL tables, and INSERT statements to populate my initial database data.

 

Comments

Permalink

Would you be interested in adapting this script to loop and create a variable number of databases?

And then populate the databases from an SQL file?

Could be two scripts.

Thanks, Warren

My mysql command skills aren't good enough to do this, but I think you could write a Unix shell script to accomplish this with the mysqladmin command.

Actually, I think you might have to create the databases with mysqladmin, then import your data with mysql or mysqlimport, but if that's okay, and you don't mind having all your passwords in those scripts, sure, I think this can be done.