Mysql – thesqldump –single-transaction on slave (without stopping replication)

MySQLmysql-replicationmysql5.5

Is it okay to run mysqldump --single-transaction on Slave DB without stopping replication (stop slave;)? This is for regular daily backup so we can restore Master DB in case of a storage disaster. Most of the tables are InnoDB, only a couple of them are MyISAM.

Official documentation says that:

"You should stop replication on the slave before starting the dump
process to ensure that the dump contains a consistent set of data".

What does it mean consistent data? Does it mean like latest data?

I know --single-transaction means that it takes data from the time it was issued, so new further changes while the dump was happening will not be recorded to the dump. Am I correct?

Do not want to run the backup on master due to lag it creates on our application.

Best Answer

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 users table.

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.
  • The 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.

Related Topic