Importing/restoring A Database

[peterlaursen]

To import a .SQL dump with SQLyog do like this:

menu .. tools .. Import from SQL statements.

“If I right-click on the empty contacts database and select “Run MySQL Restore Command”, SqlYog is looking for its own proprietary MYD file.” NOPE! The .MYD file is not a SQLyog file but a MySQL file – it is the MyISAM DATA FILE itself! The commands 'backup database' and 'restore database' should only be used when backing up and restoring on the same server. Actually they are old commands and largely depreciated, but still supported though.

Now if you intend to import with SQLyog I would recommend that you do the backup with SQLyog! You have two ways to do so: the 'export' tool and the 'secheduled backup' powertool. There can be incompability issues (especially when working across different MySQL versions). Also looks like the is a problem with this phpMyAdmin dump as even not command-line client will accept it. However those issues are most often simple things that can be fixed in an editor! Looks like it is at the very beginning of the file something is happening.

Now .. first try 'menu .. tools .. Import from SQL statements' with your existing file ..

If that won't work either you can copy the first – say 25 lines – in here or try to do the backup with SQLyog.

[Stephen Page]

Ah!

The Import from Sql Statements did the trick for 3 out of the 4 databases I needed to import. I am still having problems with the 4th database.

When I attempt the import of the 4th database and it fails, I get the following error message:

Query:

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT *//*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */

Error occured at:2006-09-03 16:43:20

Line no.:8

Error Code: 1064 – You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */' at line 1

Here are the first few lines of the sql file:

— MySQL dump 10.9

—

— Host: localhost Database: leadcap_prospects

—

---

— Server version 4.1.19-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 */;

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

—

DROP TABLE IF EXISTS `brim_admin`;

CREATE TABLE `brim_admin` (

`name` char(70) NOT NULL default '',

`value` char(50) default NULL,

PRIMARY KEY (`name`)

) ENGINE=MyISAM DEFAULT CHARSET=latin1;

Thanks.

[peterlaursen]

Simply try removing all the lines containing 'conditional comments'. SQLyog 'import from SQL-statements' have a problem with those. It is allready reported here: http://www.webyog.com/forums//index.php?showtopic=2299 – and is a SQLyog bug. 'Conditional comments' should not be stripped out by a client. The server should handle this!

There is no problem importing data from MySQL 4.1 to 5.0 without them.

You can also paste the DUMP into the SQLyog editor and 'execute all' (double green icon). Here is no problem with those comments!!

BTW: This looks to be a 'mysqldump' dump and not a phpMyAdmin dump?

[Stephen Page]

I copied the dump directly into the query text area, just like you said. The first execution failed telling me that the tables didn't exist in the database, which I thought was strange because the sql script drops the tables if they exist, then creates them explicitly. The second execution was successful!

Yes, this is a MySQL dump; after my initial failed attempts at restoring the databases from the phpMyAdmin Dump (my lack of understanding), I tried other methods of getting backup data. The mysqldump was where I ended up, because it seemed that I could execute more control over export parameters.

Thank you very much! 😀

~ Stephen Page

[Author]

Posts