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

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 77

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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

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 77

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Transferring a wordpress site from a host or local dev server to another host can be a pain. So I've included my steps on how I have accomplished this task. Steps include an assumption that you have Cpanel access or Ftp access.. If you do not hav…
So you have coded your own WordPress plugin and now you want to allow users to upload images to a folder in the plugin folder rather than the default media location? Follow along and this article will show you how to do just that!
The purpose of this video is to demonstrate how to add AdSense Ads to a WordPress Website, and how to set up WordPress to automatically place Ads in Sidebars. This will be demonstrated using a Windows 8 PC. Log into your AdSense account. : Cli…
The purpose of this video is to demonstrate how to Import and export files in WordPress. This will be demonstrated using a Windows 8 PC. Go to your WordPress login page. This will look like the following: : Click on Too…

828 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