backing up mysql

rulian

okay, for reasons that really piss me off, i need to reformat my computer. But I'm developing a php site that uses mysql on win2k, last time i formatted my hard drive, i lost my db, so now I just wanna make sure i have everything backed up before i do this. So is there anything special or tricky i should know other than not erasing the mysql folder. Also, i'm going to use phpmyadmin to backup all the structrue and data in text format. Is this also good?

calevans

on a *nix machine:

mysqldump {databaseName} --opt > {fileNameToCreate}.sql

Since I use InnoDB tables, I always use the --opt. It groups together several of the commands including fast inserts.

Not sure how on Windows but it can't be that different.

Cal

Cal Evans
- Senior Internet Dreamer
- Techno-Mage
- Last of the great Internet Burma-Shave poets.
http://www.calevans.com
*

Anon

Just copy directory mysql\data

[deleted]

That's not the right solution.
Use mysqldump like Carl said.

NEVER copy/backup a database by copying the datafiles directly.

Anon

Why?

[deleted]

You must shutdown the database server before copying the files from or to it, which is not acceptable in a production environment.
If you upgrade MySQL your backup files may become incompatible with the new version.
You cannot restore the files on a different OS.
You can't simply restore one table from one database.

But the first reason should be enough.

Anon

On the other hand, a dump will break if you're storing binary data in the database.

[deleted]

Maybe on your planet it does :-)

Anon

You're saying it doesn't?

I've never been able to get an SQL dump with binary data in to import sucessfully. I've always had to shut down the SQL server, copy the data files and restart the SQL server.

How do you get it to import ok?

andriy

How I can make database work again after dumping it?

What I have to do if I'm using ISP and do not have direct posiblity to copy files from mysql/data?

[deleted]

Nope, never had any problems.
to backup:
mysqldump databasename --opt >dump.sql

and to import:
mysql databasename < dump.sql

All the binary data is escaped by MySQL itself.

[deleted]

You'll need shell-access to backup and restore the database.

use mysqldump to make a backup, and
use mysql itself to restore:

to backup:
mysqldump databasename --opt >dump.sql

and to import:
mysql databasename < dump.sql

andriy

I have got after
$mysql databasename < dump.sql
Output:
ERROR 1044: Access denied for user: '@' to database 'databasename'

calevans

mysql {databasename} < {fileName}

Make sure the database exists first. (Do a create database if it doesn't)

Once you do a dump, open it with a text editor and take a look at it. You'll find that it should have DROP statements for each table, CREATE statements for each table and INSERT statements for each row in each table.

Depending on your hardware, hw you have MySQL configured, what tables types you use and how much data you have, the import can be very quick or take a while.

Anyhow, it is the best way to backup a database. DO NOT (for reasons explained elsewhere) copy the files directly. My favorite reason for using mysqldump is that I have 2 servers, production and development. They are usually NOT running the same version of mysql. I can refresh my development server with data from my production server anytime I want with mysqldump. Copying the directories wouldn't work at all.

HTH,
Cal

Cal Evans
Senior Internet Dreamer
http://www.calevans.com
*

[deleted]

'obviously' you may have to add the username and password information in order to be able to logon in mysql.

calevans

Vincent is right. Sorry, I set mine in my .my.cnf so long ago I forgot that if you don't have it setup you need to specify the -l {username} and -p options as well.

Cal

Cal Evans
Senior Internet Dreamer
http://www.calevans.com
*

andriy

Do I have to delete database before
$mysql --user=user --password dbname < db.sql ?

calevans

No.

If you use --opt:
Same as --add-drop-table --add-locks --all --extended-insert --quick --lock-tables

This will drop each table before it creates it.

Cal

Cal Evans
Senior Internet Dreamer
http://www.calevans.com
*

Anon

vincent wrote:

You must shutdown the database server before copying the files from or to it, which is not acceptable in a production environment.

-Environment in which he is going to format server hard disk is a "production environment"!

If you upgrade MySQL your backup files may become incompatible with the new version.

-you tried this - I don't think.

"3. You cannot restore the files on a different OS."

-Are you serious , or you just talk by heard

"4. You can't simply restore one table from one database."

-In his case he wants the whole db!

But the first reason should be enough.

Anon

It is a matter of size. I had to do this with a db of 100Mb and longblobs. The dump run on Linux machine, but on Win - failed in the middle.

I,m not pretending that coping files is better idea to do db ðåplication, but in Rulian's case it is acceptable!