Digma
02-25-2011, 08:51 AM
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?
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?