Copying (backing-up) a database the other way?

[Digma]

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?

[Zachery]

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.

[Digma]

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?

[Zachery]

1. Post a sever optimization request over @ vBulletin.

2. You can use mysqlhotcopy
http://dev.mysql.com/doc/refman/5.0/...qlhotcopy.html

[Digma]

Ok, thanks for the advice. Will have a look at vb.com.

[SamirDarji]

Originally Posted by Digma

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.