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.

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

        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.

          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?

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

              10 days later

              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?

                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.

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

                    Write a Reply...