SQL Server – Easy Database Migration

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 !!!

If you are changing versions, DO NOT MOVE THE mysql SCHEMA.

Why should you not move the mysql folder? It has to do with the authentication privileges.

The number of columns in mysql.user is different from version to version

If you run desc mysql.user

• You will see 31 rows for MySQL 4.1
• You will see 37 rows for MySQL 5.0
• You will see 39 rows for MySQL 5.1
• You will see 42 rows for MySQL 5.5

I wrote about this before

• Cannot GRANT privileges as root
• Mysql users deleted

It is OK to move everything else. On the new machine that has MySQL 5.5.24, do this:

mv /var/lib/mysql /var/lib/mysql/mysql55
mkdir /var/lib/mysql
<scp or rsync /var/lib/mysql of MySQL 5.1.41 over to /var/lib.mysql of MySQL 5.5.24>
rm -f /var/lib/mysql/mysql/*
cp /var/lib/mysql/mysql55/* /var/lib/mysql/mysql/*
chown -R mysql:mysql /var/lib/mysql
service mysql start

So, the question remains:

How do you move the User Privileges in the old MySQL 5.1.41 to MySQL 5.5.24 ???

There are two ways to do this starting on the MySQL 5.1.41 machine:

METHOD #1 : Use pt-show-grants

This Percona Toolkit program move print out the User Permission in Pure SQL. You could run the result output into a Text File. Then, execute the Text File in MySQL 5.5.24. End of Story.

pt-show-grants ... > MySQLUserGrants.sql

METHOD #2 : Emulate pt-show-grants

I made my own technique for pt-show-grants

mysql -hhostaddr -umyuserid -pmypassword --skip-column-names -A -e"SELECT CONCAT('SHOW GRANTS FOR ''',user,'''@''',host,''';') FROM mysql.user WHERE user<>''" | mysql -hhostaddr -umyuserid -pmypassword --skip-column-names -A | sed 's/$/;/g' > MySQLUserGrants.sql

Either way, move MySQLUserGrants.sql over to the MySQL 5.5.24 machine and execute the script

I wrote about this before : importing myisam 5.0 database into a 5.5 innodb server