next »

[badkarma]

Yesterday we dealt with the import of exceptionally large database backups. Today we take it a step further as there are issues that haven´t been dealt with in the previous tutorial about the import process. In particular i am talking about not being able to upload the entire database backup due to restrictions of the maximum fileupload size, which some hosts restrict to only a few Kilobytes or 2 Megabytes or something.

This certainly prevents you from uploading a MySQL backup bigger than the allowed size. So, you can´t upload your backup but need it on your server to import it. What to do now ? A logical approach on that would be splitting the file into smaller chunks, that means taking some sort of text-editor, and then copy and paste away, until you you have the original file split up into smaller files that can be uploaded and then imported one by one. I don´t know if you have ever done that, but believe me…it can make you go mental depending on the size of your backup. Wouldn´t a script that does all that for you be nice ?

There is a script which does a lot more actually. It can create a multi-part backup, can restore multi-part backups as well as “regular” backups, gives you the ability to download the backup, have it emailed to you and a whole lot of other features. Now what´s the script i am talking about ?

It´s mysqldumper, a very handy script when it comes to backing up databases and restoring them. Let´s have a look at it´s features first:

Yes, that is exactly what we are looking for. This script is able to create multi-part backups that should upload no problem, even on hosts that have tight restrictions on the upload file size. So go and download it via this link and when finished downloading unpack the archive and upload the content to a folder on your server. If you have any problems uploading it or don´t know how to do it, then leave a comment here and i will help you with it.

This script needs to be installed on both, your old server and the new one as well to successfully import the multi-part backup created by mysqldumper.

Now we have to sort of install the script, which is a piece of cake if you got all the details you need at hand. To run the webbased Installer just point your browser to

   

Register or Login

where yourdomain.com and your_folder obviously need to be replaced with the name of your domain and the name of the folder you uploaded mysqldumper to. When you have done that, you will see the initial screen of the installation which looks like this:

See, i almost forgot to mention another important feature. It´s multilingual, which can come in handy. Back to the installation though, select the language of your choice and then click on Installation in the bottom left corner. This will take you to the next installation step and will open this screen:

Now you might know what i meant when i said “…have your details ready” earlier. This screen asks for the Hostname of your MySQL server, a username and password to access the MySQL server, as well as Port and Socket. The latter two options, Port and Socket, you can leave well alone. Unless they aren´t configured on standard values. mysqldumper will tell you, but as said already…you can leave them blank for now. So just fill in the information for Hostname, User and Password and then left-click the Connet to MYSQL button to test your settings. When the connection has been successfully established you will see a screen similar to this one:

Seems like my details worked alright, and now i am given a list of all availabe databases that are found under my account. Looking good so far, everything goes smoothly so we proceed to the next installation step by left-clicking the Save and continue installation button. Now there are two possible scenarios that you might face. Either everything goes alright and you are presented with the Main Screen of mysqldumper which looks like this:

Or things might become ugly and you will need to do some additional steps. In that case mysqldumper will tell you what to do. As during my testinstallaion i had folder permissions setup already, i will now show you a screenshot of what happens if mysqldumper isn´t happy with your folder permissions it will tell you straight away and mock at you for a bit. Let´s have a look at what mysqldumper will tell you in case folder permissions were incorrect:

Ooooops. Oh my, look at that. Couple of errors, but nothing to worry about. Here mysqldumper tells you that it can´t create necessary directories for it to work properly. This can be solved easily and the simplest way to do that is to logon to your FTP server, then locate your mysqldumper directory and change it to CHMOD 777. When you have done that, just go back to the browser window and left-click Check my directories. mysqldumper will now re-check your directories and if not present create them. Thanks to chmodding the directory to 777 the script can now create the folders that are necessary for it to work.When it´s done all that it will come up with the Main Interface and we take it from there.

    Do not forget to chmod the mysqldumper directory back to CHMOD 755 when the installation has finished.

Good, the installation is done. Now here are a few hints on how to set it up. Remember, we needed it to primarily get smaller backup files that we then can upload to the server and have them imported. So where do we set that ? If you look at the left side you will see the configuration button which takes you to….tadaaaa….the configuration.

The configuration screen will present you with basic setup information for mysqldumper. The thing we should focus on now is the General option. Just left click it and you will see the screen above. There are a few things highlighted that i will explain now. Let´s start with the option

Multipart-Backup
This is the option that is responsible for splitting up your backup file. When activated it will activate the Maximum Filesize option right below it.

Maximum Filesize
This defines how big each of the split files will be. So you can define something below the maximum upload size of your hoster to be able to successfully import the mysql backup. Say your hoster has a maximum upload size of 2MB, then you would go for anything below that in this option.

Restore
Now there are two options here, pretty self-explanatory so i won´t be going into detail here.

When you have set all settings, Save them and let´s do a test run of a backup. To do so, click on Backup in the navigation frame on the left

This will open up the Backup screen with a few options. First option we pay attention to is the selection of the database. See the drop down list in the bottom left corner ? Choose the database you would like to back up there. When chosen, all that´s left to do is clicking on Start new backup and all hell will break loose. The backup starts and will tell you what it does as well as keep you informed about it´s status, e.g. how much tables have been backed up, how big the file is and a lot more as you can see on this screenshot

Ok, depending on the size of your database it might take a while. So let it R.I.P and do something else, vaccum, take the dog for a walk, phone your parents and have some chitchat or whatever comes to your mind. When the backup is completed mysqldumper will tell you how many files there are, how big each file is and the numbers for the lottery…..i wish.

So we have a couple of files now, around 1MB in size. Now what to do with those files ?
First download those files via FTP. They can be found in the mysqldumper/work/backup directory and are named by the database and date/time.

So download them to your harddrive and then upload them to the exact same directory on your new hoster from where we will import them now.

Remember: You have to upload those files to the mysqldumper/work/backup directory, else the script will not be able to find them.

So how to restore the backup on the new host then ?
I guess we just have established the use of the Restore function in the left navigation frame. Let´s click it and look at the restore
options we got.

Not much to do here, except a few things.
First you want to select the database that the backup should be imported to. To select it just choose it in the drop down list in the bottom left corner. Next step is to select the backup which should be restored. All available backups are shown in this part of the screen

So just check the radio button in front of the backup you want to restore and then left-click the Restore button at the top of the screen. Now mysqldumper will start to import the backups we made earlier, and again you will see sort of a progress screen to keep you informed about what´s going on behind the curtain.

This also will take a while, so you might be in for another walk with your dog or something. Once the backup has been restored the screen will show you something along the lines of this:

And that´s it. All data has been restored to your new mysql database. If you have any questions about this, comments, suggestions or whatever feel free to post them here in the comment field. If you need additional assistance on importing a large backup contact me and i am sure we can sort it out.