1

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,

user3310782
  • 811
  • 2
  • 10
  • 18

2 Answers2

2

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.

Maulik
  • 2,881
  • 1
  • 22
  • 27
  • 1
    "_if you think it's security issue to include password in command_" - it is a security issue, don't do it. – Attie Jul 11 '17 at 11:20
  • Thanks, that's really useful. Somehow I thought mySQL workbench internally used mysqldump to perform these operations. Your answer might suggest the opposite. I'll try this asap and let everyone know. – user3310782 Jul 11 '17 at 11:29
  • I get 'Access denied error'. This might be basic, but I hope helps other users too: my setup is a local PC (no server), and 2 servers that I access online. -In step1: 1) you run mysqldump locally, and 2) do you need permissions on Server1 to save a file there? Same goes if you go for the ProTip and transferring the file. In a way you're saving in a machine but can't see its directories..... – user3310782 Jul 11 '17 at 12:54
  • Hi, it's likely remote mysql is disabled. you need to login via ssh in to your servers. Do step 1 on Old=server1, do step 3 on new=server2. – Maulik Jul 12 '17 at 02:06
1

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 )

Jethan
  • 71
  • 5