windows cmd connection to remote mysql dbf

Question

is there a way of how to connect to mysql dbf on a remote server and run sql queries using windows command line?

Answer 1

You could use telnet, or SSH if you want to be more secure.

Answer 2

If the MySQL is running on Linux or BSD, you need a Telnet or SSH connection through something like putty

This will open a command line on the remote server. The command is mysql. There will be issues around authentication of remote users (as you would expect).

If the remote server is running Windows, you have a whole different set of issues.

I'm not sure you can connect to a remote Windows server and control it this way.

I should say I'm not sure HOW you could connect to a remote Windows server and use it this way. But no doubt it's possible.

Answer 3

MySQL has a command-line client, where you can run queries. If you don't want to allow remote connections to the database on the server, you can still script things into a batch. There are command-line telnet/ssh clients, that either accept external file as a list of commands to run remotely, or you can pass it with the input stream redirection (less then symbol) to them.

When opening a connection to server - most clients are programmed so that the only way to specify the login password is by typing it in from keyboard (yeah, they don't use default input stream). Things like that make it hard to script it. However, it may be possible to set up a certificate based login on SSH - you'd actually have to research that.

If the server that's hosting the MySQL database is also a web server - you could also think about putting some script (PHP, Perl, Python, Ruby - whatever you like) on the password protected area, that would allow you to execute queries by simply making a HTTP(S) queries on that script. Although, Windows doesn't have a command-line HTTP(S) client, you can always get something like wget.exe and perform queries with it. Note, that if you choose this approach - I strongly advice to put that script under HTTPS - if discovered by malicious user, it could be lethal to your data.

Answer 4

Yes, you can connect to a different host by running mysql -h 123.45.67.89. Please note that there are a few security implications:

1. You will have to grant yourself access. You will need to run something like GRANT ALL on db_name.table TO user@your_ip IDENTIFIED BY 'password'. db_name, table and your_ip can be * but beware of opening your server to hackers.
2. You will have to open your server's firewall if you are not on the same LAN. Again, ymmv and you should be aware not to open the door to exploits.
3. You may want to use SSL and use secure-auth in order to protect your traffic and credentials.

Hope that helps.