How to backup database from CRON excluding search word and attachements table ?

[Sharg]

Hi, I have a very precise need.
To make my database backup, I currently have cron run:

mysqldump --opt -uroot -ppass databasename >/var/www/dbbackup/currentdb.txt

How should I modify this, so it will backup the same database, WITHOUT all the searches related table that can be rebuit anyway, + without the attachement table ?

Thanks,

[eva2000]

use this http://vbulletin.com/forum/showthread.php?s=&threadid=4256

[Sharg]

Hi Eva, I don't see anything there that will tell me how to exclude tables from a database backup.:confused:

[Sharg]

C'mon people. Is it so hard to exclude some VB tables from being backuped ?

[chrispadfield]

yeah i would really like to know how to do this, i don't want to email that great big search table every time... suggestions?

[eva2000]

i have no idea how to exclude a table but you can specifiy which tables to dump

mysqldump dbname table1 table2 table3 table4 table 6 > /path/to/dump.sql

[Sharg]

Hey Eva, great news thanks, so I guess I will have to type:


mysqldump --opt -uroot -ppass databasename vbtable1 vbtable2....

There are 37 Vbulletin tables in beta 5 right ?

[eva2000]

i suppose so i never tried it myself

mysqldump --opt dbname table 1 table 2 > /dump.sql

you can dump out the table structure of beta 5 install

mysqldump --opt dbname -d > /tabledump.sql and look to see or as Freddie suggest look into the install1.php for beta 5 to see the Create table commands

[Sharg]

Ok,
if someone is interested, here is a list of all the tables:

access adminlog announcement attachment avatar bbcode calendar_events customavatar forum forumpermission icon moderator poll pollvote post privatemessage profilefield replacement replacementset search searchindex session setting settinggroup smilie style subscribeforum subscribethread template templateset thread threadrate user userfield usergroup usertitle word

Now, I only need to know which table(s) to exclude from this, since we don't need to backup the table from which the content get rebuilt when you reindex.

[eva2000]

i think searchindex, sessions, and word are the tables you can exclude BUT wait for Ed or Freddie to confirm :)

[Sharg]

Well here are my results:

It works great.

I excluded the tables suggested by wayne here: http://www.vbulletin.com/forum/showthread.php?s=&threadid=13638

and have a separate backup only for attachement table.

So I backup daily my database and gzip it and download it often, and its not big because it doesn't have all the search and attachement data.
All with cron.

Then, from time to time i'll download the attachement table.

This way, my download don't take longer than with 1.x.

[chrispadfield]

Sharg, have you tried Chris script, you can get it to automatically email you the compressed filed of server, great if you have a virtual account somewhere else or something - saves a lot of time.

[eva2000]

download FlashGet from www.amazesoft.com and you can point the program to your mysql dump location and grab it divided into 10 equal chunks for simultaneous download - i get 60 - 64kb/s download on my 60mb sql gzipped mysql dump