Migrate from Plesk 10 to Plesk 12

Main problem for migrating from Plesk 10 to Plesk 12 is absence of “Clone Plans” option in Plesk 10. The only thing that one can do is to recreate Service Plans on new server by hand.
Because of that i have wrote MySQL procedure that will migrate Service Plans directly. Procedure does work for me and i cannot guarantee that it will do the job for you, not in it’s entirety. Please, leave a comment and i will implement additional functionality.

1. Preparing data
Log in to new MySQL server and make few queries. Gathered data will be used as starting point for MySQL procedure:
my_next_notes_id:

MariaDB [psa]> SELECT auto_increment FROM information_schema.tables WHERE table_name='Notes' AND table_schema='psa';
+----------------+
| auto_increment |
+----------------+
|             38 |
+----------------+

my_next_php_parameter_id:

MariaDB [psa]> SELECT COALESCE(MAX(id)+1,1,MAX(id)+1) from PhpSettingsParameters;
+---------------------------------+
| COALESCE(MAX(id)+1,1,MAX(id)+1) |
+---------------------------------+
|                             1 |
+---------------------------------+

my_next_php_settings_id:

MariaDB [psa]> SELECT auto_increment FROM information_schema.tables WHERE table_name='PhpSettings' AND table_schema='psa';
+----------------+
| auto_increment |
+----------------+
|            119 |
+----------------+

2. Create MySQL procedure
MySQL procedure will not change anything on your old server. Result of procedure is set of MySQL queries that needs to be inserted into new database, resulting with all your Service Plans created on new server. Before you copy/paste this procedure you need to change variables values (gathered in previous step).

DELIMITER $$

CREATE PROCEDURE migrate_service_plans ()

MAIN: BEGIN

	DECLARE rez text;
	DECLARE my_filename VARCHAR(255);
	DECLARE my_temp_table_name VARCHAR(64);
	DECLARE my_next_notes_id INT;
	DECLARE my_next_php_parameter_id INT;
	DECLARE my_next_php_settings_id INT;

	SET my_filename = "/tmp/service_plans";
	SET my_temp_table_name = "temp_migration_table";
	
	-- gather data from new server:
	-- max_notes_id from new server (SELECT auto_increment FROM information_schema.tables WHERE table_name='Notes' AND table_schema='psa';)
	SET my_next_notes_id = 38;
	-- max ID from new server, table PhpSettingsParameters (SELECT COALESCE(MAX(id)+1,1,MAX(id)+1) from PhpSettingsParameters;)
	SET my_next_php_parameter_id = 1;
	-- max ID from new server, table PhpSettings (SELECT auto_increment FROM information_schema.tables WHERE table_name='PhpSettings' AND table_schema='psa');
	SET my_next_php_settings_id = 119;
	
	-- temporary table for a result
	SET @query = CONCAT('CREATE TEMPORARY TABLE `',my_temp_table_name,'` (`text` text)');
	PREPARE qr1 FROM @query;
	EXECUTE qr1;

	READ_TEMPLATES: BEGIN

		-- cursor 1 handle - read Templates table
		DECLARE curhndl1 INTEGER DEFAULT 0;
		-- cursor 2 handle - read TmplData table
		DECLARE curhndl2 INTEGER DEFAULT 0;
		-- cursor 3 handle - read PhpSettingsParameters table
		DECLARE curhndl3 INTEGER DEFAULT 0;

		-- variables for Templates table
		DECLARE my_plan_id INT;
		DECLARE my_plan_name VARCHAR(255);
		DECLARE my_plan_note INT;
		DECLARE my_plan_owner INT;
		DECLARE my_plan_type VARCHAR(16);
		DECLARE my_plan_uuid VARCHAR(36);
		DECLARE my_plan_external_id VARCHAR(255);

		-- variables for TmplData table
		DECLARE my_data_id INT;
		DECLARE my_data_element VARCHAR(245);
		DECLARE my_data_value VARBINARY(255);
		
		-- variables for PhpSettingsParameters
		DECLARE my_php_id INT;
		DECLARE my_php_name VARCHAR(255);
		DECLARE my_php_value VARCHAR(255);

		-- cursor 1 - get Templates
		DECLARE c1 CURSOR FOR SELECT a.id,a.name,COALESCE(a.note_id,"NULL"),a.owner_id,a.type,a.uuid,COALESCE(a.external_id,'') FROM Templates a LEFT JOIN TmplData b ON (a.id=b.tmpl_id AND b.element='predefined_template') WHERE (a.type='domain' OR a.type='domain_addon') AND b.value IS NULL AND a.external_id='';
		DECLARE CONTINUE HANDLER FOR NOT FOUND SET curhndl1 = 1;
		OPEN c1;

		-- CURSOR 1 - START
		-- cursor 1 - loop
		get_templates: LOOP
		FETCH c1 INTO my_plan_id,my_plan_name,my_plan_note,my_plan_owner,my_plan_type,my_plan_uuid,my_plan_external_id;
		IF curhndl1 = 1 THEN
			CLOSE c1;
			LEAVE get_templates;
		END IF;
		
		-- if it's a domain then we must take care of logrotation setup
		IF (my_plan_type = 'domain') THEN
			SELECT value FROM TmplData WHERE tmpl_id=my_plan_id AND element='logrotation_id' INTO @log_id;
				IF (@log_id <> 0) THEN
					SELECT CONCAT("INSERT INTO log_rotation VALUES ('",id,"','",period_type,"',",period,",",max_number_of_logfiles,",'",compress_enable,"','",email,"','",turned_on,"');") FROM log_rotation WHERE id=@log_id INTO @log;
					SET @query = CONCAT("INSERT INTO `",my_temp_table_name,"` VALUES (\"",@log,"\");");
					PREPARE qr1 FROM @query;
					EXECUTE qr1;
				END IF;
		END IF;
		
		-- construct a query that will insert data into new server database
		SET rez = CONCAT("INSERT INTO Templates VALUES ('",my_plan_id,"','",my_plan_name,"','",my_plan_note,"','",my_plan_owner,"','",my_plan_type,"','",my_plan_uuid,"','",my_plan_external_id,"');");
		SET @query = CONCAT("INSERT INTO `",my_temp_table_name,"` VALUES (\"",rez,"\");");
		PREPARE qr1 FROM @query;
		EXECUTE qr1;

		READ_TMPLDATA: BEGIN
		-- cursor 2 - TmplData
			DEClARE c2 CURSOR FOR SELECT tmpl_id,element,value FROM TmplData WHERE tmpl_id=my_plan_id;
			DECLARE CONTINUE HANDLER FOR NOT FOUND SET curhndl2 = 1;
			OPEN c2;
			
			-- CURSOR 2 - START
			-- cursor 2 - loop
			get_tmpldata: LOOP
			FETCH c2 INTO my_data_id,my_data_element,my_data_value;
			IF curhndl2 = 1 THEN
				CLOSE c2;
				SET curhndl2 = false;
				LEAVE get_tmpldata;
			END IF;

			-- PREPARING TmplData
			IF (my_data_element NOT IN ('catch_mode','php_handler_type','php_safe_mode','phpSettingsId')) THEN
				SET rez = CONCAT("INSERT INTO TmplData VALUES ('",my_data_id,"','",my_data_element,"','",my_data_value,"');");
				SET @query = CONCAT("INSERT INTO `",my_temp_table_name,"` VALUES (\"",rez,"\");");
				PREPARE qr1 FROM @query;
				EXECUTE qr1;
			END IF;
			
			-- do we have some additional PHP settings?
			IF (my_data_element = 'phpSettingsId') THEN
				SELECT noteId FROM PhpSettings WHERE id=my_data_value INTO @note_id;
				IF (@note_id <> 0) THEN

					SET rez = CONCAT("INSERT INTO TmplData VALUES ('",my_data_id,"','",my_data_element,"','",my_next_php_settings_id,"');");
					SET @query = CONCAT("INSERT INTO `",my_temp_table_name,"` VALUES (\"",rez,"\");");
					PREPARE qr1 FROM @query;
					EXECUTE qr1;
					
					SELECT text FROM Notes WHERE id=@note_id INTO @note;
					SET rez = CONCAT("INSERT INTO PhpSettings VALUES ('",my_next_php_settings_id,"','",my_next_notes_id,"');");
					SET @query = CONCAT("INSERT INTO `",my_temp_table_name,"` VALUES (\"",rez,"\");");
					PREPARE qr1 FROM @query;
					EXECUTE qr1;
					
					SET rez = CONCAT("INSERT INTO Notes VALUES ('",my_next_notes_id,"','",@note,"');");
					SET @query = CONCAT("INSERT INTO `",my_temp_table_name,"` VALUES (\"",rez,"\");");
					PREPARE qr1 FROM @query;
					EXECUTE qr1;
				ELSE
					SET rez = CONCAT("INSERT INTO TmplData VALUES ('",my_data_id,"','",my_data_element,"','",my_next_php_settings_id,"');");
					SET @query = CONCAT("INSERT INTO `",my_temp_table_name,"` VALUES (\"",rez,"\");");
					PREPARE qr1 FROM @query;
					EXECUTE qr1;
					
					SET rez = CONCAT("INSERT INTO PhpSettings VALUES ('",my_next_php_settings_id,"','",0,"');");
					SET @query = CONCAT("INSERT INTO `",my_temp_table_name,"` VALUES (\"",rez,"\");");
					PREPARE qr1 FROM @query;
					EXECUTE qr1;
				END IF;

				READ_PHPSETTINGS: BEGIN
				-- cursor 3 - PhpSettingsParameters
					DEClARE c3 CURSOR FOR SELECT id,name,value FROM PhpSettingsParameters WHERE id=my_data_value;
					DECLARE CONTINUE HANDLER FOR NOT FOUND SET curhndl3 = 1;
					OPEN c3;
					
					-- CURSOR 3 - START
					-- cursor 3 - loop
					get_phpsettings: LOOP
					FETCH c3 INTO my_php_id,my_php_name,my_php_value;
					IF curhndl3 = 1 THEN
						CLOSE c3;
						SET curhndl3 = false;
						LEAVE get_phpsettings;
					END IF;

					-- PREPARING PhpSettingsParameters
					SET rez = CONCAT("INSERT INTO PhpSettingsParameters VALUES ('",my_next_php_settings_id,"','",my_php_name,"','",my_php_value,"');");
					SET @query = CONCAT("INSERT INTO `",my_temp_table_name,"` VALUES (\"",rez,"\");");
					PREPARE qr1 FROM @query;
					EXECUTE qr1;

					END LOOP get_phpsettings;
				-- end cursor 3 loop
				END READ_PHPSETTINGS;
				
				SET my_next_php_settings_id = my_next_php_settings_id+1;
				SET my_next_notes_id = my_next_notes_id+1;
				SET my_next_php_settings_id = my_next_php_settings_id+1;
				
			END IF;

			-- end cursor 2 loop
			END LOOP get_tmpldata;

			-- ADDITIONAL TmplData
			SET rez = CONCAT ("INSERT INTO TmplData VALUES ('",my_data_id,"','allow_license_stubs','false');");
			SET @query = CONCAT("INSERT INTO `",my_temp_table_name,"` VALUES (\"",rez,"\");");
			PREPARE qr1 FROM @query;
			EXECUTE qr1;
			SET rez = CONCAT ("INSERT INTO TmplData VALUES ('",my_data_id,"','default_server_mysql',1);");
			SET @query = CONCAT("INSERT INTO `",my_temp_table_name,"` VALUES (\"",rez,"\");");
			PREPARE qr1 FROM @query;
			EXECUTE qr1;
			SET rez = CONCAT ("INSERT INTO TmplData VALUES ('",my_data_id,"','excl_ipv6_num',0);");
			SET @query = CONCAT("INSERT INTO `",my_temp_table_name,"` VALUES (\"",rez,"\");");
			PREPARE qr1 FROM @query;
			EXECUTE qr1;
			SET rez = CONCAT ("INSERT INTO TmplData VALUES ('",my_data_id,"','excl_ip_num',0);");
			SET @query = CONCAT("INSERT INTO `",my_temp_table_name,"` VALUES (\"",rez,"\");");
			PREPARE qr1 FROM @query;
			EXECUTE qr1;
			SET rez = CONCAT ("INSERT INTO TmplData VALUES ('",my_data_id,"','mailservice','true');");
			SET @query = CONCAT("INSERT INTO `",my_temp_table_name,"` VALUES (\"",rez,"\");");
			PREPARE qr1 FROM @query;
			EXECUTE qr1;
			SET rez = CONCAT ("INSERT INTO TmplData VALUES ('",my_data_id,"','manage_security_wordpress_toolkit','false');");
			SET @query = CONCAT("INSERT INTO `",my_temp_table_name,"` VALUES (\"",rez,"\");");
			PREPARE qr1 FROM @query;
			EXECUTE qr1;
			SET rez = CONCAT ("INSERT INTO TmplData VALUES ('",my_data_id,"','manage_wordpress_toolkit','false');");
			SET @query = CONCAT("INSERT INTO `",my_temp_table_name,"` VALUES (\"",rez,"\");");
			PREPARE qr1 FROM @query;
			EXECUTE qr1;
			SET rez = CONCAT ("INSERT INTO TmplData VALUES ('",my_data_id,"','max_mn',-1);");
			SET @query = CONCAT("INSERT INTO `",my_temp_table_name,"` VALUES (\"",rez,"\");");
			PREPARE qr1 FROM @query;
			EXECUTE qr1;
			SET rez = CONCAT ("INSERT INTO TmplData VALUES ('",my_data_id,"','outgoing_messages_domain_limit','default');");
			SET @query = CONCAT("INSERT INTO `",my_temp_table_name,"` VALUES (\"",rez,"\");");
			PREPARE qr1 FROM @query;
			EXECUTE qr1;
			SET rez = CONCAT ("INSERT INTO TmplData VALUES ('",my_data_id,"','outgoing_messages_enable_sendmail','default');");
			SET @query = CONCAT("INSERT INTO `",my_temp_table_name,"` VALUES (\"",rez,"\");");
			PREPARE qr1 FROM @query;
			EXECUTE qr1;
			SET rez = CONCAT ("INSERT INTO TmplData VALUES ('",my_data_id,"','outgoing_messages_mbox_limit','default');");
			SET @query = CONCAT("INSERT INTO `",my_temp_table_name,"` VALUES (\"",rez,"\");");
			PREPARE qr1 FROM @query;
			EXECUTE qr1;
			SET rez = CONCAT ("INSERT INTO TmplData VALUES ('",my_data_id,"','outgoing_messages_subscription_limit','default');");
			SET @query = CONCAT("INSERT INTO `",my_temp_table_name,"` VALUES (\"",rez,"\");");
			PREPARE qr1 FROM @query;
			EXECUTE qr1;
			SET rez = CONCAT ("INSERT INTO TmplData VALUES ('",my_data_id,"','oversell','true');");
			SET @query = CONCAT("INSERT INTO `",my_temp_table_name,"` VALUES (\"",rez,"\");");
			PREPARE qr1 FROM @query;
			EXECUTE qr1;
			SET rez = CONCAT ("INSERT INTO TmplData VALUES ('",my_data_id,"','php_handler_id','fastcgi');");
			SET @query = CONCAT("INSERT INTO `",my_temp_table_name,"` VALUES (\"",rez,"\");");
			PREPARE qr1 FROM @query;
			EXECUTE qr1;
			SET rez = CONCAT ("INSERT INTO TmplData VALUES ('",my_data_id,"','predefined_template','false');");
			SET @query = CONCAT("INSERT INTO `",my_temp_table_name,"` VALUES (\"",rez,"\");");
			PREPARE qr1 FROM @query;
			EXECUTE qr1;
			SET rez = CONCAT ("INSERT INTO TmplData VALUES ('",my_data_id,"','remote_db_connection','false');");
			SET @query = CONCAT("INSERT INTO `",my_temp_table_name,"` VALUES (\"",rez,"\");");
			PREPARE qr1 FROM @query;
			EXECUTE qr1;
			SET rez = CONCAT ("INSERT INTO TmplData VALUES ('",my_data_id,"','shared_template','true');");
			SET @query = CONCAT("INSERT INTO `",my_temp_table_name,"` VALUES (\"",rez,"\");");
			PREPARE qr1 FROM @query;
			EXECUTE qr1;
			SET rez = CONCAT ("INSERT INTO TmplData VALUES ('",my_data_id,"','tmpl_pool_id',0);");
			SET @query = CONCAT("INSERT INTO `",my_temp_table_name,"` VALUES (\"",rez,"\");");
			PREPARE qr1 FROM @query;
			EXECUTE qr1;
			SET rez = CONCAT ("INSERT INTO TmplData VALUES ('",my_data_id,"','unpaid_website_status','suspended');");
			SET @query = CONCAT("INSERT INTO `",my_temp_table_name,"` VALUES (\"",rez,"\");");
			PREPARE qr1 FROM @query;
			EXECUTE qr1;
			SET rez = CONCAT ("INSERT INTO TmplData VALUES ('",my_data_id,"','webServerSettingsId',2);");
			SET @query = CONCAT("INSERT INTO `",my_temp_table_name,"` VALUES (\"",rez,"\");");
			PREPARE qr1 FROM @query;
			EXECUTE qr1;

		END READ_TMPLDATA;

		-- end cursor 1 loop
		END LOOP get_templates;
	END READ_TEMPLATES;

	-- put data into file
	SET @query = CONCAT("SELECT * FROM `",my_temp_table_name,"` INTO OUTFILE '",my_filename,"'");
	PREPARE qr1 FROM @query;
	EXECUTE qr1;

	-- drop temporary table
	SET @query = CONCAT('DROP TEMPORARY TABLE ',my_temp_table_name);
	PREPARE qr1 FROM @query;
	EXECUTE qr1;

END MAIN$$

DELIMITER ;

There are two easy ways of creating this procedure:
1. Copy procedure, log in to your MySQL server, choose psa database and paste. In case MySQL parser gives you errors try:
2. Copy/Paste procedure content to text file, for example /tmp/migrate_service_plans.sql and then execute:

root@old_server:~ # mysql psa -uadmin -p`cat /etc/psa/.psa.shadow` < /tmp/migrate_service_plans.sql

3. Executing procedure
Result of procedure will be in /tmp/service_plans. You need to copy that file to new server and execute it.
Note: Before executing procedure make sure that /tmp/service_plans doesn’t exist. MySQL will create it during procedure execution.
On your old server:

root@old_server:~ # mysql psa -uadmin -p`cat /etc/psa/.psa.shadow`
mysql> call migrate_service_plans();
Query OK, 0 rows affected (0.86 sec)
mysql> exit
Bye
root@old_server:~ # scp /tmp/service_plans root@new_server:/root/
root@new_server password: 
service_plans
root@old_server:~ #

On your new server:

[root@new_server ~]# mysql -uadmin -p`cat /etc/psa/.psa.shadow` psa < /root/service_plans

4. Migrate your sites
If everything went well you have created your Service Plans on your new server. All what's left is to start Migration & Transfer Manager. Enjoy 🙂

M	T	W	T	F	S	S
« Mar
1	2	3	4	5	6	7
8	9	10	11	12	13	14
15	16	17	18	19	20	21
22	23	24	25	26	27	28
29	30

Igor Smitran

Mental Pictures

Migrate from Plesk 10 to Plesk 12

Leave a Reply Cancel reply