I'm looking for something like createdb in PostgreSQL or any other solution that would allow me to create database with a help of a shell command. Any hints?
This question is related to
mysql
shell
command-line
You mean while the mysql environment?
create database testdb;
Or directly from command line:
mysql -u root -e "create database testdb";
You can use SQL on the command line:
echo 'CREATE DATABASE dbname;' | mysql <...>
Or you can use mysqladmin
:
mysqladmin create dbname
Connect to DB using base user:
mysql -u base_user -pbase_user_pass
And execute CREATE DATABASE, CREATE USER and GRANT PRIVILEGES Statements.
Here's handy web wizard to help you with statements www.bugaco.com/helpers/create_database.html
The ist and 2nd answer are good but if anybody is looking for having a script or If you want dynamic i.e (db/username/password in variable) then here:
#!/bin/bash
DB="mydb"
USER="user1"
PASS="pass_bla"
mysql -uroot -prootpassword -e "CREATE DATABASE $DB CHARACTER SET utf8 COLLATE utf8_general_ci";
mysql -uroot -prootpassword -e "CREATE USER $USER@'127.0.0.1' IDENTIFIED BY '$PASS'";
mysql -uroot -prootpassword -e "GRANT SELECT, INSERT, UPDATE ON $DB.* TO '$USER'@'127.0.0.1'";
mysqladmin -u$USER -p$PASSWORD create $DB_NAME
Replace above variables and you are good to go with this oneliner. $USER is the username, $PASSWORD is the password and $DB_NAME is the name of the database.
If you create a new database it's good to create user with permissions only for this database (if anything goes wrong you won't compromise root user login and password). So everything together will look like this:
mysql -u base_user -pbase_user_pass -e "create database new_db; GRANT ALL PRIVILEGES ON new_db.* TO new_db_user@localhost IDENTIFIED BY 'new_db_user_pass'"
Where:
base_user is the name for user with all privileges (probably the root)
base_user_pass it's the password for base_user (lack of space between -p and base_user_pass is important)
new_db is name for newly created database
new_db_user is name for the new user with access only for new_db
new_db_user_pass it's the password for new_db_user
Use
$ mysqladmin -u <db_user_name> -p create <db_name>
You will be prompted for password. Also make sure the mysql user you use has privileges to create database.
Source: Stackoverflow.com