Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
  1. How mysqldump is processing backup?

  2. From which all files is it reads data for creating dump?

  3. Is the process differs if storage engines are different?

share|improve this question
add comment

1 Answer 1

up vote 3 down vote accepted

mysqldump is a client application. It connects to the MySQL server using "normal" credentials and queries for data.

It has no immediate access to the data other than what it can ask of the server. The mysqldump binary doesn't have to run on the same host as the MySQL server. Hence it is obvious it cannot access files directly.

It is instructive to turn the general log on for the duration of the dump to understand how it works.

For example, you might see in the general log queries such as:

                   29 Query     SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA
            WHERE SCHEMA_NAME = 'test'
                   29 Query     COMMIT
                   29 Query     SHOW CREATE TABLE `test`.`t1`
                   29 Query     COMMIT
                   29 Query     SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA
            WHERE SCHEMA_NAME = 'test'
                   29 Query     COMMIT
                   29 Query     SHOW CREATE TABLE `test`.`t2`
                   29 Query     COMMIT
                   29 Query     SELECT TABLE_SCHEMA, TABLE_NAME, ENGINE, AUTO_INCREMENT, AVG_ROW_LENGTH, CHECKSUM, TABLE_COLLATION, TABLE_COMMENT, ROW_FORMAT, CREATE_OPTIONS
  FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME = 't1'
                   29 Query     COMMIT

As you can see, mysqldump interrogates the schema via IFNORMATION_SCHEMA. It later follows to get all data via standard SELECT:

                6 Query     SELECT /*!40001 SQL_NO_CACHE */ * FROM `t1`

So, mysqldump's power is by correctly iterating all objects and querying for data, then producing the SQL statements to generate those.

Since mysqldump accesses as any normal client (seen by the above SELECT), it does not care about the storage engine, other than caring about what to export as table's engine. It is up to the MySQL server to access the engine (via storage engine API) and fetch the data.

share|improve this answer
    
Yea. But data should reside somewhere physically, right? From which file mysqldump utility gets (select) data from the server, particularly for innodb? –  Praveen Prasannan Sep 30 '13 at 4:03
    
It gets the data from the MySQL server. The MySQL server is in charge of data, including all storage engines, which, by themselves, are in charge of storing the data in files. So what exactly is the question? Are you interested in which particular files contain which data? –  Shlomi Noach Oct 1 '13 at 6:35
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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