How to quickly make a backup?

Question

MySQL

Hello!
Every day at 4 am I make a backup of the database. The total size of all tables is approximately 100 Gb.
I wrote a bash script that cycles through all the tables in the database, checks them, dumps them and archives them.

/usr/bin/mysqlcheck $DB $TABLE -u$USER -p$PASSWORD -o
/usr/bin/mysqldump --opt --default-character-set=cp1251 -u$USER -p$PASSWORD $DB $TABLE > $ARCHIVE/$DB/$DATE/$TABLE/$TABLE.sql
/usr/bin/pbzip2 $ARCHIVE/$DB/$DATE/$TABLE/$TABLE.sql

The problem is that at the time of checking and dumping the table, it is blocked for reading and writing, as a result of which the site becomes unavailable. And given the size of the database, the site becomes unavailable to users from 4 to 5 in the morning.
Please tell me, is it possible to solve the backup problem and make the site available 24 hours? Thank you.
MyISAM table engine.

Answer 1

If your tables are in InnoDB, use the --single-transaction
For transactional tables such as InnoDB and BDB, --single-transaction is
a much better option, because it does not need to lock the tables at
all.

Answer 2

Put the database files on a file system with snapshots, such as btrfs or on lvm+ext3/4. Next, we stop the base, create a snapshot, raise the base, for about 5 seconds. After that, we mount the snapshot and quietly back up the files themselves for at least 10 hours, at the end of the process, delete the snapshot. And, in fact, it doesn't matter what type of tables you have.
I found a similar one - habrahabr.ru/post/63394

Answer 3

Percona, as always, will save everyone =)
www.percona.com/software/percona-xtrabackup
Both on innodb and on myisam, the table locking time for writing / reading will be minimal.

Answer 4

Change table type to InnoDB and use innobackupex:
www.percona.com/doc/percona-xtrabackup/2.2/howtos/...