LinuxQuestions.org
Help answer threads with 0 replies.
Go Back   LinuxQuestions.org > Forums > Linux Forums > Linux - Server
User Name
Password
Linux - Server This forum is for the discussion of Linux Software used in a server related context.

Notices


Reply
  Search this Thread
Old 12-08-2010, 12:41 AM   #1
minim
LQ Newbie
 
Registered: Apr 2010
Posts: 8

Rep: Reputation: 0
Locks table during mysqldump - pls help


Hello,

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 in advance
 
Old 12-08-2010, 01:36 AM   #2
paulsm4
LQ Guru
 
Registered: Mar 2004
Distribution: SusE 8.2
Posts: 5,863
Blog Entries: 1

Rep: Reputation: Disabled
Hi -

The "lock table" issues you're talking about are discussed here:

http://forums.mysql.com/read.php?34,...7019#msg-87019
http://dev.mysql.com/doc/refman/5.0/en/mysqldump.html

In our shop, stopping the web server and mysql once a week, taking a mysqldump (with the server down), and then starting things back up is No Big Deal. I assume that's probably not the case in your scenario

But taking a backup every hour on a live, 1++GB production database probably isn't the best approach, either.

What should you do?

"It depends". On, among other things:

1. Your MySQL version
2. Whether you're using InnoDB, myISAM, or "something else"
3. Your database access patterns (mostly updates? lots of complex queries? heavy transactional use? other?)
4. Your resources (disk, CPU, RAM ... and $$$ budget)
5. Etc etc

Replication is certainly worth considering. The full MySQL documentation is on-line, but this little "howto" might be helpful:

http://www.howtoforge.com/mysql_database_replication

Incremental backups might be an even better solution for you:

http://dev.mysql.com/doc/refman/5.1/...p-methods.html
http://www.linuxbackups.org/mysql-backup
http://www.sqlpronews.com/2010/0511.html
<= Incremental backups are NOT a substitute for periodic full backups...
... but they might be an excellent alternative for you

And yes, I'd probably still recommend sticking with the table locks even for the incremental backup, but the performance impact for incremental backups should be minimal.

IMHO...
 
  


Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off



Similar Threads
Thread Thread Starter Forum Replies Last Post
Can't play .pls files in Rhythmbox, closes when I try to load .pls file lqv Linux - Software 2 01-07-2013 09:30 AM
pls u pls help me to install asterisk on a server in italy abdul rashid Linux - Newbie 2 02-17-2010 04:32 PM
Pls. Help -- about routing table swmok Linux - Networking 3 08-17-2004 01:25 PM
Mandrake 10 Locks up at Startup - Newbie help PLS jimhe Mandriva 1 08-16-2004 08:52 PM
pls pls pls help me ! i'm tired with httpd config on fedora apache 2.0.48 AngelOfTheDamn Fedora 0 01-24-2004 06:12 PM

LinuxQuestions.org > Forums > Linux Forums > Linux - Server

All times are GMT -5. The time now is 06:19 AM.

Main Menu
Advertisement
My LQ
Write for LQ
LinuxQuestions.org is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
Syndicate
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
Open Source Consulting | Domain Registration