Using mysqldump to Download Databases via SSH
Updated last month
To run mysqldump from the terminal and download a backup of one of our databases, we must use the --no-tablespaces option.
For example:
mysqldump --no-tablespaces -u dbuser -p dbname > backup.sql
Replacing dbuser with the MySQL user that has permission to access the database, and dbname with the name of our database.
Why we must use no-tablespaces
If we run the command without no-tablespaces, we will get the following error:
mysqldump: Error: 'Access denied; you need (at least one of) the PROCESS privilege(s) for this operation' when trying to dump tablespaces
The PROCESS privilege was removed from our hosting plans for security reasons.
In MySQL and MariaDB, PROCESS is a global privilege rather than one isolated to a single database. If this privilege is allowed, as described in this article from cPanel, the user will be able to view processes across the entire server by using commands such as SHOW PROCESSLIST.
This could put the data of other customers on the same server at risk if a database query contains passwords in plain text, APIs, or any other sensitive data.
Tablespaces is used in advanced InnoDB configurations, which are not necessary for downloading typical WordPress, Drupal, or a