Daily mysqldump locking database

[tomp_gl]

Hi,

I run a busy website and perform daily mysql backups. Every day on cue my database locks up due to SELECT /*!40001 SQL_NO_CACHE */ * FROM table queries. Is there any way i can prevent this lock up?

I have the following my.cnf settings for mysqldump:

[mysqldump]
quick
max_allowed_packet=16M

Would increasing max_allowed_packet improve the backup speed?

Thanks,
Tom

[EDIT] Sorry i think i have found a solution to this - i use cpanel, which performs automatic backups - i believe the command being used by cpanel contains the --lock-tables or --lock-all-tables options which should be removed for my purposes – the problem is i just can’t find where the backup script is.

[sterin]

You do understand the implications if you skip the read lock on the tables, right?

That your backup might not be consistent due to inserts are taking place in other tables than the one that you are currently backing up.

If you really can’t live with a locked database during backup then take a look at InnoDB and the InnoDB Hot Backup software.

The other backup you can do if you are running MyISAM tables is to run something like mysql-hotcopy. That copies the entire directory, hence making the backup faster since it doesn’t have to dump it as sql commands or text which is what mysqlbackup does.

[tomp_gl]

I have a mixture of innodb and myisam in my database - loosing inserts during the backup procedure for my purposes is a more acceptable compromise than users being locked out. Unfortunately i’m bound to the limitations of the cpanel backup system for the moment, so i can only alter the [mysqldump] variables in my.cnf.

Thanks