0

I recently developed a script that spawns multiple processes to import tables in parallel using mysqlimport and a --tab type mysqldump export. On the development server it works very well and compared to a standard mysql db_name < backup.sql type of import it cuts the time from around 15 minutes to 4 or 5 minutes.

The problem is on our production server this script seems to be locking tables system wide. That is to say, I'm importing a backup to a completely different database but our live application tables still end up locked. A SHOW PROCESSLIST confirms that tables on our live db are indeed locked but no INSERT or UPDATE queries are running on any tables in that database.

Why is this happening? Is there a configuration variable / setting that I can adjust to prevent this lock from happening?

  • Are the dev copies on the same mysql instance? Otherwise, I wonder why the import side matters. – Rick James Feb 12 at 4:38
  • @RickJames - i don't exactly understand what you mean but yes same server, same instance, different database. – billynoah Feb 12 at 5:20
  • One computer normally has one copy of mysqld running -- that is one "instance of MySQL" on one "server. A single computer can have multiple copies of mysqld running, either on different ports on with the help of VMs. One instance can have multiple "databases", as created with CREATE DATABASE. The term "database" is sometimes used to refer to the computer. I want you to nail down the terminology we address the question (and potentially give you a 'wrong' answer). – Rick James Feb 12 at 15:23
  • Ok, to clarify we are talking about one physical server (running Ubuntu 16.04), one instance of mysqld (v5.7.25) and multiple databases. So in this case the issue is that doing mysqlimport into one database is locking another database. As far as I can tell this is explained in the docs and as danblack ponted out the default is to lock all tables, regardless of which database is being imported into. That said, I'm still open to more insight on the mechanics of what's happening here. – billynoah Feb 12 at 17:43
3

If you start mysqlimport with --lock-tables=0 then there will be no locks.

mysqlimport uses LOAD DATA rather than INSERT/UPDATE.

  • so was an assumption, --lock-tables=0 worked. mysqlimport --help lists it as a boolean. Looking at the code it appears to be the default. – danblack Feb 11 at 23:30
  • That worked. I guess they aren't kidding when it says "Lock all tables for writing before processing any text files". I searched forever for a reference on this and came up with nothing - really appreciate the tip. – billynoah Feb 11 at 23:45
-1

(The Question is about mysqlimport, and has been answered. This 'Answer' is about the underlying goal, which I will restate.)

How to rapidly clone a single database (to a database with another name) on a single instance of MySQL?

One thought is have dev_db created and empty, then do something like

mysqldump  prod_db  |  mysql  dev_db

Caveats:

  • Make sure it does (or does not do) CREATEs and DROPs and "locks".
  • For copying two databases, consider running them in parallel.
  • For multiple tables, consider splitting up the tables across different dump|load pairs.
  • But watch out for FOREIGN KEY issues.

By using a pipe, the intermediate file does not need to touch the disk (at least not for *nix OS.)

  • Thanks Rick, but that's actually not what my question is about. The script I have currently does a great job and as mentioned in my question is approx 3 - 4x faster than mysqldump prod_db | mysql dev_db. The question is about why a table lock is happening on a table outside of the database being imported into. Sorry If I wasn't clear enough but I think this answer is off topic. – billynoah Feb 12 at 20:16
  • @billynoah - And that's why I prefaced it with the parenthesized paragraph. – Rick James Feb 12 at 21:11

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.