Problem description
Recently, when backing up MySQL data, an error was found in dump database:
mysqldump: Got error: 1556: "You can't use locks with log tables." when using LOCK TABLES
Solution steps:
Checked because there are two table locks in the dump MySQL library.
Add --lock-tables =0
after mysqldump instruction.
Summary
Mysqldump backup will turn on the lock table by default – opt is turned on by default, which will affect the online business when backing up big data
To prevent this, we can use --skip-opt
to cancel this default option in backup After adding this parameter, the above error report will not appear.
mysqldump --skip-opt -h<host> -u<user> -p<password> -A >> backup.sql --Perform full backups
Read More:
- [Solved] mysqldump Command Error: Got error: 2002: “Can‘t connect to server on ‘127.0.0.1‘ (36)“ when trying to connect
- Mysqldump: Got error: 1045: Access denied for user ‘root‘@‘localhost‘
- [Solved] mysqldump: Error: ‘Access denied; you need (at least one of) the PROCESS privilege(s) for this opera
- [Solved] MySQL Lock error: Lock wait timeout exceeded; try restarting transaction ; Lock wait timeout
- MYSQL Enter password:ERROR 2003 (HY000): Can‘t connect to MySQL server on ‘localhost:3306‘
- [Solved] MySQL Error: Can‘t find record in ‘order_form‘
- [Solved] Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: ‘event read from binlog did not pass crc check; the first event
- [Solved] Worker 1 failed executing transaction ‘ANONYMOUS‘ at master log mall-mysql-bin.000001, end_log_pos
- mysqldump: Error: Binlogging on server not active [How to Solve]
- [Solved] MYSQL Command Execute Error: Can ‘t connect to local MySQL server through socket ‘/tmp/mysql.sock ‘(2) “
- MYSQL Error: Can‘t find error-message file [How to Solve]
- MySql Install Error: Can‘t connect to local MySQL server through socket ‘/tmp/mysql.sock‘
- MYSQL Use cmd to change root password error: ERROR 1064 (42000): You have an error in your SQL syntax; check the manual tha
- How to Solve can‘t connect to local mysql server through socket ‘/var/lib/mysql/mysql.sock‘
- MYSQL8 Startup Error: mysqld_safe error: log-error set to ‘/var/log/mariadb/mariadb.log‘
- RECEIVED ERROR PACKET: ERRNO = 1236, SQLSTATE = HY000 ERRMSG = COULD NOT FIND FIRST LOG FILE NAME IN BINARY LOG INDEX FILE
- [Solved] Centons7 docker:mysql:5.7 [ERROR] InnoDB: Unable to lock ./ibdata1 error: 11
- [Solved] Canal Error: Could not find first log file name in binary log index file
- Centos7 Initialize mysql Error: Could not open file ‘/usr/local/mysql/log/mysqld.log‘ for error logging: Per
- How to release Oracle PLSQL data lock table