Register Members List Search Today's Posts Mark Forums Read

Reply
 
Thread Tools
  #1  
Old 25 Feb 2011, 09:51
Digma Digma is offline
 
Join Date: Nov 2004
Copying (backing-up) a database the other way?

After running into problems (with the postindex table) trying to restore a .sql dump into a new database using the command line option, I started looking at other options.

One of them was changing the vb based search to fulltext search, through the vbulletin options > search type, so I could flush the postindex table at some point. I ended up doing the fulltext search alteration in SSH as well, as our database (2.9m records in post) is apparently to large to process properly and don't make the roof come down on us. In the end it worked (after having taken more than 11 hours) and there is a difference in cardinality between the fulltext index and postid of about 1.5m.

Because of the difference between the postid and fulltext index cardinality, I was still reluctant to flush the table, but that would still leave me with the same problem, not being able to restore a backup into a new database (because of the postindex table messing up).

I then started looking at alternatives and wondered if it was possible to simple create a database and then copy the .opt, .frm, .MYD and .MYI files into the directory of the other database, set chown for all those files to mysql.mysql and work with that.

Now I actually tested this by creating 2 new databases and in one a table with just a few id's and names. I then copied the files, changed ownership and went into PHPMyAdmin and so far so good, it seems to work.

Right, I believe that I have found a solid way and less stressing way of copying/backing up a database this way, but is it??

What will happen if I approach the vb database in this manner (after closing the msg board first of course)? Will I run into problems in the long run, are there pitfalls? It just seems to good to be true. Any suggestions or advice on this part?
Reply With Quote
  #2  
Old 25 Feb 2011, 10:24
Zachery's Avatar
Zachery Zachery is offline
 
Join Date: Jul 2002
Real name: Zachery Woods
It sounds like your mysql isn't well optmized for the queries you're running :/

People have run the older, normal vB3 fulltext, searches on larger forums than yours.
__________________
Looking for ImpEx?
Reply With Quote
  #3  
Old 25 Feb 2011, 10:28
Digma Digma is offline
 
Join Date: Nov 2004
Zachary, thanks for the reply.

Got two questions though:
1. Do you have any suggestions I should look into in order to get my MySQL properly optimized?

2. And on a second note, the option I suggested, (with the MYD, MYI, etc..) will this work as alternative or am I completely daft in thinking this?
Reply With Quote
  #4  
Old 25 Feb 2011, 11:14
Zachery's Avatar
Zachery Zachery is offline
 
Join Date: Jul 2002
Real name: Zachery Woods
1. Post a sever optimization request over @ vBulletin.

2. You can use mysqlhotcopy
http://dev.mysql.com/doc/refman/5.0/...qlhotcopy.html
__________________
Looking for ImpEx?
Reply With Quote
  #5  
Old 25 Feb 2011, 17:56
Digma Digma is offline
 
Join Date: Nov 2004
Ok, thanks for the advice. Will have a look at vb.com.
Reply With Quote
  #6  
Old 06 Mar 2011, 06:04
SamirDarji SamirDarji is offline
 
Join Date: Apr 2004
Originally Posted by Digma View Post
I then started looking at alternatives and wondered if it was possible to simple create a database and then copy the .opt, .frm, .MYD and .MYI files into the directory of the other database, set chown for all those files to mysql.mysql and work with that.

Now I actually tested this by creating 2 new databases and in one a table with just a few id's and names. I then copied the files, changed ownership and went into PHPMyAdmin and so far so good, it seems to work.

Right, I believe that I have found a solid way and less stressing way of copying/backing up a database this way, but is it??
When I was running vB in an intranet and had direct access to both servers I would do this all the time. Just be sure to shut down everything mysql, apache, etc. so that nothing can touch the files. Then they're just files.

It wasn't until I started my own public vb site that I learned that I couldn't do this anymore. And then had to learn to deal with phpmyadmin and other such tools.
__________________
Pics of the Huntsville Car Scene: www.huntsvillecarscene.com, hsv.streetracing.org
Pics of the Huntsville Night Life: www.thetoe.cc
Reply With Quote
Reply



Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off


New To Site? Need Help?

All times are GMT. The time now is 04:00.

Layout Options | Width: Wide Color: