Merging the user.profiles table

1. Get the user table from the old machine

    su - mysql
    cd /tmp
    mysqldump -q --quote-names --default-character-set=latin1 --skip-extended-insert -u root user > andrews_user.sql
   

2. Get the user table from the new machine

    su - mysql
    cd /tmp
    mysqldump -q --skip-tz-utc --default-character-set=latin1 --skip-extended-insert -u root user > moog_user.sql
    scp www@andrews:/tmp/andrews_user.sql .
   

3. Use diff to check for changes:

   egrep "^INSERT" andrews_user2.sql | sort > aus.sql
   egrep "^INSERT" moog_user.sql | sort > mus.sql
   diff aus.sql mus.sql > us.sql
   

4. Edit /tmp/andrews_user.sql and update the table name from profiles to profiles_andrews
5. Edit /tmp/moog_user.sql and update the table name from profiles to profiles_moog
6. On the new machine, create a new database and load the above files:

   mysqladmin create user_merge
   mysql user_merge < andrews_user.sql
   mysql user_merge < moog_user.sql
   

7. Here's the tricky part. We want to create a new table that consists of a merge of the two tables where we take the record with the latest lastProfileUpdate.
   So, we use the SQL create command https://dev.mysql.com/doc/refman/5.0/en/create-table-select.html
   How do I join the most recent row in one table to another table?
   How to write flexible INSERT and UPDATE statements in MySQL
8. On the new machine (moog), create a database for experimenting

   mysqladmin create user_merged
   

9. Create a copy of the profile table on moog. The file create.sql contains

   CREATE TABLE profiles_merged LIKE user.profiles; INSERT profiles_merged
   SELECT * FROM user.profiles;
   

   Run:

   mysql user_merged < create.sql
   

10. Here's the sql that gets any user profiles that have been updated on andrews. Place it in a file m1.sql

    REPLACE INTO profiles_merged
    SELECT a.*
    FROM profiles_andrews a, profiles_moog m
     WHERE a.username = m.username
       AND a.lastProfileUpdate > m.lastProfileUpdate;
    

    Then run

    mysql user_merged < m1.sql
    

11. Here's the sql that gets any profiles that are present on andrews but not on moog. Place it in a file m2.sql

    INSERT INTO profiles_merged
    SELECT *
    FROM profiles_andrews a
    WHERE NOT EXISTS(SELECT * FROM profiles_moog m WHERE a.username = m.username);
    

    Then run

    mysql user_merged < m2.sql
    

12. Then replace the user.profile table with the contents of the user_merged.profile_merged.

Old below here

To copy the database to a new machine, it is best to work from the nightly mysql backups located in ~www/mysqlarchive/Day.

See ~www/php/util/copydatabase for a script that will copy archived database files from two machines and merge them.

See ~www/php/util/mysqlmerge for a script that then destroys the current database and loads in the new database.