Solved

mysqldump question

Posted on 2011-09-27
7
500 Views
Last Modified: 2012-08-13
I have a question about mysqldump.  

I currently run a mysqldump script on my databases daily. One database is very large with blob data and the backup takes quite a while. During the backup we are still able to search update and insert into this database which is necessary at all times.

My question is.. Will mysqldump backup/dump data that is updated or inserted into tables after the mysqldump process is started?

If it does i would be concerned that a restore from the dump would be incomplete.

Example:
Say I have 5 tables. MysqlDump starts dumping and completes the first two tables and starts backing up the third table which is large and takes a while. In the mean time my application is still running and users are inserting and updating data which affects the first two tables that have already been dumped as well as the third which is still being dumped as well as the fouth and fifth table which haven't been dumped yet. This would make for an incomplete record for my application if table three, four and five dumps include the new data but tables one and two don't...
0
Comment
Question by:Ben720
7 Comments
 
LVL 24

Accepted Solution

by:
johanntagle earned 250 total points
Comment Utility
mysqldump by default only locks the current table being backed up, so yes, you are likely going to get tables inconsistent from each other.  Using --databases or --all-databases option will lock and dump all tables a database at a time, which will enable a consistent state, but then you won't be able to do inserts.

Since you put MS OS as one of the zones for this question, I assume your database is on a Windows server, so no option for LVM snapshots.  Suggest you configure replication, then do the backup on the slave while replication is temporarily turned off.

There's also Percona Xtrabackup that does hot backup, but the Windows version is still in Alpha.  See http://www.percona.com/downloads/XtraBackup/XtraBackup-1.6/Windows-alpha/.
0
 

Author Comment

by:Ben720
Comment Utility
Was afraid of that. After some more googleing for backing up the database with out taking it offline I came across "MySQL Enterprise Backup" . It seems to have the ability to do a complete backup while the database is still online (selects/updates/inserts/ect..) then updates the backed up data with any changes made during the backup. Going to run some testing with it, I'll post back the results...
0
 
LVL 24

Assisted Solution

by:johanntagle
johanntagle earned 250 total points
Comment Utility
Oh yes forgot about that because I don't use it (I believe it's $5000/server?).  Anyway, it's what's Percona Xtrabackup is positioned against - see http://www.percona.com/software/percona-xtrabackup/
0
 

Assisted Solution

by:rwwff
rwwff earned 125 total points
Comment Utility
Replication can be a little intimidating to setup the first time, but its really not so bad; and once you have a replication server running, you can do any ole horrible thing to it that you might want without impacting the working server.

0
 
LVL 21

Assisted Solution

by:theGhost_k8
theGhost_k8 earned 125 total points
Comment Utility
I'd rather suggest to setup a slave and take a backup from there.
If you are using Innodb "--single-transaction" will help in taking the consistent snapshot.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Have you ever had a hard drive that you can't boot into, but need to change the registry? Here is the solution! This article guides you through accessing and editing a registry of a non-primary drive. To read registry information on a non-prim…
Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL (http://www.experts-exchange.com/articles/201/Handling-Date-and-Time-in-PHP-and-MySQL.html) several years ago, it seemed like now was a good time to updat…
In this video, we discuss why the need for additional vertical screen space has become more important in recent years, namely, due to the transition in the marketplace of 4x3 computer screens to 16x9 and 16x10 screens (so-called widescreen format). …
Windows 8 came with a dramatically different user interface known as Metro. Notably missing from that interface was a Start button and Start Menu. Microsoft responded to negative user feedback of the Metro interface, bringing back the Start button a…

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now