Solved

Backing up MySQL DB

Posted on 2001-06-15
11
193 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
 

Author Comment

by:kuist
Comment Utility
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
Comment Utility
is there a way like mysqldump to dump a whole database at a time instead of just 1 table?
0
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
LVL 8

Expert Comment

by:us111
Comment Utility
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
Comment Utility
0
 
LVL 2

Expert Comment

by:higijj
Comment Utility
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
Comment Utility
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
Comment Utility
// 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
Comment Utility
Thanks for everything All!

Worked great!
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Foreword (July, 2015) Since I first wrote this article, years ago, a great many more people have begun using the internet.  They are coming online from every part of the globe, learning, reading, shopping and spending money at an ever-increasing ra…
This article discusses how to create an extensible mechanism for linked drop downs.
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 count occurrences of each item in an array.

771 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now