Database copy with minimal access rights

Currently, I faced a very specific problem. I needed to duplicate a remote database locally. The problem was that I had only rights to execute SELECT statements on the remote computer. Maybe this post will help others to save time looking for the solution.

The reference manual of MySQL describes how to copy a database from one computer to another. Using the instructions in the manual, I ended up using mysqldump and mysql programs on my local computer. This utility programs are parts of the Community Server package and not of Workbench Tools as some people would assume ;-)

Once the software is installed. Run


mysqldump -h <remote_db_hostname> -u <username> -p --compress --single-transaction db_name | gzip > db_name.gz

to save the tables of the database locally. And then

gunzip < db_name.gz | mysql db_name

to create the tables in the local database.

Without the parameter --single-transaction for mysqldump I received the error message

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

P.S. If you are using SQuirreL SQL client, there is a plugin DBCopy available for these purposes.