XF 2.2 dumping the database errors

[Jim Boy]

hey, usually when I try to do a mysqldump on my very large xf (2.2.15) database I get errors on certain tables causing the dump to fail. Errors are usually DDL being changed on tables like xf_table_view. Asking copilot, its response is that I should ignore that table and nine others in the dump command and that Xenforo will simply recreate them.

Is this true, is there a better way and is this actually documented somewhere?

 

[Rene_V]

Errors are usually DDL being changed on tables like xf_table_view.

There is no such xenforo default table a.f.a.i.k. Maybe it's added by a third party add on?

Asking copilot, its response is that I should ignore that table and nine others in the dump command and that Xenforo will simply recreate them.

What are the other tables mentioned in the error? My advice, if I may of course, is that such errors shouldn't be ignored because it can lead to an incomplete backup and restore afterwards.

usually when I try to do a mysqldump on my very large xf (2.2.15) database

By mysqldump, do you mean when using the mysqldump SSH command, or the mysqldumper script?

 

[Itworx4me]

hey, usually when I try to do a mysqldump on my very large xf (2.2.15) database I get errors on certain tables causing the dump to fail. Errors are usually DDL being changed on tables like xf_table_view. Asking copilot, its response is that I should ignore that table and nine others in the dump command and that Xenforo will simply recreate them.

Is this true, is there a better way and is this actually documented somewhere?

P

Post in thread 'How to duplicate MYSQL database on CentOS VPS?'

Mar 18, 2015

I prefer to use this for dumping:

mysqldump -u -p --single-transaction --skip-lock-tables database_name > /path/to/backup/database_name_$(date +%d.%m.%y).sql

• Paul B

• 

 

[Jim Boy]

What are the other tables mentioned in the error? My advice, if I may of course, is that such errors shouldn't be ignored because it can lead to an incomplete backup and restore afterwards.

"Volatile" tables allegedly include:
xf_thread_view
xf_thread_view_daily
xf_search_index
xf_search_log
xf_session
xf_session_activity
xf_session_admin
xf_image_proxy
xf_link_proxy

Its alleged that Xenforo will "recreate" them. However I might back them up without data and simply recreate them

 

[Jim Boy]

and xf_attachment_view

 

[Jim Boy]

P

Post in thread 'How to duplicate MYSQL database on CentOS VPS?'

Mar 18, 2015

I prefer to use this for dumping:

mysqldump -u -p --single-transaction --skip-lock-tables database_name > /path/to/backup/database_name_$(date +%d.%m.%y).sql

• Paul B

• 

For starters the command should be more like

mysqldump -h 127.0.0.1 -u root --password=xxxxxx --add-drop-table --create-options --disable-keys --extended-insert --quick--set-charset --default-character-set=utf8mb4 --single-transaction --skip-lock-tables --hex-blob .........

But this isn't really good enough either. You can get away with it on smaller datasets but then there are some cron jobs which optimise tables and might do things like reset counters, which is a DDL action. If that happens while you are doing the backup, then your backup fails - even when using the single-transaction flag - mysqlbackup just cant handle it. If your database is only small, like 400MB, then the chances of failure are slim. If the database is bigger (mine is 100GB+) then you almost certainly hit this as the dump takes at least 90 minutes.
so the options as I see it are:
1. exclude the tables and let XF recreate them or treat the tables with their own backups
2. use mariadbbackup - which doesn't have this issue, unfortunately it is not really an option for me
3. suspend cronjobs, allegedly this can be done via an option in the config

 

[briansol]

my observed ratios are around 2gb per million posts.
do you have a 50 million post forum?


I use --force to let it roll even if there's an error like this and --opt to go row by row.

 

[Rene_V]

Its alleged that Xenforo will "recreate" them. However I might back them up without data and simply recreate them

They will have to be rebuilt I think, but I would still backup them up with all the data, unless their sizes are mega large.

 

[Jim Boy]

my observed ratios are around 2gb per million posts.
do you have a 50 million post forum?


I use --force to let it roll even if there's an error like this and --opt to go row by row.

90 million posts - I'll take the suggestion of forced - will highlight the tables that failed, which I can recreate from the schema

And "--opt" - it doesn't do what you think it does, and is implemented as a default option anyway so you never have to specify it

 

[MySiteGuy]

For starters the command should be more like

mysqldump -h 127.0.0.1 -u root --password=xxxxxx --add-drop-table --create-options --disable-keys --extended-insert --quick--set-charset --default-character-set=utf8mb4 --single-transaction --skip-lock-tables --hex-blob .........

But this isn't really good enough either. You can get away with it on smaller datasets but then there are some cron jobs which optimise tables and might do things like reset counters, which is a DDL action. If that happens while you are doing the backup, then your backup fails - even when using the single-transaction flag - mysqlbackup just cant handle it. If your database is only small, like 400MB, then the chances of failure are slim. If the database is bigger (mine is 100GB+) then you almost certainly hit this as the dump takes at least 90 minutes.
so the options as I see it are:
1. exclude the tables and let XF recreate them or treat the tables with their own backups
2. use mariadbbackup - which doesn't have this issue, unfortunately it is not really an option for me
3. suspend cronjobs, allegedly this can be done via an option in the config

Look into mydumper/myloader which does multi-threaded dumps and restores. That 90 minutes will be cut to a fraction.