288 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,981
113 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
69 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
  • 791
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 Shah-Quinn's user avatar
22 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_unicode_520_ci in my SQL dump file.
Francesco Borzi's user avatar
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
20 votes

mysql to mariadb: unknown collation utf8mb4_0900_ai_ci

In MariaDB use utf8mb4_unicode_520_ci instead, or you may even use uca1400_as_ci if you have a newer version of MariaDB. Don't use "_general_ci", as it does not correctly sort or compare ...
thomasrutter's user avatar
17 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
17 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
  • 271
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
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
12 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
11 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
  • 219
11 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
  • 77.6k
10 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,699
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
10 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
  • 14.3k
9 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
9 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
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
  • 727
8 votes

Any option for mysqldump to ignore databases for backup?

Since MySQL 5.7.8, you can use mysqlpump (which is NOT the same as mysqldump) as follows: mysqlpump --user=user --password --exclude-databases=db1,db2,db3,db4 --result-file=partial_backup.sql Just ...
Leo Galleguillos'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
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
7 votes

What is the recommended way to backup a MySQL/Amazon RDS database to S3?

AWS RDS now supports cross-region and cross-account copying of snapshots which will allow you to complete your goals simply using RDS. You still have to use a dump script method to get backups to S3 ...
Mike Lapinskas's user avatar
7 votes
Accepted

Non-Blocking MySQL Backup

Indeed the bottleneck was IO. Since I was using tar -czf to compress the files and the CPU was so performant, the drive simply couldn't handle all the data coming so fast (although we use mirrored ...
Gerald's user avatar
  • 221
7 votes
Accepted

mysqldump Not Exporting All Rows or mysql Not Importing

Sometimes this can occur because of a combination of a certain sequence of characters and the character set of the MySQL client at the time of the import. You could have trapped such errors without ...
RolandoMySQLDBA's user avatar
7 votes
Accepted

Passwordless mysqldump via shell script in /etc/cron.daily

Solved with the help of this answer. cron doesn't know the path to ~/.my.cnf, so as per the MYSQL documentation you need to specify the path to the .my.cnf file. The proper passwordless mysqldump ...
Vila's user avatar
  • 223
7 votes
Accepted

Error Unknown command '\U' when restoring MySQL dump on Windows

MariaDB [theDBName]> SOURCE C:\Users\myUserName\theDBName_data.dump Either of the following should be correct: MariaDB [theDBName]> SOURCE C:\\Users\\myUserName\\theDBName_data.dump MariaDB [...
Rick James's user avatar
  • 77.6k

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