Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2009
    Posts
    57

    Lock table during mysqldump - pls help

    Hello Guys,
    We are running an adserver with mysql as the database. We backup the database every hour and lately noticed that ads doesn't get delivered at the time of backup. The database size is 1.2 GB in which a single table ifself is 1.1 GB. Googling around the problem says this may be due to lock table feature in mysqldump and disabling it might solve the issue but i'm afraid whether this would be a problem. Can anyone please explain what lock table means and will it be a problem if i disable it or may be any other alternative solutions/assitance (other than master/slave or replication since we cannot afford it at this time) would be really helpful.

    Thanks
    Minim

  2. #2
    Join Date
    Aug 2010
    Location
    Prague, Czech Republic
    Posts
    404
    Locking table means MySQL server doesn't allow any write operations on it to get consistent database. The better way to backup database is to backup from slave which used only for read operations.
    Supportex.Net server management, full range of services. EU-based outsourced company. Since 1998.
    Outstanding quality for high performance projects; clustering and high-availability solutions, DDoS protection.
    Cisco/Juniper network management & deployment assistance. Network design and monitoring.

  3. #3
    Join Date
    Mar 2003
    Location
    California USA
    Posts
    13,681
    You don't want to back it up without locking.

    If locking is an issue, you need to either replicate to a slave and backup from the slave server, or utilize r1soft.
    Steven Ciaburri | Industry's Best Server Management - Rack911.com
    Software Auditing - 400+ Vulnerabilities Found - Quote @ https://www.RACK911Labs.com
    Fully Managed Dedicated Servers (Las Vegas, New York City, & Amsterdam) (AS62710)
    FreeBSD & Linux Server Management, Security Auditing, Server Optimization, PCI Compliance

  4. #4
    Consider using mysqlhotcopy, it still does lock tables, but copys the database files extremely quickly compared to mysqldump.

    The downside is, it only supports myisam and archive table types, so if your using innodb look into another solution.

Similar Threads

  1. MySQL: Copy specific fields from Table A into Table B
    By damainman in forum Programming Discussion
    Replies: 4
    Last Post: 09-28-2010, 05:19 PM
  2. Difference betweeb Reseller Lock and Customer Lock
    By oozypal in forum Domain Names
    Replies: 1
    Last Post: 01-23-2009, 07:38 PM
  3. Sorting a table by the results of another table (MySQL/PHP)
    By jon31 in forum Programming Discussion
    Replies: 4
    Last Post: 07-10-2007, 07:51 PM
  4. Synchronize MSSQL Table (DB) with a MySQL Table (DB)
    By kalinga in forum Hosting Security and Technology
    Replies: 0
    Last Post: 03-26-2006, 12:58 AM
  5. Lock to lock rackmount to the rails
    By Fremont Servers in forum Hosting Security and Technology
    Replies: 1
    Last Post: 12-20-2004, 04:41 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •