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