Applicable to:
- Plesk for Linux
Symptoms
Unable to migrate a subscription, e.g.
example.com
, which has got databases on different remote servers, e.g.
mysqlserver1.com
,
mysqlserver2.com
and
mysqlserver3.com.
After the migration, the domain
example.com
on the destination server should have the same databases on the same database servers.
The migration via Plesk > Tools & Settings > Migration & Transfer Manager migrates only databases which belong to one remote database server and fails with the following error messages for other databases:
PLESK_ERROR: Failed to copy content of database 'database_2_from_mysqlserver2.com'
Migration tools tried to perform operation in 3 attempts: Command execution failed on the source server 'source' (203.0.113.2) with non-zero exit code.
command: MYSQL_PWD="$(cat)" mysqldump --no-defaults -h mysqlserver2.com -P 3306 -uroot --quick --quote-names --add-drop-table --default-character-set=utf8 --set-charset --routines --events database_2_from_mysqlserver2.com > /root/plesk_migrator/plesk_migrator-17l90rj176g9on332v2nu1w4957xibfo/db-dumps/database_2_from_mysqlserver2.com.sql
exit code: 2
stdout:
stderr: mysqldump: Couldn't execute 'show events': Cannot proceed because system tables used by Event Scheduler were found damaged at server start (1577)PLESK_ERROR: Unable to create database user 'user_of_database_2_from_mysqlserver2.com' in subscription 'example.com': Command execution failed on the local server with non-zero exit code.
command: /usr/local/psa/bin/database --create-dbuser user_of_database_2_from_mysqlserver2.com -passwd '***hidden***' -domain example.com -database database_2_from_mysqlserver2.com -type mysql -server mysqlserver2.com:3306 -ignore-nonexistent-options
exit code: 1
stdout:
stderr: Unable to add database user: The user user_of_database_2_from_mysqlserver2.com already exists.
Resync and solution from this article does not fix the issue
As a result, the whole content, except failed databases, are migrated successfully.
Cause
Currently, such scenario is not supported by Plesk Migrator.
Resolution
As a workaround, perform the following actions:
- Connect to the source server via SSH;
-
Get the list subscriptions which have got databases on more than one remote database server ("Count" value means an amount of different remote database servers used by the subscription):
# plesk db "select subc.name as 'Subscription_name', ccc.count as 'Count' from (select dom_id as ddd,COUNT(DISTINCT db_server_id) AS count from data_bases group by dom_id order by count desc) ccc, domains subc where subc.id=ccc.ddd and ccc.count!=1;"
+-------------------+------+
| Subscription_name | Count|
+-------------------+------+
| example.com | 2 |
+-------------------+------+ -
Go to Plesk > Tools & Settings > Migration & Transfer Manager > 203.0.113.2 > List of subscriptions;
-
Choose the subscriptions which should be migrated, e.g.
example.com;
-
Unmark the checkbox Migrate database data (tables, rows, etc) :
Note: even though the checkbox Migrate database data (tables, rows, etc) was disabled, the "skeletons" of databases and users will be created. Also, corresponding records in
psa
database will be also added on the destination server -
Start the migration via Migrate button;
-
Wait until the migration will be completed;
-
Log in via SSH to the destination server;
-
Create a backup of psa database on both servers:
# plesk db dump > /root/psa_backup.sql
-
Perform the following actions for each migrated subscription:
-
Check
id
values of remote database servers on both servers:# plesk db "select id, host from DatabaseServers;"
[SOURCE]
+----+-----------------------+
| id | host |
+----+-----------------------+
| 1 | localhost |
| 2 | mysqlserver1.com |
| 3 | mysqlserver2.com |
| 4 | mysqlserver3.com |
+----+-----------------------+
[DEST]
+----+-----------------------+
| id | host |
+----+-----------------------+
| 1 | localhost |
| 2 | mysqlserver1.com |
| 3 | mysqlserver2.com |
| 4 | mysqlserver3.com |
+----+-----------------------+Note: in this example, the
id
of remote database servers are coincide -
Get the domain
id
on both servers:# plesk db "select id from domains where name='example.com';"
[SOURCE]
+----+
| id |
+----+
| 5 |
+----+
[DEST]
+----+
| id |
+----+
| 10 |
+----+ -
Get the information from
data_bases
anddb_users
tables on both servers for the separate subscription usingdom_id
gotten from the previous step:# [SOURCE SERVER]
# plesk db "select dom_id, name, db_server_id, default_user_id from data_bases where dom_id=17;"
+----+--------+----------------------------------+------------------------------------------+--------------+-----------------+
| id | dom_id | name | login | db_server_id | default_user_id |
+----+--------+----------------------------------+------------------------------------------+--------------+-----------------+
| 15 | 5 | database_2_from_mysqlserver2.com | user_of_database_2_from_mysqlserver2.com | 3 | 115 |
+----+--------+----------------------------------+------------------------------------------+--------------+-----------------+
# plesk db "select id, db_id, dom_id, db_server_id from db_users where dom_id=17;"
+----+-------+--------+--------------+
| id | db_id | dom_id | db_server_id |
+----+-------+--------+--------------+
| 115 | 15 | 5 | 3 |
+----+-------+--------+--------------+
# [DESTINTAION SERVER]
# plesk db "select dom_id, name, db_server_id, default_user_id from data_bases where dom_id=5;"
+----+--------+----------------------------------+------------------------------------------+--------------+-----------------+
| id | dom_id | name | login | db_server_id | default_user_id |
+----+--------+----------------------------------+------------------------------------------+--------------+-----------------+
| 30 | 10 | database_2_from_mysqlserver2.com | user_of_database_2_from_mysqlserver2.com | 1 | NULL |
+----+--------+----------------------------------+------------------------------------------+--------------+-----------------+
# plesk db "select id, db_id, dom_id, db_server_id from db_users where dom_id=17;"
+----+-------+--------+--------------+
| id | db_id | dom_id | db_server_id |
+----+-------+--------+--------------+
| 330 | NULL | 10 | 1 |
+----+-------+--------+--------------+ -
Change the
db_server_id
value in thedata_bases
table on the destination server to the correct one:# plesk db "update data_bases set db_server_id=3 where id=30;"
-
Change the
default_user_id
value in thedata_bases
table on the destination server to the correct one:# plesk db "update data_bases set default_user_id=330 where id=30;"
-
Change the
db_id
value in thedb_users
table on the destination server to the correct one:# plesk db "update db_users set db_id=30 where id=330;"
-
Change the
db_server_id
value in thedb_users
table on the destination server to the correct one:# plesk db "update db_users set db_server_id=3 where id=330;"
-
Comments
0 comments
Please sign in to leave a comment.