I have a 58Gb database that is live, using InnoDB engine and I want to dump restore it to a development machine for test purposes. I'm using mariadb 10.4 client and servers on Debian 11 Buster.
I've read in the mysql and mariadb manuals that I should be using:
mysqldump --single-transaction --skip-lock-tables --flush-privileges --all-databases > alldb.sql
or:
mysqldump --single-transaction --quick --flush-privileges --all-databases > alldb.sql
to correctly avoid locking issues on the tables and to produce a clean reusable snapshot that can be imported using redirection. There are old tickets from 2012 that address this issue and they are using similar suggested solutions to me, but it doesn't work.
On the development machine I run:
mysql -u myuser -p < alldb.sql
I end up with the following error message no matter what combination of recommended mysqldump options I use:
ERROR 1100 (HY000) at line 6169: Table 'myTable' was not locked with LOCK TABLES
I've now found that if I look into the sql file, which incidentally contains 61.6 billion lines, I can see that there is a LOCK on the TABLE for WRITE two lines before the INSERT. Then, there is an unlock after the INSERT. I presume the locks are performed to cleanly dump the MySQL database, but are not actually needed at restore time. I cannot find any explanation for my assumption, anywhere online, so I'm wondering if my assertion is true. If it is true, then there is an easy hack to import any SQL file.
I suppose my question is, if the database is successfully dumped then why can't I run the following to ensure a successful restore (remove any UNLOCK/LOCK with simple grepping):
grep -v "LOCK TABLES" alldb.sql > alldb-no-locks.sql
mysql -u myuser -p < alldb-no-locks.sql
to avoid the locking issue. Is this a legitimate fix that will ensure the restore happens? Or am I restoring a database to the test machine that isn't actually the same as my original?
