MYSQL :: Backup And Restore

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.

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 / 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.

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.

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?

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.

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?

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?

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.

Restore And Backup Column
is it possbile to backup and restore column in table in db

How Do You Backup/Restore Big MySQL DB's?
i found a few hints with the search function, but would love to know your ways to backup and restore your "big" mysql databases?

I Need To Restore Data From My Backup Tables
I have installed Joomla and it created new database tables and took the existing ones and made them a backup. So I have one set that has a prefix of bak_ and the other set begins with jos_

How do I go about taking the data in the bak_ tables and inserting or copying it into the jos_ tables?

Get A 1064 Error When I Try To Restore A Backup
I am migrating my website from 1 server to another the versions of MySQL are as follows

Old Server 4.1.14-standard

New Server 4.0.25-standard

I am restoring a Wordpress SQL backup using MyPHPAdmin version 2.6.4-pl2

and get the following error each time I try to restore

Error

SQL query:

CREATE TABLE `ajones_Counterize` (
`id` int( 11 ) NOT NULL AUTO_INCREMENT ,
`IP` varchar( 16 ) NOT NULL default 'unknown',
`timestamp` datetime NOT NULL default '0000-00-00 00:00:00',
`url` varchar( 255 ) NOT NULL default 'unknown',
`referer` varchar( 255 ) NOT NULL default 'unknown',
`useragent` text,
PRIMARY KEY ( `id` )
) 'ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=10911'

MySQL said: Documentation
#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 ''ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=10911'' at

Newbie Seeks Help Using Backup/restore
I am attempting to bring over a MySQL database from a Windows XP server
to a Sun Solaris server. I seem to be having permission problems, but I
don't even know where to start.

NOTE: I have root access to MySQL, but cripplingly little access to the
server itself. If I knew what access to request, I could probably get
it, but I don't know
I was able to find the .frm files for the old database, as well as to
output a .sql backup file, and to store them on the new server. I tried
to restore them using the following steps:

[Login to MySQL as root user and enter password, then...]

CREATE DATABASE mpf1;
USE mpf1;
RESTORE TABLE submitted_records FROM '/var/apache2/htdocs';

(Note: htdocs is one of the few places I have permission to access.)

And it said "error copying .frm file errno13" and failed to create the
table.

So then I tried it again with FROM
'/var/apache2/htdocs/submitted_records.frm';

same error. Ditto when I went and maxed out the permissions on those
..frm files.

Next, I tried creating a dummy database on the Solaris server and
backing it up to see whether I was having version/migration problems.

BACKUP TABLE dummy_table TO...

Same error.

Anyone have any ideas on where to go with this? I can ask the sysadmin
(at another location) for anything I like, so long as I know why I'm
asking for it

Backup/Restore MySQL Databases.
Is it safe to say that restoring the [/usr/local/myql/data] directory from a
backup would that restore all database data and structure? All usr/pwd and
GRANT info?

Looking For A Backup/restore MySQL DB Application
I'm looking for a very simple application to backup/restore a mySQL database.

Actually, I would need to be able to launch the application from a command line so that I dont have to select a profile or anything like that...just put the command line in a bat file, run the bat file and everything is done.

I've already tried :

- Backup Watcher for Mysql
- MyWitch
- DumpTimer
- SQLyog
- MySQL Snap

None of them seems to work with a command line.

ERROR 1064 When Trying To Restore From Backup
Some time ago I installed MySQL on Debian Sarge ia64 and ended up with v. 4.1.11. I went on to build a database and a few days ago I wanted to copy it across to a web server, running Debian Sarge i386. I installed MySQL on the web server. As this was installed via FTP I was a bit pussled to see that I ended up with MySQL 4.0.24. Anyway, I thought no more about it, until I tried to copy across my MySQL 4.1.11 database. I backed up the original MySQL 4.1.11 database thus:

# mysqldump -u root -p databasename > datavasename_bak.sql

and encountered no problems. I also tried to restore it and everything seemed just fine.

I then copied the backup file across to the web server and tried to restore it with the command:

mysql -uroot -p databasename < databassename_bak.sql

whish resulted in the following error message:

ERROR 1064 at line 21: 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 27

Having searched various forums it seems that this is a common error, but I have not been able to find any advice as to how it may be fixed.

Just to make sure that there was nothing wrong with by installation I deinstalled the MySQL 4.1.11 version and installed it again (via FTP), which gave me a real surprise, as I now ended up with version 4.0.24. It seems that the Debian team has decided to go back to v. 4.0.24.

I don't know why; I can only guess that there might be problems with v. 4.1.11. So, I now have compatible databases (booth 4.0.24) on the development machine and the web server, but I am left with an empty database and a backup (4.1.11) that I cannot restore. Is there a solution to this problem?

MySQL Administrator Backup/Restore
I use Backup option in MySQL Administrator to backup Database & Data. When go to Restore Backup file, restoring process (showing on progress bar) is allways finished before reaching full size of Backup file. Is that mean that NOT all data are restored? (I can't check whole database because of large amount of data).

Restore From Backup With MySQL Admin
I am trying to restore a mysqldump txt file to another cpu using MySQL Admin. I receive an error:

The selected file was generated by mysqldump and cannot be restored by this application.

I understand there are other ways of transfering a database to MySQL on a different cpu.
ie. mysql -u Username -p DBname < Filename.sql

When I try this it doesn't work I'm doing something wrong. Does the "filename" need to be an exact path to the file or just the file name ie. C:folderfolderdatabase.sql

Also does it matter if the file is .txt or .sql?
Does it matter where I have the database.sql file on my computer?
Do I need to have a new empty database created?

A Cron Job To Backup And Restore Mysql Db
my server won't give me shell so i'm stuck with phpmyadmin, its stupid, i can't even restore a 20mb backup, whether gzipped or text, it just times out..
so i want to do it via a cron job, what commands are they

Backup/restore User Accounts
I have mysql 5.0.14? installed.
I need to make a fresh installation of 5.0.18 (no update).
I can create a backup of my database (export) and import it
into the new version.

But how can I migrate my useraccounts?

The probem is, I tried re-creating a user with same pwd in the new version,
assigned privileges to that user and the user to my freshly imported database,
but when trying to access the DB using a script, it tells me that "user" has no access.

Backup And Restore Loads Of Db's
how would you go about backing up loads of databases (around 80megs worth) and then put them into another server.
I can do a mysqldump --all-databases > myBackup.sql
but i dont know how to get it back in on the other server?
I did try exporting with mysqladministrator, but the linux system just closed the program when I tried to restore it (it worked hwen i tried it on windows)

MySQL Cannot Backup And Restore UCS2 Data
Did a mysqldump and the UCS2 fields where dumped with insert statements with standard 8 bit ascii character strings, not UCS2.

Accidentally Deleted Root Account - Backup Available, How To Restore?
I managed to blindly delete the root account from the mysql user list through Webmin while on a cleanup spree. As you might be able to tell i'm completely stuck, no permissions to do anything, and I would really like to get access back.

I have, by stroke of luck, a backup of all databases I made some time ago.. the problem is, without permissions, how do I get it back in there?

Is there any way to do this without completely removing MySQL from my machine and reinstalling it?

Server details:
Mandriva Linux 10.2, MySQL 4.1.11, Apache 2, PHP4, etc.

I've got a backup of the 'mysql' database that I believe holds the usernames/passwords?

Backup/Restore And Collation/encoding Question [Resolved]
i know nothing of Mysql and i have a problem which i think should be easy for you guys that know about Mysql so i would be obliged for your help.

I have an online site which i try to install in my local php/mysql environment for testing e.t.c.

The problem is that the onlione site is using greek and english characters.
I use phpmyadmin for both backup and restores.

When i backup the database the phpmyadmin of my hoster gives me various choices of the encoding to be used with default the en-iso-8859-1.

When i try to restore it in my local environment i also have various option for the restore with default the utf-8 encoding.

Unfortunatelly after the restore my site gets all greek characters subtituted with many ???????'s

I tried exporting the database with utf-8 and restore it with the same encoding but the problem persists.

So i guess i will have to backup with en-iso-8859-1 (which actually my site pages uses also) and restore it with the same encoding which doesnt exists in my local phpmyadmin (or is it there but i have to choose another encoding?).

So what should i do?

I tried to read the docs about collation and encoding but i understand so few.

Backup/Restore Mysql Database From Remote Server To Local Pc
Im trying to download mysql database "dbproducts1" which is residing in my webhost's server (Linux). From telnet i backed up using "mysqldump -u -p dbproducts1 > dbproducts1.sql"

Then i downloaded this file via ftp to my local pc(WinXP). Now on my local pc to restore database i went to command prompt and i did following:

mysql -u -p dbproducts1 < dbproducts1.sql

I got following error:

ERROR 1064 (42000) at line 380: 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 '-workshop (
title varchar(250) NOT NULL default '',
decription longtext NOT ' at line 1

I opened the phpmyadmin and found that dbproducts1 database was restored but with only few tables i.e. not all tables in the db was restored.

Can somebody please help me to backup/restore this database. I checked the mysql server version and found following:

server version: 5.0.24a-community in my local winxp pc
server version: 3.23.36 in my webhost's server (Linux)

How To Backup/restore To Move Databases From One Server To Another (Win2k3 Enterprise, Mysql)
I'm trying to move a "schema" (I guess thats the term) aka database from our webserver2 to our webserver3. I backup the .sql file, and move it over to webserver3. I tell it to restore that file and create the database if it does not exist. Part way thru i get all these errors like: Code:

Backup MySQL Via Port 3306 (which Tool? Automatic Backup?)
i have quite a big database on my website - and i cannot get any backup via system dump or scripts anymore, because my hoster added some memory limits - and scripts/dump can't finish...

so the only way left is to backup my database via connection to port 3306 and tools like mysqlfront.

mysqlfront is unfortunately quite slow and buggy - so do you know any tools which can backup databases via external connection? - it would be great if there are tools which run automatically? (batch/commandline??)
backup with mysql.exe - h -u .... is impossible, too!

Restore Db From 4.1.12 To 3.23
I've a backup from my database running on mysql 4.1.12

Now i want to restore this to an old machine running 3.23.

How to do this. I get several errors.

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)

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 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?