Enterprise Production Environment database backup lock table problem

In MySQL database scenario, when using the mysqldump command backup, we will encounter a lock table problem? If the lock table is made, the user cannot access the number during the backup, if the backup time is a few hours, it means that the user can not access the data within a few hours, it will have a significant impact on the business, if the table is not locked, it will cause the backup data inconsistent, because during the backup process, there may be data write, This does not guarantee that the data in the backup file after backup is the data you want at some point in time.

How do I fix a lock table problem?
When it comes to MySQL backup, you need a lock table, which is analyzed based on your company's business scenario.
Case 1:
A financial consulting company, the company's customers are mainly select, that is, query-oriented, the company's data is almost entirely internal import, and the company database engine is a hybrid engine, MyISAM and InnoDB two kinds of storage engine table.
For this business scenario, you can directly back up the database without locking the table, and then use the Binlog log to ensure data consistency (incremental backup)
Because MySQL default is to turn on the--lock-tables parameter, if you do not need to lock the table, you want to turn off this parameter--skip-lock-tables.
When you explicitly add a table lock to a table by using lock tables, you must also get all the locks that relate to the table, that is, after you perform lock tables, you can only access those tables that are explicitly locked, and you cannot access the unlocked tables, and if you add a read lock, you can only perform query operations on the lock table. MyISAM always gets all the locks required by the SQL statement at once. This is why the MyISAM table does not appear deadlocked (Deadlock free).

#混合引擎和MyIsam引擎mysqldump -uroot -p -A -B -F -R --skip-lock-tables --events|gzip >/tmp/all_$(date +%F).sql.gz

Case 2:
In a business scenario where the user is updating data frequently, using the mysqldump command to back up the MySQL database, you can use the Lock table function to ensure consistency of the data, but this poses a problem in which the user cannot access and update the data properly during the Backup lock table.
For this scenario, it is recommended to back up when the business is down, and it is the so-called black and high killing night.
Another better solution would be to copy from the master and then back up from the vault, which would have a lower impact on the lock table.
Internet users say that using the--lock-tables read-only lock table function (that is, you cannot update the inserted data, only read the data lock table) for backup, and then through the Binlog log to achieve data consistency. This is not a good result for scenarios where there may be a large number of data insertions.
MyISAM Engine Enterprise Production Backup command (for all engines or hybrid engines):
Because the MyISAM engine is a table-level lock, it is necessary to prevent inconsistencies in data writes during backup, so the--lock-all-tables (-X) lock table is used when backing up.

mysqldump -uroot -p -A -B -F -R -x -events|gzip >/tmp/all_$(date +%F).sql.gz

InnoDB engine Enterprise Production backup name:
The InnoDB engine is a row lock, so the backup can not lock the database operation, you can add the option--single-transaction for backup. It has some requirements: only the InnoDB engine; During the export process, no one can execute a DDL statement such as ALTER TABLE, DROP table, rename table, TRUNCATE table. The DDL is actually blocked because the transaction holds the shared lock of the metadata lock on the table, and the DDL requests metadata Lock's mutex, so it is blocked.

mysqldump -uroot -p -A -B -F -R --events --single-transaction|gzip >/tmp/all_$(date +%F).sql.gz

If there is any improper, please advise.

Mysqldump parameter explanation: http://blog.51cto.com/13691477/2149675
MySQL Lock knowledge: 51669741

Enterprise Production Environment database backup lock table problem