[How To]: Backup huge MySQL Database

r4nd0mch4r

Which is the best way?

Let's say that we have a database with 20 tables and alltogether about 1 million records in it. They are all important because tables are connected.

How would you do backup something like this? Where/how would you store the backups?

Similar important question:
How would you design a database that is expected to grow huge? And we all need the speed... =]

________

Use a script like phpMyAdmin or a program like MysqlControlCenter to download back-up the data.

How would you design a database that is expected to grow huge?

Try to limit the number of fields and index the most common search fields. Remove old data that you don't use.

r4nd0mch4r

I believe I read somewhere that if I use phpMyAdmin to backup a really big DB it just ends up with "timeout..."

Besides, I have to write this app on my own and include it in the webmin I've written...

nemonoman

exec("mysqldump -h Myhostname -uMYusername -pMypassword --opt MydatabaseName > Myfilename.sql") ;

This will create a downloadable file called Myfilename.sql

It is very fast.

A million rows isn't a lot of rows, frankly.

How do you design a database that is expected to get huge?

I expect all databases to get huge. It is their nature.

If you follow basic normalization design: that is, so much as is practical, eliminate REDUNDANT data, that is a good start.

Normalized databases might actually have more records, but will even so be significantly smaller in bytes, and much faster for basic SQL operations than their non-normal counterparts.

It is never too late to review and modify your db design for increased normalization.

r4nd0mch4r

Normalized databases? I'm really not familiar with that expression, care to explain?

Another question: Is MySQL really the best choice when storing important data in large amounts, or are there better alternatives?

nemonoman

Normalization: Start by STFW:

http://www.google.com/search?sourceid=navclient&ie=UTF-8&oe=UTF-8&q=normalized+database

In fact, start by searching the F PHPBuilder...this article is a good start:

http://www.phpbuilder.com/columns/barry20000731.php3

r4nd0mch4r

This is defintely good stuff ;-) Silly me didn't check teh Google... thanks! =)

r4nd0mch4r

Heiii! /me is back 😛

What's better... to have more databases or only 1 database and 30+ tables in it?
(if several tables would be for backup only, would it be better to create a new database only for backup and logging?)

Is it really that important [n]not[/b] having data repeating itself that I should create more tables? Is there a limit of tables per database before things become unstable?

nemonoman

What's better... to have more databases or only 1 database and 30+ tables in it?

1 database

(if several tables would be for backup only, would it be better to create a new database only for backup and logging?)

Yes

Is it really that important [n]not[/b] having data repeating itself that I should create more tables?

Yes

Is there a limit of tables per database before things become unstable?

You're not likely to reach the limit anytime soon.

r4nd0mch4r

Even if you normalize your tables, there are still some elements that will be repeated, like ID numbers... then what do you do?