I just started getting a backup file (*.7z) from the folks that run our database once a week. They're sending it to me through an FTP server (I use FileZilla) — but the file is pretty huge: 5.8 billion bytes. I could download the file to my computer, but it would probably take about seven hours to do so.
Can somebody help walk me through if it's possible to grab their .7z file through MySQL (or specifically SequelPro would be amazing, since that's what I use) from their server, so that I don't have to download it every time? I've never logged on to use MySQL before from anything but my 'root' login or local server.
If somebody could tell me the steps I need to take, it'd be very helpful! Would be happy to provide more information if any more is needed.
Thanks, everyone!
Best Answer
What you do depends heavily on what you're wanting the end result to be, but there is almost certainly not a way to do what you are thinking of... and if you examine the content of the files, the reason for this should become apparent.
The .7z file format is a compression+container format similar to zip or tar+gzip (tgz) that supports one of the highest compression ratio lossless compression formats in common use, LZMA2, among others.
Almost certainly, the file contains a heavily-compressed copy of the output from
mysqldumpof your database, either with a single file, a file for each schema, or a file for each table, depending on how they're making the backup. The potential compression ratio can easily exceed 10:1, so you could be dealing with 52GiB or more of raw data in each of those weeklyh files.MySQL is a database server (RDBMS) and SequelPro is a MySQL client application, so neither of those things would be able to directly do anything natively with the files sitting in the compressed format, and neither of them is going to fetch the file from the ftp server on its own and do anything useful with it.
If you were to download it and then uncompress it, you should be able to restore the backup onto your own local MySQL server and have a queryable copy of your data as it stood when the last backup was made -- though it would likely take many, many hours... then you could examine that data with SequelPro connected to your local MySQL server... but you're going to have to download that file somewhere, then uncompress and extract the contents, and then either review them with your eyeballs (
mysqldumpbackups are typically human-readable, they consist of the DDL necessary to recreate the empty tables, followed by the DML to insert the existing rows int he tables) or load the onto a local MySQL server if you wanted to actually do something useful with what's in the file.Ultimately, you'll need to download these files... but to where?
You could automate the download of the file from FTP with a cron job on the same machine as your local MySQL development/test server (or on a different machine) and you could even automate the decompression and restoration of that backup onto your local MySQL server, but that wouldn't be "MySQL" itself doing the downloading or decompressing... that would be other processes that might (or not, depending on what hardware and other resources are available) be running on the same machine -- but this is done at arms-length from the MySQL process itself... not "through MySQL."
If that's what you're asking how to do, that's more of a system administration question than a DBA question. The last step in that process would be piping the extracted data into the
mysqlcommand line client for restoration of its content onto a local server... but before you can consider that, you'll need to examine how the files are structured and consider what you're wanting to accomplish with these files.