How can I increment every foreign key in my database?

I have two large MySQL databases with identical schemas that I want to merge. To do that I want to increase every foreign key (and id, naturally) of one database by 10 million, and then insert all the records of the modified db into the other db.

I have thought abo开发者_如何学Cut editing the mysqldump with tools like grep and gawk, but that seems very hard to do. What would be the best approach?

Dump two databases into the same server. One will be the target scheme.

• dump database 1 into final_scheme
• dump database 2 into aux_scheme

Do this for every table (I hope it will not be difficult):

insert into final_scheme.tableA
   select id+1000000, name, etc, fk_id+1000000 from aux_scheme.tableA

I did this for a partial merge (this is, only some tables) and worked fine.

UPDATE TABLE mytable_foreign SET ID = ID + 10000000;
UPDATE TABLE mytable SET FOREIGN_ID = FOREIGN_ID + 10000000;

In the old DB:

• Drop the constraints on IDs and foreign IDs
• Update the data (using query above)
• Make a backup of the data only as INSERT statements

In the new DB:

• Insert the backup file

OK, so here is the solution that I implemented, using the information_schema and a bash script. First I get every key-column in the database and the table in which it occurs, and then I update each of those columns.

echo Incrementing every primary and foreign key by $increment
# Get the table name and column name for every key from the information_schema
select_constraints_sql="select TABLE_NAME, COLUMN_NAME from KEY_COLUMN_USAGE where CONSTRAINT_SCHEMA = 'MyDB'"
# Place the query results in an array
data=( $(mysql -e "$select_constraints_sql" -sN --user=$username --password=$passwd information_schema) )

# Step through the tables and keys and update each, with foreign key checks disabled
# Foreign key checks must be disabled at each step
ignore_fks_sql="SET FOREIGN_KEY_CHECKS = 0"
cnt=${#data[@]}
for (( i=0 ; i < cnt ; i=i+2 ))
do
    update_key_sql="$ignore_fks_sql; UPDATE ${data[$i]} SET ${data[$i+1]} = ${data[$i+1]} + $increment"
    mysql -v -e "$update_key_sql" --user=$username --password=$passwd MyDB
done

# This is just me being a bit pedantic
check_fks_sql="SET FOREIGN_KEY_CHECKS = 1"
mysql -v --user=$username --password=$passwd -e "$check_fks_sql" MyDB