As of May 31, 2023, we have updated our Code of Conduct.
261 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 --...
wolfrevo's user avatar
  • 2,711
105 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 ...
pixelbrackets's user avatar
67 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) ...
liangzan's user avatar
  • 771
59 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 --...
ThanksForAllTheFish's user avatar
29 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 ...
Neil P. Quinn's user avatar
22 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.
Soroush's user avatar
  • 507
21 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 ...
Solomon Rutzky's user avatar
17 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 '/...
Bruno J. Araujo's user avatar
17 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.
Francesco Borzi's user avatar
16 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 ...
Marcelo Luiz Onhate's user avatar
16 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.
SCL's user avatar
  • 261
16 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) ...
RolandoMySQLDBA's user avatar
13 votes
Accepted

How to take mysqldump with Generated column?

This is a problem when using mysqldump from MariaDB with virtual generated columns. MariaDB's mysqldump apparently dumps the generated values, but MySQL only accepts DEFAULT as value for a virtual ...
Dario Seidl's 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 ...
Michael - sqlbot's user avatar
11 votes

mysql to mariadb: unknown collation utf8mb4_0900_ai_ci

First, I can't comment on the errors you are getting when defining views as you haven't shown the statements you're trying to use to create them. In short, in MariaDB use utf8mb4_unicode_520_ci ...
thomasrutter's 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 ...
RolandoMySQLDBA's user avatar
10 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 ...
A_funs's user avatar
  • 209
10 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.
ElusiveMind's user avatar
10 votes

How to restore a specific table from a big mysqldump file?

You could achieve that by two steps : Step 1 : Locate position of your table(or tables) and it (them) in a file : grep -n "Table structure" your_dump_file > list_tables_NUM.TXT This ...
Yassine LD's user avatar
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 ...
RolandoMySQLDBA's user avatar
9 votes
Accepted

Disabling binary logging when restoring a compressed MySQL dump

I found this answer. https://geert.vanderkelen.org/2009/disabling-binary-logging-when-restoring-a-mysql-dump/ bash $ (echo "SET SESSION SQL_LOG_BIN=0;"; cat dump.sql) > dump_nobinlog.sql A ...
nelaaro's user avatar
  • 717
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 ...
danblack's user avatar
  • 7,238
9 votes
Accepted

Do I need --skip-lock-tables with --single-transaction?

If you use the --single-transaction option, it turns off --lock-tables. That's what the documentation means when it says they are mutually exclusive. https://github.com/mysql/mysql-server/blob/8.0/...
Bill Karwin's user avatar
  • 12.6k
8 votes

How to convert a MySQL database to PostgreSQL?

Here are a few open source tools, that can be very helpful: FromMySqlToPostgreSql is a feature-reach tool and very easy to use. It maps data-types, migrates constraints, indexes, PKs and FKs exactly ...
AnatolyUss's user avatar
8 votes

Error while restoring a Database from an SQL dump

Windows Create your dump files with this command .\mysqldump [dbname] -r [filename.sql] Using: .\mysqldumb --help -r, --result-file=name Direct output to a given file. This option ...
Timothy L.J. Stewart's user avatar
8 votes

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

A quick workaround is to pass the –-single-transaction option to mysqldump: $ mysqldump --single-transaction -u user -p DBNAME > backup.sql Just for sharing, the below article have a good ...
Mohamed Sabr's user avatar
8 votes

mysqldump doesn't restore database. why not?

Basically there is a basic difference between the command to take backup and restore a mysql database on linux servers To take a backup we have to use a command mysqldump like this on the command ...
Rakesh Kumar's user avatar
8 votes

How to make mysqldump use /*!40101 SET character_set_client = utf8mb4 */;?

That special type of comment says "leave this as a comment if running a version older than 4.1.1; else execute it". utf8mb4 came in in 5.5.3, so the 'correct' version would be /*!50503 SET ...
Rick James's user avatar
  • 74.8k
8 votes
Accepted

mysqldump with LIMIT and --order-by-primary

My guess is that you cant use limit in combination with --order-by-primary One way around this is to define the order by within the -where argument. If you know the primary key for the table: ...
Lennart - Slava Ukraini's user avatar
8 votes

MySQL - MySQLDump problems with large table

Your problem stems from network. In this instance, it is not the buffer used for data transmission over the network. It is the length of time the network tries to keep alive in so doing. Please note ...
RolandoMySQLDBA's user avatar

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