PowerDNS (with SolusVM ) mysql

From vpsget wiki
Revision as of 13:20, 22 November 2017 by Vq (talk | contribs)
(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)
Jump to: navigation, search

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 the 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);

Show record by name:

SELECT * FROM records where name='252.65.239.91.in-addr.arpa';
+------+-----------+----------------------------+------+----------------+------+------+-------------+
| id   | domain_id | name                       | type | content        | ttl  | prio | change_date |
+------+-----------+----------------------------+------+----------------+------+------+-------------+
| 7882 |        79 | 252.65.239.91.in-addr.arpa | PTR  | cp1.vpsget.com |  300 |    0 |           0 |
+------+-----------+----------------------------+------+----------------+------+------+-------------+

Edit the PTR record with known ID:

UPDATE records SET content='cpm.vpsget.com' WHERE id=7882;

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;


Adding bandwidth to VPS

Show current bandwidth of the VPS with known IP address:

SELECT ctid,bandwidth,bandwidthused FROM vservers where mainipaddress='192.168.1.5';
+-------+---------------+---------------+
| ctid  | bandwidth     | bandwidthused |
+-------+---------------+---------------+
| 15636 | 1099511627776 | 1099511627776 |
+-------+---------------+---------------+

Update bandwidthused with added 100GB of traffic (in bytes):

 UPDATE vservers SET bandwidthused='992137445376' WHERE ctid=15636;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0