TL;DR - You don't actually need to issue a
stop slave when working with a backup on a replica. If you run
mysqldump using the defaults and leave out
--single-transaction, a locking backup will be used by default which will automatically block any changes to the database during the backup; keeping your data consistent. This usually means delaying replication, but it will resume on its own at the completion of the backup.
MySQL is a relational database, this means that data in one table may have a relationship with data contained in another table. For example, if you are taking online orders, you may have a
line_items table where one or more rows belongs to a record of the
orders table, which in turn may have one or more rows belonging to a record of the
MyISAM is a non-transactional storage engine, which means its data will be allowed to change during the backup regardless of the
--single-transaction option. Taking the example from before, let's pretend that
line_items is MyISAM. You start your backup and one of your users submits an order while the backup is running.
update users set last_ordered_at=now() where id=306;
insert into orders (user_id, created_at, ...) values (306, now(), ...);
insert into line_items (order_id, product_id, ...) values (1021992, 10509, ...);
If the backup hasn't read the
line_items table before this runs and you perform a restore the result is:
- The maximum id of the orders table resets to a value lower than
1021992 so the line_items record is, for the moment orphaned.
last_ordered_at change is lost
- When additional orders are added, and the order_id gets to
1021992 that order will magically get this line_item record added to it (unless there are additional filters at that level). This issue would be seen with any
line_items added prior to the backup reaching that table.
- Cats and Dogs living together... mass hysteria.
If your MyISAM data is static/unchanging, then using
--single-transaction won't be a problem and your data will remain consistent. Keep in mind though, just because it is static now, doesn't mean that it always will be.
If your MyISAM data relates exclusively to the other MyISAM data, but not to the transactional data, you could move the MyISAM data to a separate schema so you can treat it differently during backup than the transactional data.
As of MySQL 5.6, InnoDB supports full-text indexes, so there really isn't much remaining use for MyISAM tables. You might consider converting these to InnoDB with a statement like
alter table [tablename] engine=innodb;. As with any change, you should evaluate this in Development/UAT before proceeding to Production. To create all the alter statements at once you could use:
select concat('alter table ', table_schema, '.', table_name, ' engine=innodb;') from information_schema.tables where engine='MyISAM' and table_schema not in ('mysql','information_schema','performance_schema');
InnoDB uses about 2-3x as much physical space as MyISAM, and these tables will be locked against writes when they are modified; you'll want to plan your conversion accordingly. When complete, you'll probably want to review your server configuration and tune it more towards InnoDB.
The one caveat to using 100% transactional tables with
--single-transaction is that if you run DDL (CREATE, DROP, ALTER) during the backup these are not transactional statements and usually will result in the backup run failing. This is usually an edge case that can be addressed with backup monitoring.