Hello,

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

If you're using innodb only then this is a good option
http://dev.mysql.com/doc/refman/5.1/en/ ... ransaction

_________________
Paid support
How to ask for help
1. Give details of your problem
2. Post any errors
3. Post relevant logs.
4. Don't hide details i.e. your domain, it just makes things harder
5. Be polite or you'll be eaten by a grue

Sorry, I'm using myisam.

Thanks anyway.

You should consider converting to innodb at some point, it's more reliable and is generally easier to configure, for myisam you could try mysqlhotcopy it's faster than mysqldump, or you could try creating a mysql replication slave and having that do the dump, the slave would mean no down time at all.

_________________
Paid support
How to ask for help
1. Give details of your problem
2. Post any errors
3. Post relevant logs.
4. Don't hide details i.e. your domain, it just makes things harder
5. Be polite or you'll be eaten by a grue

I could convert to innodb, but what about memory? For what I read, Inno db needs more memory than myisam.

Any advice for a 512/768 linode?

Thanks

Not really, innodb just makes memory management simpler these are some settings you should look at:

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 http://dev.mysql.com/doc/refman/5.1/en/ ... eters.html

The innodb_buffer_pool_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 https://github.com/rackerhacker/MySQLTuner-perl and https://launchpad.net/mysql-tuning-primer can work out the maximum amount of ram mysql can use.

_________________
Paid support
How to ask for help
1. Give details of your problem
2. Post any errors
3. Post relevant logs.
4. Don't hide details i.e. your domain, it just makes things harder
5. Be polite or you'll be eaten by a grue

You could also dump only one table at a time (which can potentially cause inconsistencies between tables; the implications of this depend on your application), or use mysqlhotcopy.

We use MyISAM tables, and didn't want to move to InnoDB. One way to do a backup is to utilize the power of LVM. The high-level process would look like this:

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 trouble with lvm is that you can't then use the linode backups or linode manager for disk resizing.

_________________
Paid support
How to ask for help
1. Give details of your problem
2. Post any errors
3. Post relevant logs.
4. Don't hide details i.e. your domain, it just makes things harder
5. Be polite or you'll be eaten by a grue

obs, I've had no problems resizing a linode with this setup. I'm using LVM over a loop device, and the resizing definitely works. We don't use the backup service, because we backup offsite to a different provider, and we like to do backups more than only once a day. Since a resize is essentially a backup and a restore to a different linode, I don't see why it would not work, but again, I personally have not tried the backup service.

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.

Alternatively, switch to InnoDB and enable binlogging and skip the whole LVM snapshot part. You can do this to get your frequent offsite backups:

/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.

_________________

/* TODO: need to add signature to posts */

The backup service says it doesn't work with LVM http://library.linode.com/linode-platfo ... imitations so I assumed resizing disks wouldn't work either (though I've never tried, bad assumption on my part!)

LVM isn't a bad idea, you could be really cool and combine LVM and InnoDB!

_________________
Paid support
How to ask for help
1. Give details of your problem
2. Post any errors
3. Post relevant logs.
4. Don't hide details i.e. your domain, it just makes things harder
5. Be polite or you'll be eaten by a grue