SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Wizard maartenvr's Avatar
    Join Date
    Jun 2002
    Location
    Canada
    Posts
    1,676
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    How can I download the entire MySQL database without PHPmyAdmin?

    I have a client who had someone setup a shopping cart for him but did not setup the database in his account, yet it connects using localhost. But with PhpMyAdmin I can not access it. I have the details how to access the databse, the username, password and database name. Is there a different way of dowloading the items?
    Maarten

  2. #2
    ☆★☆★ silver trophy vgarcia's Avatar
    Join Date
    Jan 2002
    Location
    in transition
    Posts
    21,236
    Mentioned
    1 Post(s)
    Tagged
    1 Thread(s)
    If you have MySQL installed on your machine, you can use the command-line client to connect to the remote database. Then you can generate a SQL dump and output it to a text file on your computer somewhere.

  3. #3
    Keep it simple, stupid! bokehman's Avatar
    Join Date
    Jul 2005
    Posts
    1,933
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I wrote a PHP file to backup my tables to csv files and another to reload them. If this is the sort of thing you are looking for I could post it.

  4. #4
    SitePoint Wizard maartenvr's Avatar
    Join Date
    Jun 2002
    Location
    Canada
    Posts
    1,676
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by bokehman
    I wrote a PHP file to backup my tables to csv files and another to reload them. If this is the sort of thing you are looking for I could post it.
    That would be awesome if you could!
    Maarten

  5. #5
    Keep it simple, stupid! bokehman's Avatar
    Join Date
    Jul 2005
    Posts
    1,933
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    PHP Code:
    // make a back up to CSV files

    $tables = array('table1''table2''table3');

    foreach(
    $tables as $table){
        
    $query "SELECT * FROM $table";
        
        
    $query_result mysql_query($query) or die ('Table: '$table .' - Error: 'mysql_error());
        
        
    $csv_contents '';
        if(
    mysql_num_rows($query_result) > 0){
            while(
    $row mysql_fetch_assoc($query_result)){
                
    $i 0;
                foreach(
    $row as $v){ // creates this: "value one","value two","value three"
                    
    $csv_contents .= ($i) ? ',' '';
                    
    $i++;
                    
    $csv_contents .= '"'.addslashes($v).'"';
                }
                
    $csv_contents .= "\r\n";
            }
        }
            
        
    $backup_file $_SERVER['DOCUMENT_ROOT'].str_replace(basename($_SERVER['PHP_SELF']), ''$_SERVER['PHP_SELF']).'../backup/'.$table.'_backup.csv';
                 
        
    $fp = @fopen($backup_file'w') or die('Could not create/open ' $backup_file);
        
    fwrite($fp$csv_contentsstrlen($csv_contents));
        
    fclose($fp);
    }

    echo 
    'Done!'
    PHP Code:
    // reload the backup into empty tables

    $tables = array('table1''table2''table3');

    foreach(
    $tables as $table){
        
    $backup_file $_SERVER['DOCUMENT_ROOT'].str_replace(basename($_SERVER['PHP_SELF']), ''$_SERVER['PHP_SELF']).'../backup/'.$table.'_backup.csv';
        
        
    $query "load data infile '$backup_file
                  into table 
    $table 
                  fields terminated by ',' 
                  enclosed by '\"' 
                  lines terminated by '\r\n'"
    ;
                  
        
    $query_result mysql_query($query) or die (mysql_error());
    }

    echo 
    'Done!'

  6. #6
    SitePoint Wizard maartenvr's Avatar
    Join Date
    Jun 2002
    Location
    Canada
    Posts
    1,676
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks a lot! I get one error:

    Table: items - Error: No database selected

    I replaced table1, table2 and table3 just with "items" Why would it say this?
    Maarten

  7. #7
    SitePoint Wizard maartenvr's Avatar
    Join Date
    Jun 2002
    Location
    Canada
    Posts
    1,676
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Actually... nevermind... looks like it worked!
    Maarten


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •