Migrate only E-mail accounts for a single domain from ispCP to i-MSCP

**jackstone** · Mar 12th 2019

Hello everybody,

I have an old ispCP installation with only one domain left on it, and that domain only has e-mail hosting.

The problem is, there are 2376 e-mail accounts on that domain, moving them manually would be a nightmare.

I was thinking if it's somehow possible to export manually from phpMyAdmin the table with only the e-mail accounts and forwards from this domain, and than importing them into the other i-MSCP phpMyAdmin.

I already have the unencrypted passwords for all the accounts (i found a nice script that extracts them from the db)

Of course it would be nice if i could also export the passwords, but if that's not possible at least some method to copy those accounts all at once and not manually adding each one would be great!

I've read about using incremental values in SQL when importing a table but i'm not really sure that it won't conflict with e-mail ID's already present on the server where I want to move the domain, that server also has 2500+ e-mail addresses.

As you can see below, I have mail_id ranging from 3 to 4700. Maybe i could change those values with a SQL command to replace all of them with an incremental value starting with the last value from the i-MSCP installation ? Just asking.

Display Spoiler

-- phpMyAdmin SQL Dump

-- version 4.2.1

-- http://www.phpmyadmin.net

--

-- Host: 127.0.0.1:3306

-- Generation Time: Mar 12, 2019 at 02:45 PM

-- Server version: 5.1.73-1

-- PHP Version: 5.3.3-7+squeeze19

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";

SET time_zone = "+00:00";

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;

/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;

/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;

/*!40101 SET NAMES utf8 */;

--

-- Database: `ispcp`

--

-- --------------------------------------------------------

--

-- Table structure for table `mail_users`

--

CREATE TABLE IF NOT EXISTS `mail_users` (

`mail_id` int(10) unsigned NOT NULL,

`mail_acc` varchar(200) COLLATE utf8_unicode_ci DEFAULT NULL,

`mail_pass` varchar(150) COLLATE utf8_unicode_ci DEFAULT NULL,

`mail_forward` text COLLATE utf8_unicode_ci,

`domain_id` int(10) unsigned DEFAULT NULL,

`mail_type` varchar(30) COLLATE utf8_unicode_ci DEFAULT NULL,

`sub_id` int(10) unsigned DEFAULT NULL,

`status` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,

`mail_auto_respond` tinyint(1) NOT NULL DEFAULT '0',

`mail_auto_respond_text` text COLLATE utf8_unicode_ci,

`quota` int(10) DEFAULT '209715200',

`mail_addr` varchar(200) COLLATE utf8_unicode_ci DEFAULT NULL

) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=5735 ;

--

-- Dumping data for table `mail_users`

--

INSERT INTO `mail_users` (`mail_id`, `mail_acc`, `mail_pass`, `mail_forward`, `domain_id`, `mail_type`, `sub_id`, `status`, `mail_auto_respond`, `mail_auto_respond_text`, `quota`, `mail_addr`) VALUES

(426, '2mfarm', 'jn8zVsLASv4=', '[email protected]', 1, 'normal_mail,normal_forward', 0, 'ok', 0, NULL, 209715200, '[email protected]'),

(4762, '2nafarm', 'a3w+yEa7SyQ=', '[email protected]', 1, 'normal_mail,normal_forward', 0, 'ok', 0, NULL, 209715200, '[email protected]'),

(2767, '4lifepharma', 'a3w+yEa7SyQ=', '[email protected]', 1, 'normal_mail,normal_forward', 0, 'ok', 0, NULL, 209715200, '[email protected]'),

(1620, 'a_mielcioiu', 'kO8SITdIR0LbLkmv3va9XQ==', '[email protected]', 1, 'normal_mail,normal_forward', 0, 'ok', 0, NULL, 209715200, '[email protected]'),

(1655, 'aafarm', 'GiKH3xtDLAI=', '[email protected]', 1, 'normal_mail,normal_forward', 0, 'ok', 0, NULL, 209715200, '[email protected]'),

(4274, 'abba_farm', 'axkNI+VSIh23JPJvIo3SVw==', '[email protected]', 1, 'normal_mail,normal_forward', 0, 'ok', 0, NULL, 209715200, '[email protected]'),

(1629, 'abifarm', 'rpegOFWpSd3fAM0rLMr31w==', '[email protected]', 1, 'normal_mail,normal_forward', 0, 'ok', 0, NULL, 209715200, '[email protected]'),

(3, 'abuse', '_no_', 'marius.[email protected],[email protected]', 1, 'normal_forward', 0, 'ok', 0, NULL, 209715200, '[email protected]'),

(4068, 'acama', 'axkNI+VSIh23JPJvIo3SVw==', '[email protected]', 1, 'normal_mail,normal_forward', 0, 'ok', 0, NULL, 209715200, '[email protected]'),

(1451, 'action', 'iFmx+7kj82tjiR47G00Jdg==', '[email protected]', 1, 'normal_mail,normal_forward', 0, 'ok', 0, NULL, 209715200, '[email protected]'),

(278, 'activ', 'SBCpOqq2Kng=', '[email protected]', 1, 'normal_mail,normal_forward', 0, 'ok', 0, NULL, 209715200, '[email protected]'),

(142, 'adefarm', 'lFTjdAt/HMI=', '[email protected]', 1, 'normal_mail,normal_forward', 0, 'ok', 0, NULL, 209715200, '[email protected]'),

(2414, 'adela', 'uzcJsJWN8ic=', '[email protected]', 1, 'normal_mail,normal_forward', 0, 'ok', 0, NULL, 209715200, '[email protected]'),

(1240, 'adelaida', 'Pca0NQGS+ac=', '[email protected]', 1, 'normal_mail,normal_forward', 0, 'ok', 0, NULL, 209715200, '[email protected]'),

Any solution, I really mean, ANY solution would be great!

Thank you!

crafter · Mar 12th 2019

Firstly, it is bad practice to publish your database, or part of it, with valid emails and passwords. Please clean up your file.

crafter · Mar 12th 2019

See if this helps Import mail users from another system

**Nuxwin** · Mar 12th 2019

Good evening,

As suggested by crafter best would be to create an importer script which would fetch accounts from the old database and insert them into the new database. This require to make the old database reacheable from TCP for a while and run the script from the new server. Basically put, you need mimic the i-MSCP behavior for mail account creation.

Note that doing such a work through PhpMyAdmin is really not recommended.

**jackstone** · Mar 12th 2019

crafter don't worry, i changed both the domain name and some random characters in the password string(s)

Quote from crafter

See if this helps Import mail users from another system

I've already read that post, i understood some parts of it but not totaly, and i'm a bit afraid to test it on production servers and break something.

Quote from Nuxwin

Good evening,

As suggested by crafter best would be to create an importer script which would fetch accounts from the old database and insert them into the new database. This require to make the old database reacheable from TCP for a while and run the script from the new server. Basically put, you need mimic the i-MSCP behavior for mail account creation.

Note that doing such a work through PhpMyAdmin is really not recommended.

I'm not afraid to admit that creating such a script and actually not f***ing up both installations is a bit above my technical skills, anyone care to help me with this ? For a fair price of course, nothing good in this world is free

**Nuxwin** · Mar 12th 2019

Quote from jackstone

crafter don't worry, i changed both the domain name and some random characters in the password string(s)

I've already read that post, i understood some parts of it but not totaly, and i'm a bit afraid to test it on production servers and break something.

I'm not afraid to admit that creating such a script and actually not f***ing up both installations is a bit above my technical skills, anyone care to help me with this ? For a fair price of course, nothing good in this world is free

I can do the work. See https://i-mscp.net/wcf/user/650-nuxwin/ for the price (Online support for common tasks such as debugging, patching).

**jackstone** · Mar 13th 2019

Thanks for your reply Nuxwin i'll msg you in private shortly to setup the details!

Migrate only E-mail accounts for a single domain from ispCP to i-MSCP

Share

Daily visitors