8

MyDumper 0.6.1 add a new option --use-savepoints. From the manual, it means:

Use savepoints to reduce metadata locking issues, needs SUPER privilege

I don't understand. How does it "reduce metadata locking issues" and why does it need "SUPER privilege"? As I think, metadata is essential to prevent other DDL modifying table structure.

2

I had to do a little digging in the source code of mydumper to find the answer to this question. If you look at the actual source code of mydumper:

You will find the following starting on line 415 as part of the process queue:

if(use_savepoints && mysql_query(thrconn, "SET SQL_LOG_BIN = 0")){
    g_critical("Failed to disable binlog for the thread: %s",mysql_error(thrconn));
    exit(EXIT_FAILURE);
}

This shows using --use-savepoints in mydumper requires the ability to turn off the MySQL bin log. I attempted this on my own MariaDB server which still uses MySQL as the base DBMS and got the following error when using a non administrator account:

MariaDB [(none)]> SET SQL_LOG_BIN = 0; ERROR 1227 (42000): Access denied; you need (at least one of) the SUPER privilege(s) for this operation

From what I was reading of the actual code and testing this condition in my own MySQL server my understanding is you need "SUPER privilege" as mydumper will be disabling logging to the binlog while it runs. This is part of "the enabling or disabling logging" power of a SUPER mentioned in the DOCS.

More specific info on the binlog is here:

http://dev.mysql.com/doc/refman/5.6/en/set-sql-log-bin.html

As far as savepoints go:

http://dev.mysql.com/doc/refman/5.6/en/savepoint.html

After reading through the manual and this bug report it looks like if savepoints are released they will release locks on the table being worked on which can prevent locking issues which have been seen on mysqldump as recently as MySQL 5.5.

I hope this gives a little more insight into the mydumper tool.

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.