Last call to make your voice heard! Our 2022 Developer Survey closes in less than a week. Take survey.
199 votes

'Access denied; you need (at least one of) the PROCESS privilege(s) for this operation' when trying to dump tablespaces

use --no-tablespaces see mysqldump-Documentation mysqldump requires at least the SELECT privilege for dumped tables, SHOW VIEW for dumped views, TRIGGER for dumped triggers, LOCK TABLES if the --...
user avatar
  • 2,091
142 votes
Accepted

MySQL any way to import a huge (32 GB) sql dump faster?

Percona's Vadim Tkachenko made this fine Pictorial Representation of InnoDB You definitely need to change the following innodb_buffer_pool_size = 4G innodb_log_buffer_size = 256M ...
user avatar
87 votes

'Access denied; you need (at least one of) the PROCESS privilege(s) for this operation' when trying to dump tablespaces

tl;dr Breaking change introduced in minor MySQL update, use --no-tablespaces option in mysqldump from now on (recommended) or add the global PROCESS privilege to the user running the command. I ...
user avatar
64 votes

Cannot locally import UTF-8 encoded SQL database on Mac

Based on this blog post http://ilikekillnerds.com/2014/08/fixing-sequel-pro-sql-encoding-error-for-imported-sql-files/ When importing your database dump, you need to select Western (Mac OS Roman) ...
user avatar
  • 741
55 votes

mysqldump: Got error: 1044: Access denied for user 'username'@'localhost' to database 'databasename' when using LOCK TABLES

I found two possible solutions, either: your user is missing the LOCK privilege, so you should ask your database administrator to grant it to you run the same mysqldump command, simply adding the --...
user avatar
43 votes
Accepted

How can I mysqldump all databases except the mysql schema?

You need to collect all the database names into a space delimited list. Use that for mysqldump MYSQL_USER=root MYSQL_PASS=rootpassword MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}" # # Collect all ...
user avatar
35 votes
Accepted

What are the optimal mysqldump settings?

You are going to find this shocking, but you only need one major option : --opt What is --opt ? This option, enabled by default, is shorthand for the combination of --add-drop-table --add-locks --...
user avatar
27 votes

mysqldump vs mysqlpump

Since I happened to be researching this myself, here's a summary of what I found. According to a 2015 blog post from the MySQL dev team, the main advantages of mysqlpump are that it can use multiple ...
user avatar
25 votes
Accepted

mysqldump doesn't restore database. why not?

You have to use the mysql client to reload mysql -u root -p -Ddatabasename < /home/databasename_bkup.sql Another way to reload would be mysql -u root -p -Ddatabasename then from the MySQL ...
user avatar
21 votes
Accepted

How to dump whole database from remote server on sequel pro

You can dump database with SequelPro (Mac OSX) with File > Export (⇧⌘E) option.
user avatar
  • 489
19 votes

mysqldump vs mysqlpump

I have also been looking into the differences between these two utilities, and in addition to what is mentioned in @Neil's answer, it seems that mysqlpump also has fewer options than mysqldump, such ...
user avatar
18 votes

Any option for mysqldump to ignore databases for backup?

Use grep to exclude databases you don't want: candidates=$(echo "show databases" | mysql | grep -Ev "^(Database|mysql|performance_schema|information_schema)$") mysqldump --databases $candidates ...
user avatar
16 votes

MySQL any way to import a huge (32 GB) sql dump faster?

Do you really need the entire database to be restored? If you don't, my 2c: You can extract specific tables to do your restore on "chunks". Something like this: zcat your-dump.gz.sql | sed -n -e '/...
user avatar
15 votes

How can I monitor the progress of an import of a large .sql file?

Every 2 seconds you will see the processes running. watch 'echo "show processlist;" | mysql -uuser -ppassword'; If you want it less frequent then add -n x where x is the number of seconds. 5 seconds ...
user avatar
15 votes

How can I monitor the progress of an import of a large .sql file?

If you just want to check if it is stalled you can query show processlist; and see what is being executed.
user avatar
  • 251
15 votes

mysqldump Error 2013

I could easily suggest changing InnoDB settings which might be a littel heavy-handed just to get a mysqldump to work. You may not like what I am about the suggest, but I believe it's your best (only) ...
user avatar
12 votes
Accepted

MySqlDump's DISABLE KEYS have no effect on import

You cannot rely on DISABLE KEYS; and ENABLE KEYS; for InnoDB because it is not implemented in the InnoDB Storage Engine. Running ALTER TABLE ... DISABLE KEYS; and ALTER TABLE ... ENABLE KEYS; were ...
user avatar
12 votes
Accepted

Is it possible to gain the precise position of a live mysql database when performing full backup

When you are using mysqldump, you can get the binary logs coordinates of the backup at the point-in-time of the the mysqldump's start. Just add the --master-data option mysqldump --master-data=2 --...
user avatar
11 votes
Accepted

Mysqldump treats some table as View

So for some tables it looks like Well, it should be that way for all the tables. That message is generated by the --verbose option, and you should see it on all the tables, when mysqldump makes its ...
user avatar
10 votes

Restore mysql database with different name

I am a big fan of dump, edit and insert. but you do not have to open the text file (dump file) to change it (this is especially usefull when it is several milion lines long). if you want to dump the ...
user avatar
  • 201
10 votes

mysqldump --single-transaction, yet update queries are waiting for the backup

The --single-transaction option of mysqldump does do a FLUSH TABLES WITH READ LOCK prior to starting the backup job but only under certain conditions. One of those conditions is when you also specify ...
user avatar
10 votes
Accepted

mysqldump: Got error: 1066: Not unique table/alias

A common case for this error is when the same table appears more than one in the mysqldump list of tables: mysqldump [options] TableFoo ... TableFoo ...
user avatar
  • 231
10 votes
Accepted

Mysqldump from production database, every morning

since the database size is large you should make the dump command like the following: mysqldump -u USER -p --single-transaction --quick --lock-tables=false --all-databases (or) DATABASE | gzip > ...
user avatar
10 votes
Accepted

Need for backing up MySQL databases information_schema, performance_schema, mysql and phpmyadmin?

performance_schema: Not necessary. It may contain performance metrics that are only good for the time you were using the instrumentation. It would serve no purpose to load collected metrics from the ...
user avatar
9 votes
Accepted

Problem with mysqldump and view

You need to have the SHOW VIEW privilege. I wrote about this Dec 2013 : Which are the minimum privileges required to get a backup of a MySQL database schema? In that post I show these minimum ...
user avatar
9 votes

error 2013 (hy000) lost connection to mysql server during query while load of mysqldump

If all the other solutions here fail - check your syslog (/var/log/syslog or similar) to see if your server is running out of memory during the query. Had this issue when innodb_buffer_pool_size was ...
user avatar
  • 199
9 votes
Accepted

MySQL settings useful to speed up a mysqldump import

SUGGESTION #1 No need to run unique_checks = 0 and foreign_key_checks = 0. See my 3-year-old post Speeding up mysqldump / reload (ASPECT #2 shows a standard header of a mysqldump. Lines 13 and 14 ...
user avatar
9 votes

Backup via mysqldump. Is this safe to do on production or will it crash the server?

Unless you add --single-transaction and you're using innodb tables this could block writes to the database for the period of the dump. So use --single-transaction (and innodb). The only risk is if ...
user avatar
  • 6,380
9 votes

mysql to mariadb: unknown collation utf8mb4_0900_ai_ci

I've solved my issue by replacing all occurrences of utf8mb4_0900_ai_ci with utf8mb4_general_ci in my SQL dump file.
user avatar
9 votes

'Access denied; you need (at least one of) the PROCESS privilege(s) for this operation' when trying to dump tablespaces

Resolve by adding: --column-statistics=0 --no-tablespaces to your dump command. I had to add the column-statistics also as just no-tablespaces was not working.
user avatar

Only top scored, non community-wiki answers of a minimum length are eligible