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 ', '.

Resetting auto_increment fields

Resetting the auto_increment id field in the tables can be a hassle. Here is how to reset id to run from 0; this is done from the MySQL client.

mysql> set @var_name = 1000;

# offset ids by 1000 first

mysql> update tablename set id = (@var_name := @var_name + 1);

# reset to 0

mysql> set @var_name = 0;

# set ids to run from 0:

mysql> update tablename set id = (@var_name := @var_name + 1);

Comparing SQL tables

I ran into a small problem with one of the TODBs, and I needed to create new years of data using the admin years facility. I wanted to copy the current year to the previous year, then delete the current year and recreate it as a new year.

Before deleting the current year, I wanted to be sure that the copy was complete. I tried mysqldumping the two tables for comparison, but some differences appeared (there was a 1-byte difference in the file sizes). So I used diff. However, all it told me was that there was a difference in one line, but that line contains all the data for the table - 1000s of characters! Not helpful.

So instead, I figured this out (with the generous help of Google):

1. Dump the tables using mysqldump:
   mysqldump -u root databasename tablename_old > table_old.sql;
   mysqldump -u root databasename tablename_new > table_new.sql;
2. Reformat the files to trim the lines to manageable lengths:
   fmt -s table_old.sql > table_old_fmt.sql;
   fmt -s table_new.sql > table_new_fmt.sql
3. Then run the diff on them:
   diff table_old_fmt.sql table_new_fmt.sql

Voila! Instant diff with information on where the differences occurred in that one long line!

If you are interested, it turns out that every record was different because the id numbers had auto-incremented... Back to square one (almost - see next post for sorting out the id numbers).