MariaDB 10.0 медленная работа mysqldump

Edit:

The issue is confirmed in MariaDB 10.0.8 and 5.5.36, and confirmed not to exist in MySQL 5.5.36 apparently due to implementation differences in the query optimizer.

The text below is therefore adjusted according to the recommendations by Elena Stepanova of MariaDB.

I have reproduced this behaviour in MariaDB 10.0.8 on Debian wheezy. I have not been able to reproduce it in MySQL 5.5.36.

If you execute mysqldump -uroot, or any user with full privileges, this generates a query that causes MariaDB's mysqld to scan the database superdirectory and all subdirectories and all the table metadata for all tables in these subdirectories.

If you execute mysqldump with specific user privileges, and those have been granted for access to only one or two databases, mysqldump is as quick as you would expect it to be.

There are two work-arounds.

Work-around A is still using a super-privileged user with access to all databases, but speeds up the process by disabling the semijoin feature.

Work-around B creates a backup user that is granted access to each database in turn, narrowing the privilege set.

Which method works best for you depends on how comfortable you are with using a super-privileged user to access all databases, how many databases you have, how many users you have that have access to multiple databases, how many databases have dependencies between them, and how your database is being accessed while you make your database dumps.

Work-around A is probably the quickest, work-around B slightly slower.

Work-around A - super-privileged user, disabled semijoin

Please note that disabling semijoin can affect the performance of other queries. While it can be set in my.cnf, you probably shouldn't.

1. Disable semijoin temporarily:

   SET GLOBAL optimizer_switch='semijoin=off';
   

2. Выгрузите базу данных, например, как пользователь "root":

    mysqldump db -uroot -prootpassword | gzip> DB.sql.gz
   

3. Повторно включить полусоединение:

    УСТАНОВИТЬ GLOBAL optimizer_switch = 'semijoin = on';
   

Обход B - отдельный пользователь резервного копирования

1. Предоставьте пользователю резервного копирования полные права доступа к базе данных:

    GRANT USAGE ON `db`. * TO 'backup' @ '%' IDENTIFIED BY 'backuppassword';
   GRANT SELECT, БЛОКИРОВКА ТАБЛИЦ НА `mysql`. * TO 'backup' @ '%';
   GRANT SELECT, LOCK TABLES, SHOW VIEW, EVENT, TRIGGER ON `db`. * TO 'backup' @ '%';
   ПРИВИЛЕГИИ ПРОМЫВКИ;
   

2. Дамп базы данных от имени этого пользователя:

    mysqldump db -ubackup -pbackuppassword | gzip> DB.sql.gz
   

3. Отменить привилегии:

    ОТЗЫВАТЬ ВСЕ ПРИВИЛЕГИИ НА `db`. * FROM 'backup' @ '%';
   ОТЗЫВАТЬ ВСЕ ПРИВИЛЕГИИ НА `mysql`. * FROM 'backup' @ '%';
   ПРИВИЛЕГИИ ПРОМЫВКИ;
   

If each database is owned by a separate user, and you know these users' passwords, you could of course use these instead of the backup user and its password, and thereby skip steps 1 and 3 entirely.

BTW: I also reported this to the MariaDB development team, if you have anything useful to contribute, feel free to follow up on that here:

https://mariadb.atlassian.net/browse/MDEV-5723