Mysqldump and Lock Tables
My database is growing and until now I use mysqldump schedule job to backup my databases.
The problem I'm facing is the time it takes to complete this job and during this time, since the tables are locked, nginx+fpm reach the max clients.
I think it's time to change this, since I can get a corrupt backup with this method.
I know I can place nginx in maintenance mode (status code 502) and then dump the database, but would like something less manual.
So, can anyone give me suggestions to make this and automatic thing and at the same time, avoiding table locks?
Thanks
11 Replies
Any advice for a 512/768 linode?
Thanks
cat /etc/mysql/conf.d/innodb.cnf
[mysqld]
default-storage-engine = InnoDB
innodb_file_per_table
innodb_buffer_pool_size=280M
innodb_additional_mem_pool_size=8M
That's from a 768 server.
You can read more about them here
The innodbbufferpool_size is the important one it basically specifies how much ram to use as a cache for data/indexes etc the bigger the cache the less disk IO is required the faster the application.
How much you'll want to allocate depends on how much data you have and how much ram you need for other services, you don't want to allocate much more than your entire database size if you have a small database. Otherwise i'd say try using 200M and work your way up.
Tools such as
1. Prerequisite: the DB files need to reside on an LVM logical volume.
2. Do a "flush tables with read lock" in MySQL.
3. Take a snapshot of the logical volume.
4. Release the read lock.
5. Mount the snapshot under a temporary mount.
6. Backup the DB files from the temp mount.
7. Unmount, and delete the LVM snapshot.
The database becomes unavailable for writes during steps 2 - 4. These should take only a few seconds to complete. This works regardless of the database size.
If you want, I could write up a mini-howto to set this up on a Linode that does not already have LVM. Also, there is a small utility that can automate some of the steps for you, called "mylvmbackup".
The really nice thing about running a backup off a snapshot is that if you have a busy website where users modify content using multiple applications (for example MySQL updates and uploaded file content), the point in time snapshot keeps everything in sync. The entire backup process might take a minute or maybe an hour, but the snapshot creation takes only a second, and after that your applications can continue running.
There are different backup strategies depending on one's needs, and this is just one solution. I've been using LVM for a long time, and to me it's very easy and convenient. Some people either don't get it, or don't like it, but for me, just because I moved to Linode does not mean that I have to stop enjoying the benefits that LVM can provide.
/usr/bin/mysqldump --single-transaction --quick --master-data=1 --all-databases > blah
/usr/bin/s3cmd --acl-private put blah s3://blahblah/blah
Bonus: when something writes to the database, MySQL doesn't have to lock the whole table. And you can use transactions.
There is no such thing as consistency (or resiliency…) with MyISAM. That's why it isn't recommended.
LVM isn't a bad idea, you could be really cool and combine LVM and InnoDB!