Opening and restoring large .sql files

Be prepared.  

To have backups of your moodle dbs is one thing,  being able to open, edit, and restore it is another.

I spent a little time today opening and restoring some of my backup files, just to be sure I can in the event of a fire. I used MySQL Query Browser to open and execute the scripts.

Be sure you can open and restore your large moodle backup files.

size of file	time to open	time to restore	notes
25 mb	10 seconds	15 seconds	noticed the log table was the largest and took the longest.
90 mb	25 seconds	1 minute
160 mb	40 seconds	2 minutes	I had trouble specifying the schema to use in the script, I had to choose the schema name first when launching the tool.  Other attempts allowed me to specify the schema in the script block using USE Schema_name;

Before I performed the restore from the previous night, I checked the moodle site report logs for actions = Add or New.  If I only saw Views, for example, I felt confident that I would not be losing any data from earlier in the morning.

I may consider compressing the backup files to save disk space, but the files must be un-compressed to be read or restored. phpMyAdmin says it will recognize compressed or uncompressed files, that may be the way to go in the future, since I have phpMyAdmin on my newer server, and MySQL Query Browser on the old one.  I can see the Max option set to (100 MiB). I am sure I can control that in the php.ini file.

I opened the php.ini file and changed the post_max_size variable from 100 to 200 MB, saved the file, restarted the apache service, closed and opened phpmyadmin, clicked on the Import tab and saw the change from 100 to 200 Mb.

This is useful because I may need one day to open a backup file that is very large!

Be prepared always.