?
Solved

Backing up MySQL DB

Posted on 2001-06-15
11
Medium Priority
?
236 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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

Become an Android App Developer

Ready to kick start your career in 2018? Learn how to build an Android app in January’s Course of the Month and open the door to new opportunities.

Question has a verified solution.

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

Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
Nothing in an HTTP request can be trusted, including HTTP headers and form data.  A form token is a tool that can be used to guard against request forgeries (CSRF).  This article shows an improved approach to form tokens, making it more difficult to…
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
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…
Suggested Courses

621 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