Article ID: 11167, created on Mar 25, 2011, last review on May 10, 2014

  • Applies to:
  • Odin Business Automation Standard 4.2
  • Odin Business Automation Standard 4.1
  • Odin Business Automation Standard 4.0
  • Odin Business Automation Standard 3.x

Symptoms

A provider wants to move the PBA-S system database to a separate server, to reduce the load on the PBA-S backend.

This article provides a detailed description on how to move the PBA-S database to a separate server provided that the server will be a Parallels Virtuozzo Containers (PVC) container.

Instructions for moving the PBA-S database to a separate server are also provided in the PBA-S Advanced Configuration Manual.

IMPORTANT

The following values are used in the instructions below, consider replacing them with real ones:

89 - Container ID of database server

10.55.47.289 – IP address of database server

pbas40db.provider.com9 – FQDN hostname of database server

2G9 – memory limit for database server

100G9 – disk space for database server

909 - Container ID of PBAS server

10.55.47.319 – IP address of PBA-S server

pbas40.provider.com9 - FQDN hostname of PBAS server

Transfer MySQL database to separate container-based server.

1. Create and configure new PVC for Linux container for database server and install MySQL server here:

# vzctl create 98 --ostemplate centos-5-x86
Creating Container private area (centos-5-x86)
Container is mounted
Postcreate action done
Container is unmounted
Container private area created
Container registered succesfully

Set IP address and hostname for database server:

# vzctl set 98 --save --ipadd 10.55.47.28 --hostname pbas40db.provider.com
Adding IP address(es) to pool: 10.55.47.28
Saved parameters for Container 98

Set SLM mode and memory limit to 2G for the container:

# vzctl set 98 --save --slmmode slm --slmmemorylimit 2G
Setup slm memory limit
Saved parameters for Container 98
Set other parameters:

# vzctl set 98 --save --onboot yes --offline_management no --diskspace 100G
Delete port redirection
Deleting IP address(es) from pool: 10.55.47.28
Saved parameters for Container 98

Start the container:

# vzctl start 98
Starting Container ...
Container is mounted
Setup slm memory limit
Setup slm subgroup (default)
Setting devperms 20006 dev 0x7d00
Adding IP address(es): 10.55.47.28
Hostname for Container set: pbas40db.provider.com
Container start in progress...

Add hostname of PBA-S server to /etc/hosts on database server:

# echo "10.55.47.31 pbas40.provider.com" >> /vz/root/98/etc/hosts

Upload MySQL RPM packages from PBA-S distributive to the database server and install them:

# cp <PATH_TO_PBAS_DISTRO>/packages/MySQL-* /vz/root/98/root/

Enter the database container:

# vzctl enter 98
# sudo su -

Execute the commands below in the database container:

  • stop Apache server:

    # /etc/init.d/httpd stop
    Stopping httpd:                                            [  OK  ]
    
  • disable Apache server:

    # chkconfig --del httpd
    
  • set root password:

    # passwd
    Changing password for user root.
    New UNIX password:
    Retype new UNIX password:
    passwd: all authentication tokens updated successfully.
    
  • install MySQL RPM packages (provided that you put them in the folder /root in the database container):

    # rpm -ihv /root/MySQL-*
    Preparing...                ########################################### [100%]
    1:MySQL-server           ########################################### [ 25%]
    2:MySQL-Max              ########################################### [ 50%]
    3:MySQL-client           ########################################### [ 75%]
    4:MySQL-shared           ########################################### [100%]
    
  • exit from the container:

    # logout
    exited from Container 98
    

2. Transfer PBA-S database to the separate container.

Attention! If you have PBAS 4.2.x installed download attached hspc-cluster.pl.tar archive and extract hspc-cluster.pl into /usr/sbin/ directory using the following command:

# tar -xPf  hspc-cluster.pl.tar

Use the script hspc-cluster.pl -b with few additional actions. Script performs the following actions:

  • stops and disables local MySQL service in PBA-S server;
  • transfers /var/lib/mysql directory using rcync to the darabase server;
  • enables network access to MySQL on database server (so it is worth to use private IP network or close access to MySQL on database server from outside by firewall);
  • configures MySQL users for remote access from PBA-S host to the database server.

Execute the following commands inside PBA-S server.

Add IP address of the database server to /etc/hosts in PBA-S server:

# echo "10.55.47.28 pbas40db.provider.com" >> /etc/hosts

Stop Service Monitor:

# /etc/init.d/servicemon stop
Stopping HSPcomplete Service Monitor:                      [  OK  ]

Stop Task Manager:

# /etc/init.d/taskmand stop
Stopping HSPcomplete Task Manager:                         [  OK  ]

Run the script /usr/sbin/hspc-cluster:

# hspc-cluster.pl -b
Using /etc/hspc/hspc.conf as configuration file
Configure main database? [yes]:
Configuring MAIN database.
Enter hostname for MAIN database[localhost]: pbas40db.provider.com
Enter user for MAIN database [aspcomplete]:
Enter name for MAIN database [aspc]:
Enter password for MAIN database [*********]:
Configure mutex database? [yes]:
Configuring MUTEX database.
Enter hostname for MUTEX database [pbas40db.provider.com]:
Enter user for MUTEX database [aspcomplete]:
Enter name for MUTEX database [aspc_mutex]:
Enter password for MUTEX database [*********]:
Configure session database? [yes]:
Configuring SESSION database.
Enter hostname for SESSION database [pbas40db.provider.com]:
Enter user for SESSION database [aspsession]:
Enter name for SESSION database [dbsession]:
Enter password for SESSION database [*********]:
Configure sk database? [yes]:
Configuring SK database.
Enter hostname for SK database [pbas40db.provider.com]:
Enter user for SK database [aspcsk]:
Enter name for SK database [sk]:
Enter password for SK database [*********]:
Configure ss database? [yes]:
Configuring SS database.
Enter hostname for SS database [pbas40db.provider.com]:
Enter user for SS database [aspcss]:
Enter name for SS database [ss]:
Enter password for SS database [*********]:
Stopping HSPcomplete Service Monitor:                      [  OK  ]
Configure database on a separate node now?[no]: yes
Enter hostname of the database node[]: pbas40db.provider.com
Generating public/private dsa key pair.
Enter passphrase (empty for no passphrase):
Enter same passphrase again:
Your identification has been saved in /root/.ssh/id_dsa.
Your public key has been saved in /root/.ssh/id_dsa.pub.
The key fingerprint is:
fd:26:af:5f:60:24:d8:56:34:ef:45:e8:04:c1:6e:54 root@pbas40.provider.com
The authenticity of host 'pbas40db.plesk.ru (10.55.47.28)' can't be established.
RSA key fingerprint is 01:fd:75:47:6f:10:d9:c7:78:49:b2:d8:e8:1a:24:a5.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added 'pbas40db.plesk.ru,10.55.47.28' (RSA) to the list of known hosts.
root@pbas40db.provider.com's password:
Stopping hspcd:                                            [  OK  ]
Killing mysqld with pid 9548
Wait for mysqld to exit.. done
Stopping remote services ... Killing mysqld with pid 3075
Wait for mysqld to exit. done
Connection to pbas40db.provider.com closed.
done.
8898 100%    0.00kB/s    0:00:00 0.00kB/s    0:00:00
... here will be long output of the "rsync -e ssh -azrH --progress /var/lib/mysql     pbas40db.provider.com:/var/lib" command" ...
8756 100%    0.00kB/s    0:00:00
done.
Starting remote services ... Connection to pbas40db.provider.com closed.
done.
Configuring 'pbas40db.provider.com'...
Using /etc/my.cnf as configuration file
Connection to pbas40db.provider.com closed.
Old configuration file saved as /etc/hspc/hspc.conf.hspc.backup.
Saving firewall rules to /etc/sysconfig/iptables:          [  OK  ]
Firewall configuration has been successfully finished.
Restart 'hspcd' service? [yes]:yes
Stopping hspcd:                                            [FAILED]
Starting hspcd:                                            [  OK  ]
Restart 'taskmand' service? [yes]:no
Disabling service(s): 'mysql'
No mysqld pid file found. Looked for /var/lib/mysql/pbas40.provider.com.pid.
Starting HSPcomplete Service Monitor:                      [  OK  ]

Stop Service Monitor:

# /etc/init.d/servicemon stop
Stopping HSPcomplete Service Monitor:                      [  OK  ]

3. Verify main PBA-S functionality – open PBA-S control panel and confirm that system works.

Just in case the rollback procedure on this stage is provided below (perform it in PBA-S server in case you see problem with PBA-S after database was moved to the separate server).

Stop PBAS services:

# /etc/init.d/servicemon stop
Stopping HSPcomplete Service Monitor:                      [  OK  ]
# /etc/init.d/taskmand stop
Stopping HSPcomplete Task Manager:                         [  OK  ]
# /etc/init.d/vzcoll stop
Stopping HSPcomplete VZAgent Collector:                    [  OK  ]
# /etc/init.d/hspcd stop
Stopping hspcd:                                            [  OK  ]
# /etc/init.d/pdns stop
Stopping PowerDNS authoritative nameserver: Scheduling exit

Restore PBA-S configuration files from backup, enable local MySQL service:

# mv /etc/hspc/hspc.conf /etc/hspc/hspc.conf.failed
# mv /etc/hspc/hspc.conf.hspc.backup /etc/hspc/hspc.conf
# chkconfig --level 2345 mysql on

Start PBA-S services:

# /etc/init.d/mysql start
# /etc/init.d/pdns start
Starting PowerDNS authoritative nameserver: started
# /etc/init.d/hspcd start
Starting hspcd:                                            [  OK  ]
# /etc/init.d/vzcoll start
Starting HSPcomplete VZAgent Collector:                    [  OK  ]
# /etc/init.d/taskmand start
Starting HSPcomplete Task Manager:                         [  OK  ]
# /etc/init.d/servicemon start
Starting HSPcomplete Service Monitor:                      [  OK  ]

4. Configure PowerDNS (if you verified that PBA-S is working on the previous step).

Grant access privileges for user pdns from PBA-S server in MySQL on database server.

Enter the database container #98 (or log into it using SSH):

# vzctl enter 98
entered into Container 98

Connect to MySQL database aspc:

# mysql aspc

Run the following SQL queries:

mysql> GRANT SELECT ON dm_zone TO pdns@pbas40.provider.com;
mysql> GRANT SELECT ON dm_record TO pdns@pbas40.provider.com;
mysql> GRANT ALL ON pdns_data TO pdns@pbas40.provider.com;
mysql> FLUSH PRIVILEGES;

Check granted privileges, they should look like this:

mysql> SHOW GRANTS FOR pdns@pbas40.provider.com;
+----------------------------------------------------------------------------+
| Grants for pdns@pbas40.provider.com                                        |
+----------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'pdns'@'pbas40.provider.com'                         |
| GRANT SELECT ON `aspc`.`dm_record` TO 'pdns'@'pbas40.provider.com'         |
| GRANT ALL PRIVILEGES ON `aspc`.`pdns_data` TO 'pdns'@'pbas40.provider.com' |
| GRANT SELECT ON `aspc`.`dm_zone` TO 'pdns'@'pbas40.provider.com'           |
+----------------------------------------------------------------------------+
4 rows in set (0.00 sec)

Quit MySQL console:

mysql> quit

Log out from database server:

# logout
exited from Container 98

Switch Power DNS to the new database server:

# hspc-cluster.pl -p
Using /etc/powerdns/pdns.conf as configuration file
Enter the address of PowerDNS server's host [Enter to Skip] [pbas40.provider.com]:
Configuring MYSQL..
Configuring 'pbas40db.provider.com'...
Using /etc/my.cnf as configuration file
Connection to pbas40db.provider.com closed.
Restart 'pdns' service? [yes]:yes
Restarting PowerDNS authoritative nameserver: stopping and waiting..done
Starting PowerDNS authoritative nameserver: started

Check that there is no error records in /var/log/messages inside PBA-S server for PowerDNS.

5. Finalize database transfer – start Task Manager and Service Monitor in PBA-S server:

# /etc/init.d/taskmand start
Starting HSPcomplete Task Manager:                         [  OK  ]
# /etc/init.d/servicemon start
Starting HSPcomplete Service Monitor:                      [  OK  ]

Additional information

For PBA-S version 4.3 and above: Move PBA-S database to separate server (MySQL5)

Search Words

move database to another server

Attachments

caea8340e2d186a540518d08602aa065 70a5401e8b9354cd1d64d0346f2c4a3e 400e18f6ede9f8be5575a475d2d6b0a6 62a72d260afd42b853c9105003928212 93123df14254afe4bfd02eb6096092cd 95c731ef6bb58e30865e046f2135d4c1 fc8d1cb26534a6b898faa9daa73bd86e

Email subscription for changes to this article
Save as PDF