Importing/restoring A Database

[Stephen Page]

Hi,

I am new to MySQL and to SqlYog, but I am an experienced MS Sql Server guy.

I have a project involving a group of MySQL databases which I need to backup, download, and setup on my local machine for development purposes. The version of MySQL Databases on the remote server that I am backing up are 4.1.19-standard. The Version of MySQL Database I am running in my Local development environment is 5.0.24-A-Win32. The version of SQLYog I am running in my Local development environment is 5.18 Enterprise.

After logging in to the remote CPanel I generate the Database backups for each of the databases I will be using. These databases are compressed into gz files and I download them into a local directory, like “d:mysqlbkupcontacts.gz”. I unzip the compressed db files and save them as sql files, like “dmysqlbkupcontacts.sql”. The text header at the beginning of the contacts.sql file looks like this:

“– phpMyAdmin SQL Dump — version 2.8.0.2 –“

Next, I create empty databases with the same names as the backed up databases, in this instance, I create a “contacts” database.

At this point I am not sure how to proceed with a database restore. If I right-click on the empty contacts database and select “Run MySQL Restore Command”, SqlYog is looking for its own proprietary MYD file. If I change the extension of the contacts.sql file to contacts.myd and try to restore it, I get an error which says there was a failure copying the .frm file.

If I try to run the database restore from the MySQL Command Line Client, (totally bypassing SQLYog):

mysql> contacts < d:mysqlbkupcontactscontacts.sql I get the following error: “Unknown command 'l'. Error 1064 (42000): 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 'ontacts.sql' at line 1” I am a bit lost here and I would really appreciate some help with using the SQLYog Enterprise tool to Import/Restore the backed up database(s). Thanks, ~Stephen Page

[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