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 79

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
Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

LVL 51

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 79

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: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

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

Foreword In the years since this article was written, numerous hacking attacks have targeted password-protected web sites.  The storage of client passwords has become a subject of much discussion, some of it useful and some of it misguided.  Of cou…
In order to have all security and back ups taken care of, WordPress users can sign up for services with WP Engine.
This video teaches users how to migrate an existing Wordpress website to a new domain.
The purpose of this video is to demonstrate how to set up basic WordPress SEO. This will be demonstrated using a Windows 8 PC. The plugin used will be WordPress SEO by Yoast. Go to your WordPress login page. This will look like the following: myw…

634 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