As usual there are a number of ways this can be done. However I wanted something quick and simple that could be run from anywhere and did not need access to the server, hence I used the following commandmysql --execute "select * from databasename.tablename" --batch --raw
This command does assume you are on the server and the user you are logged in as has permissions on the database, as that is probably not the case see MySQL for the switches to use to specify a hostname, username and password. In reference to the above, the following will help:
--execute is obvious, it runs the SQL query--batch means do not format as a table with pipe characters but tab delimited--raw means output special characters as they should be and not \r\n, which may or may not be what you want!--skip-column-names does what it says, it does not output column namesIn reality you probably want to do something like this:mysql --execute "select * from databasename.tablename" --batch --raw > output.txt
Which will write this "tab separated data" into a file. However if your query is more complicated then the following works:mysql --batch --raw < my_query.sql > output.txt
Do make sure the database name is specified in the .sql file, otherwise just add "--database=my_database" to the command line.
There is another way of doing this, which is to use SELECT . . . INTO OUTFILE... however it writes to files on the server, which is fine if you have the access. See MySQL :: MySQL 5.7 Reference Manual :: 13.2.9.1 SELECT ... INTO Syntax for more details on this.
Another way to export data is to use the mysqldump utility. This has a host of options but some useful examples are:mysqldump --database my_db --tables table_1 table_2 - this exports the schema and data for two tablesmysqldump --database my_db --no-data - export the whole database but only the schema, no data
There are more options but this is a good starting point.