Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Mysql data export - import issue

Posted on 2013-02-07
6
Medium Priority
?
534 Views
Last Modified: 2013-02-11
Hi,
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?
Thanks,
Nacht
0
Comment
Question by:nachtmsk
[X]
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
6 Comments
 
LVL 1

Author Comment

by:nachtmsk
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?
Thanks,
0
 
LVL 79

Expert Comment

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

Accepted Solution

by:
Rob earned 2000 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
0
Looking for a new Web Host?

Lunarpages' assortment of hosting products and solutions ensure a perfect fit for anyone looking to get their vision or products to market. Our award winning customer support and 30-day money back guarantee show the pride we take in being the industry's premier MSP.

 
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.
0
 
LVL 79

Expert Comment

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

Author Closing Comment

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

Featured Post

Looking for a new Web Host?

Lunarpages' assortment of hosting products and solutions ensure a perfect fit for anyone looking to get their vision or products to market. Our award winning customer support and 30-day money back guarantee show the pride we take in being the industry's premier MSP.

Question has a verified solution.

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

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!
In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
The purpose of this video is to demonstrate how to manually back up a WordPress Database. This will be demonstrated using a Windows 8 PC. The Host used will be IPage.com Log into your Hosting account. IPage will be used for demonstration : Locat…
The purpose of this video is to demonstrate how to Test the speed of a WordPress Website. Site Speed is an important metric of a site’s health. Slow site speed can result in viewers leaving your site quickly and not seeing your content. This…
Suggested Courses

660 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