What is the best and lowest resource way to connect to a remote mysql server (server1.example.com) and execute queries likeINSERT 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?
Connect to a remote mysql server with bash php or perl
bash;php;perl
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 namesYou can turn off this feature to get a quicker startup with -AWelcome to the MySQL monitor.  Commands end with ; or \g.Your MySQL connection id is 4927647Server 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 itsaffiliates. Other names may be trademarks of their respectiveowners.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.htmlIs 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.