[MySQL] Migrating Data from One DB to Another

There are various ways to migrate data from one MySQL DB to another MySQL DB.

Overview - 4 Ways

1) Using a tool like MySQL WorkBench to export data as CSV from one and import CSV to another.

• Advantages:
• Very Easy
• Allows incrementals
• Disadvantages:
• Can be slow for very big datasets
• Really needs table created beforehand (so that you maintain data types)

2) MySQLdump:

• Advantages:
• Fast
• Quite Easy
• Disadvantages:
• Does not allow incrementals (i.e. replace whole table/database)

3) MySQL CLI (** The Favourite **):

• Advantages:
• Fast
• Gives a lot of control
• Disadvantages:
• A little bit fiddly

4) Python Tool:

• Advantages:
• Highly customizable
• Can automate what you want to do
• Disadvantages:
• Difficult (requires Python skills)
• Takes time to do

Examples

3) MySQL CLI:

Firstly to connect:

mysql.exe -uadmin -pF00bar_baz -hsqldb01.disdev.itid.euw2.dev.aws.cloud.hsbc -P3307 --local-infile SCHEMA

SELECT `col1`, `col2`, ...

  FROM table

  WHERE foo = 'bar'

  INTO OUTFILE 'Z:\\SOMESQLDATA.csv'

  FIELDS TERMINATED BY ','

  ENCLOSED BY '"'

  LINES TERMINATED BY '\n';

LOAD DATA LOCAL

  INFILE 'Z:\\SOMESQLDATA.csv'

  INTO TABLE `schema`,`table`

  COLUMNS TERMINATED BY ','

  LINES TERMINATED BY '\n'

  IGNORE 1 LINES

  (`col1`, `col2`, ...);

To be continued...