MYSQL :: Restore Query

DB Restore
I appear to be getting the following error message everytime I want to restore this database. The message is as follows:

Unable to restore database web1_wordp : ERROR 1064 (42000) at line 754: 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 '22:17:15)' at line 1

I'm not quite sure what this means :confused: - How would I correct it?

Restore
A customer passed me her backup, for me to open in my MySql. They are 50Mb of backup. MySQLAdministrator is being long a lot, very even to make the restoration in my machine (1kbps!!!!)

Restore
I've got a table with over 400,000 records. I did a mysqldump to ensure I could restore it in case of problems. Sure enough, problems arose and I needed to do a restore. The mysqldump created the usual create database, create table and one gigantic insert statement, where all 400,000+ records are inserted. I started my restore around 10pm or so yesterday and was surprised to wake up this morning and find it still running. Considering the dump itself took a couple of seconds, why is the restore taking so long? I'm building a website that's going to have tons of traffic. A database restore that's going to take multiple hours seems to be unacceptable. One way to speed up the process would have been to simply copy the actual database files and just stick them back in the data directory.

How To Restore Database
I think i will have to da a full resortation of a database.

lets assume old database is deleted.

So I go to my hosting control panel and create a new mysql database
Use same username and password as used in original

open phpmyadmin and do to the new database and select import-then import the file that I have as a backup--i have a file that I exported as a text file-a full databse export. it is about 4 mb so I hope can do it at one time

Is that basically how I can do it??

Database Restore Help
im trying to construct a database using a backup file i made aout a year ago when i use phpmyadmin to upload the file i get an error on the first table it trys to create

CREATE TABLE `cal` (

`id` bigint( 20 ) NOT NULL AUTO_INCREMENT ,
`author` varchar( 32 ) NOT NULL default '',
`subj` varchar( 64 ) NOT NULL default '',
`txt` text NOT NULL ,
`typ` tinyint( 4 ) NOT NULL default Ɔ',
`dmy` varchar( 8 ) NOT NULL default '',
`hm` varchar( 4 ) NOT NULL default '',
`wday` tinyint( 4 ) NOT NULL default Ɔ',
`location` varchar( 64 ) NOT NULL default '',
`approved` char( 1 ) NOT NULL default '',
`tz` int( 11 ) NOT NULL default Ɔ',
`dat` bigint( 20 ) NOT NULL default Ɔ',
`mkt` bigint( 20 ) NOT NULL default Ɔ',
PRIMARY KEY ( `id` ) ,
KEY `typ` ( `typ` ) ,
KEY `wday` ( `wday` ) ,
KEY `dmy` ( `dmy` ) ,
KEY `mkt` ( `mkt` )

) ENGINE = MYISAM DEFAULT CHARSET = latin1;

With this msg

MySQL said:

#1064 - You have an error in your SQL syntax near ') ENGINE=MyISAM DEFAULT CHARSET=latin1' at line 20

this was a backup straight from phpmyadmin some time ago, can someone tell me why it wont restore?

Mysql Restore
I have been running a mysql backend to phpBB on my home Gentoo based linux server. I recently switched the mysql and phpBB to run on a dedicated web server on my home network. I was using mysql version 4.1.14 and then 4.1.14-r1 on my new web server. The problem is that the character set seems to have gotten corrupted during the backup and restore process. The forum is based in French and hence a lot of the special characters have gotten messed up. I have even tried copying the my.cfg file from the old server to the new one but with no effect.

Stalled Restore
I Exported my MySQL database (60KB) (MySQL 4.0.24-standard) from iPowerweb hosting site.

I know want to restore it to my MySQL (mysql-4.1.13a-win32.zip) on my home server.

I loged on with MySQL Administrator, went to Restore, Opened .sql file, hit Start Restore, and it gets stuck at 9598 number of bytes each time I attempt it. Total is 60812 Bytes.

How To Restore A Database (.bak)
Recently I downloaded a ASP script, so I can use on my website, however in the instructions, it tells me the following : -

Restore the bbdf.bak SQL database with SQL 2005 (any edition) Management Tools

Now I have the file bbdf.bak, but I am not sure what I am suppose to do.

I have downloaded and installed Microsofts SQL 2005, but am totally lost as to what I am suppose to open and type .... ? ?

I would of thought it would be easier for them to just give me a .mdb file or something which I could use...

Restore Of Database
How do I go about restoring multiple databases at the same time. I know if I needed to restore 1 database I can do something like.

mysql db_name < backup-file.sql

but what if backup-file.sql consists of multiple databases (ie it was created using mysqldump-all-databases) , which database would I specify in the db_name section?

How To Restore .tgz Files?
how do I restore .tgz backup files using the mysql panel? I tried restoring it as a gunzipped file but it gave me errors.

SQL Database Restore
I upgraded to Tiger and it damaged my SQL database.

I have a backup file that is about 80 mb. The backup file was created
with PHPmyAdmin and is in .SQL format.

When I try to use PHPmyAdmin to load this .SQL file and restore my database, I get an error message that says NO SQL QUERY.

The file stops executing and NO data is restored.

Is this because the file is too big ??

Is there an application that will break the .SQL file down into smaller
parts ??

Is there a way to restore the data using the .SQL file and a direct SQL command line entry ??

Restore Problem
I am trying to do a Restore on a dump file (see below) but I get the "failed
copying a .frm file" error.

RESTORE TABLE ListMembers FROM
'/home/virtual/thefrugallife.com/var/www/html/12all/backups/1089048410_Mon05
-07-2004.sql';
+-------------+---------+----------+--------------------------+
| Table | Op | Msg_type | Msg_text |
+-------------+---------+----------+--------------------------+
| ListMembers | restore | error | Failed copying .frm file |
+-------------+---------+----------+--------------------------+
1 row in set (0.77 sec)

Backup / Restore
I have this one FreeBSD box (nice Dell with one 40GB HDD). I have made a replica of the drive and put it on the secondary IDE channel. I want to do a restore of all the MySQL db's to the second drive right after the backup takes place from the primary drive.

The reasoning behind this is that this is the only system running this particular app and the company that wrote it went belly so, I NEED to have the ability to just swap drives bring it back up and be done.

Backup/Restore
I'm migrating a web site from my testing system on my local machine to the web host upon which it will live. My testing server is running MySQL v. 5.0.22, and the remote host is running 4.0.24. I am using MySQL Administrator v. 1.0.10 to run a backup and export my tables to an SQL file, and PHPMyAdmin to import the SQL file and run it on the server. At first, I was getting a 1065 - Query is Empty error. After reading through some of the postings here, I realized that some of the commented strings at the top of the file were unnecessary, and probably the cause of my error, so I removed them. I re-ran a backup that would only backup the appropriate tables and write the appropriate data (the database schema on my server is named differently, so I don't need to back up the full path). After editing the file to remove the uneeded stuff, I recieve a different error message, regarding an error in the SQL syntax of the dump file. The error in question occurs on a line that reads as follows:

)ENGINE = MYIASM DEFAULT CHARSET = latin1;

The error in question is as follows:

#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 'DEFAULT CHARSET=latin1' at line 21

I'm having a hard time finding the issue, and would really appreciate if somebody might point me in the right direction to help me create a backup file that my server will interpret charitably and restore the data I need. I'm getting to the point where I may have to re-enter it all by hand, and that thought depresses me quite thoroughly.

Slow Restore
Mysql 4.1.15 on Win2k. Using InnoDB.

Using the mysql administrator gui to create a backup, everything goes
fine, and restores quickly.

Using the command line:

mysqldump %dbname% --single-transaction > %dbname%.sql

creates a file about 15% smaller than the gui produces, and is
EXTREMELY slow to restore. I have tried adding locks, skip opt,
everything. What does the gui use for a command to create this dump?

Backup/restore
Working on a single machine (win2k) needing to do backups. Trying Backup
Table with the following syntax:

BACKUP TABLE tbldealers TO 'c:mysqlackups'
BACKUP TABLE tbldealers TO /mysql/backups

I've tried with/without quotes, ticks, nothing except the path. I either
get error in syntax, or I do get 1 row in set, but there are no files in
that directory.

Any help would be appreciated.

I've read the manual as extensively as I know to and can't find anything on
backup table syntax.

I'd prefer to stay away from mysqldump and mysqlhotcopy and control the
backups myself.

Backup And Restore
I have mysql 5 running as the db for my coldfusion application. This particular application needs to make a copy of a certain db and restore it with a new name. I have seen mysqlhotcopy but i need something that i can use from a cfquery tag in CF, can this be done?

Database Restore
How may I restore *.sql databases from linux shell?
I've read the use of mysql command I don't have installed.
This is a remote server with preinstalled Apache-php-mysql.

Restore Table
I am using mysql on my local machine, Windows operating system. I accidently execute an update statement without where condition, so I messed up all the records in this table, is there any solutions (or tricks) to restore data? I did not have any backup yet.

Restore Data
what is the difference between ?
1) mysql < backup_sunday_1_PM.sql
2) mysqlimport [options] db_name textfile1 [textfile2 ...]

3) Load data infile file

Restore Databases
Trying to restore some mysql databases that were on a hard drive which has crashed.
Although the DB's were supposed to be backed up ... it appears they were not backed up properly as I can access the drive but can only find the FRM files.
There are no MYI or MYD files on the old drive.
Is it still possible to restore my databases and tables with just the FRM files ??

Backup / Restore
I have recently installed a Fedora server - something new to me. I am just trying to get my mysql database from my old windows server, onto my fedora box.I have tried, mysql_administrator to create backups and restore but it hasn't worked.I have also used the following two commands from root to do it:

mysqldump -u charlie -p -h 10.10.10.3 --all-databases > /test.sql
(to read from the windows server and create a dump on the local machine)

followed by:

mysql -u root -p < '/test.sql'
(to try and resore the backup to the fedora box)

I got the error:

ERROR 1054 (42S22) at line 6854: Unknown column 'cl.COMPONENT_ID' in 'field list'

Restore A Backup
I have made a backup of my phpbb forum db. For some reason I can't get it restored. I'm running my own server on linux, the only thing is I'm new to this too. lol I was wondering if there's a way to transfer ther MySQL file and just have it merge itself into the MySQL system? I have access to the console if that helps. Sorry if I sound confusing.

Cannot Restore A Database
I have a text file that contains the output from mysqldump. I try to restore the database with: Quote: mysql -u root -p <password>1472 database-name < name_01_06_2007.sql and mysql produces a message as if the command is incorrect.
The message is: Quote: mysql Ver 14.12 Distrib 5.0.27, for redhat-linux-gnu (i686) using readline 5.0
Copyright (C) 2002 MySQL AB
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL license
Usage: mysql [OPTIONS] [database]

How do I restore a database after mysqldump? I read an article published in devshed and I followed their steps but...

Restore Backup Sql
If I backup the database using
shell> mysqldump --all-databases > all_databases.sql

How do I restore it? Mysql documentation only mentioned below:
shell> mysql db_name < backup-file.sql

This is only to restore one databas, not ALL databases I wanted. My guess would be
shell> mysql < all_databases.sql

Is that correct?

Backup And Restore
Can we take the back up and restore of mysql database programatically.
Including some exe files or something.

Restore Databases
is it possible to restore databases from just the /data folder?

InnoDB Backup And Restore
I've just converted the tables in a big database (approx 27 million
records) from MyISAM to InnoDB. When I was using MyISAM I backed up
the database by stopping MySQL and then copying the MYI, MYD, frm
files in the database directory, and restored the database in the same
way.

Is there a similar way to backup and restore InnoDB tables, or do I
have to use the InnoDB Hot Backup tool?

Restore To New Data Base
I am using a heavily modded PHPBB forum and want to convert all my posts, users, etc, into a new PHPBB forum using some other mods. I am using MySQL 3.23.58 .

I ran the conversion steps as the creator fo the modded forum stated but at the point where I run the install.php page I get an error that says "installation aborted as table xxxxxx already exists."

Is there a way around this like perhaps makign the new tables over write the old tables? I need to keep the tables as they are or I will lose the posts and such.

Backup And Restore Database
i did a backup of mysql database using the command line utility

mysqldump. this is the command i used "mysqldump -u root -h <hostname> --databases <dbname> > <backupfile>

when i try to restore this using mysql command line client it gives a error after some time the message says that a error has happened on a line number and it states that the table columns does not match the values in the insert statmenet.

i havent done any cahnges to the backup file i just dumped and restored thats all

but when i tried this with mysql administrator gui tool it worked fine.

Restore Database From Another Disk
I'm wanting to restore my old database by copying the contents of the old /var/lib/mysql folder to the new server, but this isn't working.

Is there a documented procedure for doing this? I'm using InnoDB and MyISAM.

Cannot Restore From Dump (phpMyadmin)
Using phpMyAdmin to export an entire database works fine - I get a
mydatabase.sql file.

Restoring (using phpMyAdmin) usually work fine. However, if any of the data
in any of the fields contains the '#' character, it bombs out citing a
syntax error. I have tried escaping the character with '' to no avail -
still gives syntax errors.

Seems strange that phpmyadmin dumps a file that will not restore...? I have
tried this on several versions of phpMyAdmin and get the same result every
time.

Have searched for ages looking for a solution, anyone else experienced thia?
solutions?

Quality Of Backup And Restore
I am using mysql administrator to perform scheduled daily backups of my
employee database.

What guarantee do I have after doing a restore that everything has
restored correctly?

My boss want wants a log of the number of employees in the database to
be created at backup time (eg: 4388). Then after restore we should
manually check that the number of employees in the database is the same
(= 4388) to give us confidence that everything is back as it was. I
believe this approach sounds terribly antiquated (back from the days of
mainframes) but without something better what confidence do we have?

In any case the backup is automated in mysql administor so how could I
create this log of the number of employees at backup time? I don't see
how I could.

As a less important question my boss also wants scheduled backups at
12pm and 5pm daily but mysql administator only supports one per day I
understand. Any ideas?

Restore From Sql Dump Fails
I have just been given laptop to configure with mysql and I have installed my usual proven 5.0.27.

When I try to re-install DBs from sql dumps, it only restores very small files and stops after a few thousands lines when attempting the bigger sql files.

I have therefore updated to the latest 5.0.45 but I am still having the same issue. Has anybody experience something similar on windoz?

Restore Changes Case Of The Letters
When I restore the mysql database , the table names are changed to small letters ? any idea why ?

Database Restore Space
i want to increase space of the database.. while restoring.. wen i click import it was '(Max: 2,048KiB)' i want to increase this space to.. higher

Cannot Restore 3.23.58 Db To MySQL 4.0.22-debug
The other day my server running MySQL 3.23.58 got hacked into and we
are migrating to another one. I backed up my databases using two
methods a) Mysqldump and b) copy the DATA folder on my server.

Now, I am trying to restore these databases on my local machine
(running MySQL 4.0.22-debug and Apache 2.0 on IIS 6.0). I have tried
importing the SQL files (mysqldump) and copying the .frm, .myd and .myi
files into the existing DATA folder on my local machine.

In both cases, the database is created and I can see the table
structure. But when I click on most of the tables, I get the following
error:

#1016 - Can't open file: '<table_name>.InnoDB'. (errno: 1)

Mysql Backup And Restore For 4.1.x
I have mysql 4.1.11 on my current server, i need my database restore another
server.. (another server mysql version is 4.1.11 same.)

And now... I just created new my database to new server following command:

CREATE DATABASE dbname DEFAULT CHARACTER SET latin5 COLLATE
latin5_turkish_ci;

because our database and tables uses latin5 and turkish_ci collation.

How can i get correct backup and restore for this character sets? example;

mysqldump -uUSER -pDBNAME > /path/backup.sql

and restore;

mysql -uUSER -pDBNAME < /path/backup.sql

is this correct for me?

OR

mysqldump --default-character-set=latin5 -uUSER -pDBNAME > /path/backup.sql

and restore;

mysql --default-character-set=latin5 -uUSER -pDBNAME < /path/backup.sql

some peoples suggest me --default-character-set= variable for mysql 4.1.x
backups and restore, is this correct? also if we backup database
--default-character-set option, restore command is same option will need to
use?

Restore InnoDB Data
In my Mysql database there is sometihng that doesn't work.

With MySQLFront I have all my InnoDB tables with this comment:
"Can't open file: 'certificatetype.InnnoDB'. (errno: 1)"

and in my loog files I read:

031215 15:33:53 InnoDB error:

Cannot find table test/certificatetype from the internal data
dictionary

of InnoDB though the .frm file for the table exists. Maybe you
have deleted and recreated InnoDB data files but have forgotten
to delete the corresponding .frm files of InnoDB tables, or you
have moved .frm files to another database?

How To Restore To MySQL? I Have Phpmyadmin.
How can I restore to mySQL using phpmyadmin? I created a database and now I want to restore from another db to a newly created database. I have it save on my local drive.

Need To Restore Deleted Dbase
Via Cpanel, and then Database Manager, I accidently clicked on the Trash
bin over the dbase and voila! It's sort of history.

I FTP'ed to the folder where the Dbase was, well, everything is there. I
think it is. All the PhP files (PhPBBS) are there and a folder called db
with lots of things in there.

Can I restore the dbases and if so, is there a step by step guide? I'm not
very adept at this. I don't have a back up.

Backup And Restore Greyed Out
I installed the MYSQL administrator, and created a new database with some tables. I want to create an sql dump of this DB now, but all functions in backup and restore are greyed out in the Administrator.

This is quite a silly question and problem, but I don't see what the problem can be. (I logged in as root).

How Do I Do A Complete Backup/restore
I have a mySQL database on an ISP site that I am using. Now, I need to move the complete database to another ISP. I cannot use command-line execution.

I do this all of the time with SQL Server and Access databases, but I can't figure out how to do it with a mySQL database.

Possible To Restore Databases From Just The /data ???
possible to restore databases from just the /data folder??

Mysqldump Restore Failing
I created a backup of my database with mysqldump.
I then installed a new server, and restored succesfully the database.
After this, for some reason I deleted the database (mysql drop database), and created it again (mysql create database).
Now I cannot anymore restore the database with mysqldump.
What is the reason?
The original *.sql file is exactly the same as it used to be.
The mysqldump restore stops after the "SETs" as below, and just doesn't continue. no errors or warnings given.

code: .....

Cannot Restore From Dump (phpMyadmin)
This may really be a phpMyAdmin problem, I think - but keen to hear your
thoughts. Any help appreciated.

Using phpMyAdmin to export an entire database works fine - I get a
mydatabase.sql file.

Restoring (using phpMyAdmin) usually work fine. However, if any of the data
in any of the fields contains the '#' character, it bombs out citing a
syntax error. I have tried escaping the character with '' to no avail -
still gives syntax errors.

Seems strange that phpmyadmin dumps a file that will not restore...? I have
tried this on several versions of phpMyAdmin and get the same result every
time.

Have searched for ages looking for a solution, anyone else experienced thia?
solutions?

Mysqldump Restore Triggers
I have inherited a stone-aged RedHat 8.0 system running mysql 3.23.52 which cannot be upgraded for various reasons I won't go into here. However the powers that be are finally interested in backing this system up, so I have started looking into mysqldump. In the interests of recoverability, I have created a RedHat 9 system running mysql version 3.23.54 which I am now attempting to restore my dump file to.

So. To create my dump, I am doing this:

# mysqldump --opt --all-databases --user="root" -p > all-databases.dump

On the restore system, I do this:

# mysql -u root -p < all-databases.dump

...and it ticks along for a while and finally announces:

ERROR 1062 at line 6005: Duplicate entry '1' for key 1

Looking at line 6005, it says (line numbers added by me):

6004: LOCK TABLES groups WRITE;
6005: INSERT INTO groups VALUES (0,'Administrators'),(1,'Anonymous'),(2,'File Admin'),(3,'Company');

This does not appear to be a TINYINT problem; the table does not appear to be defined with a TINYINT.

I have googled around and the lack of an answer implies I'm doing something trivially incorrect.