What program to use for large MySQL backups without a database lock?

Question

MySQL

There is a mysql + nginx web server for statistics. Once a day, Mysqldump with a database volume > 1 GB tightly locks the server for 5-10 minutes, which is rather unpleasant. The option with a simple dump & restore will not work due to the presence of innodb tables, what other alternatives are there?

Separately copying by tables is not interesting, because main statistics table >50% of the base volume. Do not offer a dedicated / duplicate server for the base, there is only one machine.

Answer 1

in general, the standard remedy in such cases is to raise the slave and do whatever is necessary with it

Answer 2

mysqlhotcopy
Maatkit (Percona toolkit)
The mk-parallel-dump utility takes a snapshot (dump) of tables and databases in parallel. Used in tandem with mk-parallel-restore, it loads previously made backup dumps. The main distinguishing feature of these utilities from regular MySQL tools and methods is the use of parallel database access methods, which significantly increases the speed of the operation.
And, finally, a more complex and selective backup option is mk-archiver - this utility unloads records selected by a given criterion from one MySQL table to another, the same (or any other) MySQL server, or to a specified file. The utility is designed taking into account the very simple possibility of its extension (connecting your own post-processes and pre-filters) and adding your own data saving logic.

Answer 3

look also at Percona XtraBackup, it might come in handy

Answer 4

You can solve the problem at the file system level using snapshots
en.wikipedia.org/wiki/Snapshot_%28computer_storage%29#File_systems
en.wikipedia.org/wiki/Shadow_Copy
The copy-on-write principle: when you make a copy of a file, it refers to the same blocks as the original, but if any block of the file starts to be modified, this block is actually copied.

Answer 5

If lvm is used on the server, then lock transactions on the database server, check that current transactions have completed, make an lv snapshot, remove the lock, transfer data to where you need to compress them in parallel and do not forget to clean up snapshots so that io does not sag in the end.
xtrabackup if you start using it, then immediately switch to the Percon stack, but you need to take into account all the features of the fork in the form of buns - increments, faster backup / rollback

Answer 6

It's strange ... I have a 1.5 giga base, also statistics - the main thing is there (bitrix). BUT after switching to innodb mysqldump stopped hanging the site.

Answer 7

the easiest way is to replicate the master master, you can also master the slave, and only remove dpm from the second master / slave =)

Answer 8

Percona Toolkit provides the xtrabackup utility that works with InnoDB tables without locking the tables. I highly recommend.

Percona XtraBackup is an open source, free MySQL® hot backup software that performs non-blocking backups for InnoDB and XtraDB databases. With Percona XtraBackup, you can achieve the following benefits:
Backups that complete quickly and reliably
Uninterrupted transaction processing during backups
Savings on disk space and network bandwidth
Automatic backup verification
Higher uptime due to faster restore time