Solved

importing mySQL database dump

Posted on 2013-01-21
9
827 Views
Last Modified: 2013-01-22
I have a Joomla database that was dumped into a .txt file on an appache server using the command:-
mysqldump -u admin --opt -p alexthekiddb > private/alexthekiddb.txt

Open in new window


How do import that into a new MySQL database on my IIS server so I can attempt a conversion from Joomla to Wordpress.

I can use phpmyadmin or the MySQL builtin command line tool...

I already have a 2nd new database created with the same name and user (and PW) as the original.
0
Comment
Question by:Reece Dodds
[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
  • 4
  • 3
  • 2
9 Comments
 
LVL 15

Expert Comment

by:Insoftservice
ID: 38804014
Why don't u try some gui tool like sqlyog or navicat.
I use sqlyog to do so.
0
 
LVL 7

Author Comment

by:Reece Dodds
ID: 38804115
because I am unfamiliar with those tools.
I was hoping there'd be a command which is essentially the reverse of what was used to dump it.

I've never done either a dump or restore, so I'm flying blind...
0
 
LVL 15

Accepted Solution

by:
Insoftservice earned 500 total points
ID: 38804224
It's just change of "<" symbol
Please check this link it would be helpful

http://www.thegeekstuff.com/2008/09/backup-and-restore-mysql-database-using-mysqldump/
http://webcheatsheet.com/sql/mysql_backup_restore.php

To backup
 mysqldump -u root -ptmppassword --databases bugs sugarcrm > bugs_sugarcrm.sql

To restore

mysql -u root -ptmppassword sugarcrm < /tmp/sugarcrm.sql
0
Increase Agility with Enabled Toolchains

Connect your existing build, deployment, management, monitoring, and collaboration platforms. From Puppet to Chef, HipChat to Slack, ServiceNow to JIRA, Splunk to New Relic and beyond, hand off data between systems to engage the right people.

Connect with xMatters.

 
LVL 7

Author Comment

by:Reece Dodds
ID: 38807634
Thanks insoftservice.  I'll try that.  Is it the same if the file is .txt though?
0
 
LVL 7

Author Comment

by:Reece Dodds
ID: 38807921
OK.  I figured out what I was doing wrong.
I need to run these commands from the OS command prompt and need to be in the MySQL Bin directory.

So...   now when I try the restore I get the following message:
ERROR 1064
For information's sake, the opening lines of the Apache MySQL dump are:
-- MySQL dump 8.23
--
-- Host: localhost    Database: alexthekiddb
-- ------------------------------------------------------
-- Server version	3.23.58-log
--
-- Table structure for table `jos_banner`
--

DROP TABLE IF EXISTS jos_banner;
CREATE TABLE jos_banner (
  bid int(11) NOT NULL auto_increment,
  cid int(11) NOT NULL default '0',
  type varchar(10) NOT NULL default 'banner',
  name varchar(50) NOT NULL default '',
  imptotal int(11) NOT NULL default '0',
  impmade int(11) NOT NULL default '0',
  clicks int(11) NOT NULL default '0',
  imageurl varchar(100) NOT NULL default '',
  clickurl varchar(200) NOT NULL default '',
  date datetime default NULL,
  showBanner tinyint(1) NOT NULL default '0',
  checked_out tinyint(1) NOT NULL default '0',
  checked_out_time datetime NOT NULL default '0000-00-00 00:00:00',
  editor varchar(50) default NULL,
  custombannercode text,
  PRIMARY KEY  (bid),
  KEY viewbanner (showBanner)
) TYPE=MyISAM;

/*!40000 ALTER TABLE `jos_banner` DISABLE KEYS */;

--
-- Dumping data for table `jos_banner`
--


LOCK TABLES jos_banner WRITE;
INSERT INTO jos_banner VALUES (1,1,'','Kawasaki',0,7,1,'osmbanner1.png','http://www.alexthekid.com/contact/champions.html','2007-05-15 22:57:28',1,0,'0000-00-00 00:00:00','','');

/*!40000 ALTER TABLE `jos_banner` ENABLE KEYS */;
UNLOCK TABLES;

Open in new window


Whereas the first lines of a dump from one of my Windows server's mysql databases is:
-- MySQL dump 10.13  Distrib 5.1.66, for Win64 (unknown)
--
-- Host: localhost    Database: 3disshit
-- ------------------------------------------------------
-- Server version	5.1.66-community

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table `wp_ai1ec_event_category_colors`
--

DROP TABLE IF EXISTS `wp_ai1ec_event_category_colors`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `wp_ai1ec_event_category_colors` (
  `term_id` bigint(20) NOT NULL,
  `term_color` varchar(255) NOT NULL,
  PRIMARY KEY (`term_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `wp_ai1ec_event_category_colors`
--

LOCK TABLES `wp_ai1ec_event_category_colors` WRITE;
/*!40000 ALTER TABLE `wp_ai1ec_event_category_colors` DISABLE KEYS */;
/*!40000 ALTER TABLE `wp_ai1ec_event_category_colors` ENABLE KEYS */;
UNLOCK TABLES;

Open in new window

0
 
LVL 83

Expert Comment

by:Dave Baldwin
ID: 38808448
Ignore all the stuff that starts with /*!40101 .  You need to show us the lines around line 151 where the error is like lines 148 to 154.  And by the way, it's just MySQL, Apache at that point has nothing to do with it.

Your server version may have something to do with it.  MySQL 3.23 is pretty old compared to MySQL 5.1.66.  There could be a syntax difference at the point of the error.
0
 
LVL 7

Author Comment

by:Reece Dodds
ID: 38808597
I found out what it was that was causing the issue...
The Joomla DB uses some reserved words and I had to quote them for it to import.

This article here pretty much did the trick --> http://blog.redbranch.net/2008/04/20/mysql-illegal-field-names-in-mambojoomla-import/
0
 
LVL 83

Expert Comment

by:Dave Baldwin
ID: 38808784
There you go, them details will get you every time!  Glad you found it.
0

Featured Post

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

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

When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
Containers like Docker and Rocket are getting more popular every day. In my conversations with customers, they consistently ask what containers are and how they can use them in their environment. If you’re as curious as most people, read on. . .
This tutorial will walk an individual through configuring a drive on a Windows Server 2008 to perform shadow copies in order to quickly recover deleted files and folders. Click on Start and then select Computer to view the available drives on the se…
This tutorial will walk an individual through setting the global and backup job media overwrite and protection periods in Backup Exec 2012. Log onto the Backup Exec Central Administration Server. Examine the services. If all or most of them are stop…

690 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