Visit Our UNIX and Linux User Community


mysql and bash...


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting mysql and bash...
# 1  
Old 01-24-2011
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.

Image

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....
# 2  
Old 01-24-2011
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.

Code:
# 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
# 3  
Old 01-24-2011
Quote:
Originally Posted by Corona688
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.

Code:
# 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...

Code:
#!/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?
# 4  
Old 01-24-2011
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.
# 5  
Old 01-24-2011
... 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:
Code:
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

This User Gave Thanks to otheus For This Post:

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Ubuntu

Bash script for FTP download -Mysql

Hi guys, I recently managed to write up my working script, but now I have a problem. If the file isn't there in the remote server, my actual script jumps it and all ok, but I need something like this: Search file -> if there, then download -> if not, download next file in the list. Any... (7 Replies)
Discussion started by: virtus96
7 Replies

2. Shell Programming and Scripting

Conditional bash/mysql query help

I think(hope) I've got a simple one - I just need to send an email if a mysql query returns any results (ideally - it will never match). Currently I just pipe the mysql query output to the mail program, but of course that emails regardless of the output( and I check this every 10 minutes from... (5 Replies)
Discussion started by: jcass78
5 Replies

3. Shell Programming and Scripting

Technical questions on bash,mysql and pHp

1. bash -bash escape rules, esp. ',", -how to use Ctrl+R reverse cmd search with regex? 2. mysql -how to use grep in mysql 3. php -why !0 is not evaluated to true? what's its value -php getopt: what if there is duplicate in cmdline args (2 Replies)
Discussion started by: John_Peter
2 Replies

4. Shell Programming and Scripting

Update a mysql column via bash script

Hello, I want to check the value of all MySQL columns.(column name is "status") via bash script. If value is "0" at I want to make only single column value to "1" I have many "0" values on mysql database(on "status" column) "0" means it is a draft post. I want to publish a post. I... (2 Replies)
Discussion started by: tara123
2 Replies

5. Shell Programming and Scripting

[BASH] Gawk + MYSQL script

Hello! I've got script to write. It should read databases (names, volumes) from table testdatabase and compares it to actually existing databases in /var/lib/mysql/. If there is no informations about database in table - we should see information "There is no declared informations about database... (1 Reply)
Discussion started by: Zimny
1 Replies

6. Programming

Loop in bash file for mysql

Hi, I'm having problems with a script which some of you helped me with last week. It's a script to check the status of orders in an SQL database, and if the count(*) is zero, then the script stops. If it's non-zero, the script echos the result to a file and then in cron, I cat the file and mail... (3 Replies)
Discussion started by: davidm123SED
3 Replies

7. Shell Programming and Scripting

syntax issue mysql in bash script

I'm running mysql in a bash script mysql <<EOF query EOF one query is like this: UPDATE $dbname.$prefix"config" SET value = $var WHERE "$prefix"config.name = 'table colname'; with variable but it's giving an error i'm not sure what to put for "$prefix"config.name the table... (3 Replies)
Discussion started by: vanessafan99
3 Replies

8. Shell Programming and Scripting

How to run bash script from within MySQL

Can someone tell me the syntax to run a shell script from within MySQL? I know there is a way to do it, but I can't remember the syntax. I thought it was something like: mysql> \. /user/myscript; but I get this error: Failed to open file '/user/myscript;', error: 2 Please help!... (4 Replies)
Discussion started by: peterv6
4 Replies

9. Shell Programming and Scripting

How to get df output into MySQL using bash?

Hi All, I want to run some commands and get the output into a mysql database. I want to run the df cmd into a text file, then import it into my db sysinfo using a bash script. My table "df" has the following 9 fields: server, filesystem, size, used, available, useperc, mounted, date, time... (3 Replies)
Discussion started by: pobman
3 Replies

10. Shell Programming and Scripting

bash mysql export to file

I'm trying to export a mysql query to a .csv file, right now I'm running a successful query like: us_id=`mysql -u $USER_NAME --password=$PASSWORD -D "databasename" \ -e "SELECT * \ FROM databasename.table \ WHERE somefield >0 AND otherfield ='$ctry' \ ORDER BY... (1 Reply)
Discussion started by: unclecameron
1 Replies

Featured Tech Videos