MySQL Import Export

There will come a time that you need a database copy from one server to another specially when you're doing some test and other stuffs on your data. Below are some tips I've used when doing a database export or import using MySQL. I've also posted some links that I referred too.

Exporting Data

The mysqldump utility – is one way of database backup to be able to transfer an instance to another database or server. It contains create table as well populating the data. It requires the following: SELECT privileges for the dump tables; SHOW VIEW for dumped views; LOCK TABLES if the --single-transaction option is not used;

SYNTAX:

mysqldump -p -uusername database_name table_name > Filename

Example:

1.    Login to instance

[root@localhost mysql]# bin/mysql -uadmin -p

Enter password:

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 5

Server version: 5.0.95-log MySQL Community Server (GPL)

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use presentation

Database changed

mysql> show tables

    -> ;

+------------------------+

| Tables_in_presentation |

+------------------------+

| innodb_table           |

| myisam_table           |

| mysqlimport_tab        |

+------------------------+

3 rows in set (0.01 sec)

mysql> exit

Bye

2.    Dumping a single table in a database.

[root@localhost mysql]# bin/mysqldump -p --lock-all-tables -uadmin presentation myisam_table > dump_presentation.sql

Enter password:

3.    Dumping all databases

[root@localhost mysql]# bin/mysqldump -p --lock-all-tables -uadmin --all-databases > dump_presentation_all.sql

Enter password:

Note: If the Location is not stated, the dump file will be created on the mysql base or home directory.

[root@localhost mysql]# ls -lrt

total 20692

-rw-r--r-- 1 root  mysql    2552 Dec 16  2011 README

-rw-r--r-- 1 root  mysql   19071 Dec 16  2011 COPYING

-rw-r--r-- 1 root  mysql    6387 Dec 16  2011 INSTALL-BINARY

-rwxr-xr-x 1 root  mysql    1153 Dec 17  2011 configure

drwxr-xr-x 4 root  mysql    4096 Dec 17  2011 man

drwxr-xr-x 2 root  mysql    4096 Dec 17  2011 docs

drwxr-xr-x 2 root  mysql    4096 Dec 17  2011 tests

drwxr-xr-x 3 root  mysql    4096 Dec 17  2011 share

drwxr-xr-x 2 root  mysql    4096 Dec 17  2011 support-files

drwxr-xr-x 5 root  mysql    4096 Dec 17  2011 sql-bench

drwxr-xr-x 2 root  mysql    4096 Dec 17  2011 scripts

drwxr-xr-x 2 root  mysql    4096 Dec 17  2011 bin

drwxr-xr-x 9 root  mysql    4096 Dec 17  2011 mysql-test

drwxr-xr-x 3 root  mysql    4096 Dec 17  2011 include

drwxr-xr-x 2 root  mysql    4096 Dec 17  2011 lib

-rw-r--r-- 1 root  root  8390761 Aug 15 16:15 myisam_dump_tabs.sql

-rw-rw-rw- 1 mysql mysql 4194304 Aug 15 16:22 myisam_dump_tabs.txt

-rw-r--r-- 1 root  root      750 Aug 28 06:40 dump_testisam.sql

-rw-r--r-- 1 root  root      750 Aug 28 07:21 dump_presentation.sql

drwxr-x--- 5 mysql mysql    4096 Aug 28 07:21 data

-rw-r--r-- 1 root  root  8390761 Aug 28 07:21 dump_presentation_all.sql

The use of SQL commands – is another way of database backup. It uses the command SELECT ... INTO OUTFILE to write the selected row to a file in formats like CSV, XML(applicable to 5.5 and higher versions).

SYNTAX:

SELECT * FROM Table_Name INTO OUTFILE ‘Filename';

SELECT * FROM Table_Name INTO OUTFILE ‘Filename.csv‘

   FIELDS TERMINATED BY ',';

mysql -uroot -p --xml -e 'SELECT * FROM DB_name.TABLE_name' > Filename.xml (Available on 5.5 and higher versions)

Example:

1.    Login to the instance and use the database where the table to be backup is located.

[root@localhost mysql]# bin/mysql -uadmin -p

Enter password:

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 5

Server version: 5.0.95-log MySQL Community Server (GPL)

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use presentation

Database changed

mysql> show tables

    -> ;

+------------------------+

| Tables_in_presentation |

+------------------------+

| innodb_table           |

| myisam_table           |

| mysqlimport_tab        |

+------------------------+

3 rows in set (0.01 sec)

mysql> exit

Bye

2.    Select the data that is needed for backup and then specify the location of the file where it is written.

mysql> select * from myisam_table into outfile '/usr/local/mysql/outfile_myisam_table.sql';

Query OK, 1048576 rows affected (0.32 sec)

mysql> exit

Bye

You have new mail in /var/spool/mail/root

[root@localhost mysql]# ls -lrt

total 24796

-rw-r--r-- 1 root  mysql    2552 Dec 16  2011 README

-rw-r--r-- 1 root  mysql   19071 Dec 16  2011 COPYING

-rw-r--r-- 1 root  mysql    6387 Dec 16  2011 INSTALL-BINARY

-rwxr-xr-x 1 root  mysql    1153 Dec 17  2011 configure

drwxr-xr-x 4 root  mysql    4096 Dec 17  2011 man

drwxr-xr-x 2 root  mysql    4096 Dec 17  2011 docs

drwxr-xr-x 2 root  mysql    4096 Dec 17  2011 tests

drwxr-xr-x 3 root  mysql    4096 Dec 17  2011 share

drwxr-xr-x 2 root  mysql    4096 Dec 17  2011 support-files

drwxr-xr-x 5 root  mysql    4096 Dec 17  2011 sql-bench

drwxr-xr-x 2 root  mysql    4096 Dec 17  2011 scripts

drwxr-xr-x 2 root  mysql    4096 Dec 17  2011 bin

drwxr-xr-x 9 root  mysql    4096 Dec 17  2011 mysql-test

drwxr-xr-x 3 root  mysql    4096 Dec 17  2011 include

drwxr-xr-x 2 root  mysql    4096 Dec 17  2011 lib

-rw-r--r-- 1 root  root  8390761 Aug 15 16:15 myisam_dump_tabs.sql

-rw-rw-rw- 1 mysql mysql 4194304 Aug 15 16:22 myisam_dump_tabs.txt

-rw-r--r-- 1 root  root      750 Aug 28 06:40 dump_testisam.sql

-rw-r--r-- 1 root  root      750 Aug 28 07:21 dump_presentation.sql

drwxr-x--- 5 mysql mysql    4096 Aug 28 07:21 data

-rw-r--r-- 1 root  root  8390761 Aug 28 07:21 dump_presentation_all.sql

-rw-rw-rw- 1 mysql mysql 4194304 Aug 28 07:57 outfile_myisam_table.sql

Importing Data

SOURCE command – it is a SQL statement use to run a batch command.

Note: Better if it will be used on a blank database to properly copy the contents of the dump file.

SYNTAX:

mysql> source file_name or mysql> \. file_name

Example:

Source:

mysql> use presentation;

Database changed

mysql> select count(*) from myisam_table;

+----------+

| count(*) |

+----------+

|  1048576 |

+----------+

1 row in set (0.00 sec)

Destination:

mysql> create database temp_loc

    -> ;

Query OK, 1 row affected (0.03 sec)

mysql> use temploc

ERROR 1049 (42000): Unknown database 'temploc'

mysql> use temp_loc

Database changed

mysql> source /usr/local/mysql/dump_presentation_all.sql

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

...

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> select count(*) from myisam_table;

+----------+

| count(*) |

+----------+

|  1048576 |

+----------+

1 row in set (0.01 sec)

Using LOAD DATA INFILE – is an SQL command which is the counterpart of SELECT INTO OUTFILE command. It copies the data from the source file to the specified table.

LOAD DATA INFILE ‘Filename‘ INTO TABLE Table_Name;

LOAD DATA INFILE ‘Filename‘ INTO TABLE Table_Name;

   FIELDS TERMINATED BY ',';

Example:

Source:

mysql> use presentation

Database changed

mysql> show tables;

+------------------------+

| Tables_in_presentation |

+------------------------+

| innodb_table           |

| myisam_table           |

| mysqlimport_tab        |

+------------------------+

3 rows in set (0.01 sec)

mysql> select * from myisamtable into outfile '/usr/local/mysql/outfile_myisam_table.sql';

ERROR 1146 (42S02): Table 'presentation.myisamtable' doesn't exist

mysql> select * from myisam_table into outfile '/usr/local/mysql/outfile_myisam_table.sql';

Query OK, 1048576 rows affected (0.32 sec)

Destination:

mysql> show databases;

+--------------------+

| Database           |

+--------------------+

| information_schema |

| mysql              |

| mysqlimport        |

| presentation       |

+--------------------+

4 rows in set (0.00 sec)

mysql> use mysqlimport

Database changed

mysql> show tables

    -> ;

+-----------------------+

| Tables_in_mysqlimport |

+-----------------------+

| dump_testisam         |

| myisam_dump           |

| myisam_dump_tabs      |

| myisam_table          |

| outfile_myisam_table  |

+-----------------------+

5 rows in set (0.00 sec)

mysql> create table samp_myisam_table (i int, c char) engine=myisam;

Query OK, 0 rows affected (0.01 sec)

mysql> load data infile '/usr/local/mysql/outfile_myisam_table.sql' into table samp_myisam_table;

Query OK, 1048576 rows affected (0.40 sec)

Records: 1048576  Deleted: 0  Skipped: 0  Warnings: 0

The mysqlimport utility – is another way of using the LOAD DATA INFILE SQL statement wherein it will be executed at the command line.

SYNTAX:

mysqlimport -uusername -p --options DB_name Filename

Note: Can include multiple files wherein for each filename used, it will ignore the file extension of the export files. And it will be referred as the table name where it will import the data from the export file. Upon using this, the table name must be similar to the filename of the export file.

Example:

Source:

mysql> use presentation

Database changed

mysql> show tables;

+------------------------+

| Tables_in_presentation |

+------------------------+

| innodb_table           |

| myisam_table           |

| mysqlimport_tab        |

+------------------------+

3 rows in set (0.01 sec)

mysql> select * from myisamtable into outfile '/usr/local/mysql/outfile_myisam_table.sql';

ERROR 1146 (42S02): Table 'presentation.myisamtable' doesn't exist

mysql> select * from myisam_table into outfile '/usr/local/mysql/outfile_myisam_table.sql';

Query OK, 1048576 rows affected (0.32 sec)

Destination:

[root@localhost mysql]# bin/mysqlimport -uadmin -p mysqlimport --local --delete outfile_myisam_table.sql

Enter password:

mysqlimport.outfile_myisam_table: Records: 1048576  Deleted: 0  Skipped: 0  Warnings: 0

References:

MySQL Website:

http://dev.mysql.com/doc/refman/5.1/en/mysqldump.html

http://dev.mysql.com/doc/refman/5.1/en/select-into.html

http://dev.mysql.com/doc/refman/5.0/en/mysqlimport.html

http://dev.mysql.com/doc/refman/5.0/en/load-data.html

http://dev.mysql.com/doc/refman/5.0/en/batch-commands.html

Blogs:

http://zetcode.com/databases/mysqltutorial/exportimport/

http://steveswanson.wordpress.com/2009/04/21/exporting-and-importing-an-individual-mysql-table/

http://www.abbeyworkshop.com/howto/lamp/MySQL_Export_Backup/index.html