Database Backup Script - Can This Be Improved?

Does mysqldump log in any way when and what was dumped/backed up? I want to have the higher-ranked admins to be able to view backup/dump activity (including the source/how it was trigged) so that they can spot if anyone is trying to do dumps without permission.

I’m look atm for the command prompt command(s) needed to have mysql dump a copy of one table of the database to a file so I can compare the output of my script with the output of mysql dump with the output from PHPMyAdmin.

EDIT: just tried:

mysqldump -uroot -ppassword --all-databases --events > C:\dbdump.sql

To do a dump of all databases, no errors, no sign of any dump happening. Am I doing something wrong or is it going wrong somewhere (file permissions maybe)?

btw @oddz; this script was on the documentation page for mysql dump on the mysql website, what do you think of it apart from obviosuly needing the mysql_* functions replacing?

<?php
        // Change Me 

    $username = "root";
    $password = "password";
    $backup_dir = "/mnt/backup";
    $dump = "/usr/bin/mysqldump";
    $grep = "/bin/grep";
    $gzip = "/bin/gzip";

        // This should not need changing from here

    function sql_dict($sql){
        $x = mysql_query($sql);
        if ($x) return mysql_fetch_assoc($x);
    }
    
    function cleanup($dir){
        $d = dir($dir);
        echo "Path: " . $d->path . "\n";
        while (false !== ($entry = $d->read())) {
            if ($entry=="." or $entry=="..") continue;
            $e = str_replace(".sql.gz","",$entry);
            $x = sql_dict("describe $e");
            if (!$x) {
                print "Removing old backup file [$entry]\n";
                unlink("$dir/$entry");
            }
        }
        $d->close();
    }

    function crc32_file($filename)
    {
          global $gzip;
        $x = exec("$gzip --list --verbose $filename");
        $x = explode(" ",$x);
        return $x[1];
    }

    if (mysql_connect("localhost",$username,$password)) print "Connected.\n";
    else die("Failed to connect to database."); 
    $dbs = mysql_query("show databases");
    if ($dbs) while ($db = mysql_fetch_array($dbs, MYSQL_ASSOC)) {
        $db = $db['Database'];
        if ($db=="information_schema") continue;
        if (mysql_select_db($db)) print "Selected [$db]\n";
        else die("Failed to select db [$db]");
        foreach (array("schema","data") as $pass){
            $sql = mysql_query("show tables");
            $day = date("l");
            if ($pass=="schema") $dir = "/$backup_dir/$db/schema";
            else $dir =  "/$backup_dir/$db/$day";
            if (!file_exists($dir)) system("mkdir -p $dir");
            if (!file_exists($dir)) die("Couldn't Create $dir");
            if ($pass=="data"){
                $latest = "/$backup_dir/$db/latest";
                unlink($latest);
                system("/bin/ln -s \"$dir\" \"$latest\"");
            }            
            cleanup($dir);
            if ($sql) while ($s = mysql_fetch_assoc($sql)) {
                if (!isset($s["Tables_in_{$db}"])) {
                    print "no result";
                    print_r($sql);
                    die();
                }
                $t = $s["Tables_in_{$db}"];
                if (
                 $pass=="schema" ) $data = "--no-data";
                 else $data = "--lock-tables";
                 $tab = $t;
                 $lim = 30;
                 if (strlen($tab)>$lim) $tab = substr($tab,0,$lim-3)."...";
                 while (strlen($tab)<30) $tab .= " ";
                print "BACKUP: $pass : $day : $db : $tab : ";
                if ($pass=="data"){
                    print "Check : ";
                    $check = sql_dict("check table $t");
                    $check = $check['Msg_text'];
                    print "$check : ";
                    if ($check != "OK") {
                        print "Repair";
                        $repair = sql_dict("repair table $t");
                        $repair = $repair['Msg_text'];
                        print " : $repair : ";
                    }
                    if ($day=="Sunday"){
                        // optimize
                        print "Optimize : ";
                        $type = sql_dict("show table status like '$t'");
                        $type = $type['Engine'];
                        if ($type=="MyISAM") sql("optimize table $t");
                        if ($type=="InnoDB") sql("alter table $t engine='InnoDB'");
                    }
                }
                if (isset($argv[1])){
                    print "Skipping dump\n";
                } else {
                    $temp = "/tmp/backup.$t.sql.gz";
                    $out  = "$dir/$t.sql.gz";
                    print "Dump : ";
                    $cmd = "$dump -u$username -p$password $data --quick --add-drop-table $db $t | $grep -v 'Dump completed' | $gzip -n > $temp";
                    system($cmd);
                    print "CRC32 : ";
                    if (!file_exists($out)){
                        print "Saving  : ";
                        $cmd = "/bin/mv $temp $out";
                        system($cmd);
                    } else {
                        $md5  = crc32_file($temp);
                        $nmd5 = crc32_file($out);
                        if ($md5!=$nmd5) {
                            print "Saving  : ";
                            $cmd = "/bin/mv $temp $out";
                            system($cmd);
                        } else {
                            print "Skipped : ";
                            unlink($temp);
                        }
                    }
                    $size = filesize($out);
                    print "[$size]\n";
                }
            }
        }
    }
?>

This does not make any sense, why would you give anyone the kind of access they need to create the dump, if they should not be able to do so? This is a access level problem, and not a logging problem.

Mysqldump is logged, but if you have access you can also flush the logs with --flushlogs.

mysqldump -root -ppassword database table table2 > dbdump.sql

One word: legacy.

If you decide to use parts of it, be very careful with the pass section. In the event it is a low visited website, and the tables are small it should not be a problem. If the tables are large, this is processes which could take time, and create deadlocks.

If someone could also flush the logs then it makes mysqldump no good if someone has managed to hack an admin’s account, by being able to log when the database has been backed up it’ll help to spot any suspsious actuivity that might indicate that an admin has had their account hacked (who would then - at least temporarily - need to be immediately demoted back to a normal member)

Just tried

mysqldump -root -ppassword universal_empires > dbdump.sql

Just tried to do a dump of all tables in the “universal_empires” database, no sign of any activity, no dump file showing up, no errors given

If you initiate this from the control panel, there is nothing that stops you from creating a log entry in a database table for example.

If there is no errors or messages, it usually means it succeeded. The question is where it placed the file in that case. Have you tried running mysqldump without any commands to see that it actually allow you to run it?

I’m not sure that the MySQL command prompt is working properly:

mysqldump;

Gives this error
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘mysqldump’ at line 1

mysqldump is an executable program and you can’t run it from mysql command prompt, which is used to run sql queries. You have to launch mysqldump from the system command line.

I still find it a bit amusing that it gives :“entering password from command line is insecure”

Anyway, I don’t know where it would be on a live site, but on my Windows it’s in the bin folder.

c:\Program Files\MySQL\MySQL Server 5.7\bin>

The crazy thing is to get the mysql console to work (found in the Mysql section of the wamp menu), i had to add the root user back in! My own preference is normally to create my own super user and then delete the root user

WAMP and XAMP place things in all kind of crazy ass places. Using a true Linux flavor VM is more accurate to an actual prod environment unless running a windows server. Wamp and Xamp are for the birds… nothing in that local set-up will be true to an actual prod environment except PHP running.

What would be a good estimate of how long it takes to make a dump per MB of data? (all the tabbles are InnoDB)

Since this depends on a lot of factors, including CPU power, available RAM, Disk IO, size of the table, DB traffic etc. it is impossible to create an algorithm to generate an estimate.

If you run the dump early AM on Sunday, it will in most cases be much quicker than if you try to run it during peak hours on a weekday.

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.