Restore Database Backup Locally?

[WeaponX 0]

I wanted to use my live forum database (in another webhost) on Xisto for testing purposes. But I guess the free accounts we have do not support that restore backup option...tried it and got a message saying that.Is there any way we can do this locally? I will probably install XAMPP or one of the variants of those packages. I don't assume cPanel can be included also locally? If not, I just want to know how I can use the database from my live forum in my own computer.Thanks.

[Houdini 0]

You just need to export your database using phpMyAdmin as a file (there is a check box that gives you the option to save your database as a file), and put the database which will be the name of your database followed by .sql. This may be quite a large file and if so may exceed the maximum time for execution, you will not know until you try.

 

When you have XAMPP (one that I like and have used for three years) running start the phpMyAdmin there and depending of the version of phpMyAdmin you have either select SQL or Import and let it know that you are going to use a text file and then browse for the file you just downloaded from the host. One of two things will happen, it will either execute and reproduce the database for you along with the structure and data or it will produce an error complaining about the query exceeding the max execution time.

 

If it gives an error then you will need to open the sql file with an editor like PHP Designer or HTML Kit or whatever and breack it up into smaller files and run the queries one at a time untill all have been executed.

[WeaponX 0]

I usually use the cPanel backup option and go to download the database there. Is that ok also?I will install XAMPP back on this machine and see if I can import that database via phpMyAdmin.Quick question (had this on my mind for a while). When I download the database backup, it seems to save it as a .gz zip file. I actually tried unzipping it before...Winzip asks what file extension to use - I think I might have typed sql or just left it. Well, either way, it seems I got it extracted to a file over 600MB in size!!! I opened that sucker up... took a while. So is that the database itself or just a compressed one (the .gz file)?Thanks Houdini...

[Houdini 0]

That file size seems way too large, do this instead. Go to your cPanel and then select phpMyAdmin and then select the database you want to export as shown below look at the arrows included with text explainations.

 

 

Pay attention to the rest of the fields that are selected (they should be that way by default) except for the save as file and possibly the Compression:

 

Your database should never expand to more than your webspace size (!%MB or 150MB) which is all your files plus the database. It will probably be more like maybe 13-40MB. If you use the technique as shown in the screenshot above you will notice that it will be an SQL file so it is usually the name of your database plus the extension of .sql.

 

Like I said earlier when you import or run an sql query from a text file (an sql is a text file) sometimes it will throw an error about exceeding tthe max execution time. Try using the above and let me know what you get, and after running on your XAMPP if it does exceed your max execution time then you will need to break the sql into a few smaller files like sql1 sql2 sql3 and so on.

[WeaponX 0]

Do I need to select any of the tables listed on the upper left of the table there? I just left it and did the things you mentionend (only checkbox part was different).What was I downloading then (cPanel->Backups)? I saw my forum database there and have been downloading it from there for a while. I recall even doing a restore over a month ago (something bombed out on me and had to do it). It took a while restoring all the tables, but I got it up and working.I have 120 tables in the database I see half that say backup_...in front of them. Can I safely remove these? Are these making the database backup file larger and does it slow down my forum by keeping them there?Should I worry about the overhead time section? Saw a bunch that don't have any overhead, but some tables do.Thanks Houdini.

[Houdini 0]

No, what you see will send the entire contents of the database in this case it is exporting the database xtra if you just wanted to export certain databases then you would make selectio(s), but in this case you would be exporting the whole shooting match (database xtra and all tables).Well you probably did download the database but subject to the other programs control, here using phpMyAdmin you have a certain amout of control over the process, especially if you want to control compression.As far as your backups theyshould have some sort of time or date associated with them, I would delete all but the most recent one.The file you get from phpMyAdmin will pretty much be about the same but I am not sure, before you delete any file though make sure that after you have your exported file and set it up on your local machine then you can delete them, you might want a backup if you want your local machine to have all the same more recent posts and members and all, but for testing you don't really need that.The overhead can be fixed after the database is installed and running then just optimize it and it will take care of the overhead. You should optimize your web sql server tables every so often, if you don't have that ability within your ACP then you can use phpMyAdmin.

[WeaponX 0]

OK, getting mixed results again...I did exactly as you mentioned and got a SQL file that's 126MB in size. Did I try opening it? Heck yeah But it gave me a message (using Metapad here...).

This file is not a valid text file. It may be a binary file.  Convert all NULL terminators to spaces and ond load anyway?

Yes or No yields the same result...a blank Metapad/notepad file What's wrong here? At least for the Backups I downloaded via cPanel, I can extract and view that 600MB+ file

As far as your backups theyshould have some sort of time or date associated with them, I would delete all but the most recent one.

Sorry..wasn't clear there. I meant the tables I see in phpMyAdmin. They have backup_ in front as the prefix. Can I remove those from the database? They look exactly the same as the other tables (duplicates with the prefix backup_ in front) in the current DB.
Sorry for sounding "noobish", but what is ACP? cPanel? I have no idea how to optimize the tables. All I see in cPanel->Database are buttons for Delete, Check and Repair next to my forum database. More questions...what's the check and repair for and should I run them?

[Houdini 0]

OK I see what is going on (well kinda). When you made backups to your database it merely took all the tables and added a backup_ prefix and added them to your database, which means that effectively you doubled the size of your data base. Yes you can safely 'drop' those tables, and the sql file is in fact a text file but I have never heard of the editor you are using you might want to get HTML Kit or PHP Designer, both are free and under 5 meg combined and will certainly open a file like that.

 

If you have a 125 megabyte databse that has been doubled by adding the backup_ prefix to all the real tables that are actually being used by your system then you really have a 67.5MEG database, which is still kinda bug.

 

A file of that size using import would most likely exceed the 30 maximum execution time and would have to be broken down into probably 6 to 10MEG chucks, but you need an editor that understands an sql file. If when you get the sql file open with an editor that will open it use the search feature to find all instances of the backup_ and eliminate all files related to that condition. Then break the file up into several complete sql aueries, an sql dump will let ou know by just looking at it where a tables data begins and ends.

 

When you have this much done then just go to your phpMyAdmin on your localhost and import each file in order like db1, db2, db3, db4...and so on.

 

There is another alternative also, you could download just the structure of your database which will give you a rather small sql that when run will set up all the tables required by your program along with the structure of the tables. Then you can download the data sql for those tables and build back the entire database with all med=ssages, members, permissions, categories, and so on.