8.3.1  MySQL data movement

In MySQL, you can transfer tables (MyISAM and ISAM) by copying data (*.ISD, 

*.MYD) and index files(*.ISM, *.MYI) but only if your source and target operating 

system and machine architecture

are same. Otherwise, you have to use following 

utilities for data movement:

mysqldump

This is most popular MySQL utility for transferring the data from one MySQL 

server to another. It actually dumps a MySQL database into a file as SQL 

statements or as tab separated text files. You can back up and restore 

databases or tables as shown in Example 8 4.

Example 8 4   mysqldump and restore

bash>mysqldump   opt itsodb > mysql.sql

bash>mysql itsodbtemp< mysql.sql 

or 

bash>mysql  e  source mysql.sql  itsodbtemp

This utility supports dumping from remote machine, multiple databases 

dumping, flush log before dumping, and locking the table before dumping 

options.

mysqlhotcopy

If you are dumping data on server machine you may prefer to use 

mysqlhotcopy

. This command internally calls MySQL commands

 LOCK 

TABLES, FLUSH TABLES, 

and then copies files into the output directory. It 

works only for UNIX machines and MyISAM and ISAM tables. It can be 

executed as shown below:

bash>mysqlhotcopy itsodb /home/itso/mysql/dbcopy

mysqlimport

It is a simple utility to load data into the tables. It takes a comma or space 

separated data file as input and load it into the table. It is equivalent to calling 

the 

LOAD DATA INFILE SQL 

statement. This command can be executed as 

shown below:

bash>mysqlimport itsodb tablename.txt

8.3.2  DB2 UDB data movement

On the contrary, DB2 UDB provides faster and efficient tools and utilities for data 

movement across the different systems or reorganizing data on same system.

242 

MySQL to DB2 UDB Conversion Guide