Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I need to set up an automated copy of a single mysql database (not the whole DBMS). If I use mysqldump to dump and recreate a DB, the copy may be unusable because the user accounts need to also be copied. Triggers in the source DB for instance may be copied, but there's no guarantee that they will execute correctly in the new DB because the original triggers were designed to run as some particular user.

A couple of questions:

  • how can I automate copying of the user accounts and permissions?

  • is there a way, when copying a db to check whether all of its dependencies are met? (E.g. are all of the databases referred to by the stored procedures present?)

I understand that I can replicate the whole DBMS - this is overkill (and often not practical for hourly regressions)

share|improve this question
You can use this PHP tool You can find documentation here – user63949 Apr 22 '15 at 16:16

Users and grants are stored in the mysql database, so you could insert rows directly. MySQL Workbench includes the mysqluserclone utility, which provides a safer way to copy user accounts and permissions from one server to another.

I don't know of any direct way to check dependencies, but INFORMATION_SCHEMA contains all the necessary metadata. Other utilities from MySQL Workbench such as mysqldiff and mysqldbcompare would probably help in determining when something is missing.

share|improve this answer
Thanks -- I need to hook it up to an automation system. Can MySQL Workbench be used from the command-line on machines with and xserver? – user48956 Feb 27 '14 at 18:02
MySQL Workbench is largely a GUI tool; it used to be bundled with the CLI utilities but they are now a separate download called MySQL Utilities: – dartonw Mar 6 '14 at 12:26

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.