An SQL script to offset the id fields across all tables in a DB.

offset_id_fields_mysql.sql

/*
This script is for updating the auto_increment field by an offset. The logic applied is 
max(AUTO_INCREMENT) + current value in the table.
*/

SET @db:='id_new'; 

select @max_id:=max(AUTO_INCREMENT) from information_schema.tables;

select concat('update ',table_name,' set ', column_name,' = ',column_name,'+',@max_id,' ; ') from information_schema.columns where table_schema=@db and column_name like '%id' into outfile 'update_ids.sql';
 
use id_new
source update_ids.sql;

set_auto_increment.sql

select @max_id:=max(AUTO_INCREMENT) from information_schema.tables;

select concat('alter table ',table_name,' AUTO_INCREMENT= ', @max_id,';') from information_schema.tables where table_schema=@db into outfile 'increment_id_values.sql';

source increment_id_values.sql  

Couple of points to be noted before execute the scripts:

• Execute it as root user (for both OS and mysql)
• Execute it from the path /, for eg: /var/lib/mysql/id_new
• The script assumes that all your Primary Key field names will be ID and Foreign Keys will have the format <field_id>
• Change the value of '@db' variable and change 'use' command accordingly

Very clever. I thought of doing this as a PHP script, but much more elegant as a SQL script to generate SQL :)