Solved

Backing up MySQL DB

Posted on 2001-06-15
11
224 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
Technology Partners: 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 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

Secure Your Active Directory - April 20, 2017

Active Directory plays a critical role in your company’s IT infrastructure and keeping it secure in today’s hacker-infested world is a must.
Microsoft published 300+ pages of guidance, but who has the time, money, and resources to implement? Register now to find an easier way.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
migrating to phpbb forum from vBulletin 4.2 3 41
Ajax and PHP 9 54
EditableGrid how to fetch rows from MySql in php 14 48
PHP substring 3 16
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…
I imagine that there are some, like me, who require a way of getting currency exchange rates for implementation in web project from time to time, so I thought I would share a solution that I have developed for this purpose. It turns out that Yaho…
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.

730 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