One of the DBs I support in moodle is about 250 MB. I am using moodle 2.3, so that gives me about 240 tables, approx. I create backups of the DB 3 times a week and store the backup files to the F drive on my moodle server. The backup file is created using mysqldump, which nicely creates a drop, create and insert statement for each and every table in my moodle db. That is a lot of statements. This is a copy of the command that creates the backup files.
C:\wamp\bin\mysql\mysql5.5.24\bin\mysqldump -u username -ppassword --result-file="f:\DBBackups\backupStudent.%DATE:~10,4%-%DATE:~4,2%-%DATE:~7,2%.sql" nameOfDB
You can see that a .sql file is created by the backup process.
If and when I need to restore part of or the entire DB, I will need to execute the commands in the .sql file or to state it another way, I will need to run or load the .sql file. The problem I am facing is the SIZE OF THE .sql file.
Since I am using a Wamp solution on my server, I have phpMyAdmin as a tool to manipulate the DB. phpMyAdmin has a Import pane that allows me to browse to the .sql file and attempt to load it.
BUT, despite my best efforts to allow the file to upload, I am not having success trying to upload the entire file at once. phpMyAdmin also has an SQL pane, where I can copy and paste and run the commands from the .sql file. This is a very tedious process, especially tedious is the chunking of the .sql file.
I found this utility that did the chunking for me. In other words, when I loaded the .sql file into the utility, it automatically chuncked the file into about 100 individual .sql files (which I still have to execute) but it does make this process more manageable.
This is the URL to the web site where I downloaded the splitter utility. Browse to the BOTTOM OF THE PAGE FOR THE download link.
After downloading, I have a SQLDumpSplitter.exe file that I moved into my Start menu. When I launch the program, I can Browse to my .sql file, specify the max size I want per split file and choose a destination folder for the resulting split files.
I choose a 240 MB file and set the maximum file size to 30 MB (the splitter estimated it would create 8 files from the 1 source file). The process took about 20 minutes to complete and resulted in 8 .sql files.
When completed, take a look at the README.txt file, it contains an important note about the first file to run.
 |
| Notice 8 resulting .sql files from the splitting operation |
From the ReadMe.txt file
"These files are created with SQLDumpSplitter 2.
If you want to restore the dump, you have to execute the file backupStudent.2013-01-07_DataStructure.sql first because it contains the structure like Tables.
After that, you can execute the other .sql-files as they contain the data of the now existing tables."
I ended up using MYSQL workbench to execute the .sql files created by the splitter utility.
I was using phpMyAdmin, as specified, but ONE OF MY .SQL files would not execute....im sure there was a good reason, but when it failed, it stopped executing. I tested the executions of the other .sql files by browsing to the moodle instance whose db i was updating and could see the missing data from the .sql file that did not import.
After messing around with the command line a little more, and almost getting that to work, I was reading in the MySQL on-line documentation, looking for information about using the SQL command line, and saw a note about MySQL Workbench.
I fired up workbench, browsed to the server where I was trying to restore the DB, found the splitter generated .sql file, loaded it in workbench via Open | SQL Script ... after about 30 seconds the file loaded (approx 30 MB). I executed it and it proceeded to execute all the insert statements that had been aborted trying to use phpMyAdmin.
There were some primary key violation errors while trying to insert a few records, maybe about 10, but the other 25,000 records inserted just fine in the mdl_log, mdl_message and a few other tables.
The end result was that I was able to completely recover from a DB emergency by
1 - spilling the large 240 MB .sql file into 8 smaller 30 meg. files
2 - executing the files in either
- phpMyAdmin
- MySQL Workbench
I like working with the GUIs like MySQL Workbench and phpMyAdmin because they allow me to see WHICH DB I HAVE SELECTED. When I was working from the command line, I kept worrying that I would accidentally run a script on the wrong DB, like my production DB. There are commands to execute from the command line like \connect, to view which DB is selected, BUT, both of the GUIs allow me to see and select easily the active DB - easing my worrisome soul.
A final note today about MySQL Workbench -vs- phpMyAdmin.
phpMyAdmin is the more stable of the two tools and worked a little more smoothly than MySQL workbench. Workbench has always been flakey when dealing with LARGE FILES. Today was no different. Workbench did however render or execute the one file that phpMyAdmin failed on. So together, they worked well.
And the end of the day, developing expertise in the command line will be preferred, since these files are so large, they struggle with GUIs.
No comments:
Post a Comment