Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

importing mySQL database dump

Posted on 2013-01-21
9
Medium Priority
?
841 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
Are your AD admin tools letting you down?

Managing Active Directory can get complicated.  Often, the native tools for managing AD are just not up to the task.  The largest Active Directory installations in the world have relied on one tool to manage their day-to-day administration tasks: Hyena. Start your trial today.

 
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

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

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

I was prompted to write this article after the recent World-Wide Ransomware outbreak. For years now, System Administrators around the world have used the excuse of "Waiting a Bit" before applying Security Patch Updates. This type of reasoning to me …
In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
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…
The viewer will learn how to dynamically set the form action using jQuery.
Suggested Courses

610 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