There is some mention in the MySql docs that using the command mysql -e
SELECT… > file_name would allow you to generate the file on the client
machine but I couldn’t seem to get that to work from Rails.
yeah, i didn’t tell you about the -e option because it’s a bit harder to
implement and maintain.
-e just tells mysql to execute the code you want, so you could do a
system call from ruby to path/mysql -e “whatever”. Problem here is that
you were outputting into a file, so with that syntax you will still
write on the server.
You could try and do something like
mysql -u user -pPassword -e “select concat(a,’,’,b) from table” db_name
–skip-column-names -s > output_file
which tells mysql to connect to the db with the user and password and
execute the select and output no column names and in silent mode
(meaning no characters to format the output as an ascii table), and then
you just redirect that output to a file.
That leaves you with a bunch of things to pass as parameters if you want
to do it right (path to mysql, path to the file, db_name, user,
password, which can be taken from the config loaded from database.yml)
and also is sensitive to permissions on the server for the output.
If writing the file directly on the server is something you prefer not
to do (you could always make a NFS entry so you can link to your machine
from your db server) and if you really need to optimize so you cannot go
with the select and concat and write, I’d say it’s the option to go
with. But if you really don’t need it, I’d say it’s the more complex.