I want to dump and setup for replication 4 databases roughly each about 3gb of size. The problem is that my tables are mixed and I need database tables to be locked as short period as possible.(Production Server)
Is there any chance that I can dump data for InnoDB sith --skip-lock-tables and --single transaction and MyISAM will be locked? I was thinking maybe to dump all InnoDB at once, setup master-slave replication and dump and load InnoDB separatelly while GTID replication is running. WHould be that an option?
Also does anybody know how exactly locking tables works are all locked for the period of taking dump or it is possible to lock them one-by one? I mean does mysqldump copy tables in parallel or serial fashion?
Thank You
Well I setup dump script with --skip-lock-tables- and --single-transaction falag how would that affected mixed DB engine?
The messy and slow locking is to maintain consistency if you are writing to all the tables.
That is, if a change (Insert/Update/Delete) in one table needs some change in another, you need the locking. If, for example, the MyISAM tables are read-only (never changed, or at least not while doing the dump), then you can probably pretend that all the tables are InnoDB.
For further discussion, we need to know what activity might be occurring during the dump.