Copying user settings for MySQL

When releasing a copy of the database, a bunch of table data gets sent off. I consider the user privileges and setup just as important a part of the system, but this is a far trickier thing to accomplish.

Typically, a copy of the database is made as follows: mysqldump -u root databasename > dbbk.sql

To copy user information, first make a copy of the 'mysql' db:

mysqldump -u root mysql > mysqlbk.sql

Then open this file in a text editor and delete everything except the statements for INSERTing into tables 'db' and 'user'. Then search these for appearances of the username you are looking for, e.g. (for releasing to the Divinity dept) 'divt_todb_read'. Delete any inserts that are not related to the users you are interested in.

You will be left with a file that looks something like this:

INSERT INTO `db` VALUES

('localhost','divinity_todb','divt_todb_read','Y','N','N','N','N','N','N','N','N','N','N','Y','N','N','N','N','N'),

('localhost','divinity_todb','divt_todb_wrt','Y','Y','Y','Y','Y','Y','N','N','N','N','N','Y','N','N','N','N','N');

INSERT INTO `user` VALUES

('localhost','divt_todb_wrt','hashedpwd','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','','','','',0,0,0,0),

('localhost','divt_todb_read','hashedpwd','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','','','','',0,0,0,0);

This is everything that is needed to create the MySQL users.

EDIT: Something I have noticed:

It is worth getting a list of column names for user and db above and putting these into the insert query along the lines of

'insert into table(col1, col2, ..., coln) values ( ... )'

because the number of columns in these tables seems sensitive to SQL version.

Do this by running a 'describe' command and copying + pasting the list of columns into Excel and using the transpose command to make a horisontal list; select the row, copy and paste into Wordpad/Notepad/x and find & replace all tab characters with ', '.