Issue
I need to use a table of a database on a university server for test data for a project. I'm pretty new to databases and MySQL. My professor has send me username and password for the server. And an extra username&password for the MySQL server. It took me a while but in the end I was able to connect to the server over ssh and then managed to navigate to $cd / $cd usr/bin/MySQL then logged in and found the data/sentences in a table in one of the databases.
Now there is the question: How do I get the data on my computer? I thought about a python script. But I cannot write a script what is logging in on a different server and then navigates to the MySQL folder to log in there to copy somehow the sentences in the table to a txt file I can use?
Solution
You might not need to ssh into the remote server; depending how their server and database are set up you may be able to connect a mysql client on your local machine to the database server. While there are security advantages to limiting where connections are permitted from, accessing a database at localhost is actually just a special case.
You might not even need a python script. You can export directly from mysql to a text file, or your client may have a feature to copy data directly from the remote server into a local database.
I would guess that something like this would work for you, although getting the output into the format you want can be tricky:
mysql -h "host address" -u "username" -p -e "SELECT * FROM `table`" > localFile.txt
If you wanted to do it with a python script running on the server as you're describing, you'll want to use the ssh credentials to do FTP over SSH to get the files back and forth. Your FTP client will certainly support that.
Answered By - ShapeOfMatter Answer Checked By - Marie Seifert (WPSolving Admin)
