Copy database to another domain

[Fantasyprints]

Hi all

In Plex 11 copying a database to another domain was very simple but in Plesk 12 the Subscription Destination is missing from the Copy Database window. I have tried using IP address but can't get it to work can anybody help I want to test websites on test domains.

Paul

 

[UFHH01]

Hi Fantasyprints,

did you have a look at:

Copying Databases ( Plesk 12 online documentation, Administrator's Guide, Plesk 12.0 )​

 

[Fantasyprints]

Thanks but I am trying to copy the database to another domain on the same server. On Plesk 11 there was an extra option to copy to another subscription but this is missing on Plesk 12

 

[UFHH01]

Hi Fantasyprints,

yes, I'm aware what you would like to do. Please read again the WHOLE documentation, because it states as well, that you could choose an "external" database, which then will be fully overwritten with the tables of the database, which you would like to copy. So please create a database in the subscription, afterwards go to the other subscription, choose the option to copy the external database, choose the option to copy to an existing database, choose the just created database - name in the other subscription and perform the copy with the "o.k." - button.

 

[Fantasyprints]

Thanks I have tried but keep getting

"Unable to connect to the database server. Please check the database user credentials and access restrictions."

I put the same IP address in the top box of the Plesk Server (both destination and source on same box). Put the database username and password of the destination database and the name of the database (created one as suggested).

I have tried root username and password but with not luck

Think missing something can you point me in correct direction

 

[UFHH01]

Hi Fantasyprints,

o.k. ... let's go step-by-step with examples:

1. Go to your subscription example-001.com
2. Create a database with database - name "test123" and username testadmin , password test123
3. Go to another subsciption example-002.com
4. Mark the desired database to copy and choose external database
5. Use the login - datails username testadmin , password test123
6. User the hostname from example-001.com or the depending IP - adress for this domain
7. Choose the destination "copy to existing database" with the desired database - name test123
8. Click o.k. ( The database will now being copied and Plesk will inform you, after completion )

Please be aware, that there is NO user "root" in your MySQL - database. The global database - admin for MySQL with Plesk is "admin", which never should be used, unless you know, what you are doing.
Each database has it's own username and password, which you choose upon creation.

 

[Fantasyprints]

1. Mark the desired database to copy and choose external database

I select database to copy. Press COPY. I don't have option for EXTERNAL DATABASE but I have OTHER?

I put the domain into Host Name or IP address. User the details setup you suggest and still get

Unable to connect to the database server. Please check the database user credentials and access restrictions.

 

[UFHH01]

Hi Fantasyprints,

well other than "local" is... ?

If you are unable to connect to the desired database, there are only a few reasons for it:

1. example--001.com doesn't resolve to the IP XXX.XXX.XXX.XXX. Your DNS - settings might have missing entries, or the domain is misconfigured on the DNS - nameserver of your domain - registrar You could check this by using "dig @8.8.8.8 example--001.com", which should result in "example--001.com. 21599 IN A XXX.XXX.XXX.XXX". If it doesn't, please correct the DNS - entries. If you would like to skip that, please try the IP manually, instead of using the hostname and choose the IP from your server, because this is where your "local" MySQL database is located.
2. Your database - name and password are incorrect for the database "test123". Please correct this, so that Plesk can connect to the database and copy the desired database.
3. Your initial setup for your database - name "test123" is set to "Allow only local connects". Correct this, if you would like to use the feature to copy from "local" to "external", which is the case here.
4. You have a very nice log - file for the Plesk Control Panel. Locate the file and have a look to any errors regarding your command. If the log - file doesn't show enough informations, please use a more decent log - level in your "panel.ini", to get a more verbose output.
5. Last, please check for firewall settings and missing open ports configurations.

Odin / Parallels Plesk Panel for Linux services logs and configuration files ( KB - article: 111 283 )

Which ports need to be opened for all Plesk services to work with a firewall? ( KB - article 391 )​

 

[Fantasyprints]

Thanks for your help. I have uploaded a PDF of screen shots just incase I am in wrong area
The two websites work at moment so would that mean DNS is OK?
Remote was switched on when creating the User TestAdmin.

 

[Fantasyprints]

Just tried copying a database from a dedicated server using Plesk 12 to a VPS server using Plesk 12 and still get

"Unable to connect to the database server. Please check the database user credentials and access restrictions."

So wondering is there more to the IP address than I have put?

 

[UFHH01]

Hi Fantasyprints,

"Unable to connect to the database server. Please check the database user credentials and access restrictions."

Could you please check, HOW you configured database "test123" ? With or without remote access ? You have THREE options, when you create a database:

Only allow local connections

Allow remote connections from any host

Allow remote connections from

At the moment, remote connects for user "testadmin" to your MySQL - database is not possible. Please correct this, if you would like to allow remote connects. You can do this over Plesk ( choose the desired database - user in the database - section, or over your global phpMyAdmin - interface in the user - section ).

Again, the option to use a more verbose log - level for the Plesk Panel will help you to investigate issues/problems, because the log will then show you quite explizit, what the failure is and why.

( if you wish, you can delete the PDF - attachment to secure your domain - data... the section was absolute correct and you didn't do anything wrong there )

 

[Fantasyprints]

Database name: test123
Type: MYSQL
Database Server: Local MYSQL Server (Default for MySQL)
User: testadmin
Pass: test123
Allow remote connections from any host

Both the VPS and this Dedicated Server are behaving the same so makes make think there is a setting wrong in Plesk or something as they are different hardware and providers only similar is Plesk 12.

 

[UFHH01]

No, Fantasyprints,

I tried as well to copy a test - database to your server ( because I saw that you used the test - credentials ), which didn't work. I still think that a verbose log will help to identify your issue... but due to the case that you only would like to copy a database.... don't you think that it might be faster to dump the database and restore it over the command line?

Server where your database is located, which you would like to copy:

Go to a folder, where you might use a FTP program from... as for example "/var/www/vhosts/example.com/httpdocs/SQL_BackUps"
mkdir /var/www/vhosts/example.com/httpdocs/SQL_BackUps
chown SYSTEMUSER_FOR_EXAMPLE.COMsacln /var/www/vhosts/example.com/httpdocs/SQL_BackUps
cd /var/www/vhosts/example.com/httpdocs/SQL_BackUps
mysqldump -u admin -p`cat /etc/psa/.psa.shadow ` YOUR_DATABASE_NAME > YOUR_DATABASE_NAME.sql
Your file is now located at "/var/www/vhosts/example.com/httpdocs/SQL_BackUps/YOUR_DATABASE_NAME.sql"​

Upload the file to the server where the SQL should be restored:

mkdir /var/www/vhosts/other_example.com/httpdocs/SQL_BackUps
chown SYSTEMUSER_FOR_OTHER_EXAMPLE.COMsacln /var/www/vhosts/example.com/httpdocs/SQL_BackUps
( Upload the file with your FTP - program )
cd /var/www/vhosts/other_example.com/httpdocs/SQL_BackUps
mysqldump -u admin -p`cat /etc/psa/.psa.shadow ` YOUR_OTHER_DATABASE_NAME < YOUR_DATABASE_NAME.sql​

You don't really seem to be the kind of person ( please don't get this wrong... it is just a speculation ), who likes to investigate issues, so I think the dump and restore - procedure is more the way you should go.

 

[Fantasyprints]

Thanks for reply. Not use to Plesk but going to look at log as I just don't like things that don't work but as the VPS did the same I am thinking its more the default settings in Plesk as it strange how two completely different systems are working the exact same. Version 11 of Plesk didn't have any problems doing this.

 

[Fantasyprints]

I have found port 3306 MySQL closed

I have tried
iptables -A INPUT -p tcp --dport 3306 -j ACCEPT

But doesn't seem to work. Any ideas?

 

[Fantasyprints]

Been researching the FIREWALL in Plesk. Setting had not been set/run. I run the setting but it still says 3306 is open but Open Port test says it's not. Found below

Found this /etc/mysql/my.cnf

[mysqld]
bind-address=127.0.0.1
local-infile=0
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

Should the bind-address be the ip address of server?

 

[UFHH01]

Hi Fantasyprints,

please be aware, that EACH server might have it's very own configuration and version, depending to the supported versions to work with Plesk Plesk isn't shipped with a MySQL, you install it normally by yourself, or over your vendor, or your vendor's installation templates. Please name your operating system, the mysql - version and other relevant versions, when asking questions with are not relevant to Plesk. MySQL ( standard ) installation vary as well for CentOS or Ubuntu for example and each operating system may use unique sources.

The "bind option" for example in your configuration is outdated for standard MySQL 5.5 - versions. You could see standard settings in the doc - files at:

/usr/share/doc/mysql-server-5.x/examples/*​

You could find there the example "my_medium.cnf", which looks like this:

[client]
#password    = your_password
port        = 3306
socket        = /var/run/mysqld/mysqld.sock

# Here follows entries for some specific programs

# The MySQL server
[mysqld]
port        = 3306
socket        = /var/run/mysqld/mysqld.sock
skip-external-locking
key_buffer_size = 16M
max_allowed_packet = 1M
table_open_cache = 64
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M

# Don't listen on a TCP/IP port at all. This can be a security enhancement,
# if all processes that need to connect to mysqld run on the same host.
# All interaction with mysqld must be made via Unix sockets or named pipes.
# Note that using this option without enabling named pipes on Windows
# (via the "enable-named-pipe" option) will render mysqld useless!
#
#skip-networking

Another option example is commented like this:

#
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
# bind-address        = 127.0.0.1
#
# * Fine Tuning
#

Please make your very own settings, depending to your needs and security risks, I don't recommend non-standard settings, unless you are aware of the risks and issues it might cause. Plesk itself will not change your very own settings.

 

[Fantasyprints]

CentOS 6.7 (Final)
MySQL 5.5.44

This system comes from Heart Internet both the VPS and Dedicated Server so it sounds like the templates could be the issue. As I think they must install same setup to VPS and Dedicated Server - this would explain why both are doing the same.

 

[paul133]

I had a similar problem.

Issue was that both domains are on the same server BUT on different IP addresses.

worked in end:
both databases were allow connections from all.
click copy
select other for destination, enter IP of domain to copy to
enter user/password etc