Making a copy of my board to use as a test board

[powerful_rogue]

Hi,

Im currently running 3.6.7 and have been for around 6 months.
I now want to set up test copy of 3.6.7 using an exact replica of what I have at the moment incuding posts/members and hacks.

I think im thinking the right way, but just wanted to check.

Install 3.6.7 on the test directory
Overwrite all the current ftp files with those from my current site
Download the SQL from original board and then upload it to the new board?


Any help, tips and advice would be grealt appreciated.

[powerful_rogue]

I think im almost ready to start!

Ive got a clean install of vb 3.6.7 to use as a test forum.
Ive downloaded a copy of all my currently files
Ive download a copy of the SQL files.

What do I need to do first? Import the SQL files or overwrite the 3.6.7 files with the ones ive downloaded from the original site?

[powerful_rogue]

Just when I thought I was getting there! Ive download my sql files using phpmyadmin.

Ive tried to import them into the test set up however it comes up with this error:

Error at the line 12: CREATE DATABASE `DB000000`;
Query: CREATE DATABASE `DB000000`;

MySQL: Access denied for user: 'U12345@192.168.4%' to database 'DB000000'


DB000000 is the database the original files for my forum are on.
U12345 is the username for the new database im going to be using for my test set up.

It appears the error is that in the SQL I downloaded from my main forum there is a command telling it to creare the database, however as the files so large I cant find any program to open it and alter it.

[Steve Machol]

The most likely cause of this error is that the info in your config.php file is wrong or your db user does not have full permissions to this database. Please read this for all the known causes of this error:

http://www.vbulletin.com/forum/showthread.php?t=194542

[slappy]

The problem you are experiencing is caused by the -B command, which usually occurs in the sequence:

mysqldump -q -uUSER -pPASSWORD -B

Where the -q means "quick" and the -B causes the script to add the name of the database which was backed-up by the script and puts the following language at the start of the back-up file:

-- Current Database: `vbulletin`

--CREATE DATABASE /*!32312 IF NOT EXISTS*/ `vbulletin` /*!40100 DEFAULT CHARACTER SET latin1 */;

USE `vbulletin`;

What this will do is attempt to "create" a database with whatever the "original" database was named and then "use" it for the rest of the restore operation.

The easest way to avoid this problem is to avoid the -B command when creating a backup.

From the command line in SSH you could navigate to your MySQL/bin folder and issue the following command:

mysqldump -q -p "database-to-be-backed-up" > back-up name.sql

This will prompt you for your MySQL password and create a back-up named "back-up name.sql" which does not have the:

--CREATE DATABASE /*!32312 IF NOT EXISTS*/ `DB000000` /*!40100

which is causing your problem. It's attempting to "create" DB000000 and it's not in your config.php file for the "Test Forum" so you do not have permission to "use" it.

One program which you could use to edit your SQL file to modify that line from the begining of the backup is "TextPad." It will easily open large SQL files and allow you to edit them. You can download a free, fully functionaly evaluation copy from their website.

Because you did a clean install and created a "new" database named "U12345," if you use TextPad, you can change your existing SQL databse dump and change the starting lines to say:

-- Current Database: `U12345`

--CREATE DATABASE /*!32312 IF NOT EXISTS*/ `U12345` /*!40100 DEFAULT CHARACTER SET latin1 */;

USE `U12345`;

(Note: is you are not using the "latin1" characterset, don't use that part of the command!! Use whatever your Character set may be already listed in your SQL Dump!!)


Regards,

[powerful_rogue]

Slappy, thank you so much for your reply :)

Ive just been trying to get my head around telnet, but to no avail. Ive only recently been able to get my head around exporting from phpmyadmin!

Ive downloaded textpad and wow! what a program. Opened the sql file up with no problems.

This is at the top of the file:

-- phpMyAdmin SQL Dump
-- version 2.6.4-pl3
-- http://www.phpmyadmin.net
--
-- Host: my host
-- Generation Time: Jan 10, 2008 at 03:50 PM
-- Server version: 4.0.27
-- PHP Version: 5.2.0
--
-- Database: `DB000000`
--
CREATE DATABASE `DB000000`;
USE DB000000;DB000000 is the DB in which I exported. Do i need to change all these to the name of the database where im going to import the file?

Im not sure if im using the latin1 charset and not to sure how to check

[slappy]

O.K. You are on the road to a new adventure. First thing I would do is a search and replace of all instances of `DB000000` with `U12345.` I didn't look at my installation, but I believe it has that feature. Clearly you have to change the ones you've already seen.

Since your "dump" does not appear to have a character set listing, don't worry about it. The problem is only if you change it "by mistake."

Also something to consider. This will be, afterall, a "test" forum and there should be "no problem" using the unmodified backup from your production database as the "original" starting point. By this I mean you can have the same "username" and "password" for both your production and test Forums. The only thing you need to be VERY SURE about is NOT writing your Test Forum database to your Production database and keeping them with seperate usernames and passwords does help prevent that potentially serious error.

Also, if you are not doing so already, you should be making at least a daily backup of your Production database and, with a little searching, you will find several Threads here discussion how you could set that up.

If you have additional questions I can hang around and help you in real time for awhile.

Regards,

[powerful_rogue]

Thank you so much for your help. Ive edited all instances of DB000000 (There was only one other which was related to impex).

Im currently uploading the file, should take about 10 minutes and then its fingers crossed!

[slappy]

I'll hang around and mulitask.;)

I'm assuming your config.php file is properly set with username: U12345 and the proper password.

Remember that your "test" forum is not supposed to be web accessible, so it wouldn't hurt to put an .htaccess file with an additional password on the folder to keep everyone "unauthroized" out of that version.

You can find one of many quides here:

http://httpd.apache.org/docs/1.3/howto/htaccess.html

By the way, if you overwrite your "new installation" with your Production files, remember that your config.php file from your Production Forum contains different information and is the one file you should "exclude" from copying and overwriting to the Test Forum.

Regards,

[powerful_rogue]

Right, Just tried importing using Bigdump and got this error:

Error at the line 25: ) TYPE=MyISAM;
Query: CREATE TABLE `access` (
`userid` int(10) unsigned NOT NULL default '0',
`forumid` smallint(5) unsigned NOT NULL default '0',
`accessmask` smallint(5) unsigned NOT NULL default '0',
PRIMARY KEY (`userid`,`forumid`)
) TYPE=MyISAM;

MySQL: Table 'access' already exists


Im presuming this is because I installed a new vb 3.6.7 so it will have all the files from that.

If I goback to phpmyadmin and tick the box "Add DROP TABLE" - Im guessing that will do the job when I try to import again.

Add DROP TABLE" will add a line telling MySQL to drop the table (http://dev.mysql.com/doc/mysql/en/drop-table.html), if it already exists during the import. It does NOT drop the table after your export, it only affects the import file.

Unless theres an easy option by adding something into the file im trying to import at the moment.

[slappy]

Since your existing tables from the "fresh" database should be essentially empty, I'd try the "add drop tables" option first, before trying anything else.

The next choice would be attempting to create a backup using MySQL, assuming you have SSH access to the command prompt.

Regards,

[powerful_rogue]

Hmm,

For some reason when trying to export from phpmyadmin its trying to download an export.php file.

I dont know quite whats wrong.

[slappy]

Is your test forum on the same server as your production database?

Regards,

[powerful_rogue]

yes, they are both on the same server

[slappy]

Did you check any "different" boxes for this backup? It should have been the same as the first, except that you are adding "drop table if exists"?

Regards,

[powerful_rogue]

No, ive only ticked the £drop table" box.
Ive tried a few times now but it still keeps coming up with the export.php

Ive tried logging out and then logging back in but to no avail. Very strange.

[slappy]

What version of phpMyAdmin are you running? Did you log completely off the server and log back in?

Regards,

[powerful_rogue]

I managed to get it to download and the big dump is going well, except it stops at one point with the following error:

Query: INSERT INTO `attachment` VALUES (11, 2, 1186314009, 1186314009, '100_5769.JPG', 0xffd8ffe125044578 0xffd8ffe000104a46494600010100000100010000fffe003b 43524541544f523a2067642d6a7065672076312e3020287573 696e6720494a47204a50454720763632292c207175616c6974 79203d2037350affdb00430008060607060508070707090908 0a0c140d0c0b0b0c1912130f141d1a1f1e1d1a1c1c20242e27 20222c231c1c2837292c30313434341f27393d38323c2e3334 32ffdb0043010909090c0b0c180d0d1832211c213232323232 32323232323232323232323232323232323232323232323232 3232323232323232323232323232323232323232ffc0001108 004b006403012200021101031101ffc4001f00000105010101 01010100000000000000000102030405060708090a0bffc400 b5100002010303020403050504040000017d01020300041105 122131410613516107227114328191a1082342b1c11552d1f0 2433627282090a161718191a25262728292a3435363738393a 434445464748494a535455565758595a636465666768696a73 7475767778797a838485868788898a92939495969798999aa2 a3a4a5a6a7a8a9aab2b3b4b5b6b7b8b9bac2c3c4c5c6c7c8c9 cad2d3d4d5d6d7d8d9dae1e2e3e4e5e6e7e8e9eaf1f2f3f4f5 f6f7f8f9faffc4001f01000301010101010101010100000000 00000102030405060708090a0bffc400b51100020102040403 04070504040001027700010203110405213106124151076171 1322328108144291a1b1c109233352f0156272d10a162434e1 25f11718191a262728292a35363738393a434445464748494a 535455565758595a636465666768696a737475767778797a82 838485868788898a92939495969798999aa2a3a4a5a6a7a8a9 aab2b3b4b5b6b7b8b9bac2c3c4c5c6c7c8c9cad2d3d4d5d6d7 d8d9dae2e3e4e5e6e7e8e9eaf2f3f4f5f6f7f8f9faffda000c 03010002110311003f00e26faf5f51788c912a08c10a158f7c 7afd2a8cc8a258949e37e4fe46a7482430bdc3393b4038c718 aa53c8048801fe2ed56db6eec4925a22fa948d89590a30390c a769e9d88e477a27b8b75532cc5c907862dbbb74eb4eb683ed 225d9662e24565d8bb59f0086c9da0ff00b229ba95a322c304 b6be4bbc459d0c450e7730c81db803f2a8b6971dd6c577d4ed 82e76b01d7fd5807f3dc6bd4fc3f70b71e1fd39f6e09b58f8f 4f9463f4af36d37405b9b3d384aee0ca1d1d530031323af3c6 7b0fcaba5b7f102685a3e936ceb6ece74f8646f32e44657e5c 7dd2327a569e8897ea76ad1a3801955bd323349b107451541f 5331e926f9a35000dd8dd905738041e3d8d65c3afea7a8c104 fa6d8db795221626673c10ecb8e3afddcfe343d15c5e4742c0 638c5538e3075cb366c645a4e38ff7a1ff000accb2d575393c 4034ebe16817ecc66fdcab6739c0193f8d6899366b7643b1b7 9ff9c74e2d37707e669ba707819aa322907e6007d4d4f73206 89c1e548c1fa571b269da93ab7ef664041f9846064e7d85692 7633d19da59b3a44c158a8ddd013e828a834f68e3b6d830006 2001455dcc9a3811a46b7f6396ddf46be8cec508cf11504e39 ebf87f915cede59de25e2db1b763307c04cf248355ade7d4b5 0b85b684cd71215c05dd9c00771624f40392589000e49e2ba4 b4f0f582c265d52fa6b9990e3cab59c4410fb3bc6fbfa6090a 071c330e6bcf8bad37d3faf99d1748cab8b6bdb0d2bcfb944d cd284186dc08c1c83e8475c75393d3154e0bd3362111aae012 0a719e6baebdd27c39636f691b9d69e4b841713db9d4e2db18 270873f66e58a8c9181805464e78bda2783bc1be25d464b4b4 d5f54d2aeca931adc98a54900e48042af23ae3d39ec7171a55 12f7869aee2785add6eac2d5c20325b3b13be52a01f3090380 73dcfe5eb5c57881d5934762305b46847d486907f4aeef5cf0 5f893c170edb0d4927b52f98a4f2046b267b03961bbd8e3a1c 5799ea72de2cf6d6d72b224905b793b4ae0ed0ec47d7ad6cda 69242d6ecf5b4b569fc2905b5b80af2dac7b46ce012012703d c9a8f43d3a4d3ac6281fcc768d5977088a839766ee7fdaaab6 1e21d222d1ace1b9d42d15d2de257479572a4200411db9aeb7 4ed162bed3a4d426bdb3b4b44192f28c9000c93f80f7c8a525 75629276e647117975259788a3be8d257f3145a8596264c027 3bb774233d876ad85bc865f10d9c08e0c91daccce31d013181 fcab3f58d57409b64706afa9c91a364b5be98aa24fc1e6538f a8fcaaac3abf87bfb564d485e6ab19b7b716e626d3a2cb6f6c 8208b8ec17a5118b4c96ee75972e3c87e78c649ae70ada2049 00b552cb8762e323273ddf1fa55ab8d6743b985a1b89ae046e 395bcd39c230cf7dbbc76fa540b61a14f179b6767a55c463ab c1146c07d70323f102b494398cd4ac6de9c7cdb45785d593a6 54823a515c85db082511db47245101c2c1f22f53d8514f9ac2 e5b97ef351f0cdca431c7a768f66b14c93a358bf924b2fddcb 295661ce79fd2b423d5349b8258596973b364bb4aad317cf5e 4be73d79f5c7a57985b7892e6d2de2856772b1a040be5038c7 18cee1db152ffc256f21db3dadbba9fe2923ff00f59ac799a3 7e58b3d47ec3a04f3cb3dde9915cc92b06669259874000c618 0030056859e81e1f5bb82eedf46b15553e604173701c103218 379b8ebdb6fb579445e22d359b9d2ec3af55089ffa10535069 1a0da6bdf6fbed42e845876d82374c00003d79e3040038e872 4629fb4930e448fa08f886282d24b15d2ad65b79461e29a469 1587b86cd721e2822e6d62b787c2f60f0b02c5e24937478c60 29078ce4f3c579c699aebe9fa64d15e46d20b399adcce2c8c8 08078dcfbc007b63d31d6b7749f11cbad0923d3574d8594855 92fd52de3663d14317605bff00adea2a253b9708a4eef525d0 349d32d759b8d5356d21dad6d153cb816e9c0695cfca493923 6853f9e7b5767a9eb1e1ebcd3069eda24cb6cf198f6417edc2 ff00f5fae7bf7ae1f5cd5f5ff09db0b1d4f4f8a3d4e661340a 14346db8950e31d480847ffac1ae7a7bbf1459c26fa7b8b7bd 8946f963110f946eda72428c73dc13d41e473570718c577264 e4fdd5a2ec75b3e85e1a94b79571addab139c48f14ca3f0f2d 4ffe3d58f75e0eb667924b5f13db8180cc2f6d8db8f403219b 3fe78a68d445d471dcc4ec2399030079c7b1fa53fedbb98925 73800e739a7ed576239081f40d4edd07d9ef6dae3d7c8b9c7e 8db4fe959d259ea96d751cd245730b6e00dc88dd820cf24b20 2703db26b5cdce475a67da4a6719fc0e28f69127d9319717b0 c73b27f696917fb78f3a4173037d0aec1f9e3bd1504b61a75f 4867b980c929e0b798c381f43452f6a57b347acda78af45d60 a5beb7a75b4739e374b1ac9139fab0e3e87f3a9e4f02f83a78 fcb7d0ed9001c6d2e8df98604f5af2fd4aea282429e60603ef 7a575bf0ff0056bc96da7b6998b5a001ad43372833838cf3b4 92303dbd0d67aa4461eaca7f12342e7e10f84a7cf96b796f9f f9e573d3fefb0d59adf06a1b7f33fb27c4b7d67bbae515b38e 8782bcf279ea335dc0990e38db8239e9522cc1880b21e69733 3ab951e4371f033534c9b7d5eca5edfbd0e99fc835523f0b7c 636036dadc23a8e02c179b411e986da2bdb7ce71d0e7d32319 a4f3481caf19e39ebfa51cc1ca784bf833c7103232e8b6e5e1 75983471da07ca9046190ee3cf6079a835ed4ef60d34db5e68 ba8dacbb0a8fb540ea172304ee27e6e0b0e8339073c60fbd34 f81cc67a520b8033f33a03d8656973790729f2fda6af7b6912 4417742b9c28033c9cf5c1f5ab727891f70d96d81dc3b648fc 463f957d0773a468f7c7375a6584e7fbd2dba31fcc8cd63dcf 813c2f703e6d1a3427bc52c89fa2b63f4aae644f29e2e3c451 903303e7bfb7f8fe9520d7addbb3afe15e9973f0bfc3b22931 b5f43e9b66523ff1e53fceb1ae7e1559e0f93aacc9fefdb87f d430a2f11599c945ac5bed3fbc239a2ba48be164aca76ea701 00e326261fe3451ee8b539592492ea569646c42a73927863fd 6afaebf792ea624b390c3334ab2c9290382ac1b278e808071d f1cfa5635cbb1900c9fbc47e15a1a74319ba58f6fca7191eb5 a37a5c8823e80b49bed5616d70f184334292145e082ca0e0fe 7ef4f68d588da303183d3a63269d67044b636a8170ab04600c 9fee8ab6d6f1065c2ffcb40bd4f4c91585ce9b1480652c15bd 738c1ef923f9526f707dc9eff5ab71c319d995ea33fce9a6de 265625738dc3a9e94014fed24705791d4fbff9ff002298d3a0 e1908c73c55d92de2591405efea7fba4d42608b110dbc12011 93d39a405469e22dd79ef9c7f9efd3da9a6600603fb0c375fc a95e14f34295c8f739ef4d92de2577c291c31ea7d05022291d f27e66fc71c7e9f5aa924ef8fe1c1ebc54f3c6ab8c647e26aa ba8c375e01239a04c96da5728c484fbdeb452c1f2ab01eb453 b1363fffd9, 3179, 0x6a7067, 0);

MySQL: Got a packet bigger than 'max_allowed_packet'


Ive had to delete some of the code as it was far too long!

[slappy]

Try it again, since it worked the first time.

Regards,

[powerful_rogue]

Tried it a few times but still no luck, keep getting the same error. This is the furthest ive got.

Looking into an alternative way, If I change from storing the attachments in the database and into the filesystem via VB ACP, will that automatically place them in the file system and delete the entry from the database?

If thats the case, I can then redownload the DB via phpmyadmin and as the attachments wont be in there hopefully get rid of the problem.

Ive also noticed in the past, when ive tried to back up the database via the vb acp - it also stops at attachments and throws up a database error.

[powerful_rogue]

Just been reading about converting attachments from DB into file system in the vb manual and it looks quite promising. Will give it a try I think!

[slappy]

I don't know how big your database is, but the adminCP is NOT recommended for creating backups except for very small databases, because it "timesout" and gives an incomplete backup.

I believe if you move your attachments to the file system you will save substantial space in your database. Why not give that a try before we go to the MySQL commands from the command prompt.

Regards,

[powerful_rogue]

Thanks slappy.

My database is showing as 143MB (thats the sql file i downloaded)

Just changed over to the file system and it all went well. Just going to re-downloaded from phpmyadmin again.

[powerful_rogue]

Just downloaded the new file, and since I took the attachments out of the database the file size has dropped from 143MB to 27MB

Would that seem right to you, I think I had just under 400 attachments.

[slappy]

That would depend entirely on what the "attachments" are and how big they might be.

Is the backup file still on the server, or did you download it to your local machine?

Regards,

[powerful_rogue]

I downloaded the file straight to my pc.

[slappy]

Which means you need to "upload" it again to the server to install it in the "Test Forum" database, right?

That's why eventually learning the MySQL commands for the command line with save time. You can make a backup and install it in the Test Forum fairly quickly.

Regards,

[powerful_rogue]

All done and working great!

I cant believe how nerve racking it is watching the % bar of "big dump" going up just waiting for an error to pop up! Luckily it all went smoothly.

Thank you so much for your help and assistance slappy. Id still be scratching my head now if it wasnt for you. :)

[slappy]

O.K. Best of luck.

Regards,