All geek questions in one place

MySQLDump without locking tables

It seems that if you have many tables, you can only execute MySQLDump without locking them, otherwise you can make a mistake.

What are the side effects of executing MySQLDump without locking all tables; Is a database snapshot so consistent? Do I have another alternative for getting a MySQL database backup with many tables?

+4
source share
4 answers

The best way (when using InnoDB) is to actually run the backup on the replicated slave. Thus, blocking will not make any difference. Else just use the -single-transaction flag as indicated.

+6
source

What storage mechanisms do you use?

If you use InnoDB, you can run mysqldump with the -single-transaction flag and get a consistent snapshot without locking tables.

If you use MyISAM, you need to lock the tables to get a consistent snapshot. Otherwise, any insert / update / delete statements that execute in your MyISAM tables when mysqldump starts may or may not reflect the result, depending on the time of these statements.

+3
source

The --single-transaction flag should work if your database is of type InnoDB.

+2
source

For Innodb, you need to specify --single-transaction in the mysqldump utility to avoid blocking and get a consistent snapshot.

For MyISAM, you need to lock the tables to get a consistent snapshot, otherwise you will skip DML to register while dump is running

0
source

Source: https://habr.com/ru/post/1308648/

More articles:


All Articles