Solved

importing mySQL database dump

Posted on 2013-01-21
9
817 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
  • 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
Does Powershell have you tied up in knots?

Managing Active Directory does not always have to be complicated.  If you are spending more time trying instead of doing, then it's time to look at something else. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why

 
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

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

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

Nothing in an HTTP request can be trusted, including HTTP headers and form data.  A form token is a tool that can be used to guard against request forgeries (CSRF).  This article shows an improved approach to form tokens, making it more difficult to…
A procedure for exporting installed hotfix details of remote computers using powershell
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…
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.

828 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