SitePoint Sponsor

User Tag List

Results 1 to 3 of 3

Thread: MySQL Databases

  1. #1
    SitePoint Enthusiast AimyThomas's Avatar
    Join Date
    Sep 2011
    Posts
    46
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    MySQL Databases

    Hello all again,

    How do you guys backup your MySQL databases?

    I have a few programs that have an default back up that make an .sql file which is great but I also use OpenX which runs on PHP and has a database. That does not have an auto-back-up feature so do I just download the database and that's it?

    Any recommend for the tool?

    Thanks

  2. #2
    Hosting Team Leader silver trophybronze trophy
    cpradio's Avatar
    Join Date
    Jun 2002
    Location
    Ohio
    Posts
    5,223
    Mentioned
    153 Post(s)
    Tagged
    0 Thread(s)
    I use a cronjob to generate the backups running the command
    Code:
    suffix=$(date +%u)
    mysqldump --opt -u<backup_username> -p<backup_password> -h <mysl_host> <database_name> > <file_name>-$suffix.sql
    tar -czf <file_name>-$suffix.tar.gz <file_name>-$suffix.sql
    uuencode <file_name>-$suffix.tar.gz | mail -s "MySQL Backup" <your_email_address>
    I then have this run daily so it generates a new file each day of the week, and then it will overwrite the prior week. You can then have it e-mail the file to you or store it in a folder on your server that you can download later. You can even change the %u to %F so it writes the file for each day of the year (they will never get overwritten)

  3. #3
    Non-Member
    Join Date
    Sep 2012
    Location
    Sweden
    Posts
    35
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    If you run on cPanel, it has MySQL database backup feature as well as full site backup.

    On other way you can use some php code to make a backups, XCloner is also a great solution for backup management.

    Or some custom PHP like:



    // Function to backup the table and save it to a sql file
    function backup_tables($host,$user,$pass,$name,$tables,$bckpfilename)
    {
    $link = mysql_connect($host,$user,$pass);
    mysql_select_db($name,$link);
    $return = "";
    mysql_query("set names 'utf8'");

    // Get all of the tables
    if($tables == '*') {
    $tables = array();
    $result = mysql_query('SHOW TABLES');
    while($row = mysql_fetch_row($result)) {
    $tables[] = $row[0];
    }
    } else {
    if (is_array($tables)) {
    $tables = explode(',', $tables);
    }
    }
    // Cycle through each provided table
    foreach($tables as $table) {
    $result = mysql_query('SELECT * FROM `'.$table.'`');
    $num_fields = mysql_num_fields($result);

    // First part of the output - remove the table
    $return .= 'DROP TABLE `' . $table . '`;<|||||||>';

    // Second part of the output - create table
    $row2 = mysql_fetch_row(mysql_query('SHOW CREATE TABLE `'.$table.'`'));
    $return .= "\n\n" . $row2[1] . ";<|||||||>\n\n";

    // Third part of the output - insert values into new table
    for ($i = 0; $i < $num_fields; $i++) {
    while($row = mysql_fetch_row($result)) {
    $return.= 'INSERT INTO `'.$table.'` VALUES(';
    for($j=0; $j<$num_fields; $j++) {
    $row[$j] = addslashes($row[$j]);
    $row[$j] = ereg_replace("\n","\\n",$row[$j]);
    if (isset($row[$j])) {
    $return .= '"' . $row[$j] . '"';
    } else {
    $return .= '""';
    }
    if ($j<($num_fields-1)) {
    $return.= ',';
    }
    }
    $return.= ");<|||||||>\n";
    }
    }
    $return.="\n\n\n";
    }

    // Save the sql file
    $handle = fopen($filess.'.sql','w+');
    fwrite($handle,$return);
    fclose($handle);

    // Close MySQL Connection
    mysql_close();
    }
    @ini_set('max_execution_time', 600); //300 seconds = 5 minutes
    @ini_set('max_input_time', 600);
    @ini_set('memory_limit', '256M');
    @ini_set('post_max_size', '256M');

    // Call the backup function for all tables in a DB

    backup_tables('localhost', 'database-user', 'database-pass', 'database-name', '*', 'mysql-backup-file-name');


    // Print the message
    print('The backup has been created successfully!');

    ///


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
  •