Take the 2-minute tour ×
Unix & Linux Stack Exchange is a question and answer site for users of Linux, FreeBSD and other Un*x-like operating systems.. It's 100% free, no registration required.

What is the best and lowest resource way to connect to a remote mysql server (server1.example.com) and execute queries like

INSERT into SERVICES (user, id) ($user, $id)

from server2.example.com?

Is it better/easier to use PHP or Perl rather than Bash? (I want to be able to easily run it from the terminal.)

Anyone know a good template script for it?

share|improve this question

1 Answer 1

up vote 2 down vote accepted

If you're running it from the terminal, consider using the mysql-client. My example below shows login prompting for the password, but the password could be input as a command line parameter or from file. I think you should be able to authenticate with your public key as well....? Feel free to correct me on that.

Anyway here's how I do it:

morten@desktop:~$ mysql -u USERNAME -p -h HOSTNAME DATABASENAME 
Enter password: 
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4927647
Server version: 5.1.61-0+squeeze1 (Debian)

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> 

....and now just fire away with the queries! :)

You can make it take input from files or streams. Check out the devs article on the batch mode:

http://dev.mysql.com/doc/refman/5.0/en/batch-mode.html

Is it better/easier to use PHP or Perl rather than Bash? (I want to be able to easily run it from the terminal) Anyone know a good template script for it?

I use whatever language I happen to use for what else I'm running on the server. My preference is Python and IPython - it removes me from some of the indignities of the shell ;) - but I've written a lot of straight-forward shell scripts to do what you're asking about. Do what you're comfortable with. The overhead of choosing a language to do database queries is insignificant in most cases.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.