The Community Forums

Interact with an entire community of cPanel & WHM users!
  1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

MySQL Database Locks while dumping

Discussion in 'General Discussion' started by trparky, May 4, 2006.

  1. trparky

    trparky Well-Known Member

    Joined:
    Apr 23, 2003
    Messages:
    184
    Likes Received:
    1
    Trophy Points:
    0
    MySQL Database Locks while dumping, need to stop it from locking

    I don't know where to put this, so I'm putting it here.

    Anyways, we have a very large database, and when I say large, I mean it. Roughly half a GB in size. When CPanel backups do occur, access to the database stops until it is finished. Now, I am guessing here, but I think that CPanel invokes the "mysqldump" command to dump the database to an SQL file and then backs it up.

    Ok, so I executed this at the command line...
    mysqldump database_name > database_name.sql

    Guess what, all access to that database in question stops, unless I stop the command from running by pressing Control-C.

    How do I make it so that MySQLDump doesn't do this? We need the database to be accessible when the system is backing up.
     
    #1 trparky, May 4, 2006
    Last edited: May 4, 2006
  2. rustelekom

    rustelekom Well-Known Member
    PartnerNOC

    Joined:
    Nov 13, 2003
    Messages:
    290
    Likes Received:
    0
    Trophy Points:
    16
    Location:
    moscow
    what you mean when say "locking access" ? only this database is locked or all which you have on server? if all, you should play with /etc/my.cnf variables. for example

    [mysqldump]
    quick
    max_allowed_packet = 128M

    [mysqlhotcopy]
    interactive-timeout
     
  3. trparky

    trparky Well-Known Member

    Joined:
    Apr 23, 2003
    Messages:
    184
    Likes Received:
    1
    Trophy Points:
    0
    For instance, when I am dumping the database, it says this...

    Code:
    mysql> show processlist;
    +----------+-------------+-----------+-------------+---------+------+--------------+------------------------------------------------------------------------------------------------------+
    | Id       | User        | Host      | db          | Command | Time | State        | Info                                                                                                 |
    +----------+-------------+-----------+-------------+---------+------+--------------+------------------------------------------------------------------------------------------------------+
    | 33690430 | root        | localhost | *********** | Query   |  77  | Sending data | SELECT /*!40001 SQL_NO_CACHE */ * FROM `smf_log_search_words`                                        |
    | 33690435 | *********** | localhost | *********** | Query   |  92  | Locked       | REPLACE INTO smf_log_online (session, ID_MEMBER, logTime, ip, url) VALUES ('ip***.***.***.***',      |
    | 33690439 | *********** | localhost | *********** | Query   |  92  | Locked       | UPDATE smf_sessions SET data = 'rand_code|s:32:\"********************************\";ID_MSG_LAST_VI   |
    | 33690440 | *********** | localhost | *********** | Query   |  92  | Locked       | UPDATE smf_sessions SET data = 'rand_code|s:32:\"********************************\";', last_update   |
    | 33690446 | *********** | localhost | *********** | Query   |  92  | Locked       | UPDATE smf_sessions SET data = 'rand_code|s:32:\"********************************\";ban|a:4:{s:12:   |
    +----------+-------------+-----------+-------------+---------+------+--------------+------------------------------------------------------------------------------------------------------+
    As you can see, the database tables are locked. We can't have them locked, they must still be able to be used during the database backup.
     
  4. chirpy

    chirpy Well-Known Member

    Joined:
    Jun 15, 2002
    Messages:
    13,475
    Likes Received:
    20
    Trophy Points:
    38
    Location:
    Go on, have a guess
    Yup it will do, for what ought to be obvious reasons - i.e. no change to tables while dumping otherwise you'll have an inconsistent dump. That said, you could try using --single-transaction and --quick which is explained on the --help page:
     
Loading...

Share This Page