Solved

Backing up MySQL DB

Posted on 2001-06-15
11
214 Views
Last Modified: 2008-02-01
Hey!

here is my situation:

I have way too much information in 2 of my tables (stats for an affiliate program) so when affiliates try to access the stats, it times out. What I'd like to do, is create a table that would be used for the stats of the current day and one for the current month. And I would run a php file once a day to put the previous day stats that are still on the current day table to current month table..  and I would create one table per month..

so far, is this a good idea?

What I need here.. is a way to 'transfer' the data over another table.. what's the best way to do that?!

It is kind of urgent!
0
Comment
Question by:kuist
  • 5
  • 3
  • 2
  • +1
11 Comments
 

Author Comment

by:kuist
ID: 6196816
I just checked it out.. there is 3,462,596 row in my biggest table.. that's for April, May and June.. So I guess wouldn't hurt to split this into 3 tables..

0
 

Expert Comment

by:Curieux
ID: 6197829
Hi,

The best way to backup a table or a database is:

SHELL>mysqldump my_database my_table > my_table_back.sql

and to reconstruct the table from the file do this:

SHELL>mysqlimport my_database my_table_back.sql

This will create a table called my_table_back in the database my_database.

For more information on this see the mysql manual chapters
14.4 and 14.5

Hope this helps
 
0
 
LVL 8

Expert Comment

by:us111
ID: 6197903
hmmm good idea.....I don't really know. I've never dealt with a such database :)

the idea is to retrieve the data for the last days and then to insert them into the new table.
But don't use browser php script, I mean , use a php script which is ran directly on to the server
like a shell script.
0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 

Author Comment

by:kuist
ID: 6198208
us111: what's the difference?
and how can I know if my PHP is installed in a way that I can run shell script?!
0
 

Author Comment

by:kuist
ID: 6198220
is there a way like mysqldump to dump a whole database at a time instead of just 1 table?
0
 
LVL 8

Expert Comment

by:us111
ID: 6198426
when you use browser script, your script could timed out or your browser crashed.
If you use php script as shell script, you can run it as a daemon.

I think that you can dump the whole database with mysqldump, I don-t remember, check the doc

Maybe try to access your database with mysqlfront
http://www.mysqlfront.com



0
 
LVL 8

Expert Comment

by:us111
ID: 6198438
0
 
LVL 2

Expert Comment

by:higijj
ID: 6199168
Well.. it would be
http://www.phpdeveloper.org/tutout.php?num=23

to set PHP as CGI, not num=24 ;-)


0
 

Author Comment

by:kuist
ID: 6199171
Thanks for the CGI as PHP  link.. will check it out ASAP

But my main concern here is the way I should transfert the data..


just do a SELECT statement, loop through it and for each row, do a INSERT statement.. once all statement have been added, do a DELETE statement?

that's kind of too easy.. don't you think?
It must be a better way to move data across table..

Kuist!

P.S.: As for mysqldump.. I found how it works ;-)
Thanks!
0
 
LVL 2

Accepted Solution

by:
higijj earned 150 total points
ID: 6199185
// Given the fact that you have a field in the main table that
// is of type DATE and this field would be called date_field
// and that you would have previously created the table for may

Here would be a code snipet to do that:

<?

$SQLHost = "localhost";
$SQLUser = "sql_username";
$SQLPassword = "sql_password";
$SQLDatabase = "your_database_name";


// Get all row for MAY
$link = mysql_connect($SQLHost, $SQLUser, $SQLPassword) OR DIE ("Unable To Connect To Database");

     mysql_select_db($SQLDatabase, $link) OR DIE("Unable To Select To Database");
     $sql = "SELECT * FROM tbl_name WHERE MONTH(date_field)=5"; // to grab all row for may
     $result = mysql_query($sql);

mysql_close($link);


// loop through the rows and INSERT them into the new table
// as well as DELETE them from the main table..
while ($row = mysql_fetch_array($result)) {

     $link = mysql_connect($SQLHost, $SQLUser, $SQLPassword) OR DIE ("Unable To Connect To Database");

          mysql_select_db($SQLDatabase, $link) OR DIE("Unable To Select To Database");
          $sql = "INSERT INTO tbl_may VALUES('field_1', 'field_2', 'field_3', ...)"; // add the row to the table for may
          mysql_query($sql);
          $sql = "DELETE FROM tbl_name WHERE field_1=$row[field_1] AND field_2=$row[field_2] AND field_3=$row[field_3] AND MONTH(date_field)=5"; // make sure you delete the appropriate record!!
          mysql_query($sql);
         
     mysql_close($link);

} // end while

?>


you could do the same for a daily table.. but instead of using the MONTH function, you would have to use the DAY function..
0
 

Author Comment

by:kuist
ID: 6199258
Thanks for everything All!

Worked great!
0

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
Author Note: Since this E-E article was originally written, years ago, formal testing has come into common use in the world of PHP.  PHPUnit (http://en.wikipedia.org/wiki/PHPUnit) and similar technologies have enjoyed wide adoption, making it possib…
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question