How To Create Database Using Mysql Command Line

8 Answers 8

You mean while the mysql environment?

                create database testdb;

Or directly from command line:

                mysql -u root -e "create database testdb";

answered Mar 11 '10 at 20:27

Felix KlingFelix Kling

733k 167 gold badges 1032 silver badges 1090 bronze badges

mysql -u root -e "create database testdb"; Thats what i was looking for :) thank you

Mar 11 '10 at 21:07
In case you have password, just add -p. After pressing enter, you will be asked your password. The following is an example using my user which is root (change it if you have anoter username) and it also uses the name "oc_local" for the database (change it if you want your database to be called in a different way)------> mysql -u root -p -e "create database oc_local";

Nov 12 '13 at 1:48
hi @koszikot -- if this is what you were looking for, you should accept it as the correct answer :-)

Aug 29 '18 at 16:56
the semicolon should be inside quotes

Aug 31 '20 at 19:01

                cat filename.sql | mysql -u username -p # type mysql password when asked for it

Where filename.sql holds all the sql to create your database. Or...

                echo "create database `database-name`" | mysql -u username -p

If you really only want to create a database.

Matthew

8,221 11 gold badges 60 silver badges 80 bronze badges

answered Mar 11 '10 at 20:29

KrisKris

37.4k 9 gold badges 71 silver badges 96 bronze badges

I get this error ERROR 1046 (3D000) at line 27: No database selected when I run echo "create database databasename" | mysql -u -u username -p command

Mar 15 '13 at 5:16
@keerthi: perhaps your user doesn't have privileges on any existing schema?

Mar 15 '13 at 9:40
It's because there are two -u flags

Aug 20 '13 at 16:37
@Kris Can I read database name from terminal and use the variable value as database name in create database.

Mar 11 '16 at 13:16

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

answered Jul 16 '13 at 12:06

jmarcelijmarceli

16.9k 5 gold badges 60 silver badges 58 bronze badges

Massive. Just added this great one liner to dashdocs snippets. In case anyone has a root password with spaces: -p"root password here" works fine

Jun 9 '17 at 0:13

                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.

answered Dec 2 '13 at 7:52

karlingenkarlingen

12.3k 5 gold badges 38 silver badges 67 bronze badges

came back to this question many years later and noticed I'd upvoted it :D

May 6 '20 at 22:56
i keep coming back here...

Sep 21 at 3:10
@Isaac Understandable! xD

Sep 21 at 6:54

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.

answered Aug 11 '15 at 19:08

WillaWilla

671 1 gold badge 7 silver badges 14 bronze badges

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'";

answered Apr 8 '17 at 21:05

salah-1salah-1

1,164 10 silver badges 15 bronze badges

This will fail if you include any .

Sep 16 at 23:04

You can use SQL on the command line:

              echo 'CREATE DATABASE dbname;' | mysql <...>

Or you can use mysqladmin:

              mysqladmin create dbname

answered Mar 11 '10 at 20:26

Lukáš LalinskýLukáš Lalinský

38.8k 6 gold badges 96 silver badges 118 bronze badges

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

answered Mar 3 '15 at 2:36

Not the answer you're looking for? Browse other questions tagged mysql shell command-line or ask your own question.