PowerDNS (with SolusVM ) mysql

Some examples for solusvm mysql db

Solusvm DB name looks like some ID.

mysql

show databases;
use <solusvmDBname_likeID> ;

show tables;

show all servers with IP like specified:

 select vserverid, mainipaddress, hostname from vservers  where mainipaddress like '185.31%'; 

show all servers with ip like specified and not disabled:

select vserverid, mainipaddress, hostname, cachestatus, disabled  from vservers  where mainipaddress like '%185.31%' and disabled='0';

change some record example:

UPDATE vservers SET mainipaddress = '10.10.10.143' WHERE ctid=5342;

select all servers with ip like specified and join with clients table:

SELECT clients.emailaddress, clients.clientid, vservers.clientid, vservers.vserverid, vservers.mainipaddress, vservers.hostname, 
vservers.disabled 
FROM vservers
JOIN clients ON clients.clientid=vservers.clientid
WHERE vservers.mainipaddress like '%185.31%' and disabled='0' ;

_____ how to set PTR within mysql example:

First you need to know the reverse zone id, like:

select * from domains where id=79;
mysql> select * from domains where id=78;
+----+------------------------+--------+------------+--------+-----------------+---------+-------------+
| id | name                   | master | last_check | type   | notified_serial | account | solusvm_cid |
+----+------------------------+--------+------------+--------+-----------------+---------+-------------+
| 78 | 64.239.91.in-addr.arpa | NULL   |       NULL | NATIVE |            NULL | NULL    |        NULL |
+----+------------------------+--------+------------+--------+-----------------+---------+-------------+
1 row in set (0.00 sec)

mysql> select * from domains where id=79;
+----+------------------------+--------+------------+--------+-----------------+---------+-------------+
| id | name                   | master | last_check | type   | notified_serial | account | solusvm_cid |
+----+------------------------+--------+------------+--------+-----------------+---------+-------------+
| 79 | 65.239.91.in-addr.arpa | NULL   |       NULL | NATIVE |            NULL | NULL    |        NULL |
+----+------------------------+--------+------------+--------+-----------------+---------+-------------+ 

Or select by the zone name.

we need to add reverse for the 91.239.65.252 so our ID is 79. Also you just need to know the last id in records table to add new one. Find last record:

SELECT * FROM records ORDER BY id DESC LIMIT 1;

Add the new one:

INSERT INTO records VALUES (12839,79,'252.65.239.91.in-addr.arpa','PTR','cp1.vpsget.com',300,0,0);

You may to view other records, just for example:

select * from domains where name='vpsget.com';
select * from records where type='PTR';
select * from records where domain_id=5;