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
    4,807
    Mentioned
    141 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)
    Be sure to congratulate xMog on earning April's Member of the Month
    Go ahead and blame me, I still won't lose any sleep over it
    My Blog | My Technical Notes

  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
  •