mysql and external files

A mysql database can be dumped to a file with the mysqldump command.

mysqldump -uusername -ppassword database_name > file.sql

This is particularly useful for backup purposes. The dumped database can then be reloaded with this command:

mysql -uusername -ppassword database_name < file.sql

These two commands can be combined in order to replicate a database to a remote mysql server.

mysqldump -ulocal_username -plocal_password local_database_name | mysql -uremote_username -premote_password remote_database_name

If only some of the database tables need to be dumped, the --tables option can be used.

mysqldump -uusername -ppassword database_name --tables table1 table2 > file.sql

This, however, dumps not only the table data, but also the table creation commands and other information. If we just need to write a table into a CSV file or read table data from a CSV file, we can use the SELECT ... INTO OUTFILE and LOAD DATA INFILE commands.

SELECT columns into outfile 'file_name' FIELD TERMINATED BY 'char' FROM table_name; LOAD DATA INFILE 'file_name' INTO TABLE table_name FIELD TERMINATED BY 'char';

Note that the file name should be placed in quotes and it is preferable to use absolute paths, otherwise mysql will look for the file in the mysql directory (eg /var/lib/mysql/database_name). There is also a nasty complication in this point with the permissions that I haven't figured out yet.

Other interesting options for these two commands are
FIELDS TERMINATED BY 'char' OPTIONALLY ENCLOSED BY 'char'
and
LINES TERMINATED BY 'char'.

The full documentation can be found here.