Solved

Mysql data export - import issue

Posted on 2013-02-07
6
525 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
6 Comments
 

Author Comment

by:nachtmsk
Comment Utility
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 76

Expert Comment

by:arnold
Comment Utility
Does the mysql 5.5 have innodb builtin and and enabled?
0
 
LVL 42

Accepted Solution

by:
Rob Jurd, EE MVE earned 500 total points
Comment Utility
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
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 50

Expert Comment

by:Steve Bink
Comment Utility
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 76

Expert Comment

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

Author Closing Comment

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

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

How to create your own WordPress Theme Before I begin with the theme design tutorial, I would like to give you the basics of what is required to make a WordPress Theme. The most basic WordPress theme only requires 2 files: a style.css file and an…
In Part I (http://www.experts-exchange.com/Web_Development/Blogs/WordPress/A_8410-Getting-Started-In-WordPress-Part-I.html), I introduced you to the powerful WordPress backend, the WordPress administrative Dashboard.  In Part II, I will introduce yo…
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: mywebsite.com/wp…
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…

771 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now