InnoDB Backup

adamb · December 30, 2020, 4:18pm

All - I have a very large database, containing hundreds of millions of records across multiple tables in multiple databases. The DB is setup for a master/slave replication, but I would like to start doing a full weekly dump to a DVDRom or tape backup…whatever. With MyIasm tables I can just copy the binary files, but this is not the case with InnoDB, which is what I am using. the mysqldump takes forever and a day to complete, and produces an extremely large text file, I was looking for some other way, that is a. quicker, b. smaller dump files. Is this possible? Can someone recommend a method?

Thanks,

Adam.

sterin · December 30, 2020, 4:27pm

This is what you are after:
http://www.innodb.com/hot-backup/features

adamb · December 30, 2020, 4:27pm

I was hoping for something that is OSS or free…anyone other suggestions ?

razdaman · December 30, 2020, 4:27pm

I’m not an “backup expert” in any way, but I just wanted to add that you actually can backup the binary files of innodb tables. It is a bit more complex than with myisam and it is not the ideal solution, but it is doable.

However, you have to shutdown your mysql-server while doing it.

adamb · December 30, 2020, 4:27pm

There is no constraint about having mysql up and running, ie, i can shutdown the server for any amount of time and perform the backup. there is really only a critical window of about 5 hours a day when it needs to be running. is there a guide somewhere about backing up binary InnoDB tables? thanks.

sterin · December 30, 2020, 4:27pm

If you can shut down the server during backup then it’s pretty easy.

All data is normally stored in the mysql data directory (unless you have performed any special configuration).
Which means that backing up the database is as easy as:

Shutdown the mysql server
Copy/run a backup client on the mysql data directory.
Start the mysql server again.

The problem with copying the files while the server is runnning is the internal caching of data.
But once the mysql server is shut down the files in the directory is “dead” and you can just copy them to wherever you want without any risk of getting corrupt files.

adamb · December 30, 2020, 4:27pm

follow-up question…

lets say in my many databases i have a few tables which are MyISAM like 10 of them, and the rest are InnoDB. would this technique work for databases with mixed storage engines ?

sterin · December 30, 2020, 4:27pm

Yes without a doubt!

Think of it this way.

When the mysql server process is shut down it will not know anything more than what the files in the data directory contains (except the my.cnf file which is usually placed on a different location).

So that means that the files in the data directory contains all information that is needed to start a mysql server process.

The main reason why you can’t just copy the files when running InnoDB is that InnoDB is using a write cache (myisam doesn’t have this) so the files are not consistent since it hasn’t written everything to them yet.

But when the mysql server process is shut down it will trigger a flushing of the write cache and it will write the changes to disk since itself needs to have the files consistent when it starts up again later.

So the summary is that once the server process is shut down you can always just make a copy of the files somewhere as a backup because they are consistent and “dead” since nothing is in the process of writing something to them.

adamb · December 30, 2020, 4:27pm

got it. thank you very much for all your help. backup up the files is significantly quicker then doing a mysql dump!