1) Export from DBM or upload the backup to import (AdminCP->Export Database [from DBM] or upload with FTP) 2) set up the link/tables in SQL BUT dont add the default databases (AdminCP->Set-up Database [setup the SQL one but DONT add the default databases]) 3) import into SQL (AdminCP->Import Database [into SQL]) 4) switch DB's (AdminCP->Switch DB driver [over to SQL])
Now if you get stuck, you can get the board back onto DBM by editing
cgi-bin/Data/Boardinfo.cgi
and finding in there:
Code Sample
'DB_DRIVER' => q!mySQL!,
and change that to
Code Sample
'DB_DRIVER' => q!DBM!,
and your board will use DBM again allowing you to rescue the board and try the switch again.
1. Turn board Offline (precautionary) 2. Export DB 3. Setup DB -- a. Set DB to MySQL -- b. Create Tables - Yes, since you haven't created them -- c. DB Files: No, re-install from backup -- d. DB passwd, username & DB name -- e. If DB is on same server, use localhost, otherwise enter IP -- f. Port: default is 3306 or leave blank -- g. Just leave the prefix set to ib_ 4. Import DB -- a. Choose recent Backup file if more than one -- b. Set to MySQL -- c. Leave blank. 5. Switch DB Driver -- d. Switch to MySQL -- e. Leave Admin set to YES 6. Rebuild Board Stats 7. Check your forum - read a few topics, is everything okay?
At this point, don't expect everything to be 100% okay. The switch and import process has problems. You most likely will need to fix polls and also forums. The signs of trouble include forums not being found and polls showing up duplicated and/or in the wrong forum. Luckily, IkonBoard authors knew about this and designed some fix utilities in the AdminCP:
SQL Client -> SQL poll fixer (remove any listed where poll title and topic title do not match up) SQL Client -> Forum info fixer (resolves broken topic links)
8. Rebuild board stats (do it once more to ensure all is well) 9. Check your forum - read a few topics again, is everything okay now? It should be. 10. AdminCP
The next two steps are optional, but sensible at this stage to ensure all data is in perfect synchronization when users start logging back in:
11. Clean temp files (set all to "Yes" then relogin afterward) 12. Reset sessions (all users regardless of time)
And finally...
13. Turn board back online (enable forums for your users)
First off, I do not recommend switching if both of these situations are true:
1) Run a small forum with only a few hundred users or less 2) Have no desire to hack your forum the near/far future
But if you wish to switch anyway, there are a few important advantages to using MySQL over DBM: speed, scalability, universal compatibility.
Remember to always backup first!
Ikonboard uses Perl to interface with a MySQL database which lives on your server. You can run MySQL without Perl, and vice versa, but for both to work together Perl needs the proper modules installed.
Those are the only two things you really need to know since the rest can be done in the AdminCP.
Step 1: Initial setup (before you go into AdminCP)
Please note for purposes of this document the word "example" represents the name of your database and the word "webuser" represents the user name and "mysql>" is the prompt you'll see when you login successfully to MySQL.
All valid MySQL database commands end with a semi-colon character.
a) If necessary, install the proper Perl CPAN modules first (see links and info above) b) If necessary, Install MySQL on your server (visit the download site listed above for instructions, be sure to set a password for any user you setup, including default root) c) Login to MySQL as root user on your server, i.e. "/usr/local/mysql/bin/mysql -u root" (use your path) d) Create a new database as follows:
CODE SAMPLE mysql> create database example;
e) Create a database user:
CODE SAMPLE mysql> GRANT usage -> ON example.* -> TO webuser@localhost; Query OK, 0 rows affected (0.15 sec) mysql> GRANT select, insert, delete -> ON example.* -> TO webuser@localhost; Query OK, 0 rows affected (0.00 sec) exit
At this point you've logged into MySQL, created a new database called "example" and a user named "webuser" who has full permissions to access and update the database and then exited MySQL. That is what needs to be done before you login to your AdminCP.
DO ** NOT ** SETUP ANY TABLES YET. TABLE SETUP IS BEST DONE VIA THE ADMINCP.
Other simple MySQL commands to ensure the database is active:
CODE SAMPLE use database example; status
In the future you can re-login to MySQL anytime using whatever username and password and database you setup, i.e. "/usr/local/mysql/bin/mysql -u webuser -p example"
Please note the -p prompts for password, this is an optional parameter and "example" is the database name to use which saves typing later.
f) Exit MySQL once your know you can login with the correct username, password and database you created are viable.
Step 2: AdminCP Commands At this point it's time to go into the AdminCP and do the following:
1. Turn board Offline (precautionary) 2. Export DB 3. Setup DB a. Set DB to MySQL b. Create Tables - Yes, since you haven't created them c. DB Files: No, re-install from backup d. DB passwd, username & DB name e. If DB is on same server, use localhost, otherwise enter IP f. Port: default is 3306 g. Just leave the prefix set to ib_ 4. Import DB a. Choose recent Backup file if more than one b. Set to MySQL c. Leave blank. 5. Switch DB Driver d. Switch to MySQL e. Leave Admin set to YES 6. Rebuild Board Stats 7. Check your forum - read a few topics, is everything okay?
At this point, don't expect everything to be 100% okay. The signs of trouble include forums not being found and polls showing up duplicated and/or in the wrong forum.
Luckily, IkonBoard authors knew about this and designed some fix utilities in the AdminCP:
SQL Client -> SQL poll fixer (remove any listed where poll title and topic title do not match up) SQL Client -> Forum info fixer (resolves broken topic links) 8. Rebuild board stats (do it once more to ensure all is well)
9. Check your forum - read a few topics again, is everything okay now? It should be. 10. AdminCP 11. Clean temp files (set all to "Yes" then relogin afterward) 12. Reset sessions (all users regardless of time) 13. Turn board back online (enable forums for your users)
You're done!
Post-setup minor forum issues you may experience:
1) Topics subscribed in "Topic Tracker" may appear or disappear suddenly. If so, simply cancel them via the UserCP. 2) Check to make sure birthday is setup in UserCP personal info, I noticed mine was lost after the switch.
At this point you've done all the setup and major maintenance you can do for switching DBM to MySQL.
Credit: Hate98. Thanks also to Soda and Lister for the original FAQ I based this on.
but what, my export?....it works when i import to dbm. my username and password for sql?.....ikonboard makes the tables, so it can access it. The MySQL database itself?.....I wouldnt think there to be a problem since it makes the tables and all, but I am new to this.
okay let me go through this, and see if i'm doing anything wrong here.
1) turn off board....not needed, so I didnt do it.
2) export dbm database......done, I went to "Database/Export Database" a. Create '.tar' file?.....Create .tar file (uncompressed) b. No. Topics to export at a time......left blank c. No. Files to put into each TAR file......left blank
3) set up the database.....did that, I went to "Database/Set-up Database"....then selected a. Which database do you wish to set up?.....MySQL b. Do you want Ikonboard to create the tables for you?......Yes c. Do you want Ikonboard to re-install the default database files for you?......No - I will be re-installing from a backup d. SQL Username.....username e. SQL Password.....password f. SQL Database name......database name g. SQL Database Server IP......localhost h. SQL Database Server Port......left blank I. SQL Table Prefix(so you can have multiple IBs in one DB......ib_
4) Import DB......did that at "Database/Import Database" a. Available Exported Databases.......current database I exported above b. Import into.....MySQL c. No. Topics to import at a time.....left blank
now, its at this step that I get the
Quote
IMPORTED 0 topics of forum 1 of 0 for import, moving on to the next batch...
Continuing to import the database into...
(Click here if you do not wish to wait)
and it just keeps going, last time i did it it went to
Quote
IMPORTED 0 topics of forum 1262 of 0 for import, moving on to the next batch...
2) export dbm database......done, I went to "Database/Export Database" a. Create '.tar' file?.....Create .tar file (uncompressed) b. No. Topics to export at a time......left blank c. No. Files to put into each TAR file......left blank
I would set b. = 500 and c. = 5000 and
Quote
4) Import DB......did that at "Database/Import Database" a. Available Exported Databases.......current database I exported above b. Import into.....MySQL c. No. Topics to import at a time.....left blank
here I would set c. = 500 too
these are fairly safe numbers for a normal sized board on a regular (but not heavily overloaded) server.
IMPORTED 0 topics of forum 1 of 0 for import, moving on to the next batch...
Continuing to import the database into...
(Click here if you do not wish to wait)
what do those mean anyway....no. Topics to export at a time, and No. Files to put into each TAR file? If I knew more about databases, it would be self explanitory I'm sure, but, I'm clueless.
and should I export it 500......5000 all the time, even when using dbm, or is it just a mysql thing?
So, I thought I would try to do another test board on my server, and make it mysql...instead of DBM....and no problems, then I try to import my database backup. The one that I exported from my DBM board, into mysql. and I get this
I might be blind and maybe I have even asked this before, but what exactly (in the way of software running) does my server need to run the sql database. I want to make sure everything is perfect.
sorry in advance if this question has come up and you have to answer it once again
"If the Gods could build me a ladder to the heavens, I'd climb up the ladder and drop a big elbow on the world." -- Cactus Jack
is there any certain modules or anything that I would need to run mysql? I ran the "Ikonboard Perl Test Script" and everything checked out, so I would imagine I would be okay.
OK to start with you will need to find a topic and file amount that will work with your server.
The number of topics to export at a time is how many topics it will put into each file for the backup, the number of files for each tar file is how many files the tar file can hold before it makes a new one.
On most of the servers I have worked with it has worked best to just have one big tar file, so I set the number of files for each tar file to 100000.
The number of topics to export and import at a time is the one that varies the most for me anyway. On some servers I must set this to a very low number like 10 or 20 and on others I can go as high as 100 - 500.
my $url = "CODE=do_tables&dir=$new_directory&id=0&DB_DRIVER=$iB::IN{'DB_DRIVER'}&MEMBERS=$iB::IN{MEMBERS}&TOPICS=$iB::IN{TOPICS}";
$obj->_redirect( $url, "Tar file extracted, preparing to create the new database" ); }
Quote
In the Backup.pm file locate and change the "do_forums" sub as follow
Code Sample
sub do_forums { my ($obj, $db) = @_;
# Cap the max topics at 500
my $max = $iB::IN{TOPICS} || 500; my $st = $iB::IN{st} || 0; my $end = $max + ($st - 1); my $id = $iB::IN{fid};
# Which forum are we on? my $fids = {}; my $fcnt; my $string; { # Parse the config file local $/ = undef; open FORUMS, "$obj->{'this_dir'}/tmp_forums" or die "Cannot read the forums config file ($!)"; $string = <FORUMS>; close FORUMS; chomp $string; } for ( split /;/, $string ) { my ($k, $v) = split /\:/; $fids->{ $k } = $v; ++$fcnt; }
# If we've done...
if ($fids->{ $id } eq '!!END!!') {
# We are all done, simply tar up the database if that's what we # intend to do.
if ($iB::IN{'pack'} eq 'tar') { $iB::IN{'per_tar_files'} ||= 15;
opendir DIR, $obj->{'this_dir'}; my @read_files = readdir DIR; closedir DIR;
my @files; for (@read_files) { push @files, "$obj->{'this_dir'}/$_" unless ($_ eq '.' or $_ eq '..'); }
my $files = scalar @files; my $filecount = $iB::IN{'filecount'} || 0; $filecount = $files if $filecount > $files;
my $filecount2 = $filecount + $iB::IN{'per_tar_files'};
# Bring up the Redirect screen, so we can process the next batch of # backup data files... my $Url = "?AD=1&s=$iB::SESSION&act=bak&CODE=dobak&time=".$obj->{'time'}."&pack=$iB::IN{'pack'}&forums=1&fid=$id&per_tar_files=$iB::IN{'per_tar_files'}&filecount=$filecount2"; $obj->_redirect( "Created TAR files for $filecount2 out of $files files, processing next batch...", $Url); } }
$ADMIN->static_screen( URL => "act=backup", TITLE => "Export Completed", TEXT => "The database has been successfully exported. Your exported database is in the Back-ups directory" ); }
# Correct the total forum count. --$fcnt; my $rcnt = 0; # Get the topics to export...
my $topics = $db->query( TABLE => 'forum_topics', ID => $fids->{$id}, RANGE => "$st to $end", WHERE => "FORUM_ID == $fids->{$id}", );
if (scalar (@{$topics}) > 0) { # Start the exporting
# Open the topic file open THETOPICS, ">>$obj->{'this_dir'}/tmp_topics-$fids->{ $id }"; for my $t (@{$topics}) { my $record = $db->join_record( TABLE => 'forum_topics', VALUES => $t ); print THETOPICS $record."\n"; ++$rcnt; # Get the posts.. my $posts = $db->query( TABLE => 'forum_posts', DBID => 'f'.$fids->{$id}, ID => $t->{TOPIC_ID}, WHERE => "FORUM_ID == $fids->{$id} and TOPIC_ID == $t->{TOPIC_ID}", );
# Open the posts file open THEPOSTS, ">$obj->{'this_dir'}/tmp_fposts-$fids->{ $id }-$t->{TOPIC_ID}"; for my $p (@{$posts}) { my $post = $db->join_record( TABLE => 'forum_posts', VALUES => $p ); print THEPOSTS $post."\n"; } # close the posts file close THEPOSTS; } # Close the topics file close THETOPICS;
# Redirect to the new screen my $next = $end + 1;
my $Url = "?AD=1&s=$iB::SESSION&act=bak&CODE=dobak&time=".$obj->{'time'}."&pack=$iB::IN{'pack'}&TOPICS=$iB::IN{TOPICS}&forums=1&fid=$id&st=$next&per_tar_files=$iB::IN{'per_tar_files'}"; $obj->_redirect( "Exported $rcnt topics of forum unique ID $fids->{$id} so far, processing next batch....", $Url); }
else { # We must have completed the export on that forum, lets move onto the next... ++$id; my $Url = "?AD=1&s=$iB::SESSION&act=bak&CODE=dobak&time=".$obj->{'time'}."&pack=$iB::IN{'pack'}&TOPICS=$iB::IN{TOPICS}&forums=1&fid=$id&st=0&per_tar_files=$iB::IN{'per_tar_files'}"; $obj->_redirect( "Completed forum unique ID $fids->{$id}, processing next forum....", $Url); } }
Warning!!! make a backup of these files first!
then, export your current DBM forum and import in to your Mysql!! and Done!
The problem is in the tar/untar feature (I don't know what is happen exactly, but something is wrong in the code or my server)
So I just avoid deletion of temporary folder, so when you export your data, all files created remains in your server. Then I avoid untar process, and the forum, just read the info directly from the temp folder.
Quote
do I do this before I export also? or do I do this, and then import my old export?
I'm assuming you have a DBM forum runing and you want to convert it to Mysql
