Not the answer you need?
Register and ask your own question!
Many Forum changes were implemented on Tue 22 Sep. Read about new Ranks, Scoring, and Reactions.
Email [email protected] for any comments or concerns.

Daily mysqldump locking database

tomp_gltomp_gl EntrantInactive User Role Beginner
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.

Comments

  • sterinsterin Mentor Inactive User Role Contributor
    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_gltomp_gl Entrant Inactive User Role Beginner
    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
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.