sql users / databases migration

[sci2tech]

The way I migrated 300+ domains from an old server with vhcs to a new server with ispcp installed:
I mount the old HDD in the new box unde /olddap folder.
From old vhcs database using pma I exported and after that imported in ispcp database following tables:

Quote:admin.sql
domain.sql
ftp_users.sql
htaccess_groups.sql
htaccess_users.sql
quotalimits.sql
reseller_props.sql
sql_database.sql
subdomain.sql
auto_num.sql
ftp_group.sql
hosting_plans.sql
htaccess.sql
mail_users.sql
quotatallies.sql
server_ips.sql
sql_user.sql
user_gui_props.sql

I didn't have domain aliases so I did not need domain_aliasses.sql maybe you will need this too.
Some table dump was manually edited because there was some differences between ispcp and vhcs2 (I really did not remember right now witch).
then:

Quote:UPDATE `ispcp`.`domain` SET `domain_gid` = '0', `domain_uid` = '0', `domain_status` = 'toadd';

Also you need to set status field to 'toadd' for alias table, subdomain table and mail_users. then manually run ispcp/engine/ispcp-rqst-mgr.
After domains/subdomains/aliass and email are added I used this script:

Quote:#!/bin/bash
rm -fR /olddap/var/lib/mysql/ispcp
for domain_id in `echo "SELECT domain_id FROM ispcp.domain" | mysql --defaults-file=/home/daniel/.my.cnf -s`;
do
uid=`echo "SELECT domain_uid FROM ispcp.domain WHERE domain_id='$domain_id'" | mysql --defaults-file=/home/daniel/.my.cnf -s`;
gid=`echo "SELECT domain_gid FROM ispcp.domain WHERE domain_id='$domain_id'" | mysql --defaults-file=/home/daniel/.my.cnf -s`;
dmn=`echo "SELECT domain_name FROM ispcp.domain WHERE domain_id='$domain_id'" | mysql --defaults-file=/home/daniel/.my.cnf -s`;
echo "===========================================================";
echo " $dmn";
echo "===========================================================";
echo "===========================================================" >&2
echo " $dmn" >&2
echo "===========================================================" >&2
# process mysql
for db_id in `echo "SELECT sqld_id FROM ispcp.sql_database WHERE domain_id='$domain_id'" | mysql --defaults-file=/home/daniel/.my.cnf -s`;
do
db_name=`echo "SELECT sqld_name FROM ispcp.sql_database WHERE sqld_id='$db_id'" | mysql --defaults-file=/home/daniel/.my.cnf -s`;
for db_user_name in `echo "SELECT sqlu_name FROM ispcp.sql_user WHERE sqld_id='$db_id'" | mysql --defaults-file=/home/daniel/.my.cnf -s`;
do
db_user_pass=`echo "SELECT sqlu_pass FROM ispcp.sql_user WHERE sqld_id='$db_id' AND sqlu_name='$db_user_name'" | mysql --defaults-file=/home/daniel/.my.cnf -s`;
echo "grant all on \`$db_name\`.* to '$db_user_name'@'localhost' identified by '$db_user_pass'" | mysql --defaults-file=/home/daniel/.my.cnf -s;
echo "grant all on \`$db_name\`.* to '$db_user_name'@'%' identified by '$db_user_pass'" | mysql --defaults-file=/home/daniel/.my.cnf -s;
done
cp -vR /olddap/var/lib/mysql/$db_name /var/lib/mysql/
done

# process ftp
echo "UPDATE \`ispcp\`.\`ftp_group\` SET \`gid\`='$gid' WHERE \`groupname\`='$dmn'" | mysql --defaults-file=/home/daniel/.my.cnf -s
echo "UPDATE \`ispcp\`.\`ftp_users\` SET \`uid\`=$uid, \`gid\`='$gid' WHERE \`userid\` like '%@$dmn'" | mysql --defaults-file=/home/daniel/.my.cnf -s
cp -vR /olddap/var/www/virtual/$dmn /var/www/virtual/
chown -vR vu$uid:vu$gid /var/www/virtual/$dmn
chown -v vu$uid:www-data /var/www/virtual/$dmn
chown -vR vu$uid:www-data /var/www/virtual/$dmn/backups
chown -vR vu$uid:www-data /var/www/virtual/$dmn/logs
chown -vR vu$uid:www-data /var/www/virtual/$dmn/phptmp
chown -vR root:root /var/www/virtual/$dmn/cgi-bin
done
chown -vR mysql:mysql /var/lib/mysql

Modify /home/daniel/.my.cnf to a path to a file that contain:

Quote:[client]
user=user_for_ispcp_database_usualy_root
password=password_of_user

. If you already did this you may want to use only mysql part of script so modify it according.