mysqldump | A multi-threaded MySQL backup and restore tool | Continuous Backup library

No, there is no tool to query the .sql file as if it's a database. You have to restore that dump file, then you can query its data.

A comment above suggests to count the INSERT statements in the dump files, but that isn't reliable, because by default mysqldump outputs multiple rows per INSERT statement (the --extended-insert option, which is enabled by default). The number of rows per INSERT varies, depending on the length of data.

I once had to solve a problem exactly like yours. A bug in my app caused some rows to vanish, but we didn't know exactly when, because we didn't notice the discrepancy until some days after it happened. We wanted to know exactly when it happened so we could correlate it to other logs and find out what caused the bug. I had daily backups, but no good way to compare them.

Here's how I solved it:

I had to restore every daily backup to a temporary MySQL instance in my development environment. Then I wrote a Perl script to dump all the integer primary key values from the affected table, so each id value corresponded to a pixel in a GIF image. If a primary key value was found in the table, I drew a white pixel in the image. If the primary key value was missing, I drew a black pixel in the position for that integer value.

The image filenames are named for the date of the backup they represent. I repeated the process for each day's backup, writing to a new image.

Then I used an image preview app to scroll through my collection of images slowly using the scroll wheel of my mouse. As expected, each image had a few more pixels than the image before, representing records were added to the database each day. Then at some point, the data loss event happened, and the next image had a row of black pixels where the previous day had white pixels. I could therefore identify which day the data loss occurred on.

After I identified the last backup that contained the data before it was dropped, I exported the rows that I needed to restore to the production system.

Exporting and importing is indeed sort of clunky in MySQL.

One problem is that it introduces a race condition. What if you export data to work on it, then someone modifies the data in the database, then you import your modified data, overwriting your friend's recent changes?

If you say, "no one is allowed to change data until you re-import the data," that could cause an unacceptably long time where clients are blocked, if the table is large.

The trend is that people want the database to minimize downtime, and ideally to have no downtime at all. Advancements in database tools are generally made with this priority in mind, not so much to accommodate your workflow of taking the data out of MySQL for transformations.

Also what if the database is large enough that the exported data causes a problem because where do you store a 500GB TSV file? Does pandas even work on such a large file?

What most people do is modify data while it remains in the database. They use in-place UPDATE statements to modify data. If they can't do this in one pass (there's a practical limit of 4GB for a binary log event, for example), then they UPDATE more modest-size subsets of rows, looping until they have transformed the data on all rows of a given table.

You can't make a new WHERE condition for each table. There is a single WHERE condition, and it will be applied to every table. So you can only reference a column if it exists in every table included in the backup.

You can run one mysqldump command for each table, but if you do that, you can't get a consistent backup. I mean, you can't use a lock or a transaction to ensure the backups include data from a single point in time. So if the database is in use during this time, it's possible tables you back up later will have references to new rows that have been created since you made the backup of earlier tables.

date "+%D--%T" outputs something like 12/18/21--00:19:21.

/ is not allowed in file names with Linux/Unix.

Whichever directory you are in when you run that command will have db_backup.sql.

The statement:

mysqldump -u user -p db_name generates an output on the terminal screen, assuming you are SSH'ed into the server. Instead of the output written on the screen, you can redirect the output to a file.

To do that, you use > db_backup.sql after the command.

If you are in directory /home/hyunjae/backups and run the command:

$ mysqldump -u [uname] -p db_name > db_backup.sql

You will see a new file created called db_backup.sql. If there's already a file with that name, it will be overwritten.

If you don't know what directory you are in, you can type pwd for present working directory.

InnoDB options would only apply to mysqld, so I would put the options you show into /etc/mysql/mysql.conf.d/mysqld.conf, assuming that's the option file with a [mysqld] section.

But ultimately, all the option files will be read, and all options in the [mysqld] section, regardless of which file they appear in, will take effect.

So it's really up to you how you want to organize your own config. The only reason to separate the files is so you can find the options you're setting, or deploy different option files in different environments.

I have found a workaround. Process::fromShellCommandline can be used to redirect the output. This is my solution:

By default, when you use mysqldump DB, the output includes table-creation statements, but no CREATE DATABASE statement. It just assumes you have created an empty schema first.

So you could do this to create the schema first:

There is no such option to mysqldump. The --hex-blob option only applies to data values.

Mysqldump gets the CREATE TABLE statement using SHOW CREATE TABLE, which in turn relies on the INFORMATION_SCHEMA.

A bug was reported in 2013 that there's effectively no way to get column DEFAULT values from this method if the value is binary and contains non-printable characters. https://bugs.mysql.com/bug.php?id=71172

The bug report was acknowledged, but so far it has not been fixed. Feel free to upvote the bug using the "Affects Me" button.

Or try to get MariaDB to fix it themselves, instead of the upstream MySQL code.