Mysql data export - import issue

Posted on 2013-02-07
Last Modified: 2013-02-11
I have two Linux servers.  Both are running mysql. Server 1 had mysql v.5.0.77,  Server 2 has mysql v.5.524.
I'm trying to dump a Wordpress database from server 2 and import it into server 1.
I used mysqlhotcopy on Server 2 and it seemed to export fine. I moved the database files over to Server 1 then restarted Server 1.
The database on Server 1 seemed ok at first but then I noticed some tables were blank that should have been populated.
One table in particular was table called "Profile".
I went into /var/lib/mysql and found the table file profiles.frm, but that was the only file. I thought there should also be a profiles.MYD and profiles.MYI.

Anyway, it appears as though mysqlhotcopy did not move all of the data across accurately. I'm thinking it's because the mysql versions are different.
I can't easily update the mysql version on Server 1, so I'm wondering what might be a better way to get my data moved across?
Question by:nachtmsk
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions

Author Comment

ID: 38864262
I found the problem.
The tables that didn't move across correctly using mysqlhotcopy were all InnoDB tables. The tables that did move across correctly were MyISAM tables. I used a third party tool to connect to the database on Server 2 and dump the SQL of those InnoDB tables, then import on Server 1.
Odd though, I wonder why the InnoDB tables didn't move across correctly. Any suggestions?
LVL 78

Expert Comment

ID: 38866739
Does the mysql 5.5 have innodb builtin and and enabled?
LVL 43

Accepted Solution

Rob earned 500 total points
ID: 38866756
You might want to also look at a 3 rd party tool  for importing and exporting WordPress databases as there can be name conflicts
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

LVL 50

Expert Comment

by:Steve Bink
ID: 38866813
From the manual, emphasis added:

mysqlhotcopy is a Perl script that was originally written and contributed by Tim Bunce. It uses FLUSH TABLES, LOCK TABLES, and cp or scp to make a database backup. It is a fast way to make a backup of the database or single tables, but it can be run only on the same machine where the database directories are located. mysqlhotcopy works only for backing up MyISAM and ARCHIVE tables. It runs on Unix and NetWare.
LVL 78

Expert Comment

ID: 38866818
Along the lines above, if you are knowledgable with perl, you could create a script that will perform the copy.

Author Closing Comment

ID: 38876100
Thanks for the comments everyone. I wound up using Navicat to move the databases across and it worked perfectly.

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Creating and Managing Databases with phpMyAdmin in cPanel.
Introduction This article is intended for those who are new to PHP error handling (  It addresses one of the most common problems that plague beginning PHP develop…
The purpose of this video is to demonstrate how to properly insert a Vimeo Video into a WordPress site or Blog. This will be demonstrated using a Windows 8 PC. Go to your WordPress login page. This will look like the following:…
The purpose of this video is to demonstrate how to prevent comment spam on a WordPress Website. This will be demonstrated using a Windows 8 PC. Plugin Akismet will be used. Go to your WordPress login page. This will look like the following: myw…

738 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question