Mysql – Where Mysqldump gets data from

MySQLmysqldumpstorage-engine

How mysqldump is processing backup?
From which all files is it reads data for creating dump?
Is the process differs if storage engines are different?

Best Answer

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.

Related Solutions

How to Set Up a MySQL Slave Using Mysqldump

If you are dumping a mysql database that has a mixture of InnoDB and MyISAM and you have scheduled downtime:

mysqldump -u... -p... --master-data=1 --single-transaction --flush-privileges --routines --triggers --all-databases > /root/mydata.sql

Since --opt is enabled by default, the following options are already enabled

--add-drop-table
--add-locks
--create-options
--quick
--lock-tables
--set-charset
--disable-keys

If you are concerned about InnoDB/MyISAM together in the mysqldump and you want the database placed in a read-only state, try putting a read lock across all tables manually.

MYSQL_CONN="-hhostip -uusername -ppassword"
mysql ${MYSQL_CONN} -A -e"FLUSH TABLES WITH READ LOCK; SELECT SLEEP(86400)" &
SEARCHING_FOR_SLEEP=0
while [ ${SEARCHING_FOR_SLEEP} -eq 0 ]
do
    sleep 3
    SEARCHING_FOR_SLEEP=`${MYSQL} ${MYSQL_CONN} -A -e"SHOW PROCESSLIST;" | grep -c "SELECT SLEEP(86400)"`
done
sleep 1
SLEEP_ID=`mysql ${MYSQL_CONN} -A -e"SHOW PROCESSLIST;" | grep "SELECT SLEEP(86400)" | awk '{print $1}'`

mysqldump ${MYSQL_CONN} --master-data=2 --single-transaction --flush-privileges --routines --triggers --all-databases > /root/mydata.sql

mysql ${MYSQL_CONN} -A -e"KILL ${SLEEP_ID}"

The reason this is better to do is that --single-transaction does not protect MyISAM from changing during the dump.

Before launching the mysqldump, make sure binary logging is enabled on the master. If it is not, do the following:

Step 01) Add this to /etc/my.cnf on the master

[mysqld]
log-bin=mysql-bin

Step 02) service mysql restart

This will enable binary logging on the master.

On the new slave, you can run the following command:

CHANGE MASTER TO
MASTER_HOST='IP of the master',
MASTER_PORT=3306,
MASTER_USER='whatever_username',
MASTER_PASSWORD='whatever_password',
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=107;

Make sure the rep_username exists in the master. If it does not, run this command on the master:

GRANT SELECT, REPLICATION SLAVE, REPLICATION CLIENT
ON *.* TO 'whatever_username'@'%' IDENTIFIED BY 'whatever_password';

Then do the mysqldump to /root/mydata.sql.

Move the /root/mydata.sql from the master to the slave.

Next, execute in the mysql client this command on the slave:

source /root/mydata.sql

This will load mysqldump in the slave and the set the correct log file and log position in /var/lib/mysql/master.info.

Finally, run START SLAVE; on the slave and mysql replication should get going.

Run SHOW SLAVE STATUS\G to check replication status.

Give it a Try !!!

Mysql – slow load speed of data from thesqldump

Take these some points in your consideration they may help you in case of generating the dump and restoring it.

Use Extended inserts in dumps.
Dump with --tab format so you can use mysqlimport, which is faster than mysql < dumpfile.
Import with multiple threads, one for each table.
Use a different database engine if possible. importing into a heavily transactional engine like innodb is awfully slow. Inserting into a non-transactional engine like MyISAM is much much faster.
Turn off foreign key checks and turn on auto-commit.
If you are importing to innodb the single most effective thing you can do is to put innodb_flush_log_at_trx_commit = 2 in your my.cnf, temporarily while the import is running. you can put it back to 1 if you need ACID

Give it a try..

Best Answer

Related Solutions

How to Set Up a MySQL Slave Using Mysqldump

Mysql – slow load speed of data from thesqldump

Related Question