Mysql Backup on all InnoDB tables environment -

at moment doing backup mysqlbackup. call "flush tables read lock" , rise possibility of locking tables until flush done , global lock released.

so, there anyway backup database, big, there tables more 100 gb, without global lock ?

something mysqldump --single-transaction, read useful on small dbs, not case. there sometinh similar appliable case ?

hope !

percona xtrabackup excellent option when have tables large. due following 2 points:

a) time taken restore 100gb table logical backup (ie: created mysqldump) excessive, since mysql has rebuild indexes , lot of i/o processing sql replayed in.

b) xtrabackup can take incremental backups after have made first full backup, making routine nightlies or hourlies quicker , smaller in terms of disk space.

that said, aware because xtrabackup copies relevant files in /lib/mysql directory, diskspace requirements significant. if have database has 200gb allocated on disk (even if there empty allocated space on tables), backup 200gb in size.

this compared mysqldump's logical backup (sql statements can recreate data, table structures, indexes etc), take tiny fraction of space.

read more how percona xtrabackup works here: https://www.percona.com/doc/percona-xtrabackup/latest/how_xtrabackup_works.html

mysqldump used innodb tables , combination of --master-data , --single-transaction option option consider. acquires global read lock on tables, long enough read binary log coordinates. typically quick, unless there significant updates in progress, in case stall until updates complete.

this gives consistent point-in-time backup, , there no locking beyond initial read lock.

here syntax:

  mysqldump --all-databases --master-data --single-transaction > all_databases.sql 

or

  mysqldump --master-data --single-transaction my-database > my-database.sql 

to single database.

more information can read on dev.mysql.com site here: https://dev.mysql.com/doc/refman/5.7/en/mysqldump.html#option_mysqldump_single-transaction