Friday, May 25, 2007

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

The full documentation can be found here.

No comments: