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 --...
- 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 ...
- 1,151
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) ...
- 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 --...
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 ...
- 391
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.
- 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 ...
- 67.7k
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 '/...
- 271
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.
- 271
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 ...
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.
- 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) ...
- 179k
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 ...
- 258
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 ...
- 22.4k
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 ...
- 211
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 ...
- 179k
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 ...
- 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.
- 201
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 ...
- 798
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 ...
- 179k
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 ...
- 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 ...
- 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/...
- 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 ...
- 81
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 ...
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 ...
- 91
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 ...
- 181
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 ...
- 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:
...
- 22.8k
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 ...
- 179k
Only top scored, non community-wiki answers of a minimum length are eligible