A Little Noise

mysqldump privileges required

Written by

snoyes

in

“mysqldump requires at least the SELECT privilege for dumped tables, SHOW VIEW for dumped views, TRIGGER for dumped triggers, and LOCK TABLES if the –single-transaction option is not used. Certain options might require other privileges as noted in the option descriptions.”
– http://dev.mysql.com/doc/refman/5.6/en/mysqldump.html

Format Default Privileges Required
–add-drop-database Off  
–add-drop-table On in –opt  
–add-drop-trigger Off  
–add-locks On in –opt  
–all-databases Off SELECT, SHOW DATABASES ON *.*
–allow-keywords Off  
–apply-slave-statements Off  
–bind-address=ip_address Off  
–comments On  
–compact Off  
–compatible=name[,name,…] Off  
–complete-insert Off  
–create-options On in –opt  
–databases Off  
–debug[=debug_options] Off  
–debug-check Off  
–debug-info Off  
–default-auth=plugin Off  
–default-character-set=charset_name utf8/latin1  
–delayed-insert Off  
–delete-master-logs Off SUPER ON *.*
–disable-keys On in –opt  
–dump-date On in –comments  
–dump-slave[=value] Off SUPER or REPLICATION CLIENT ON *.*
–events Off EVENT
–extended-insert On in –opt  
–fields-enclosed-by=string ” in –tab  
–fields-escaped-by ‘\\’ in –tab  
–fields-optionally-enclosed-by=string Off  
–fields-terminated-by=string ‘\t’ in –tab  
–flush-logs Off RELOAD ON *.*
–flush-privileges Off  
–help Off  
–hex-blob Off  
–host localhost  
–ignore-table=db_name.tbl_name Off  
–include-master-host-port Off  
–insert-ignore Off  
–lines-terminated-by=string ‘\n’ in –tab  
–lock-all-tables Off LOCK TABLES ON *.*
–lock-tables On in –opt LOCK TABLES
–log-error=file_name Off  
–login-path=name Off controlled at OS level
–master-data Off RELOAD ON *.*
SUPER or REPLICATION CLIENT ON *.*
–max_allowed_packet=value 24MB  
–net_buffer_length=value 1022KB  
–no-autocommit Off  
–no-create-db Off  
–no-create-info Off  
–no-data Off  
–no-set-names Off  
–no-tablespaces Off  
–opt On  
–order-by-primary Off  
–password[=password] Off  
–pipe Off  
–plugin-dir=path Off  
–port=port_num 3306  
–quick On in –opt  
–quote-names On  
–replace Off  
–result-file=file Off  
–routines Off SELECT ON mysql.proc
–set-charset On  
–set-gtid-purged=value Auto  
–single-transaction Off  
–skip-add-drop-table Off in –opt  
–skip-add-locks Off in –opt  
–skip-comments Off  
–skip-compact On  
–skip-disable-keys Off in –opt  
–skip-extended-insert Off in –opt  
–skip-opt Off  
–skip-quick Off in –opt  
–skip-quote-names Off  
–skip-set-charset Off  
–skip-triggers Off  
–skip-tz-utc Off  
–ssl-ca=file_name Off  
–ssl-capath=dir_name Off  
–ssl-cert=file_name Off  
–ssl-cipher=cipher_list Off  
–ssl-crl=file_name Off  
–ssl-crlpath=dir_name Off  
–ssl-key=file_name Off  
–ssl-verify-server-cert Off  
–tab=path Off  
–tables Off  
–triggers On TRIGGER since 5.1.23, SUPER ON *.* earlier
–tz-utc On  
–user=user_name system user on Linux, ‘ODBC’ on Windows  
–verbose Off  
–version Off  
–where=’where_condition’ Off  
–xml Off  

Comments

3 responses to “mysqldump privileges required”

  1. Trig Avatar
    Trig

    The chart says –triggers (default on) requires “SUPER ON *.* or TRIGGER” but this is ambiguous. In 5.0, you need SUPER. In later releases, triggers are silently skipped if you lack TRIGGER

  2. snoyes Avatar
    snoyes

    Thanks Trig, corrected.

  3. sathorn Avatar
    sathorn

    Thanks. I was having problem with mysqldump.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

More posts