shell script - create a mysql database

Question

I am trying to crate a shell script that would:

So far I tried:

#!/bin/bash
mysql -h 111.11.11.11 -u root -p'XXXXXXX'
mysql> CREATE DATABASE foo;

or

echo "CREATE DATABASE foo;";

But no success.

I am using the command line to run the shell script.

# sh create_mysql_database.sh

but all it does is force mysql to connect and wait for input and that is it.

Anyone knows how to actually achieve this?

Server:
Centos 6.2
MySQL client version: 5.1.61

Thanks, Alex

score 5 · Answer 1 · answered Jul 12 '12 at 13:55

5

Id use the mysqladmin command for this type of task:

mysqladmin -u root -pPASSWORD create foo

answered Jul 12 '12 at 13:55

stew

Frederik · Accepted Answer · 2012-07-12T13:50:26.730

3

First of all, there shouldnt be a space between -p and the actual password.

Secondly, as far as I could see from the documentation, you can just add the commands right after the first part, with a -e switch on.

So the final result will look like this:

#!/bin/bash
mysql -h 111.11.11.11 -uroot -pPASSWORD -e "CREATE DATABASE foo;"

edited Jul 12 '12 at 13:50

answered Jul 12 '12 at 13:45

Frederik

Hi @Frederik I already tried it and it doesnt work, it will give an error "ERROR 1049 (42000): Unknown database 'CREATE DATABASE foo;'". In my example above there is no space between the password (-p'XXXXX') and to make sure that it works I tried running it and it connects successfully, but I cannot pass the command inside the shell script. – Alex Jul 12 '12 at 13:49
Check my updated answer :) – Frederik Jul 12 '12 at 13:50

score 2 · Answer 3 · answered Jul 12 '12 at 13:54

2

You should use -e option, like it :

#!/bin/bash
mysql -h 111.11.11.11 -uroot -pPASSWORD -e "CREATE DATABASE foo;"

or :

#!/bin/bash
SQL="CREATE DATABASE foo;"
mysql -h 111.11.11.11 -uroot -pPASSWORD <<<$SQL

answered Jul 12 '12 at 13:54

profy

score 0 · Answer 4 · answered Jul 12 '12 at 16:10

0

Believe it or not, you can create a database without logging into mysql.

For example, to create a DB called foo, try the following:

cd /var/lib/mysql
mkdir foo
chown mysql:mysql foo

and you are done !!!

answered Jul 12 '12 at 16:10

RolandoMySQLDBA

4 Answers4