sviko.com

How to do mysqldump on production state

Mysqldump remains one of the popular backup solutions. If at this stage of development of the project more advanced means of creation of backups are not used, it is necessary to use mysqldump with the correct parameters.

The most critical impact mysqldump has in production is table locking:

root@webminer-manager-1:~# mysql -e "show processlist"
+-------+------+-----------------+-----------+---------+------+---------------------------------+------------------------------+
| Id    | User | Host            | db | Command | Time | State                           | Info                                |
+-------+------+-----------------+-----------+---------+------+---------------------------------+------------------------------+
|   416 | user | localhost:52102 | db | Query   |  632 | Waiting for table metadata lock | INSERT INTO products SET url = '... |
|   417 | user | localhost:52104 | db | Query   |  633 | Waiting for table metadata lock | INSERT INTO products SET url = '... |
|   418 | user | localhost:52106 | db | Query   |  633 | Waiting for table metadata lock | INSERT INTO products SET url = '... |
...

lock tables during the execution of the dump

In this case, any requests to change the data will wait for a full dump to be executed. This means that your application will almost stop working for users. You can disable table locking by using the following options:

-l, --lock-tables   Lock all tables for read.
                  (Defaults to on; use --skip-lock-tables to disable.)

–single-transaction
Creates a consistent snapshot by dumping all tables in a
single transaction. Works ONLY for tables stored in
storage engines which support multiversioning (currently
only InnoDB does); the dump is NOT guaranteed to be
consistent for other storage engines. While a
–single-transaction dump is in process, to ensure a
valid dump file (correct table contents and binary log
position), no other connection should use the following
statements: ALTER TABLE, DROP TABLE, RENAME TABLE,
TRUNCATE TABLE, as consistent snapshot is not isolated
from them. Option automatically turns off --lock-tables.

disable lock during dump

The second option will automatically disable the first for engines that support versioning (for example, InnoDB). But if you use different engines, it is worth using both options.

In addition, it is useful to enable the --insert-ignore option to avoid errors during subsequent recovery (for example, if the keys have changed).

And of course, it is worth using streaming compression with gzip to save disk subsystem resources. The-v option is also useful when dumping multiple tables-displays system information about the progress of the dump.

The final dump command to execute in production might look like this:

mysqldump -v --insert-ignore --skip-lock-tables --single-transaction=TRUE db products | gzip > /root/products.sql.gz

This dump will not lock tables and will minimize the impact on the main application:

mysql> show processlist;
+-------+------+-----------------+----+---------+------+-------------------+------------------------------------------------------------------------------------------------------+
| Id    | User | Host            | db | Command | Time | State             | Info                                                                                                 |
+-------+------+-----------------+----+---------+------+-------------------+------------------------------------------------------------------------------------------------------+
| 15655 | user | localhost:39430 | db | Query   |    0 | query end         | INSERT INTO products SET url = '... 
| 15656 | user | localhost:39450 | db | Sleep   |  614 |                   | NULL                                                                                                 |
| 15661 | root | localhost       | db | Query   |  589 | Sending to client | SELECT /*!40001 SQL_NO_CACHE */ * FROM `products`
| 15682 | user | localhost:39504 | db | Sleep   |    0 |                   | NULL                                                                                           
...

Mysqldump can be used in production, but you should disable table locking.