sql users / databases migration

sql users / databases migration - Printable Version

+- ispCP - Board - Support (http://www.isp-control.net/forum)
+-- Forum: ispCP Omega Support Area (/forum-30.html)
+--- Forum: Migration (/forum-35.html)
+--- Thread: sql users / databases migration (/thread-4092.html)

sql users / databases migration - nicolas - 08-22-2008 08:26 PM

Hi,

I am migrating from a VHCS installation on server1 (old) to ISCP Omega on server2 (new). So far everything is going ok.

The only question I have, is how to migrate the user databases and myqsl accounts?

Is there a way to regenerate empty "user defined" mysql databases and mysql users from the ISCP command line tools (as databases names and usernames/passwords are stored in tables sql_users and sql_database) ?
I do not feel like adding all the users manually in mysql.

Thanks and best regards;
Nicolas.

RE: sql users / databases migration - sci2tech - 08-22-2008 09:16 PM

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.

RE: sql users / databases migration - nicolas - 08-22-2008 09:46 PM

Many thanks, this is exactly what I was looking for. Your script should be added on the wiki!

RE: sql users / databases migration - nicolas - 08-22-2008 10:09 PM

By the way, using your script I have made a script to recreate directories and correct ownerships + directory / file modes. Someone might find this usefull.

Quote:#!/bin/bash
#
# PROCESS DIRECTORY RIGHTS (OWNERSHIP & CHMOD)
#
# !!!! Edit path to mysql cnf file !!!!
mycnf=/home/manager/mysql.cnf

# sample mysql.cnf looks like this:
# [client]
# user=user_for_ispcp_database_usualy_root
# password=password_of_user

for domain_id in `echo "SELECT domain_id FROM ispcp.domain" | mysql --defaults-file=$mycnf -s`;
do
uid=`echo "SELECT domain_uid FROM ispcp.domain WHERE domain_id='$domain_id'" | mysql --defaults-file=$mycnf -s`;
gid=`echo "SELECT domain_gid FROM ispcp.domain WHERE domain_id='$domain_id'" | mysql --defaults-file=$mycnf -s`;
dmn=`echo "SELECT domain_name FROM ispcp.domain WHERE domain_id='$domain_id'" | mysql --defaults-file=$mycnf -s`;
echo "===========================================================";
echo " $dmn";
echo "===========================================================";

# process ftp
mkdir /var/www/virtual/$dmn
chown -vR vu$uid:vu$gid /var/www/virtual/$dmn
chmod 770 /var/www/virtual/$dmn
chown -v vu$uid:www-data /var/www/virtual/$dmn

mkdir /var/www/virtual/$dmn/backups
chown -vR vu$uid:www-data /var/www/virtual/$dmn/backups
chmod 770 /var/www/virtual/$dmn/backups

mkdir /var/www/virtual/$dmn/cgi-bin
chown -vR vu$uid:vu$gid /var/www/virtual/$dmn/cgi-bin
chmod 755 /var/www/virtual/$dmn/phptmp

mkdir /var/www/virtual/$dmn/errors
chown -vR vu$uid:vu$gid /var/www/virtual/$dmn/errors
chmod 775 /var/www/virtual/$dmn/errors

mkdir /var/www/virtual/$dmn/htdocs
chown -vR vu$uid:vu$gid /var/www/virtual/$dmn/htdocs
chmod -R -s /var/www/virtual/$dmn/htdocs
chmod -R 664 /var/www/virtual/$dmn/htdocs
find /var/www/virtual/$dmn/htdocs -type d -exec chmod 775 {} \;

chown -v vu$uid:www-data /var/www/virtual/$dmn/.ht*
chmod 640 /var/www/virtual/$dmn/.ht*

mkdir /var/www/virtual/$dmn/logs
chown -vR vu$uid:www-data /var/www/virtual/$dmn/logs
chmod 770 /var/www/virtual/$dmn/logs

mkdir /var/www/virtual/$dmn/phptmp
chown -vR vu$uid:www-data /var/www/virtual/$dmn/phptmp
chmod 770 /var/www/virtual/$dmn/phptmp

mkdir /var/www/fcgi/$dmn
chown -vR vu$uid:vu$gid /var/www/fcgi/$dmn
chmod 755 /var/www/fcgi/$dmn

done

Edit: corrected ownership for /var/www/fcgi/* dirs
Edit2: fine tuned chmod under htdocs (775 for dirs and 664 for files)

RE: sql users / databases migration - sci2tech - 08-22-2008 10:17 PM

+1 for your modifications to the script. In that moment I did not need this so I did not take care of this Smile