I mean when backup command is issued to database, does backup process read all data pages from disk or use in memory pages?

  • 1
    Interesting question - AFAIK, it chugs through the database table by table and outputs a text file. It's not even guaranteed to be consistent (i.e. do not do!) if you're making a hot backup! I doubt very much if it keeps much in memory - why would it? How would it deal with a table bigger than RAM? If you want a hot backup, use Percona's XtraBackup! – Vérace Nov 3 '17 at 21:32
  • @Vérace it's guaranteed consistent if you use InnoDB tables and --single-transaction. – Michael - sqlbot Nov 4 '17 at 20:05
  • @Michael-sqlbot - maybe I haven't understood very well, but it appears that RolandoMySQLDBA is saying that if you attempt an insert into a table being dumped after the dump has started, then that insert will block until the dump (of that table) is complete - am I wrong? – Vérace Nov 4 '17 at 20:23
  • @Vérace... link? – Michael - sqlbot Nov 4 '17 at 20:29
  • Sorry - you're in the thread - assumed you'd know - silly me! It's here! – Vérace Nov 4 '17 at 20:53

By "in memory", I assume you are referring to cached data and index blocks.

mysqldump issues a bunch of SELECTs, which use the caching that is available to any other SELECTs. Since it does not dump the indexes, most cached index blocks do not help the the dump process.

Xtrabackup has some extra hooks into InnoDB to help achieve a 'consistent' dump in a less intrusive way. But still the caching is in effect.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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