CODEWITHSUNDEEP
mysqlmysql-workbenchdatabase-migration
user3310782
user3310782

Reputation: 831

Foolproof methods for mysql -> mysql migration

I've been migrating ddbb (a few GB size) in mySQL workbench 6.1, from one mySQL server to another mySQL. Never having done this before I thought it was 99% reliable. Instead, 2 out of 3 tries have failed.

My ddbb dont have complex features (triggers, SP & functions,...). The errors, though, are difficult to interpret, almost always about tables failing to export, reason unknown. There might be occasionally a duplicated key index in source, but that shouldn't prevent an export from happening?

I've tried all the different methods available in the interface:

1) Server > Data Export > Data Import

2) Migration wizard

3) Schema transfer wizard

4) Reverse engineer

but no real difference.

Also, all methods seem variants of the same, do these menu options rely on the same procedure internally, how really different are they?

My questions are generic:

1) Is there a foolproof method, relaxed about errors, e.g. is mysqldbcopy from myQL utilities much better that workbench wizards?

2) Does mySQL wizards configuration make any difference (e.g. a checkbox that causes errors by being too demanding if the source db has a problem) I just want to transfer the db, not perfection in the target server. I've switched SSL=NO, but still not working.

3) What is the single most important cause of errors in migration, e.g. server overloaded, enough memory, table structure?

Thanks in advance,

Upvotes: 1

Views: 747

Answers (2)

Maulik
Maulik

Reputation: 2991

There might be occasionally a duplicated key index in source, but that shouldn't prevent an export from happening?

Yeah, It shouldn't prevent export operation.

I've tried all the different methods available in the interface:

All interface you have used might have some timeout configured so it don't really execute fully as your database is BIG.

So how to migrate MySQL database from one server to another?

To do it properly, I suggest you use command line like this:

Step 1: create backup file on old server

mysqldump -u [[user_name]] -p[[password]] [[db_name]] > db_backup.sql

Step 2: Transfer backup file to new server.

Step 3: Import backup file in new server.

mysql -u [[user_name]] -p[[password]]  [[db_name]] < db_backup.sql

Pro tip:

you can combine step 1 & 2 if you have remote MySQL enabled on old server. Just execute this command on new server so it will download the backup file in current directory of new server.

mysqldump -h [[xxx.xx.xxx.xxx]] -u [[user_name]] -p[[password]] [[db_name]] > db_backup.sql

where [[xxx.xx.xxx.xxx]] represents ip address/hostname for old server.

Extra Note:

Please note that there is no space between -p and [[password]]. you can also omit the [[password]] if you think it's security issue to include password in command.

Upvotes: 2

Jethan
Jethan

Reputation: 71

If you have access to your terminal you can try using "mysqldump" and also you could try percona xtrabackup tool.

Mysql dump : (If your DB is too large then I suggest you to use screens)

Backup all DB : mysqldump -u root -pxxxx --all-databases > all_db_backup.sql

Backup Tables : mysqldump -u root -pxxxx DatabaseName table1 table2 > tables.sql

Backup Individual databases : mysqldump -u root -pxxx --databases DB1 DB2 > Only_DB.sql

To import : Sync all the files to another server and try importing as show below

mysql -u root -pxxxx < all_db_backup.sql (Use Screen for large Databases)

Individual DB : mysql -u root -pxxx DBName < DB.sql

( Note : Before you import make sure your backuped file already has create database if not exists statements or you could create those DB names before importing )

Upvotes: 1

Related Questions