ZoomCities IT Community Webmaster Forum

Is there a "one step" way that I can restore a database onto my PC using the dbName.sql file that I downloaded through the cPanel? I am currently going through the file one item at a time, dropping existing tables, creating the tables, and then I copy and paste to INSERT the values into the fresh table.

Sam
;

Where do you want to restore it to? Tell us more what exactly you need to do

Zac

I want to make a duplicate copy onto my PC of what I have on line.

This is the first part of a very small database dump. For it of course it is no problem to cut and paste the info onto the MySQL monitor and make the database and table. But for larger databases and tables there is a lot of work.

Quote:

-- MySQL dump 10.10
--
-- Host: localhost Database: ga7503_test
-- ------------------------------------------------------
-- Server version 5.0.27-standard

/*!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 `cars`
--

DROP TABLE IF EXISTS `cars`;
CREATE TABLE `cars` (
`id` int(4) NOT NULL auto_increment,
`date` varchar(4) default NULL,
`category` varchar(30) default NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=37 DEFAULT CHARSET=latin1;

--
-- Dumping data for table `cars`
--

LOCK TABLES `cars` WRITE;
/*!40000 ALTER TABLE `cars` DISABLE KEYS */;
INSERT INTO `cars` (`id`, `date`, `category`) VALUES (1,'1985','000 Ford'),(2,'1995','010 Ranger'),(3,'1995','010 Ranger'),(4,'1986','020 Mustang'),(5,'1986','020 Mustang'),(6,'1986','020 Mustang'),(7,'2006','030 Focus'),(8,'2006','030 Focus'),(9,'2006','030 Focus'),(10,'2006','030 Focus'),(11,'2002','100 Chevrolet'),(12,'1971','110 Camaro'),(13,'1971','110 Camaro'),(14,'1971','110 Camaro'),(15,'1971','110 Camaro'),(16,'1971','120 Corvette'),(17,'1971','120 Corvette'),(18,'1971','120 Corvette'),(19,'1971','120 Corvette'),(20,'1971','120 Corvette'),(21,'1971','120 Corvette'),(22,'1971','120 Corvette'),(23,'1971','120 Corvette'),(24,'1971','120 Corvette'),(25,'1999','130 Impala'),(26,'1999','130 Impala'),(27,'2002','140 Tahoe'),(28,'2002','140 Tahoe'),(29,'2002','140 Tahoe'),(30,'1992','400 Toyota'),(31,'2004','410 Avalon'),(32,'2004','410 Avalon'),(33,'1997','420 Celica'),(34,'2007','430 Tundra'),(35,'2007','430 Tundra'),(36,'2007','430 Tundra');
/*!40000 ALTER TABLE `cars` ENABLE KEYS */;
UNLOCK TABLES;

Is there a command that you can give to have the entire dump file like this read to do each command it contains?

The best thing I can suggest to you is to use the phpMyAdmin feature of cPanel so you can read your database well and dump or export them table by table to MS Excel or any text file.

Zac

^ like what Zac said. phpMyAdmin is really useful that way Biggrin

yeah phpmyadmin has a restore function which allows you to upload the plain text file containing the dump. but it gets difficult once the database you have to restore goes above the max file size for upload set in php.ini of the server.

A sidenote,
You can make use of free tools from MySQL.
They provide GUI tools and the administrator tool allows the backing up of the databases to a file.
You can use it to connect to your Database to backup.

Or you can just copy and paste the whole SQL file into the SQL tab for the database you want to insert the stuff into.

darkfate Wrote:

Or you can just copy and paste the whole SQL file into the SQL tab for the database you want to insert the stuff into.

I crashed my Firefox doing that while trying to restore my forum's database since the import function of phpMyAdmin was acting up for me.

It was around 19MB big, so you can probably guess why.

If anyone needs to restore large backups, I suggest using BigDump.

wow I never knew big dump existed! if only I knew earlier, then I could have imported my old forum to the new one.... aww, nvm all that matters is that i know it now Tongue

thanks for sharing Kwek Smile

Kwek Wrote:

darkfate Wrote:

Or you can just copy and paste the whole SQL file into the SQL tab for the database you want to insert the stuff into.

I have encountered that problem too when restoring using phpMyAdmin -_-"

BigDump is great! It is just what I was looking for. It works with the file that I download by clicking the individual database in the cpanel backup. And it will work through the whole file as the one I posted above to DROP IF EXIST table and CREATE TABLE then INSERT the downloaded data into the table.

I will make a minor modification to add an input box to select the database I want to use so I want have to change the code each time.

Sam
;

bigdump is a great script and I intend to give it a outstanding review. What I did not like was coding a database name into the script and having to change it to use another database name. This is how I, as an armature, fixed it. It works and I look forward to someone showing me a better way. Here are the modifications I made to the script:

1. Add session_start(); to the beginning, of course, before any output.

2. Put this php and HTML form code at about line 230 inside the first <td> cell of the <table>.

3. I moved the login information lines from about lines 41 - 44 into this new script. I have my login information in a login.txt file (the real name I am keeping secret). This could be different on each client's hosting server so I include() it here.

4. I transfer the values which are assign to the variables in my login.txt file to the $db_server, etc variable names the author used so I want have to change anymore variable names.

5. And last, I POST and assign which database name I want to use to a SESSION.

This is where my low skill level shows. When you first start the script you will get an undefined index notice for dbname and the Database will fail to connect due to no database selected. I tried to use else and set the default database to one I use the most, but then the script would upload to the wrong database even after I set a different SESSION dbname.

With the exception of this minor startup error, this is a very useful improvement for me. I will include this post to the author with my evaluation. Perhaps someone here will find it helpful.

Code:

<?php

if (isset($_POST['dbname']))

{

    $_SESSION['dbname'] = $_POST['dbname'];

}

include('login.txt');

$db_server   = $host;

$db_name     = $_SESSION['dbname'];

$db_username = $user;

$db_password = $password;

?>

<form name="selectDB" action="bigdump.php" method="post">

Database Name: <input name="dbname" type="text" size="15" maxlength="15" 

    value="<?php echo isset($_SESSION['dbname'])?$_SESSION['dbname']:'' ; ?>" />

<input name="submit" type="submit" value="Select Database">

</form>

Here again is the link which Kwek posted to bigdump.php

Sam
;

I don't even had know that mysql database can be restore!

NevadaSam

ZoomCities

NevadaSam

ZoomCities

dTsuyu

ngwz

gensephyr

darkfate

Kwek

ngwz

gensephyr

NevadaSam

NevadaSam

niklindg