?
Solved

importing mySQL database dump

Posted on 2013-01-21
9
Medium Priority
?
834 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 2000 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
Get MySQL database support online, now!

At Percona’s web store you can order your MySQL database support needs in minutes. No hassles, no fuss, just pick and click. Pay online with a credit card.

 
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 84

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 84

Expert Comment

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

Featured Post

Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

Question has a verified solution.

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

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. . .
In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
The viewer will learn how to count occurrences of each item in an array.
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.
Suggested Courses

770 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