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 --...
- 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
...
- 173k
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 ...
- 971
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) ...
- 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 --...
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 ...
- 173k
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 --...
- 173k
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 ...
- 371
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 ...
- 173k
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.
- 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 ...
- 65.6k
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
...
- 280
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 '/...
- 261
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 ...
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.
- 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) ...
- 173k
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 ...
- 173k
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 --...
- 173k
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 ...
- 22k
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 ...
- 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 ...
- 22k
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 ...
- 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 > ...
- 2,612
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 ...
- 173k
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 ...
- 173k
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 ...
- 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 ...
- 173k
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 ...
- 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.
- 191
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.
- 191
Only top scored, non community-wiki answers of a minimum length are eligible