Take the 2-minute tour ×
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. It's 100% free, no registration required.

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

1 Answer 1

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 at 18:02
1  
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: dev.mysql.com/downloads/tools/utilities –  dartonw Mar 6 at 12:26

Your Answer

 
discard

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.