Live backup without lock

In our company we have a lot of MySQL databases. Some of these are critical and we want to take a back-up of all databases. These databases can't be locked because the application can change these database at every moment. I want to write a script that makes a dump of the database an store it somewhere on a backupdisk. What is the best way to achieve this without locking databases and get a consistent dump off all databases from a remote server.

Thanks

Easiest option, if you aren't relying on features specific to MyISAM or other storage engines: change your tables to the InnoDB storage engine, and use the --single-transaction option with mysqldump.

This tells mysqldump to perform all the work within a single transaction, causing InnoDB to give it a consistent point-in-time snapshot of the data, without blocking other data access (including writes).

However, MySQL DDL operations are apparently not transactional, and altering table structures during a backup can cause them to be missing from the backup with this option.

http://www.mysqlperformanceblog.com/2012/03/23/best-kept-mysqldump-secret/

StackExchange Database Administrators Q#40784, answer score: 2