Is it possible to bulk change NS-records from SSH?

[HHawk]

Hi all,

I have quick question. Because one of our older server (Plesk 12) had problems, we are migrating all those accounts to different Plesk servers (Plesk 12.5). Though Plesk Migrator works generally well, we have one (actually two) issue.

Contents, databases, emails, etc. are all correctly transfered. Most DNS records are also correct, except for the two NS records. They are still showing the old records (from the old server). So instead of showing:

domain.com NS ns1.newnameserver.nl
domain.com NS ns2.newnameserver.nl

It's showing the old ones like this:

domain.com NS ns1.oldnameserver.nl
domain.com NS ns2.oldnameserver.nl

Is there a quick way to fix this from SSH? Instead of doing all accounts by hand?

Also there are two other DNS entries which are also incorrect. These entries are both A-records and are pointing to an incorrect IP-address, like this:

*.webmail.domain.com A <wrong.ip>
webmail.domain.com A <wrong.ip>

Is there a way to bulk change this also (into a correct IP).

Thank you in advance for your help and input!

Regards

 

[HHawk]

I think this is possible by replacing the old nameserver and IP with the new values through phpMyAdmin, but I have no clue on how to execute a SQL (sorry kind of a noob here).

I want to replace these values:

oldnameserver.nl > newnameserver.nl
old.ip.number > new.ip.number

If someone could give me a SQL job for this, I think it will work...

Thanks in advance.

Regards

 

[PriyanA]

Why using SSH for this?

I think you can do this via Panel DNS Manager tool

 

[HHawk]

What "Panel DNS Manager Tool" ?

You mean 'General Settings' > 'DNS Template', right? Well that doesn't work. Plesk considers the records I mentioned as user created and therefor won't change if I apply (any of the two options). I tested this already. So it's pretty much useless.
If you mean changing them under 'Domains' > 'Domainname' > 'DNS Settings' well that's too much work. I would have to edit 3 or 4 records and save/apply them. For 1200 accounts/domains thats way to much work.

So back to the SSH solution, anyone??

 

[Alex@Plesk]

I think it should be something like this:

1. Create psa database backup according to article https://kb.plesk.com/881

2. Change NS records in two queries for each server to exclude zero entries:

mysql> update dns_recs set val='ns1.new_ns_server.' where displayVal='ns1.old_server.' and type='NS';
mysql> update dns_recs set displayVal='ns1.new_ns_server.' where displayVal='ns1.old_server.' and type='NS';
mysql> update dns_recs set val='ns2.new_ns_server.' where displayVal='ns2.old_server.' and type='NS';
mysql> update dns_recs set displayVal='ns2.new_ns_server.' where displayVal='ns2.old_server.' and type='NS';

3. For webmail:

mysql> update dns_recs set displayVal='new_ip_address' where displayHost like 'webmail%' and val='old_ip_address' and type='A';
mysql> update dns_recs set val='new_ip_address' where displayHost like 'webmail%' and val='old_ip_address' and type='A';

mysql> update dns_recs set displayVal='new_ip_address' where displayHost like '*.webmail%' and val='old_ip_address' and type='A';
mysql> update dns_recs set val='new_ip_address' where displayHost like '*.webmail%' and val='old_ip_address' and type='A';

4. Update DNS settings for all domains:

# mysql -uadmin -p`cat /etc/psa/.psa.shadow ` psa -Ne'select name from domains' > /root/domains.list
# cat /root/domains.list | while read dom; do /usr/local/psa/admin/bin/dnsmng --update $dom && echo "$dom is OK" ;done

Note: This was not tested. I suggest to test this on one domain first.

 

[HHawk]

Hi Alex!

First off; thanks for repying.

When I run the query (after changing it to the desired values obviously) I get this as a result:

Query OK, 0 rows affected (0.00 sec)
Rows matched: 0 Changed: 0 Warnings: 0

Doesn't matter what I do, it seems it cannot find the items to replace?
Maybe I am doing something wrong here?

I did the following to enter the MySQL:
mysql -u admin -p`cat /etc/psa/.psa.shadow` psa

Or is that wrong?

Regards

//edit

Made some progress. I am able to change the NS records. I was forgetting to add a dot (.) behind the NS records (yeah, stupid). But I cannot change the webmail A-records. No matter what I try. It seems to ignore it.

 

[HHawk]

Okay, I managed to make the changes to the NS records and necessary (webmail) records, by using:


mysql> update dns_recs set displayVal='ns1.newserver.nl.' and val='ns1.newserver.nl.' where displayVal='ns1.oldserver.nl.' and type='NS';

Result:
Query OK, 32 rows affected, 32 warnings (0.07 sec)
Rows matched: 32 Changed: 32 Warnings: 32

Same for ns2.

Next I did similar to the A-records for webmail:

mysql> update dns_recs set displayVal='<new.ip.number>' and val='<new.ip.number>' where host like 'webmail%' and type='A';

Result:
Query OK, 38 rows affected, 38 warnings (0.07 sec)
Rows matched: 38 Changed: 38 Warnings: 38

Then I did:

mysql> update dns_recs set displayVal='<new.ip.number>' and val='<new.ip.number>' where host like '*.webmail%' and type='A';
Query OK, 7 rows affected, 7 warnings (0.03 sec)
Rows matched: 7 Changed: 7 Warnings: 7

Followed by:
mysql -uadmin -p`cat /etc/psa/.psa.shadow ` psa -Ne'select name from domains' > /root/domains.list
cat /root/domains.list | while read dom; do /usr/local/psa/admin/bin/dnsmng --update $dom && echo "$dom is OK" ;done

Everything was "OK", so to make sure I restarted the server.

But now the edited records are being shown as '0'.

 

[Alex@Plesk]

I've updated queries. To exclude zero values, it is better to do in two queries.

 

[PriyanA]

Hi,

Sorry! I missed the part which Plesk's 'DNS Management Tools' Ignored DNS Records which user created!.

Just an idea,

would it be possible to empty all DNS records for domains and apply the new template?

 

[Rens]

Hey all,

I tried this today but when i run the second query nothing is happening. It doesn't change any rows

mysql> update dns_recs set val='ns1.new_ns_server.' where displayVal='ns1.old_server.' and type='NS';

It has been changed in plesk, but i don't think its working yet.

How to fix it?

 

[IgorG]

It has been changed in plesk, but i don't think its working yet.

Have you started

# /usr/local/psa/admin/bin/dnsmng --update

after that?

 

[Ricardo_altertable]

Hello!

This has always worked for me, for Plesk migrator migrations that leave the same NS records as in origin, create a bash script with this content and excute it:

for domain in $(MYSQL_PWD=`cat /etc/psa/.psa.shadow` mysql -u admin psa -Ns -e"select name from dns_zone where name not in (select val from misc where param = 'FullHostName')") ; do


    /usr/local/psa/bin/dns --del $domain -ns '' -nameserver 'old_DNS_RECORD-1';
    /usr/local/psa/bin/dns --add $domain -ns '' -nameserver 'New_DNS_RECORD-1';
    /usr/local/psa/bin/dns --del $domain -ns '' -nameserver 'old_DNS_RECORD-2';
    /usr/local/psa/bin/dns --add $domain -ns '' -nameserver 'New_DNS_RECORD-2';


done

When old_DNS_RECORD-* are the old DNS that you need replace to New_DNS_RECORD-*