Solved

importing mySQL database dump

Posted on 2013-01-21
9
823 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
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
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 15

Expert Comment

by:Insoftservice
ID: 38808581
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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

A procedure for exporting installed hotfix details of remote computers using powershell
This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
This tutorial will show how to configure a single USB drive with a separate folder for each day of the week. This will allow each of the backups to be kept separate preventing the previous day’s backup from being overwritten. The USB drive must be s…
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.

732 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