SQL Tutorial

50 / 68
Previous Index Next  

Database Export

Export of data may be needed to have a backup of the data or to migrate data from one database to another.

Exporting Data with the SELECT ... INTO OUTFILE Statement

  • The file is always created on the server side on MySQL host.
  • Default output format is tab-delimited, linefeed-terminated file.

Example:

select * from emp into outfile '/tmp/emp.txt';

The output format can be customized. For example, to get the output file in CSV format with CRLF-terminated lines, use below code:

select * from emp into outfile '/tmp/emp.txt'
fields terminated by ',' enclosed by '"'
lines terminated by '\r\n';

Exporting Tables using mysqldump program
mysqldump program is used to dump data into files either in raw data format or in create/insert statements format.

Syntax:

$ mysqldump [options] db_name [tbl_name ...]
$ mysqldump [options] --databases db_name ...
$ mysqldump [options] --all-databases

Exporting as Raw Data

  • We specify a directory where output files will be written.
  • Produce tab-separated text-format data files.
  • For each dumped table, mysqldump creates a tbl_name.sql file that contains the CREATE TABLE statement that creates the table, and the server writes a tbl_name.txt file that contains its data.
  • The option value is the directory in which to write the files.

Below command will export data of emp and deptt tables from retail_db database into /tmp directory into files emp.txt and deptt.txt, and will write 'create table' statements into files emp.sql and deptt.sql.

mysqldump -u sqoopuser -p --tab=/tmp retail_db emp,deptt

If --no-create-info option is used, then 'create table' statements files are not created as shown below.

mysqldump -u sqoopuser -p --tab=/tmp retail_db emp,deptt

Exporting as Create/Insert statements

Done using mysqldump but without --tab option.

Exporting certain tables:

Below will export the tables into a file which will have 'create table' statements for the tables and 'insert' statements for the data.

mysqldump -u sqoopuser -p retail_db emp,deptt > emp_dump.txt

Exporting whole database:

mysqldump -u sqoopuser -p retail_db > retail_db_dump.txt

Exporting all databases:

mysqldump -u sqoopuser -p --all-databases > all_database_dump.txt

Copying Tables or Databases to Another Host

Export into a dump file from source database:

mysqldump -u sqoopuser -p retail_db emp,deptt > tables_dump.txt

Import the dump into target database:
Create database retail_db_new before hand on target server.

mysql -u sqoopuser -p retail_db_new < tables_dump.txt

Export / Import directly without intermediate dump file

If you have access to a remote MySQL database from source database server, then you can directly copy databases/tables from current server to a remote server over the network without creating any dump file.

mysqldump -u sqoopuser -p retail_db | mysql -h remote-host.com retail_db_new

No hints are availble for this assesment

Answer is not availble for this assesment

Previous Index Next  

Loading comments...