mysql and bash...

Ok the issue I have is a bit complicated, for me, and my understanding of shell programming and bash is non-existent.

Background: I do web development for a team of programmers that make custom kernels for the Android OS. The guys are using a bash script (through gerrit or github, like I said i'm retarded in this area) that automatically uploads a compiled .zip to multiple servers for downloads and mirrors. On the server I am using php/mysql to pull information from the database to populate tables with information. The screenshot below is a sample of the display output.

What I need to do is tack onto the bash script the appropriate code to connect to the mysql database, insert values to the db, and close the db.

I've checked a lot of online resources and there aren't many good examples/usage from me to work from, and I don't have an extra box to load ubuntu on to test things so i'm shooting for accuracy the first time we try the script.

Thanks in advance for any help....

The 'mysql' command is your gateway from shell to mysql. Feed it one or more SQL statements (seperated by ; if there's more than one) on stdin and it does queries and returns results on stdout.

# example using no databases
$ echo "SHOW DATABASES;" | mysql -uusername
Database
information_schema
gate
satellite
solar
# example that uses the 'solar' database
$ echo "SHOW TABLES" | mysql -uusername solar
$

...the 'solar' database happens to be empty right now but you get the idea.

You can also supply password with -ppassword and so forth. See its myriad options in man mysql

So essentially if I need to add values to the database...

#!/bin/bash
MyHostName="mysql.website.com";
MyUserName="sqluser";
MyPassWord="sqlpass";
Value="John";
Attribute="Tall";
Something="FooBar";
CMD="INSERT into tablename VALUES ($Value,$Attribute,$Something)";
mysql -h $MyHostName -u $MyUserName -p $MyPassWord" -e $CMD; 

This would work?

I'd quote CMD like "${CMD}" to make sure spaces didn't split it into multiple arguments, do -uUSERNAME without the space, do -pPASSWORD without the space, and get rid of that extra quote after it, etc. But yes, you're getting the idea.

The hostname can be omitted when you're running it on the same system, i.e. almost almost always. Most mysql servers are connected to on a local socket, not a network one, and have network connections disabled by default.

... and adding to Corona...
You can create a .my.cnf in the user's home directory, and store the username/password there, that way it doesn't show up in the process/command table.

You could also do:

Value=X
Attribute=Y
Something=Z

mysql <<"EOF"
create table tablename ( Value : int, Attribute: varchar(80), xtra : varchar(80) ) 
INSERT into tablename VALUES ($Value,$Attribute,$Something)
EOF
1 Like