3

I have a backup file (.sql.gz) and I want to import it to my empty database on localhost programmatically like how phpmyadmin does, inside a browser with php/mysql/javascript and not via command line. Since it's my localhost, security is not really a concern, I just want to automate a task I have to perform regularly when developing.

There are a bunch of questions here but they all deal with command line solution.

edit: I already have a xampp installation. But the whole procedure is tedious, I have to first delete the database, then recreate it and then import my data. I have to move b/w previous database backus fairly often. So I want to automate the process. I just give in the backup .sql.gz file via html form input and it does all of the above automatically.

Achshar
  • 5,153
  • 8
  • 40
  • 70
  • Do you care if you have to uncompress the `.gz` to just a `.sql` file? – doitlikejustin Apr 16 '13 at 15:24
  • Not sure, But I guess I will have to do whatever it takes to achieve my end result. If it isn't required then I would rather not do it. This is not production code. – Achshar Apr 16 '13 at 15:25
  • mysql can't import a gzipped file directly. you'll have to uncompress it externally first. e.g. `gzcat backup.sql.gz|mysql -p` – Marc B Apr 16 '13 at 15:26
  • @MarcB can that be done server side in any way, not by command line? I have to do all of it inside the browser. – Achshar Apr 16 '13 at 15:27
  • 1
    Why don't you just download phpMyAdmin? – doitlikejustin Apr 16 '13 at 15:27
  • @doitlikejustin I already have it. I have a xampp installation. But the whole procedure is tedious, I have to first delete the database, then recreate it and then import my data. I have to move b/w previous database backus fairly often. So I want to automate the process. I just give in the backup .sql.gz file via html form input and it does all of the above automatically. I should mention this in the question description itself. – Achshar Apr 16 '13 at 15:30
  • @Achshar: no. it's not mysql's job to guess what of the bajillion compression formats available you're using. it expects plain-text sql, and it's your job to feed it that. – Marc B Apr 16 '13 at 15:30
  • @MarcB from server side I meant via PHP or something. Not via mysql. If it's not possible then can i execute command line code via a browser in xampp? As i said I am not worried about security. – Achshar Apr 16 '13 at 15:32
  • 1
    if you're uploading to a php script, then you can easily `exec()` that command from within PHP. – Marc B Apr 16 '13 at 15:35

3 Answers3

1

I'd open it with gzfile, separate it on the query-delimiter and put it into mysqli::query

$file = implode('', gzfile($sqlFile)); // there doesn't exist a gz* function which reads it completely into a string?
$query = $substring_delimiter = "";
$last_was_backslash = false;
$outside_begin_end = true;
$delimiter = ';';
for ($i = 0; $i < strlen($file); $i++) {
    if ($i > 3 && !strcasecmp(substr($file, $i - 4, 5), "BEGIN") && !$substring_delimiter)
        $outside_begin_end = false;
    if (!$outside_begin_end && !strcasecmp(substr($file, $i - 2, 3), "END") && !$substring_delimiter)
        $outside_begin_end = true;
    if ($i > 7 && !strcasecmp(substr($file, $i - 8, 9), "DELIMITER") && trim($query) == '') {
        $delimiter = '';
        do {
            $delimiter .= $file[$i];
        } while (++$i < strlen($file) && $file[$i] != PHP_EOL)
        $delimiter = trim($delimiter);
    }
    if ($file[$i] == '\'' || $file[$i] == '"')
        if ($substring_delimiter) {
            if ($substring_delimiter == $file[$i] && !$last_was_backslash) 
                $substring_delimiter = "";
        } else {
            $substring_delimiter = $file[$i];
        }
    if ($outside_begin_end && !$substring_delimiter && !strcasecmp($delimiter, substr($file, $i))) {
        $sql->query($query); // where $sql is a mysqli instance
        $query = "";
    } else {
        $query .= $file[$i];
    }
    if ($file[$i] == '\\')
        $last_was_backslash = !$last_was_backslash;
    else
        $last_was_backslash = false;
}
if (trim($query) != "")
    $sql->query($query);
bwoebi
  • 23,637
  • 5
  • 58
  • 79
  • No, this does not work for SQL scripts. SQL scripts can contain lines that you can't feed to mysqli::query(). The `;` delimiter can occur inside the bodies of stored routines. Some statements don't terminate in `;`. Also, your $file variable contains the *entire* uncompressed contents of the SQL script? The script is probably compressed because it's very long, longer than your PHP memory limit. – Bill Karwin Apr 16 '13 at 15:40
  • @BillKarwin Effectively, I also have to check for `BEGIN ... END` constructs, thanks. Every statement which doesn't need to terminate in `;` can be fed into a mysqli::query – bwoebi Apr 16 '13 at 15:43
  • No, there are several statements that are builtin commands for the `mysql` client, which *cannot* be fed to mysqli::query(). See http://dev.mysql.com/doc/refman/5.6/en/mysql-commands.html – Bill Karwin Apr 16 '13 at 15:44
  • @BillKarwin this aren't SQL statements, only commands, which can't (shouldn't) be used in a .sql – bwoebi Apr 16 '13 at 15:50
  • You can't make that assumption. The OP says he is importing a backup file (typically produced by mysqldump). It is ordinary for that script to contains at least the builtin command `DELIMITER`, if there are triggers and stored procedures in the backup. – Bill Karwin Apr 16 '13 at 15:51
  • @BillKarwin I'm sure that this aren't sql statements. When I fed the mysqld with it I receive only syntax errors. And the docs state that "[...] mysql itself interprets" (mysql is the shell utility, not the daemon). – bwoebi Apr 16 '13 at 15:54
  • If it helps this is the cron job that generates the backup `mysqldump --opt --user username -ppassword database | gzip -c | uuencode sqlbackups/backup.sql.gz` – Achshar Apr 16 '13 at 15:56
  • @Achshar, I don't know why you're using uuencode, but are you aware that using uuencode makes your gzipped file about 35% larger than it needs to be? – Bill Karwin Apr 16 '13 at 16:00
  • @BillKarwin I am a command line newbi. Can you suggest a better command? I would love to reduce the size of my backup file. It is increasing quickly. – Achshar Apr 16 '13 at 16:01
  • @BillKarwin better now? It now even recognizes the `DELIMITER` command. – bwoebi Apr 16 '13 at 16:04
  • @bwoebi, BEGIN and DELIMITER can occur inside comments, inside string literals, or even as table or column identifiers. They can also be lower case. Look, you can eventually write enough code to duplicate the functionality of the mysql client. Then you will have something that works exactly like a tool that already exists, except implemented in a slower language (PHP) and with no unit tests. – Bill Karwin Apr 16 '13 at 16:07
  • @Achshar: `mysqldump --user username -ppassword database | gzip -c > sqlbackups/backup.sql.gz` – Bill Karwin Apr 16 '13 at 16:09
  • @BillKarwin I use strcasecmp which is case insensitive. I know that it may appear in identifiers, but there are none ;) (And no, it cannot appear in string literals, there are checks against it, look.) – bwoebi Apr 16 '13 at 16:11
  • @BillKarwin I also have it rigged to mail it as an attachment. And this doesn't work. `mysqldump --user username -ppassword database | gzip -c > sqlbackups/backup.sql.gz | mail -s"title" example@example.com` Any ideas? Says no such file in directory. – Achshar Apr 16 '13 at 16:24
  • @BillKarwin but this older version works, which uses uuencode `mysqldump --opt --user username -ppassword database| gzip -c | uuencode sqlbackups/backup.sql.gz | mail -s"title" example@example.com` – Achshar Apr 16 '13 at 16:29
  • @Achshar, we're getting off track from the original question, but yes that's a valid use of uuencode. It wasn't clear from your previous description that you were emailing the file. – Bill Karwin Apr 16 '13 at 16:32
  • @BillKarwinyea sorry about that. And thanks for help! I am trying both the answers out, I will accept the answer in a few hours. Thanks! – Achshar Apr 16 '13 at 16:36
1

Comment from @MarcB is correct. Use PHP to call out to a shell process to load the SQL script.

Writing a PHP script to execute a backup script is a waste of time. You basically have to implement the mysql client in PHP.

The better solution is something like this:

shell_exec("gunzip -c $file_sql_gz | mysql --defaults-file=$my_cnf $database_name");

Where $my_cnf is the name of a my.cnf-like file that contains host, user, password to connect.

See also some of my past answers:

Re your comment:

Refer to http://www.php.net/manual/en/features.file-upload.post-method.php
You can access the temp name of a file upload with $_FILES['userfile']['tmp_name'].

Community
  • 1
  • 1
Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
  • How would the command change if the file is taken from user via html form input tag and submitted via POST? If it requires the file to exist on the disk, can we make a temp file in the root and use that in the command? How would I come about doing something like that? – Achshar Apr 16 '13 at 15:59
0

Sypex Dumper can do it I believe.

Your Common Sense
  • 156,878
  • 40
  • 214
  • 345
  • Is there a non library method to do it? I am doing this for practice and on my localhost to learn stuff, using a library would defeat the purpose. – Achshar Apr 16 '13 at 15:28