Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I wrongly designed my application to have one database to each user. each user had 3 similar tables. I now want to have one database and 3 tables only; where i will use the database name in the old databases as a reference in the new system. There was another database called "users" in the old database that stored the database names. I'm done with the schema design of the new database and now left with the migration.enter image description here

The trick here is that I have 3 db connections. I first connect to the users database and userinfo table, pick up the database_name in a loop, use it to connect each old db and further connect to personal, accounts and games table.

After picking up the tables, i will like to populate/join it with the new Database (DATA_ONE) and the tables whiles i append the old database_name to the new tables. Any help on this or is there a better way to do this? Thanks much

<?php

$dbhost = "localhost";  
$dbname = "users"; 
$dbuser = "root"; 
$dbpass = "*****"; 

$conn1 = mysql_connect($dbhost, $dbuser, $dbpass, TRUE) or die("MySQL Error: " .  mysql_error()); 
$conn2 = mysql_connect($dbhost, $dbuser, $dbpass, TRUE) or die("MySQL Error: " . mysql_error()); 
$conn3 = mysql_connect($dbhost, $dbuser, $dbpass, TRUE) or die("MySQL Error: " . mysql_error());

mysql_select_db($dbname,$conn1) or die("MySQL Error: " . mysql_error()); 

$query  = "SELECT database_name FROM userinfo";
$result1 = mysql_query($query,$conn1);
while($row = mysql_fetch_array($result1, MYSQL_ASSOC))
{
$database_name =$row['database_name'];
$conn2 = mysql_connect($dbhost, $dbuser, $dbpass) or die("MySQL Error: " . mysql_error());
  $db = mysql_select_db($database_name ,$conn2) ;
     // now, pick personal, accounts, games tables of user and populate new database and    tables.
  // this is where i need help.   
    }

?>
share|improve this question
    
@RolandoMySQLDBA: any help here? –  karto May 22 '12 at 14:32
add comment

2 Answers

Instead of Migration to another System, try doing it in place, then mysqldump DATA_ONE and import that mysqldump into the new server.

First, let's create DATA_ONE

DROP DATABASE IF EXISTS DATA_ONE;
CREATE DATABASE DATA_ONE;

Next, Create DATA_ONE tables using the template from database aa123

CREATE TABLE DATA_ONE.personal LIKE aa123.personal;
CREATE TABLE DATA_ONE.accounts LIKE aa123.accounts;
CREATE TABLE DATA_ONE.games    LIKE aa123.games;

Now, Create a UserInfo Table

CREATE TABLE DATA_ONE.UserInfo
(
    userid INT NOT NULL AUTO_INCREMENT,
    username VARCHAR(32),
    last_update DATETIME NOT NULL,
    PRIMARY KEY (userid),
    KEY (username)
);
INSERT INTO DATA_ONE.UserInfo (username,last_update)
SELECT database_name,last_update FROM users.userinfo;

Then, add the database_name column (and index it) to the DATA_ONE tables

ALTER TABLE DATA_ONE.personal ADD username VARCHAR(32);
ALTER TABLE DATA_ONE.personal ADD userid INT;
ALTER TABLE DATA_ONE.accounts ADD userid INT;
ALTER TABLE DATA_ONE.games    ADD userid INT;
ALTER TABLE DATA_ONE.personal ADD INDEX (username);
ALTER TABLE DATA_ONE.personal ADD INDEX (userid);
ALTER TABLE DATA_ONE.accounts ADD INDEX (userid);
ALTER TABLE DATA_ONE.games    ADD INDEX (userid);

OK, that makes the new DATA_ONE database.

Now comes the adventurous part: Loading all those tables into DATA_ONE.

Run these three queries to formulate the SQL needed for migration

SELECT CONCAT('INSERT INTO DATA_ONE.personal (address,password,username,userid) SELECT address,password,'''username''',',userid,' FROM ',username,'.personal;') FROM DATA_ONE.UserInfo;
SELECT CONCAT('INSERT INTO DATA_ONE.accounts (amount,bank,userid) SELECT amount,bank,',userid,' FROM ',username,'.accounts;') FROM DATA_ONE.UserInfo;
SELECT CONCAT('INSERT INTO DATA_ONE.games (game_name,userid) SELECT game_name,',userid,' FROM ',username,'.games;') FROM DATA_ONE.UserInfo;

Here is everything mentioned above shell-scripted and executed:

SQLFILE=/root/MakeDATA_ONE_Database.sql
echo "DROP DATABASE IF EXISTS DATA_ONE;" > ${SQLFILE}
echo "CREATE DATABASE DATA_ONE;" >> ${SQLFILE}
echo "CREATE TABLE DATA_ONE.personal LIKE aa123.personal;" >> ${SQLFILE}
echo "CREATE TABLE DATA_ONE.accounts LIKE aa123.accounts;" >> ${SQLFILE}
echo "CREATE TABLE DATA_ONE.games    LIKE aa123.games;" >> ${SQLFILE}
echo "CREATE TABLE DATA_ONE.UserInfo (userid INT NOT NULL AUTO_INCREMENT,username VARCHAR(32),last_update DATETIME NOT NULL,PRIMARY KEY (userid),KEY (username));" >> ${SQLFILE}
echo "INSERT INTO DATA_ONE.UserInfo (username,last_update) SELECT database_name,last_update FROM users.userinfo;" >> ${SQLFILE}
echo "ALTER TABLE DATA_ONE.personal ADD username VARCHAR(32);" >> ${SQLFILE}
echo "ALTER TABLE DATA_ONE.personal ADD userid INT;" >> ${SQLFILE}
echo "ALTER TABLE DATA_ONE.accounts ADD userid INT;" >> ${SQLFILE}
echo "ALTER TABLE DATA_ONE.games    ADD userid INT;" >> ${SQLFILE}
echo "ALTER TABLE DATA_ONE.personal ADD INDEX (username);" >> ${SQLFILE}
echo "ALTER TABLE DATA_ONE.personal ADD INDEX (userid);" >> ${SQLFILE}
echo "ALTER TABLE DATA_ONE.accounts ADD INDEX (userid);" >> ${SQLFILE}
echo "ALTER TABLE DATA_ONE.games    ADD INDEX (userid);" >> ${SQLFILE}
mysql -u... -p... -ANe"SELECT CONCAT('INSERT INTO DATA_ONE.personal (address,password,username,userid) SELECT address,password,'''username''',',userid,' FROM ',username,'.personal;') FROM DATA_ONE.UserInfo;" >> ${SQLFILE}
mysql -u... -p... -ANe"SELECT CONCAT('INSERT INTO DATA_ONE.accounts (amount,bank,userid) SELECT amount,bank,',userid,' FROM ',username,'.accounts;') FROM DATA_ONE.UserInfo;" >> ${SQLFILE}
mysql -u... -p... -ANe"SELECT CONCAT('INSERT INTO DATA_ONE.games (game_name,userid) SELECT game_name,',userid,' FROM ',username,'.games;') FROM DATA_ONE.UserInfo;" >> ${SQLFILE}
mysql -u... -p... -A < ${SQLFILE}

Now open mysql-workbench or phpmyadmin or whatever you browse data with and look through the DATA_ONE database.

If you are satisfied with what is there, just do this:

mysqldump -u... -p... --databases DATA_ONE > /root/DATA_ONE.sql

Move the DATA_ONE.sql file over to the new server and execute the script.

That's it. Pure SQL solution. No PHP. If anything does not work, it is probably because I do not know the layout and current contents of the personal, account, & games tables and made assumptions on data. Please post the following in your question:

SHOW CREATE TABLE aa123.personal\G
SHOW CREATE TABLE aa123.accounts\G
SHOW CREATE TABLE aa123.games\G

Give it a Try !!!

UPDATE 2012-05-23 13:57 EDT

Oh, you have 2500 databases? My above solution should still work.

I do have an alternative suggestion: Try to mysqldump all databases into individual files.

Here is a shell script that will dump all 2500 databases into separate files and gzipped

MYSQL_CONN="-uusername -ppassword"
SQLSTMT="SELECT DISTINCT table_schema FROM information_schema.tables"
SQLSTMT="${SQLSTMT} WHERE table_schema NOT IN"
SQLSTMT="${SQLSTMT} ('information_schema','mysql','performance_schema')"
mysql ${MYSQL_CONN} -ANe"${SQLSTMT}" > /tmp/ListOfDatabases.txt
MYSQL_CONN="-uusername -ppassword"
MYSQLDUMP_OPTIONS="--hex-blob --routines --triggers"
COMMIT_COUNT=0
COMMIT_LIMIT=50
for DB in `cat /tmp/ListOfDatabases.txt`
do
    mysqldump ${MYSQL_CONN} ${MYSQLDUMP_OPTIONS} --databases ${DB} | gzip > ${DB}.sql.gz &
    (( COMMIT_COUNT++ ))
    if [ ${COMMIT_COUNT} -eq ${COMMIT_LIMIT} ]
    then
        COMMIT_COUNT=0
        wait
    fi
done
if [ ${COMMIT_COUNT} -gt 0 ]
then
    wait
fi

Once you execute this, all databases will be mysqldump'd. For example, the database aa123 will be dumped and gzipped to aa123.sql.gz. This and all the other 2499 mysqldumps can be copied over to the new DB server. Place the dumps in /root/mysqldata on the new DB server. Once they are on the new DB server, you can then unzip all files like this:

cd /root/mysqldata
for X in `ls *.sql.gz` ; do echo ${X} ; gzip -d ${X} ; done

OK this will unzip all the files.

Now, run this to load the SQL scripts into mysql on the new server

cd /root/mysqldata
for X in `ls *.sql` ; do echo ${X} ; mysql -u... -p... -A < ${X} ; done

That's it. No PHP involved. All iteration is handled in the shell script I provided. You can then combine them in the new DB Server as DATA_ONE like I mentioned before.

Give it a Try !!!

share|improve this answer
    
WOW..this answer seems great!! the problem here is that there are about 2500 databases. so obviously need php to do some iteration here. any professional help, Ro? can i contact u on skype(rinkarto)/email:?? –  karto May 23 '12 at 9:30
    
thanks a billion times. tweaked it to my style and it works like magic. –  karto May 24 '12 at 13:27
    
Do I get an upvote and acceptance for this one ??? –  RolandoMySQLDBA May 24 '12 at 14:14
    
I created an account on the site just to +1. Excellent answer, sir. –  Jim Schubert Sep 1 '13 at 18:15
add comment

The most important thing to watch out for is clashes on Primary Key fields. It looks like person.id and games.gameid are two potential problem areas.

Ways to resolve this:

  1. Add a new column named source_db_id to each table with this problem. When populated person from database aa123 set this field to aa123. Make the primary key for Person include id AND source_db_id. This way, if database aa123 has the value 123456 in Person.ID and dcs345 also has 123456 for person.id, you won't have a key clash because the primary key should differ on source_db_id.

    For all new data inserted into this table, you'll have to have some default value for source_db_id such as "new_db".

  2. Modify the values of person.id and games.game_id. This could be similar to the first option, but instead of adding a new column, you just add a suffix to Person.id when you insert it. Example: When processing data from aa123, instead of inserting 1234 for Person.ID, insert 1234_aa123.

  3. Modify all primary keys to avoid key clashes. This would involve changing data in all potential key clash fields so that there are no clashes once the data is all settled in the new database. You could do this as a pre-processing step, or do it whil you're copying the data. The simplest way might be to simply not copy the old ID fields, and generate new values as you go.

    The problem with this is that if you have very complex relationships, you have to propagate the new ID field to all other records before any of them can be inserted. This can get tricky when there are many highly interconnected tables, but if the tables and relations are simple this might be the best way to make your data look consistent because there are no new fields or weird-looking data in key fields.

    You could also use Option 1 or 2 listed here as intermediate steps to complete this option. Ie.: create a new field for source_db_id, and then after all the data has been migrated, use source_db_id to update other key fields with a new generated sequence, and then remove the source_db_id field.

One problem you might have with any method is if there are URLs out there in the wild that point to www.someserver.com/yourgame?personid="12345". If you change the IDs you might break those URLs. In this case, you may want to come up with a way of redirecting these old URLs if you need to. This should probably be done by the application though, not by the database.

share|improve this answer
    
thanks..offer professional services??? –  karto May 22 '12 at 14:59
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.