[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Backing up MySQL DB

Posted on 2001-06-15
11
Medium Priority
?
234 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

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

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!

Question has a verified solution.

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

Developers of all skill levels should learn to use current best practices when developing websites. However many developers, new and old, fall into the trap of using deprecated features because this is what so many tutorials and books tell them to u…
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…
The viewer will learn how to dynamically set the form action using jQuery.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
Suggested Courses

656 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